Home » SQL & PL/SQL » SQL & PL/SQL » Problem in 'dbms_utility.comma_to_table' (Oracle 10g, Win XP.)
Problem in 'dbms_utility.comma_to_table' [message #532483] Wed, 23 November 2011 01:22 Go to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
declare
a varchar2(100) :=  '10,11,12,13';
c dbms_utility.uncl_array;
l_tablen  NUMBER;
I NUMBER;
begin
dbms_utility.comma_to_table(a,l_tablen,c);
FOR I IN C.FIRST..C.LAST LOOP
    DBMS_OUTPUT.put_line(C(I));
END LOOP;
DBMS_OUTPUT.put_line(l_tablen);
exception when others then
DBMS_OUTPUT.put_line( sqlerrm);
end;

--ORA-00931: missing identifier



If i gave parameter as 'a,b,c,d' then the output will be printed where as if we gave '1,2,3,4' then the mentioned error raised.
I think it does not support Number format. But my requirement is to convert as number .

Is there any way to solve it.

Regards
Stalin Dorai.
Re: Problem in 'dbms_utility.comma_to_table' [message #532484 is a reply to message #532483] Wed, 23 November 2011 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Remove the "when others" part it is a bug
2/ Use SQL*Plus and copy and paste your session, we want to see your error happen
3/ Post your Oracle version with 4 decimals

Regards
Michel
Re: Problem in 'dbms_utility.comma_to_table' [message #532494 is a reply to message #532483] Wed, 23 November 2011 02:36 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
You need to enclose the numbers in double quotes to consider them as varchar2.
DECLARE
a varchar2(100) :=  '"1","3","5","7"';
c dbms_utility.uncl_array;
l_tablen  NUMBER;
I NUMBER;
BEGIN
a:=TO_CHAR(a);
dbms_utility.comma_to_table(a,l_tablen,c);
FOR I IN C.FIRST..C.LAST LOOP
    DBMS_OUTPUT.put_line(rtrim(ltrim(C(I),'"'),'"'));
END LOOP;
DBMS_OUTPUT.put_line('Count is: '||l_tablen);
end;

Result:
1
3
5
7
Count is: 4

PL/SQL procedure successfully completed.

You can find more details in this page: psoug.org/reference/dbms_utility.html
Regards,
Lakshmi.
Re: Problem in 'dbms_utility.comma_to_table' [message #532497 is a reply to message #532494] Wed, 23 November 2011 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
You need to enclose the numbers in double quotes to consider them as varchar2

This is wrong you can take anything to prefix the values:
SQL> declare
  2  a varchar2(100) :=  'a10,a11,a12,a13';
  3  c dbms_utility.lname_array;
  4  l_tablen  NUMBER;
  5  I NUMBER;
  6  begin
  7  dbms_utility.comma_to_table(a,l_tablen,c);
  8  FOR I IN C.FIRST..C.LAST LOOP
  9      DBMS_OUTPUT.put_line(C(I)||' -> '||substr(C(I),2));
 10  END LOOP;
 11  DBMS_OUTPUT.put_line(l_tablen);
 12  end;
 13  /
a10 -> 10
a11 -> 11
a12 -> 12
a13 -> 13
 ->
4

This seems to be a bug (or a limit), search on Metalink.

Regards
Michel
Re: Problem in 'dbms_utility.comma_to_table' [message #532499 is a reply to message #532497] Wed, 23 November 2011 02:52 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Michel,
Yes. This is one of the limitations of DBMS_UTILITY Package.
In order to make this work "with only numbers", we need to enclose them in double quotes.
More details are found in asktom site:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1415803954123

Regards,
Lakshmi.
Re: Problem in 'dbms_utility.comma_to_table' [message #532506 is a reply to message #532499] Wed, 23 November 2011 03:22 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Yes giving in double quotes the error not raised.
anyway thanks a lot.

Re: Problem in 'dbms_utility.comma_to_table' [message #532507 is a reply to message #532499] Wed, 23 November 2011 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You do not NEED to, you may as you may use anything else than " as I showed it.

Regards
Michel
Re: Problem in 'dbms_utility.comma_to_table' [message #532521 is a reply to message #532507] Wed, 23 November 2011 03:48 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,

Yes we can use anything other than double quotes.
I got it.

Regards,
Lakshmi.

[Updated on: Wed, 23 November 2011 05:06]

Report message to a moderator

Re: Problem in 'dbms_utility.comma_to_table' [message #532549 is a reply to message #532521] Wed, 23 November 2011 07:23 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not exactly. dbms_utility.comma_to_table works with Oracle names only and therefore Oracle name rules apply. Thefore, in general, you can't use "anything other than double quotes". You can't use any character Oracle name can't start with. Anything other than double quotes also will not work if name contains characters which are not allowed in Oracle names (e.g. space, percent sign, question mark, etc.) while double quotes will take care of such characters. But even double quotes will not help you if item in your csv list is 31 bytes or longer since Oracle names can't exceed 30 bytes. But again, using "anything other than double quotes" will constraint you even more - it will fail if item in your csv list is 30 bytes or longer

SY.

[Updated on: Wed, 23 November 2011 07:30]

Report message to a moderator

Previous Topic: Not updating all records in 1st commit
Next Topic: spfile or pfile
Goto Forum:
  


Current Time: Thu Sep 04 23:39:53 CDT 2025