Sort PL/SQL Table [message #650872] |
Thu, 05 May 2016 22:24 |
vikram_2050
Messages: 10 Registered: June 2005 Location: bangalore
|
Junior Member |
|
|
Hi,
When I tried to sort pl/sql table as mentioned in below procedure. Its throwing an error message.
create or replace PROCEDURE proc1
AS
v_result VARCHAR2(100);
TYPE list
IS
RECORD
(
ename VARCHAR2(1000),
htype VARCHAR2(1),
hsort NUMBER,
esort NUMBER );
TYPE t_list
IS
TABLE OF list;
v_list t_list :=t_list();
v_list_all t_list :=t_list();
BEGIN
FOR i IN
(
SELECT
a.name
FROM
tab1 a
)
LOOP
proc2 (p_name=>i.name, p_result=>v_result);
SELECT
ename ,
htype,
hsort,
esort bulk collect
INTO
v_list
FROM
tab2
WHERE
ename =v_result;
v_list_all:=v_list_all MULTISET
UNION v_list;
END LOOP;
SELECT
CAST ( multiset
(
SELECT
*
FROM
TABLE( v_list_all )
ORDER BY
4, 3
) AS list ) bulk collect
INTO
v_list_all
FROM
dual;
END;
Error(50,10): PL/SQL: ORA-00902: invalid datatype
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
PL/SQL Release 11.2.0.4.0 -
Win 7
Request to provide any example or reference to sort the pl/sql table
Thanks in Advance.
|
|
|
|
Re: Sort PL/SQL Table [message #650874 is a reply to message #650873] |
Thu, 05 May 2016 22:56 |
vikram_2050
Messages: 10 Registered: June 2005 Location: bangalore
|
Junior Member |
|
|
Thanks for providing your response.
I have provided here with simpler example but in real its much more complex so trying do with pl/sql. Is there any way to do it with PL/SQL?
Can you please provide any example to do the same with pl/sql.
Thanks in Advance
|
|
|
|
Re: Sort PL/SQL Table [message #650878 is a reply to message #650874] |
Fri, 06 May 2016 00:32 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
When you use CAST and MULTISET, you need to select into something that is based on a SQL table type, not a scalar type, and not a PL/SQL type and you can't combine that with bulk collect. Please see the revised code below. I have added tables, procedure, types, and output for demonstration purposes.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE tab1
2 (name VARCHAR2(1000))
3 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO tab1 (name) SELECT ename FROM emp
2 /
14 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE tab2
2 (ename VARCHAR2(1000),
3 htype VARCHAR2(1),
4 hsort NUMBER,
5 esort NUMBER)
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO tab2 (ename, htype, hsort, esort)
2 SELECT ename, SUBSTR (job, 1, 1), sal, comm FROM emp
3 /
14 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE proc2
2 (p_name IN VARCHAR2,
3 p_result OUT VARCHAR2)
4 AS
5 BEGIN
6 p_result := p_name;
7 END proc2;
8 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE list AS OBJECT
2 (ename VARCHAR2(1000),
3 htype VARCHAR2(1),
4 hsort NUMBER,
5 esort NUMBER);
6 /
Type created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE t_list AS TABLE OF list;
2 /
Type created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE proc1
2 AS
3 v_result VARCHAR2(100);
4 v_list t_list := t_list();
5 v_list_all t_list := t_list();
6 BEGIN
7 FOR i IN
8 (SELECT a.name FROM tab1 a)
9 LOOP
10 proc2 (p_name=>i.name, p_result=>v_result);
11
12 SELECT list (ename, htype, hsort, esort)
13 BULK COLLECT INTO v_list
14 FROM tab2
15 WHERE ename = v_result;
16
17 v_list_all := v_list_all MULTISET UNION v_list;
18 END LOOP;
19
20 SELECT CAST
21 (MULTISET
22 (SELECT * FROM TABLE (v_list_all) ORDER BY 4, 3)
23 AS t_list )
24 INTO v_list_all
25 FROM DUAL;
26
27 FOR i IN 1..v_list_all.COUNT LOOP
28 DBMS_OUTPUT.PUT_LINE
29 (RPAD (v_list_all(i).ename, 17) ||
30 v_list_all(i).htype || ' ' ||
31 v_list_all(i).hsort || ' ' ||
32 v_list_all(i).esort);
33 END LOOP;
34 END proc1;
35 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXEC proc1
TURNER S 1500 0
ALLEN S 1600 300
WARD S 1250 500
MARTIN S 1250 1400
SMITH C 800
JAMES C 950
ADAMS C 1100
MILLER C 1300
CLARK M 2450
BLAKE M 2850
JONES M 2975
SCOTT A 3000
FORD A 3000
KING P 5000
PL/SQL procedure successfully completed.
|
|
|
Re: Sort PL/SQL Table [message #650916 is a reply to message #650878] |
Fri, 06 May 2016 09:09 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Question is where collection data is coming from. In many cases it comes from some query. So it is easier to add order by right at the source:
SELECT list (ename, htype, hsort, esort)
BULK COLLECT INTO v_list
FROM tab2
WHERE ename = v_result
ORDER BY esort,hsort
;
SY.
|
|
|
Re: Sort PL/SQL Table [message #650923 is a reply to message #650916] |
Fri, 06 May 2016 12:26 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Hi Solomon,
I think the problem is that the query that produces v_list is run in a loop which is then added onto v_list_all within each loop. I have provided an excerpt of that portion of the code below. So ordering of the query that produces v_list does not order the v_list_all. As the original poster said that this is a simplification of a real problem and we don't have those details, I don't know if the select from tab1 and whatever the proc2 does could be combined with the query that produces v_list into one ordered query without a loop. If so, that would certainly be better. If the original poster will provide some realistic sample data and the code for proc2, perhaps we can offer some suggestions.
FOR i IN
(SELECT a.name FROM tab1 a)
LOOP
proc2 (p_name=>i.name, p_result=>v_result);
SELECT list (ename, htype, hsort, esort)
BULK COLLECT INTO v_list
FROM tab2
WHERE ename = v_result;
v_list_all := v_list_all MULTISET UNION v_list;
END LOOP;
|
|
|