Home » SQL & PL/SQL » SQL & PL/SQL » dbms_utility.table_to_comma output
dbms_utility.table_to_comma output [message #306522] Fri, 14 March 2008 09:56 Go to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Hi
Am working on an Oracle database 9i Enterprise Edition Release 9.2.0.6.0 running on Red Hat Enterprise Linux “Advanced Server” (RHEL-AS) version 4.0.

Within a PL/SQL That I was building for a webMethods 6.5 integration, I was using the dbms_utility.table_to_comma procedure to generate a comma-seperated list of values to be used as a return variable.

For debugging purposes, I was inserting the output in a table with a column length of varchar2 4000

But during the initial unit testing using PL/SQL Developer, I ran into a strange problem.

Whenever the Pl/SQL table (which I was getting converted into comma-seperated string) had smaller number of rows, the output string was readable as a watched variable and the insert was working.

However when the PL/SQL table had a relatively higher number of rows (let's say around 1900), the output string would simply appear as "[Long Value]" in the watch and the insert would run into an error:

ORA-01461 can bind a LONG value only for insert into a LONG column

I looked up the documentation which maintains that this procedure returns a varchar2 as output.
Even the API description present in the database dictionary lists both the overloaded versions of the table_to_comma procedure returning varchar2.

Question I had, has anyone come across this problem of the dbms_utility.table_to_comma internally casting the output to LONG instead of Varchar2?
If yes, then how did you deal with it?

Thanks in advance
Re: dbms_utility.table_to_comma output [message #306530 is a reply to message #306522] Fri, 14 March 2008 10:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.
http://www.orafaq.com/forum/m/303012/94420/

Regards

Raj
Re: dbms_utility.table_to_comma output [message #306532 is a reply to message #306530] Fri, 14 March 2008 10:30 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Raj
I looked up the thread that you've posted in your reply.
That particular thread's problem is pretty different.
It involves 1 column which is already populated with a delimited string and the guy needs to break it down across multiple columns.

My problem is the other way round.
It involves converting a PL/SQL Table to a comma-seperated string using dbms_utility.table_to_comma; which, by the way, works fine if the PL/SQL table doesn't have a whole lot of rows in it.

When I have the PL/SQL table populated with over 1900 rows, the dbms_utility.table_to_comma undergoes an internal transformation which results in the output datatype getting changed to LONG instead of VARCHAR2, causing problems in the subsequent peices of code.

Thanks for your response,
Re: dbms_utility.table_to_comma output [message #306536 is a reply to message #306532] Fri, 14 March 2008 10:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies. I didn't read it properly. I hope you know that there is a limitation in pl/sql for varchar2 with maximum length of 32767. My guess will be it is because of this.

Regards

Raj

[Updated on: Fri, 14 March 2008 10:46]

Report message to a moderator

Re: dbms_utility.table_to_comma output [message #306543 is a reply to message #306536] Fri, 14 March 2008 11:15 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Raj
Am aware of the 32kb limitation of Varchar2 in Pl/SQL.
Ironically, that's exactly the size that I had defined for my variable being used for the dbms_utility.table_to_comma output.

What astounds me is that the internal conversion to LONG did not throw up any VALUE_ERROR exception or any other kind of error.

Here's a snippet of the code that might help in further understanding what am trying to convey.

DECLARE
lt_party_id_string dbms_utility.uncl_array;
--
lv_party_id_list VARCHAR2 (32767);
ln_tab_length BINARY_INTEGER;
--
TYPE t_party IS TABLE OF APPS.HZ_PARTIES.party_id%TYPE INDEX BY PLS_INTEGER;
lt_party t_party;
--
BEGIN
--
-- an custom API Call which returns a ref cursor
--
xxecm_data_ws_pkg.get_party_id (. <input parameters>
,.
,x_party_id_cur => lrc_party
) ;
--
-- collect cursor output into Pl/SQL Table
--
FETCH lrc_party BULK COLLECT INTO lt_party;
CLOSE lrc_party;
--
-- assign each value in PL/SQL Table to another for conversion
--
FOR i IN lt_party.FIRST..lt_party.LAST
LOOP
lt_party_id_string(i) := lt_party(i);
END LOOP;
--
-- convert to list to string
--
dbms_utility.table_to_comma (lt_party_id_string, ln_tab_length, lv_party_id_list);
--
<this is where I do the INSERT..INTO to table having a column width of 4000>
END;

Hope this explains where exactly am running into my problem.
Re: dbms_utility.table_to_comma output [message #306547 is a reply to message #306543] Fri, 14 March 2008 11:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tried inserting the data into table. I suspect it could be a limitation of pl/sql developer displaying it as Long but technically it will be a varchar2.
Re: dbms_utility.table_to_comma output [message #306552 is a reply to message #306547] Fri, 14 March 2008 11:57 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Raj
I was indeed doing an Insert into a table for debugging purposes in the code <listed at the bottom of the sample code snippet and mentioned in the original post as well>.

That's when the following error popped up:

ORA-01461 can bind a LONG value only for insert into a LONG column.

which grabbed my attention to this problem.

Re: dbms_utility.table_to_comma output [message #306559 is a reply to message #306552] Fri, 14 March 2008 12:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> create table test_long_string (long_string varchar2(4000));

Table created.

SQL> create table debug_test_long_string (debug_string varchar2(4000));

Table created.

insert into test_long_string (long_string)
select rpad('100 chars',100,' ') from dual connect by level <= 100
/

SQL> select length(long_string), count(*) from test_long_string
  2  group by length(long_string);

LENGTH(LONG_STRING)   COUNT(*)
------------------- ----------
                100        100

  1  declare
  2  l_string_id dbms_utility.uncl_array;
  3  l_string_list varchar2(32767); -- Check this line
  4  ln_tab_length binary_integer;
  5  begin
  6  select long_string bulk collect into l_string_id from test_long_string;
  7  ln_tab_length := l_string_id.count;
  8  dbms_utility.table_to_comma(l_string_id, ln_tab_length, l_string_list);
  9  insert into debug_test_long_string values (l_string_list);
 10  commit;
 11* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 9


  1  declare
  2  l_string_id dbms_utility.uncl_array;
  3  l_string_list varchar2(32767); -- Check this line
  4  ln_tab_length binary_integer;
  5  begin
  6  select long_string bulk collect into l_string_id from test_long_string where rownum <= 39;
  7  ln_tab_length := l_string_id.count;
  8  dbms_utility.table_to_comma(l_string_id, ln_tab_length, l_string_list);
  9  dbms_output.put_line(length(l_string_list));
 10  insert into debug_test_long_string values (l_string_list);
 11  commit;
 12* end;
SQL> /
3938

PL/SQL procedure successfully completed.

  1  declare
  2  l_string_id dbms_utility.uncl_array;
  3  l_string_list varchar2(32767);-- Check this line
  4  ln_tab_length binary_integer;
  5  begin
  6  select long_string bulk collect into l_string_id from test_long_string where rownum <= 40;
  7  ln_tab_length := l_string_id.count;
  8  dbms_utility.table_to_comma(l_string_id, ln_tab_length, l_string_list);
  9  dbms_output.put_line(length(l_string_list));
 10  insert into debug_test_long_string values (l_string_list);
 11  commit;
 12* end;
SQL> /
4039
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 10

  1  declare
  2  l_string_id dbms_utility.uncl_array;
  3  l_string_list varchar2(4000);-- Check this line
  4  ln_tab_length binary_integer;
  5  begin
  6  select long_string bulk collect into l_string_id from test_long_string where rownum <= 40;
  7  ln_tab_length := l_string_id.count;
  8  dbms_utility.table_to_comma(l_string_id, ln_tab_length, l_string_list);
  9  dbms_output.put_line(length(l_string_list));
 10  insert into debug_test_long_string values (l_string_list);
 11  commit;
 12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_UTILITY", line 245
ORA-01403: no data found
ORA-06512: at line 8


Hope it explains why you are hitting that error.

Regards

Raj
icon14.gif  Re: dbms_utility.table_to_comma output [message #306579 is a reply to message #306559] Fri, 14 March 2008 14:41 Go to previous message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Raj
Thanks for all the 4 pieces of code.
I simulated all the 4 cases at my end as well.

So the final conclusion is:
dbms_utility.table_to_comma output does keep returning back varchar2.
But since the PL/SQL's varchar2 stretches up to 32kb, the invocation works fine.

It's the variance in VARCHAR2 limits between the table definition and Pl/SQL Variable raises the ORA-01461 error.

Thanks for your time on this.

Previous Topic: ORA-00936: Missing Expression
Next Topic: comparing data between two databases
Goto Forum:
  


Current Time: Sun Dec 04 04:34:56 CST 2016

Total time taken to generate the page: 0.08985 seconds