You Asked
Hi Tom,
when I use the following code :
------------------------------------------------
CREATE OR REPLACE procedure pr1
as
mytab dbms_utility.uncl_array;
mystring varchar2(200);
len binary_integer;
begin
mystring := 'yes,no,this,that,is';
dbms_utility.COMMA_TO_TABLE(mystring,len,mytab);
for i in 1..len
loop
dbms_output.put_line('The '||i||' name is '||substr(mytab(i),1,25));
end loop;
end;
/
-----------------------------------------------
It gives me the following error
SQL> exec pr1
BEGIN pr1; END;
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 79
ORA-06512: at "SYS.DBMS_UTILITY", line 108
ORA-06512: at "NEELABH.PR1", line 18
ORA-06512: at line 1
while if I replace line 7 which is
mystring := 'yes,no,this,that,is';
BY
mystring := 'yes,no,this,that,are';
it works to give me the following
SQL> exec pr1
The 1 name is yes
The 2 name is no
The 3 name is this
The 4 name is that
The 5 name is are
PL/SQL procedure successfully completed.
Why is the behaviour like this. Are there some limitations in the string values which one can use ??
Regards
Neelabh
and Tom said...
In a nutshell, these procedures do not do what you think. These were built for replication and parsing of snapshot group tablename strings. They parse IDENTIFIERS. In order to understand COMMA_TO_TABLE, you need to understand NAME_TOKENIZE:
NAME_TOKENIZE
This utility routine simply takes a string that represents some object name and breaks it into its component pieces for you. Objects are referenced via:
[schema].[object_name].[procedure|function]@[database link]
NAME_TOKENIZE simply takes a string in that form and breaks it out into the three leading pieces and the last (database link) piece. Additionally, it tells us what byte it stopped parsing the object name at. Here is a small example showing what you can that you might expect back from various object names you pass to it. Note that you do not have to use REAL object names (these tables and procedures do not have to exist) but you must use VALID object identifiers. If you do not use a valid object identifier, NAME_TOKENIZE will raise an error. That makes NAME_TOKENIZE suitable as a method to discover whether a given string of characters will be a valid identifier or not:
scott@TKYTE816> declare
2 l_a varchar2(30);
3 l_b varchar2(30);
4 l_c varchar2(30);
5 l_dblink varchar2(30);
6 l_next number;
7
8 type vcArray is table of varchar2(255);
9 l_names vcArray :=
10 vcArray( 'owner.pkg.proc@database_link',
11 'owner.tbl@database_link',
12 'tbl',
13 '"Owner".tbl',
14 'pkg.proc',
15 'owner.pkg.proc',
16 'proc',
17 'owner.pkg.proc@dblink with junk',
18 '123' );
19 begin
20 for i in 1 .. l_names.count
21 loop
22 begin
23 dbms_utility.name_tokenize(name => l_names(i),
24 a => l_a,
25 b => l_b,
26 c => l_c,
27 dblink => l_dblink,
28 nextpos=> l_next );
29
30 dbms_output.put_line( 'name ' || l_names(i) );
31 dbms_output.put_line( 'A ' || l_a );
32 dbms_output.put_line( 'B ' || l_b );
33 dbms_output.put_line( 'C ' || l_c );
34 dbms_output.put_line( 'dblink ' || l_dblink );
35 dbms_output.put_line( 'next ' || l_next || ' ' ||
36 length(l_names(i)));
37 dbms_output.put_line( '-----------------------' );
38 exception
39 when others then
40 dbms_output.put_line( 'name '||l_names(i) );
41 dbms_output.put_line( sqlerrm );
42 end;
43 end loop;
44 end;
45 /
name owner.pkg.proc@database_link
A OWNER
B PKG
C PROC
dblink DATABASE_LINK
next 28 28
As you can see this breaks out the various bits and pieces of our object name for us. Here the NEXT is set to the length of the string parsing ended when we hit the end of the string in this case. Since we used every possible piece of the object name, all 4 components are filled in. Now for the remaining examples:
name owner.tbl@database_link
A OWNER
B TBL
C
dblink DATABASE_LINK
next 23 23
-----------------------
name tbl
A TBL
B
C
dblink
next 3 3
-----------------------
Notice here how B and C are left NULL. Even though an object identifier is SCHEMA.OBJECT.PROCEDURE, NAME_TOKENIZE makes no attempt to put the TBL into the B out parameter. It simply takes the first part it finds and puts it in A, the next into B and so on. A, B and C do not represent specific pieces of the object name just the first found, next found and so on.
name "Owner".tbl
A Owner
B TBL
C
dblink
next 11 11
-----------------------
Here is something interesting. In the previous examples, NAME_TOKENIZE uppercased everything. That is because identifiers are in upper case unless you use QUOTED identifiers. Here, we used a quoted identifier and NAME_TOKENIZE will preserve that for us and remove the quotes!
name pkg.proc
A PKG
B PROC
C
dblink
next 8 8
-----------------------
name owner.pkg.proc
A OWNER
B PKG
C PROC
dblink
next 14 14
-----------------------
name proc
A PROC
B
C
dblink
next 4 4
-----------------------
name owner.pkg.proc@dblink with junk
A OWNER
B PKG
C PROC
dblink DBLINK
next 22 31
-----------------------
There is an example where the parsing stopped BEFORE we ran out of string. NAME_TOKENIZE is telling us it stopped parsing at byte 22 out of 31. That is the space right before with junk. It simply ignores the remaining pieces of the string for us.
name 123
ORA-00931: missing identifier
PL/SQL procedure successfully completed.
And lastly, this shows if we use an invalid identifier, NAME_TOKENIZE will throw and exception. It checks all tokens for being valid identifiers before returning. That makes it useful as a tool to validate object names if you are building an application that will create objects in the Oracle database. For example, if you are building a data modelling tool and would like to validate that the name the end user wants to use for a table or column name is valid NAME_TOKENIZE will do the work for you)
COMMA_TO_TABLE, TABLE_TO_COMMA
These two utilities either take a comma delimited string of IDENTIFIERS and parse them into a PLSQL table (COMMA_TO_TABLE) or table a PLSQL table of any type of string and make a comma delimited string of them (TABLE_TO_COMMA). I stress the work IDENTIFIERS above because COMMA_TO_TABLE uses NAME_TOKENIZE to parse the strings hence as we saw in that section, we need to use valid Oracle identifiers (or quoted identifiers). This still limits us to 30 characters per element in our comma-delimited string however.
This utility is most useful for applications that want to store a list of tablenames in a single string for example and have them easily converted to an array in PLSQL at runtime. Otherwise, it is of limited use.
Here is an example using this routine and demonstrating how it deals with long identifiers and invalid identifiers:
scott@TKYTE816> declare
2 type vcArray is table of varchar2(4000);
3
4 l_names vcArray := vcArray( 'emp,dept,bonus',
5 'a, b, c',
6 '123, 456, 789',
7 '"123", "456", "789"',
8 '"This is a long string, longer then 32 characters","b",c');
9 l_tablen number;
10 l_tab dbms_utility.uncl_array;
11 begin
12 for i in 1 .. l_names.count
13 loop
14 dbms_output.put_line( chr(10) ||
15 '[' || l_names(i) || ']' );
16 begin
17
18 dbms_utility.comma_to_table( l_names(i),
19 l_tablen, l_tab );
20
21 for j in 1..l_tablen
22 loop
23 dbms_output.put_line( '[' || l_tab(j) || ']' );
24 end loop;
25
26 l_names(i) := null;
27 dbms_utility.table_to_comma( l_tab,
28 l_tablen, l_names(i) );
29 dbms_output.put_line( l_names(i) );
30 exception
31 when others then
32 dbms_output.put_line( sqlerrm );
33 end;
34 end loop;
35 end;
36 /
[emp,dept,bonus]
[emp]
[dept]
[bonus]
emp,dept,bonus
So, that shows that it can take the string emp,dept,bonus and break it into a table and put it back together again.
[a, b, c]
[a]
[ b]
[ c]
a, b, c
This example shows that if you have white space in the list, it will be preserved. You would have to use the rtrim function to remove leading white space if you do not want any.
[123, 456, 789]
ORA-00931: missing identifier
This shows that to use this procedure on a comma delimited string of numbers, we must go one step further as demonstrated below:
["123", "456", "789"]
["123"]
[ "456"]
[ "789"]
"123", "456", "789"
Here is is able to extract the numbers from the string. Note however how it not only retains the leading whitespace but it also retains the quotes. It would be upto you to remove them if you so desire.
["This is a long string, longer then 32 characters","b",c]
ORA-00972: identifier is too long
PL/SQL procedure successfully completed.
And this last example shows that if the identifier is too long (longer then 30 characters) it will raise an error as well these routines are only useful for strings of 30 characters or less. While it is true that TABLE_TO_COMMA will take larger strings then 30 characters, COMMA_TO_TABLE will not be able to undo that work.
Rating
(15 ratings)
Is this answer out of date? If it is, please let us know via a Comment