How to pass String Parameter (Character) have Size more than 14000 in Length in Procedure in Oracle Database

Question

0.00/5 (No votes)

See more:

Hi,
I am facing a problem of passing parameter of Branch Code's in PL/SQL as a parameter,The soze of String in around 14000+ character in length.
I know Varchar2 have max limit of 4000 length.I was trying with CLOB but was helpless.
Can any one tell how can i pass those comma separated Branch Codes in my parameter

Any help suggestion would be appriciated

Posted 26-Oct-15 3:42am

Ravindranath_Kanojiya

Updated 26-Oct-15 4:39am

v3

Add a Solution

Comments

Jörgen Andersson 28-Oct-15 11:30am

Don't use a comma separated string, pass your codes as an array.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2015-10-26T03:57:00

Solution 1

Sounds like you might need to start compressing your data.

There are a few way to do this but the 'correct' way (imho) is to use the built in oracle data compression.

Take a look here for more:
http://www.oracle.com/technetwork/database/options/compression/overview/index.html[^]

Posted 26-Oct-15 3:57am

Andy Lanng

Comments

Ravindranath_Kanojiya 26-Oct-15 10:37am

Thanks Andy but i dont want to do data Compression, but want to Execute my Procedure and want to pass those Branch Code comma separated (which is 14000 length) in my Query

Andy Lanng 26-Oct-15 10:46am

Oic. Hmm, more of a problem. I'm better with tsql, but I may be able to help.

Please update the question and let me know:
1: What version of oracle db are you using
2: how are you passing the parameter (what language, connection api etc)
3: how are you using the parameter (i.e. post the stored proc, or at least a pseudo code version)

Thanks ^_^
Andy

Ravindranath_Kanojiya 3-Nov-15 4:29am

I am using Oracle 11g, Coding in asp.net C#,
getting All Branch Code comma separated in a String variable, and i am passing that to my PL/SQL procedure;

Even when i am try to execute in Query analyzer Window (Qry execution Window) its not taking the csv value

Andy Lanng 3-Nov-15 5:30am

Ah - Maybe its the odbc driver your using in C#. 11g can take in large char lengths (as you have tried) but the driver might be older.

I have no idea why it doesn't work in the QA window. That restriction may still be in place for other reasons.

I had the idea that you could create a temporary table and populate it with < 4000 chars of your csv, run the sp using that table then drop the table after. It's a bit more effort but it would get around the issue