ranadhir
asked on
pl/sql type creation problem
I am trying to define p_varchar2_256_table as a table of varchar2.
When i define p_varchar2_256_table as
type p_varchar2_256_table IS TABLE OF VARCHAR2(256) - everything compiles fine
Thereafter , i try to define p_varchar2_256_table as
type p_varchar2_256_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER
I get the following error:
SQL> show errors
Errors for TYPE P_VARCHAR2_256_TABLE:
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
0/0 PL/SQL: Compilation unit analysis terminated
1/30 PLS-00355: use of pl/sql table not allowed in this context
Any views on why this is so will be appreciated
When i define p_varchar2_256_table as
type p_varchar2_256_table IS TABLE OF VARCHAR2(256) - everything compiles fine
Thereafter , i try to define p_varchar2_256_table as
type p_varchar2_256_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER
I get the following error:
SQL> show errors
Errors for TYPE P_VARCHAR2_256_TABLE:
LINE/COL ERROR
-------- --------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/30 PLS-00355: use of pl/sql table not allowed in this context
Any views on why this is so will be appreciated
are you defining the type as a stand alone object or a type within package?
CREATE OR REPLACE TYPE p_varchar2_256_table AS TABLE OF VARCHAR2(256);
that's a standalone type.
an associative array (i.e. using "index by") can only be constructed with another pl/sql object like a package
CREATE OR REPLACE TYPE p_varchar2_256_table AS TABLE OF VARCHAR2(256);
that's a standalone type.
an associative array (i.e. using "index by") can only be constructed with another pl/sql object like a package
ASKER
Actually the problem is related ,but i may have got the diagnosis wrong
In my package i have the above data-types(p_varchar2_256_ table and p_varchar2_256_list_type[t hsi one with binary index]) defined.
A function string2table in the package is defined as below:
FUNCTION string2table
(p_str VARCHAR2,
p_delim VARCHAR2 default '.')
return p_varchar2_256_list_type
as
l_str long default p_str || p_delim;
l_n number;
l_data p_varchar2_256_list_type;
begin
l_data :=p_varchar2_256_list_type (); ------ compilation error at this line
loop
..... l_data.extend(); --- compilation erro at this line
....
END
When I use the p_varchar2_256_table(inste ad of p_varchar2_256_list_type) in the above excerpt,I get no compilation issues.
However ,if I use p_varchar2_256_list_type data type , i get the following errors:
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
6757/2 PL/SQL: Statement ignored
6757/11 PLS-00222: no function with name 'P_VARCHAR2_256_LIST_TYPE'
exists in this scope
6761/2 PL/SQL: Statement ignored
6761/2 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
6765/2 PL/SQL: Statement ignored
6765/9 PLS-00382: expression is of wrong type
What causes this issues for a binary indexed pl/sql table type?
In my package i have the above data-types(p_varchar2_256_
A function string2table in the package is defined as below:
FUNCTION string2table
(p_str VARCHAR2,
p_delim VARCHAR2 default '.')
return p_varchar2_256_list_type
as
l_str long default p_str || p_delim;
l_n number;
l_data p_varchar2_256_list_type;
begin
l_data :=p_varchar2_256_list_type
loop
..... l_data.extend(); --- compilation erro at this line
....
END
When I use the p_varchar2_256_table(inste
However ,if I use p_varchar2_256_list_type data type , i get the following errors:
LINE/COL ERROR
-------- --------------------------
6757/2 PL/SQL: Statement ignored
6757/11 PLS-00222: no function with name 'P_VARCHAR2_256_LIST_TYPE'
exists in this scope
6761/2 PL/SQL: Statement ignored
6761/2 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
6765/2 PL/SQL: Statement ignored
6765/9 PLS-00382: expression is of wrong type
What causes this issues for a binary indexed pl/sql table type?
please post all of the relevant pieces of the package, or the whole thing if not too big.
ASKER
the package is too big - but the only problem i face is in the compilation of this particular function
Using TABLE OF VARCHAR2(256) ( p_varchar2_256_table) ,instead of TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER(p_varchar2_ 256_list_t ype) solves the problem.
But i am curious as to why using p_varchar2_256_list_type never compiles
Using TABLE OF VARCHAR2(256) ( p_varchar2_256_table) ,instead of TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER(p_varchar2_
But i am curious as to why using p_varchar2_256_list_type never compiles
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
could you please give a bit more of the code? When do you invoke
the first definition and when the second?