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 73

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?
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert char to decimal in a SQL Server View 14 41
MySQL left join performance 4 30
Strange msg in the SSMS pane 13 47
poor performance from  MySQL stored procedure 6 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now