Hello ,
I have this collection: a nested table built from a custom record that holds three varchars fields, and two numeric ones.
The collection is filled with data inside a procedure.
I need to sort that collection and I also need to choose some element based in the data inside it.
In order to avoid making several loops with the collection I would like to do it with DML language, it's going to be much easier and elegant.
For instance:
SELECT * FROM MY_COLLECTION WHERE field_1=x;
I found that there is a way to do that using the TABLE function.
So I've defined the types AT SCHEMA LEVEL (m_lincs user) with a script:
--The record:
CREATE or REPLACE TYPE rCprefBalQty AS OBJECT(
agg_chan varchar2(20)
,chan_id varchar2(20)
,prod_cd varchar2(30)
,cp_ref number(10)
,nBalQty NUMERIC);
/
--The nested table definition:
CREATE or replace TYPE tCpRefQty IS TABLE OF rCprefBalQty;
/
--and the function to use with the TABLE function:
CREATE OR REPLACE FUNCTION fecth_collection(collec_in IN tCpRefQty) RETURN tCpRefQty AS
coll tCpRefQty := tCpRefQty();
BEGIN
RETURN(coll);
END;
But when I use the TABLE function inside a package I get the following error:
Compilation errors for PACKAGE M_LINCS.TESTING_TABLE
Error: PL/SQL: ORA-00947: not enough values
Line: 11
Text: BEGIN
Error: PL/SQL: SQL Statement ignored
Line: 9
Text: temp_tShortageColl tCpRefQty := tCpRefQty();
Here's the package I wrote (m_lincs also):
CREATE OR REPLACE PACKAGE TESTING_TABLE IS
END TESTING_TABLE;
/
CREATE OR REPLACE PACKAGE BODY TESTING_TABLE IS
PROCEDURE test_dml(tShortageColl_in IN tCpRefQty) IS
temp_tShortageColl tCpRefQty := tCpRefQty();
BEGIN
SELECT * BULK COLLECT
INTO temp_tShortageColl
FROM TABLE(fecth_collection(tShortageColl_in))
ORDER BY agg_chan
,chan_id
,prod_cd;
END test_dml;
END TESTING_TABLE;
Thank you Connor!
Ernesto.
I'm not sure what you mean.
"I need to query a nested table with custom types, so I need to use the TABLE function."
Are you saying that you have a physical database table, that has some nested table types as columns ?
If so, can you give us some sample DDL for that table ?
Or do you mean something else ?
========================
Addenda: I'm still lost - you never actually *provide* anything to the table as an input or an output.
So I've made some changes - I hope they explain things for you ....
SQL> CREATE or REPLACE TYPE rCprefBalQty AS OBJECT(
2 agg_chan varchar2(20)
3 ,chan_id varchar2(20)
4 ,prod_cd varchar2(30)
5 ,cp_ref number(10)
6 ,nBalQty NUMERIC);
7
8 /
Type created.
SQL>
SQL> CREATE or replace TYPE tCpRefQty IS TABLE OF rCprefBalQty;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION fecth_collection RETURN tCpRefQty AS
2 col tCpRefQty := tCpRefQty();
3 BEGIN
4
5 col.extend(3);
6 col(1) := rCprefBalQty('agg1','chan1','prod1',1,10);
7 col(2) := rCprefBalQty('agg2','chan2','prod2',2,20);
8 col(3) := rCprefBalQty('agg3','chan3','prod3',3,30);
9
10 RETURN(col);
11 END;
12 /
Function created.
SQL>
SQL> select * from table(fecth_collection);
AGG_CHAN CHAN_ID PROD_CD CP_REF NBALQTY
-------------------- -------------------- ------------------------------ ---------- ----------
agg1 chan1 prod1 1 10
agg2 chan2 prod2 2 20
agg3 chan3 prod3 3 30
3 rows selected.
SQL>
SQL>
SQL> create table t (
2 c1 varchar2(20)
3 ,c2 varchar2(20)
4 ,c3 varchar2(30)
5 ,c4 number(10)
6 ,c5 number);
Table created.
SQL>
SQL> insert into t values ('a','b','c',1,2);
1 row created.
SQL> insert into t values ('e','f','g',5,6);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION fecth_collection(rc sys_refcursor) RETURN tCpRefQty AS
2
3 v1 varchar2(20);
4 v2 varchar2(20);
5 v3 varchar2(30);
6 v4 number(10);
7 v5 number;
8
9 col tCpRefQty := tCpRefQty();
10 BEGIN
11 loop
12 fetch rc into v1,v2,v3,v4,v5;
13 exit when rc%notfound;
14 col.extend;
15 col(col.count) := rCprefBalQty(v1,v2,v3,v4,v5);
16 end loop;
17
18 RETURN(col);
19 END;
20 /
Function created.
SQL>
SQL> select * from table(fecth_collection(cursor(select * from t)));
AGG_CHAN CHAN_ID PROD_CD CP_REF NBALQTY
-------------------- -------------------- ------------------------------ ---------- ----------
a b c 1 2
e f g 5 6
2 rows selected.