Solved

pl/sql type creation problem

Posted on 2009-05-04
6
1,067 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 73

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 73

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

18 Experts available now in Live!

Get 1:1 Help Now