Mega Code Archive
Use dbms_utility name_tokenize to split a name
<![CDATA[
SQL> set echo on
SQL>
SQL> 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 := vcArray( 'owner.pkg.proc@database_link',
10 'owner.tbl@database_link',
11 'tbl',
12 '"Owner".tbl',
13 'pkg.proc',
14 'owner.pkg.proc',
15 'proc',
16 'owner.pkg.proc@dblink with junk',
17 '123' );
18 begin
19 for i in 1 .. l_names.count
20 loop
21 begin
22 dbms_utility.name_tokenize(name => l_names(i),
23 a => l_a,
24 b => l_b,
25 c => l_c,
26 dblink => l_dblink,
27 nextpos=> l_next );
28
29 dbms_output.put_line( 'name ' || l_names(i) );
30 dbms_output.put_line( 'A ' || l_a );
31 dbms_output.put_line( 'B ' || l_b );
32 dbms_output.put_line( 'C ' || l_c );
33 dbms_output.put_line( 'dblink ' || l_dblink );
34 dbms_output.put_line( 'next ' || l_next || ' ' || length(l_names(i)));
35 exception
36 when others then
37 dbms_output.put_line( 'name ' || l_names(i) );
38 dbms_output.put_line( sqlerrm );
39 end;
40 end loop;
41 end;
42 /
name owner.pkg.proc@database_link
A OWNER
B PKG
C PROC
dblink DATABASE_LINK
next 28 28
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
name "Owner".tbl
A Owner
B TBL
C
dblink
next 11 11
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
name 123
ORA-00931: missing identifier
PL/SQL procedure successfully completed.
SQL>
]]>