Link to home
Create AccountLog in
Avatar of ranadhir
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


Avatar of MarkusId
MarkusId
Flag of Austria image

Hi,

could you please give a bit more of the code? When do you invoke
the first definition and when the second?
Avatar of Sean Stuber
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
Avatar of 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?
please post all of the relevant pieces of the package, or the whole thing if not too big.
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
Avatar of Andytw
Andytw
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer