Home » SQL & PL/SQL » SQL & PL/SQL » Error using Select .. TABLE() on PL/SQL nested tables (Oracle Database 11g (11.1.0.7))
Error using Select .. TABLE() on PL/SQL nested tables [message #578796] Tue, 05 March 2013 04:59 Go to next message
tabishimam
Messages: 5
Registered: August 2010
Junior Member

Hi,

I am trying to select columns (primarily for sorting the collection)from a PL/SQL nested table in a PL/SQL procedure as:

CREATE OR REPLACE PROCEDURE <Procedure_Name>
is
    TYPE RTG_REC IS RECORD (
    ROUTING_ID T_ROUTING_OPERATION.RTG_ROUTING_ID%TYPE,
    OPERATION_ID T_OPERATION_MASTER.OPN_OPERATION_ID%TYPE,
    SETUP_TYPE T_ROUTING_OPERATION.RTG_SEQ_DEP_SETUP_TYPE%TYPE,
    STEP_NUMBER T_ROUTING_OPERATION.RTG_STEP_NUMBER%TYPE,
    YIELD T_ROUTING_OPERATION.RTG_BASE_YIELD%TYPE
    );
    
    TYPE RTG_TAB IS TABLE OF RTG_REC; 
    T_RTG_OPN RTG_TAB;
BEGIN
...

SELECT ROUTING_ID, OPERATION_ID 
    FROM TABLE(T_RTG_OPN)
    ORDER BY ROUTING_ID;
...
END;


On compiling this procedure, I am getting the following compilation errors at the SELECT line:

PLS-00642: local collection types not allowed in SQL statements
ORA-22905: cannot access rows from a non-nested table item

Can anyone help me in identifying the main problem over here?

Thanks and Regards,
Tabish

[Updated on: Tue, 05 March 2013 04:59]

Report message to a moderator

Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578798 is a reply to message #578796] Tue, 05 March 2013 05:01 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#sthref18487
Quote:

PLS-00642: local collection types not allowed in SQL statements
Cause: A locally-defined (i.e. not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.

Action: Define the collection type in your schema, not inside a PL/SQL subprogram.



Regards,
Dariyoosh
Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578800 is a reply to message #578796] Tue, 05 March 2013 05:36 Go to previous messageGo to next message
tabishimam
Messages: 5
Registered: August 2010
Junior Member

Thanks Dariyoosh.

Seems like i will have to use conventional sorting algos for this one.
Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578801 is a reply to message #578800] Tue, 05 March 2013 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe not.

SQL> create or replace type typ is object (
  2    routing_id integer,
  3    operation_id integer
  4  );
  5  /

Type created.

SQL>  create or replace type tab is table of typ;
  2  /

Type created.

SQL> declare t tab := tab();
  2  begin
  3    for rec in (select routing_id, operation_id from table(t) order by routing_id) loop
  4      null;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.


With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

[Updated on: Tue, 05 March 2013 05:45]

Report message to a moderator

Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578802 is a reply to message #578800] Tue, 05 March 2013 05:44 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
@tabishimam

Just for information (because as I understand you want to sort your records):

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CJAIIBHD
Quote:

. . .
Records cannot be tested natively for nullity, equality, or inequality. These BOOLEAN expressions are illegal:

My_Record IS NULL

My_Record_1 = My_Record_2

My_Record_1 > My_Record_2

You must write your own functions to implement such tests . . .



Regards,
Dariyoosh

[Updated on: Tue, 05 March 2013 05:46]

Report message to a moderator

Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578810 is a reply to message #578802] Tue, 05 March 2013 06:37 Go to previous messageGo to next message
tabishimam
Messages: 5
Registered: August 2010
Junior Member

Hi Michel,

Thanks for the pointers. The main problem is I dont have sufficient privileges to create my own 'Types', hence i was trying to make do with temporary type definitions within the PL/SQL procedure.

Thanks and regards,
Tabish
Re: Error using Select .. TABLE() on PL/SQL nested tables [message #578814 is a reply to message #578810] Tue, 05 March 2013 07:38 Go to previous message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is not a real problem.
You MUST have sufficient privileges to do your work.
Ask your DBA.

Regards
Michel
Previous Topic: help to avoid duplicity in a string
Next Topic: Format Horizontal - UNION
Goto Forum:
  


Current Time: Mon Aug 04 14:26:42 CDT 2025