Problem in 'dbms_utility.comma_to_table' [message #532483] |
Wed, 23 November 2011 01:22  |
 |
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 #532494 is a reply to message #532483] |
Wed, 23 November 2011 02:36   |
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   |
 |
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 #532549 is a reply to message #532521] |
Wed, 23 November 2011 07:23  |
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
|
|
|