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

MarkusId

Hi,

could you please give a bit more of the code? When do you invoke
the first definition and when the second?

Sean Stuber

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

ranadhir

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[thsi 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(instead 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?

Sean Stuber

please post all of the relevant pieces of the package, or the whole thing if not too big.

ranadhir

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_type) solves the problem.
But i am curious as to why using p_varchar2_256_list_type never compiles

ASKER CERTIFIED SOLUTION

Andytw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial