Solved

pl/sql type creation problem

Posted on 2009-05-04
6
1,102 Views
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:

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


0
Comment
Question by:ranadhir
6 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 24302276
Hi,

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

Expert Comment

by:sdstuber
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
0
 

Author Comment

by:ranadhir
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
        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?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 74

Expert Comment

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

Author Comment

by:ranadhir
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
0
 
LVL 11

Accepted Solution

by:
Andytw earned 50 total points
ID: 24338799
ranadhir,

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
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

'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 …
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

791 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