Home » SQL & PL/SQL » SQL & PL/SQL » Sort PL/SQL Table (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit, Win 7)
Sort PL/SQL Table [message #650872] Thu, 05 May 2016 22:24 Go to next message
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 #650873 is a reply to message #650872] Thu, 05 May 2016 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NEVER do in PL/SQL that which can be done in plain SQL.
Re: Sort PL/SQL Table [message #650874 is a reply to message #650873] Thu, 05 May 2016 22:56 Go to previous messageGo to next message
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 #650875 is a reply to message #650874] Thu, 05 May 2016 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vikram_2050 wrote on Thu, 05 May 2016 20:56
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


We don't have your tables.
We don't have your data.
We don't have your your requirements.
We don't know what are expected/desired results.

How will you, I or anyone know when correct solution is posted here?
Re: Sort PL/SQL Table [message #650878 is a reply to message #650874] Fri, 06 May 2016 00:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;

Previous Topic: Date Updation
Next Topic: Preserving Blanks in External Table
Goto Forum:
  


Current Time: Thu Mar 28 17:52:26 CDT 2024