We help IT Professionals succeed at work.

str2tbl

ram_0218
ram_0218 asked
on
i've a function

str2tbl(input_list varchar2) {
 pipe row..
}

i've a procedure

proc(input_list) {
  insert into temp_table select * from table(str2tbl(input_list));
  select * from maintable where id in (select id from temp_table)
}

issue here is that, if the application passes the string list more than 4000 chars long then the call to str2tbl fails with ORA-01461 can bind a LONG value only for insert into a LONG column..

my question is, even the proc(input_list varchar2) .. this procedure accepting varchar2 and it can accept more than 4000 chars and bind it.. now when the str2tbl function is called passing same value it throws out this error.. any help how to avoid?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can't pass LONG columns to functions  it has nothing to do with str2tbl.  It's a limit of LONG data type
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you convert your column to CLOB type  you can use
an extended version of str2tbl I wrote called CLOB2TBL


http://www.experts-exchange.com/Database/Oracle/Q_27062317.html

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Inside pl/sql itself a varchar2 can be 32K.  Outside pl/sql it has a 4000 character limit.

If the application is not written in PL/SQL, you have a 4000 character limit.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
even inside pl/sql,  if you try use the function within the context of sql then you're subject to the 4000 character limit

insert into temp_table select * from table(str2tbl(input_list));
  select * from maintable where id in (select id from temp_table)

Author

Commented:
>>you can't pass LONG columns to functions  it has nothing to do with str2tbl.  It's a limit of LONG data type

there's no long column in the db or in any tables..

>>if you convert your column to CLOB type  you can use
an extended version of str2tbl I wrote called CLOB2TBL

so should i just replace str2tbl with clob2tbl? passing parameter will remain same? i'm afraid i'm getting:
ORA-00932: inconsistent datatypes: expected - got CLOB

>>Inside pl/sql itself a varchar2 can be 32K.  Outside pl/sql it has a 4000 character limit.
if that's the case, my java program passing 10K length string to procedure, it gets executed just fine.. if i comment out the line where i insert to tmptable using str2tbl all is well.. why is that?

>>even inside pl/sql,  if you try use the function within the context of sql then you're subject to the 4000 character limit
how to aviod this?



CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> my java program passing 10K length string to procedure

passing a value to a procedure is pl/sql,  thus varchar2 limit of 32767,  10K falls within that limit.

"insert to tmptable" - this is sql - thus varchar2 limit of 4000, 10K fails as too big for that limit

how to avoid it?

Don't use str2tbl function.  Rewrite it as a procedure that populates a collection OUT variable.
Or, rewrite it as a procedure that populates your temp table instead of doing PIPE ROW

Author

Commented:
correct me if i'm wrong, so you're saying

if i change str2tbl function to a procedure and do the insert in that procedure..

i can still call that procedure from SQL and pass 10K chars?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
no,  you can't call procedures from sql.

what I'm saying is str2tbl can do the insert for you.

instead of populating a nested table collection,  it could simply populate your temp table.  Skip the collection step altogether

Author

Commented:
replaced the function to procedure, it worked..