[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

pl/sql type creation problem

Posted on 2009-05-04
6
Medium Priority
?
1,218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

649 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