pl/sql type creation problem

Posted on 2009-05-04
Last Modified: 2013-12-18
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:

-------- -----------------------------------------------------------------
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

Question by:ranadhir

Expert Comment

ID: 24302276

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

Expert Comment

ID: 24303498
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

Author Comment

ID: 24311348
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
        l_str       long default p_str || p_delim;
        l_n         number;
        l_data     p_varchar2_256_list_type;
        l_data :=p_varchar2_256_list_type(); ------  compilation error at this line
    .....        l_data.extend(); --- compilation erro at this line

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:

-------- -----------------------------------------------------------------
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?
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

LVL 74

Expert Comment

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

Author Comment

ID: 24333030
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
LVL 11

Accepted Solution

Andytw earned 50 total points
ID: 24338799

The compilation errors that you see are due to the differences between the "Nested table" and "Associative array" (Index by table) PL/SQL collection types.    

p_varchar2_256_list_type -> associative array
p_varchar2_256_table       -> nested table

When you use p_varchar2_256_list_type, your function doesn't compile due to the following:

>>  l_data :=p_varchar2_256_list_type(); ------  compilation error at this line
The above initialisation is necessary for nested tables.  However, associative arrays don't need to be initialised (you can start using them in your code straight away).  As such there no constructor function for associative arrays, hence the error.

>>l_data.extend(); --- compilation erro at this line
You cannot use EXTEND on associative array - the EXTEND method is only needed to increase the size of Nested table or varray collections.

So, both of these statements are not needed for the p_varchar2_256_list_type case.  If you delete both statements your example should then work.

Hope this has helped clear things up

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question