Error using Select .. TABLE() on PL/SQL nested tables [message #578796] |
Tue, 05 March 2013 04:59  |
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 #578801 is a reply to message #578800] |
Tue, 05 March 2013 05:44   |
 |
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
|
|
|
|
|
|