Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL sorting (Oracle 10g)
PLSQL sorting [message #325672] Sat, 07 June 2008 08:50 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Experts,
I am working on a requirement to sort based on following structure..

Select 1 as package, ‘Before’ as dependency, 5 as pack_ref
Union
Select 5 as package, ‘After’ as dependency, 1 as pack_ref
union
Select 1 as package, ‘Before’ as dependency, 3 as pack_ref
Union
Select 3 as package, ‘After’ as dependency, 1 as pack_ref
Union
Select 3 as package, ‘Before’ as dependency, 6 as pack_ref
Union
Select 6 as package, ‘After’ as dependency, 3 as pack_ref


As an output I want to receive a list of “package” column sorted in the way to fulfill dependencies provided by given query. For this example that will be:
1
5
3
6
OR
1
3
5
6
OR
1
3
6
5

Also, theoretically we can have infinitive depth of this chain (e.g. 1 before 3, 3 before 6, 6 before something else, etc) and script should take this into account and work this out properly.
Any loops of different depth should be taken into account as well and raise informative error (e.g. 1 before 3, 3 before 6, 6 before 1 – this is loop and we cannot build correct sorting based on this set – errors should be raised with information containing these numbers – 1, 3, 6)

I have written the script and it seems to be working fine. I have used the PL-SQL script with plsql table structure and got the desired result. Please see the attached code file for your reference. I would like to know if there is any other better way of doing it and whether the code written by me right way of doing it?

Regards
Himanshu
  • Attachment: test.sql
    (Size: 5.95KB, Downloaded 156 times)
Re: PLSQL sorting [message #325673 is a reply to message #325672] Sat, 07 June 2008 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I would like to know if there is any other better way of doing it
better based upon which quantifiable metric?

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.
Re: PLSQL sorting [message #325677 is a reply to message #325672] Sat, 07 June 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.

Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

Re: PLSQL sorting [message #325697 is a reply to message #325677] Sat, 07 June 2008 11:16 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Sat, 07 June 2008 19:44
Post a test case: create table and insert statements.
Also post the result you want with these data.

Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel




Oracle version - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

As far as I understand the guidelines I followed those (may be my understanding is incorrect). The code I have attached is inline with the guidelines provided and the requirement I have doesn't really need table and insert statements. The code piece is running and without any errors. My requirement is fulfilling with my piece of code. Only thing I would like to know whether the code I have written is fine or there is better way of writing it. May be in terms of performance or if it can be achieved in a SQL statement itself. As there are members in this forum who are experts and much more experienced than me, so I just wanted their opinion on it.

Regards
Himanshu
Re: PLSQL sorting [message #325699 is a reply to message #325697] Sat, 07 June 2008 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's your opinion mine is I need a test case to understand and help.
You don't want to provide it, so you will do without my help.

Regards
Michel
Re: PLSQL sorting [message #325700 is a reply to message #325699] Sat, 07 June 2008 11:28 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Sat, 07 June 2008 21:50
It's your opinion mine is I need a test case to understand and help.
You don't want to provide it, so you will do without my help.

Regards
Michel



As far as I understand, I provided the test case and my exact requirement. The data is itself in the SELECT clause.

SELECT package, UPPER(dependency) dependency, pack_ref
	FROM
	(
	SELECT 1 AS package, 'BEFORE' AS dependency, 5 AS pack_ref FROM DUAL
	UNION
	SELECT 5 AS package, 'AFTER' AS dependency, 1 AS pack_ref FROM DUAL
	UNION
	SELECT 1 AS package, 'BEFORE' AS dependency, 3 AS pack_ref FROM DUAL
	UNION
	SELECT 3 AS package, 'AFTER' AS dependency, 1 AS pack_ref FROM DUAL
	UNION
	SELECT 3 AS package, 'BEFORE' AS dependency, 6 AS pack_ref FROM DUAL
	UNION
	SELECT 7 AS package, 'AFTER' AS dependency, 3 AS pack_ref  FROM DUAL
	UNION
	SELECT 6 AS package, 'AFTER' AS dependency, 3 AS pack_ref  FROM DUAL
	);


As an output I want to receive a list of “package” column sorted in the way to fulfill dependencies provided by given query. For this example that will be:
1
5
3
6
OR
1
3
5
6
OR
1
3
6
5


1. Also, theoretically we can have infinitive depth of this chain (e.g. 1 before 3, 3 before 6, 6 before something else, etc) and script should take this into account and work this out properly.
2. Any loops of different depth should be taken into account as well and raise informative error (e.g. 1 before 3, 3 before 6, 6 before 1 – this is loop and we cannot build correct sorting based on this set – errors should be raised with information containing these numbers – 1, 3, 6)

Do I need to provide any more explanation?
Re: PLSQL sorting [message #325701 is a reply to message #325700] Sat, 07 June 2008 11:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
Is the select from dual with union intended as a substitute for your actual data or is that your actual data? It is unclear exactly what you are trying to do. However, I get the general impression that you are trying to do a hierarchical query, avoiding infinite loops. If so, you can do all of this easily in one sql query using an Oracle hierarchical query that uses the syntax of "start with" and "connect by prior" to extract the hierarchy based on a column from one row matching a different column from a prior row. You can include "nocycle" to avoid the infinite loop problem. This is all explained and demonstrated in the following section of the online documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#sthref3132


Re: PLSQL sorting [message #325703 is a reply to message #325672] Sat, 07 June 2008 12:17 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Barbara,
Thanks for you reply. Yeah I actually thought about using the hierarchial query but was not able to understand how to use it. The Select from DUAL is replacement of the actual data from table, but in the table also the data would look similar.
I am attaching the table script with test data scripts

create table test_ref (package varchar(100), dependency varchar(100), pack_ref varchar(100));

insert into test_ref values ('1','BEFORE','5');
insert into test_ref values ('5','AFTER','1');
insert into test_ref values ('1','BEFORE','3');
insert into test_ref values ('3','AFTER','1');
insert into test_ref values ('3','BEFORE','6');
insert into test_ref values ('6','AFTER','3');



Actually what I am trying to achieve here is to find the dependency of the package column and to sort the package column based on the BEFORE or AFTER dependency of the reference column. So the above data should give me output something as
:

1
5
3
6
OR
1
3
5
6
OR
1
3
6
5

If it can be done in hierarchial query, then how? Also, I would like to report the error message in case it has cyclic loops (e.g. 1 before 3, 3 before 6, 6 before 1 – this is loop and we cannot build correct sorting based on this set – errors should be raised with information containing these numbers – 1, 3, 6)

Regards
Himanshu



Re: PLSQL sorting [message #325706 is a reply to message #325672] Sat, 07 June 2008 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
If/when you ever think you have this problem solved,
please let us know so I can then post some different data which will promptly reveal the flawed implementation.
Re: PLSQL sorting [message #325707 is a reply to message #325706] Sat, 07 June 2008 14:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
Your BEFORE and AFTER dependencies seem to be in matched pairs, so all you really need is one of them. In the example below, I have used only the BEFORE. If that is not the case, then you can select the AFTER pairs in reverse order and union them with the BEFORE to get the distinct values in BEFORE order and use that for your dataset to select from. If there is a loop in part of your data, an error will be raised. If your whole data is one loop, you will get no rows. If you are going to use this in some pl/sql, you could handle your exceptions based on those two criteria. The ordering of the siblings is random, so you may get any of the acceptable results. The first run below demonstrates a normal run and the remaining two show exceptions.

Also, I noticed that you are using packages and dependencies. If you are talking about pl/sql packages and dependencies, then there are other ways to do such things, using Oracle data dictionaries, that have these dependencies and Oracle utilities, like those in utldtree.sql that display these dependencies conveniently.

-- normal run:
SCOTT@orcl_11g> create table test_ref (package varchar(100), dependency varchar(100), pack_ref varchar(100));

Table created.

SCOTT@orcl_11g> insert into test_ref values ('1','BEFORE','5');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('3','BEFORE','6');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('1','BEFORE','3');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('7','BEFORE','4');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('1','BEFORE','7');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> COLUMN package	  FORMAT A7
SCOTT@orcl_11g> COLUMN dependency FORMAT A10
SCOTT@orcl_11g> COLUMN pack_ref   FORMAT A8
SCOTT@orcl_11g> SELECT	DISTINCT package
  2  FROM    test_ref
  3  WHERE   package NOT IN
  4  	     (SELECT pack_ref
  5  	      FROM   test_ref)
  6  UNION ALL
  7  SELECT  pack_ref
  8  FROM    test_ref
  9  START   WITH package NOT IN
 10  	     (SELECT pack_ref
 11  	      FROM   test_ref)
 12  CONNECT BY PRIOR pack_ref = package
 13  /

PACKAGE
-------
1
5
3
6
7
4

6 rows selected.

SCOTT@orcl_11g> 


-- partial loop:
SCOTT@orcl_11g> TRUNCATE TABLE test_ref;

Table truncated.

SCOTT@orcl_11g> insert into test_ref values ('1','BEFORE','3');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('6','BEFORE','1');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('3','BEFORE','6');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('5','BEFORE','1');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> COLUMN package	  FORMAT A7
SCOTT@orcl_11g> COLUMN dependency FORMAT A10
SCOTT@orcl_11g> COLUMN pack_ref   FORMAT A8
SCOTT@orcl_11g> SELECT	DISTINCT package
  2  FROM    test_ref
  3  WHERE   package NOT IN
  4  	     (SELECT pack_ref
  5  	      FROM   test_ref)
  6  UNION ALL
  7  SELECT  pack_ref
  8  FROM    test_ref
  9  START   WITH package NOT IN
 10  	     (SELECT pack_ref
 11  	      FROM   test_ref)
 12  CONNECT BY PRIOR pack_ref = package
 13  /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

SCOTT@orcl_11g> 


-- all data with one loop:
SCOTT@orcl_11g> TRUNCATE TABLE test_ref;

Table truncated.

SCOTT@orcl_11g> insert into test_ref values ('1','BEFORE','3');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('6','BEFORE','1');

1 row created.

SCOTT@orcl_11g> insert into test_ref values ('3','BEFORE','6');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> COLUMN package	  FORMAT A7
SCOTT@orcl_11g> COLUMN dependency FORMAT A10
SCOTT@orcl_11g> COLUMN pack_ref   FORMAT A8
SCOTT@orcl_11g> SELECT	DISTINCT package
  2  FROM    test_ref
  3  WHERE   package NOT IN
  4  	     (SELECT pack_ref
  5  	      FROM   test_ref)
  6  UNION ALL
  7  SELECT  pack_ref
  8  FROM    test_ref
  9  START   WITH package NOT IN
 10  	     (SELECT pack_ref
 11  	      FROM   test_ref)
 12  CONNECT BY PRIOR pack_ref = package
 13  /

no rows selected

SCOTT@orcl_11g> 

[Updated on: Sat, 07 June 2008 14:19]

Report message to a moderator

Re: PLSQL sorting [message #325752 is a reply to message #325706] Sun, 08 June 2008 06:07 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

anacedent wrote on Sat, 07 June 2008 23:54
If/when you ever think you have this problem solved,
please let us know so I can then post some different data which will promptly reveal the flawed implementation.


Yes I feel I solved the problem, with the PL/SQL script I have attached in initially post. If you can give me an example where it would fail, please do let me know..

Thanks in advance..

Regards
Himanshu
Re: PLSQL sorting [message #325754 is a reply to message #325672] Sun, 08 June 2008 06:12 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
Your BEFORE and AFTER dependencies seem to be in matched pairs, so all you really need is one of them. In the example below, I have used only the BEFORE. If that is not the case, then you can select the AFTER pairs in reverse order and union them with the BEFORE to get the distinct values in BEFORE order and use that for your dataset to select from. If there is a loop in part of your data, an error will be raised. If your whole data is one loop, you will get no rows. If you are going to use this in some pl/sql, you could handle your exceptions based on those two criteria. The ordering of the siblings is random, so you may get any of the acceptable results. The first run below demonstrates a normal run and the remaining two show exceptions.

Also, I noticed that you are using packages and dependencies. If you are talking about pl/sql packages and dependencies, then there are other ways to do such things, using Oracle data dictionaries, that have these dependencies and Oracle utilities, like those in utldtree.sql that display these dependencies conveniently.


Hi Barbara,
Thanks again for explaining the problem and giving the near about solution. The solution is not intended for pl/sql packages, but for the customized deployment requirement we have for out code deployment (not only oracle but shell script as well). The dependency can be BEFORE or AFTER as this is user entered value and hence we have to consider both the cases. I would work and see if your solution would help or do I need to tweak it bit more..

Thanks again for your help...

Regards
Himanshu
Re: PLSQL sorting [message #325767 is a reply to message #325754] Sun, 08 June 2008 09:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The following assumes that you might have some with just before and some with just after and some with both. It does a union of the befores and afters in reverse column order to create distinct values of befores, then performs the same query on that data set.

SCOTT@orcl_11g> create table test_ref
  2    (package     varchar(100),
  3  	dependency  varchar(100),
  4  	pack_ref    varchar(100))
  5  /

Table created.

SCOTT@orcl_11g> insert all
  2  into test_ref values ('1','BEFORE','5')
  3  into test_ref values ('5','AFTER', '1')
  4  into test_ref values ('6','AFTER', '3')
  5  into test_ref values ('1','BEFORE','3')
  6  into test_ref values ('4','AFTER', '7')
  7  into test_ref values ('1','BEFORE','7')
  8  select * from dual
  9  /

6 rows created.

SCOTT@orcl_11g> COLUMN package	  FORMAT A7
SCOTT@orcl_11g> COLUMN dependency FORMAT A10
SCOTT@orcl_11g> COLUMN pack_ref   FORMAT A8
SCOTT@orcl_11g> WITH	befores AS
  2  	     (SELECT package, pack_ref
  3  	      FROM   test_ref
  4  	      WHERE  dependency = 'BEFORE'
  5  	      UNION
  6  	      SELECT pack_ref, package
  7  	      FROM   test_ref
  8  	      WHERE  dependency = 'AFTER')
  9  SELECT  DISTINCT package
 10  FROM    befores
 11  WHERE   package NOT IN
 12  	     (SELECT pack_ref
 13  	      FROM   befores)
 14  UNION ALL
 15  SELECT  pack_ref
 16  FROM    befores
 17  START   WITH package NOT IN
 18  	     (SELECT pack_ref
 19  	      FROM   befores)
 20  CONNECT BY PRIOR pack_ref = package
 21  /

PACKAGE
-------
1
3
6
5
7
4

6 rows selected.

SCOTT@orcl_11g> 

Re: PLSQL sorting [message #325770 is a reply to message #325672] Sun, 08 June 2008 10:13 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Barbara,
Thanks for your awesome reply, but it looks it is failing for certain conditions...

insert all
into test_ref values ('1','BEFORE','5')
into test_ref values ('5','AFTER', '1')
into test_ref values ('6','AFTER', '3')
into test_ref values ('1','BEFORE','3')
into test_ref values ('4','AFTER', '7')
into test_ref values ('1','BEFORE','7')
into test_ref values ('7','AFTER','1')
into test_ref values ('8','AFTER','3')
into test_ref values ('8','BEFORE','6')
select * from dual
/


gives me result as (the 6 value is coming twice and breaking the rules)

PACKAGE
-------
1
3
6
8
6
5
7
4

8 rows selected.

whereas my program gives me

PACKAGE
-------
VALUE :1
VALUE :5
VALUE :3
VALUE :8
VALUE :6
VALUE :7
VALUE :4

which is actually correct as per the conditions. I am not sure whether it would work for certain complex conditions as well...

Regards
Himanshu

[Updated on: Sun, 08 June 2008 10:16]

Report message to a moderator

Re: PLSQL sorting [message #325773 is a reply to message #325770] Sun, 08 June 2008 10:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
I added a little to the code to take the last row where duplicates occur.

SCOTT@orcl_11g> create table test_ref
  2    (package     varchar(100),
  3  	dependency  varchar(100),
  4  	pack_ref    varchar(100))
  5  /

Table created.

SCOTT@orcl_11g> insert all
  2  into test_ref values ('1', 'BEFORE', '5')
  3  into test_ref values ('5', 'AFTER' , '1')
  4  into test_ref values ('6', 'AFTER' , '3')
  5  into test_ref values ('1', 'BEFORE', '3')
  6  into test_ref values ('4', 'AFTER' , '7')
  7  into test_ref values ('1', 'BEFORE', '7')
  8  into test_ref values ('7', 'AFTER' , '1')
  9  into test_ref values ('8', 'AFTER' , '3')
 10  into test_ref values ('8', 'BEFORE', '6')
 11  select * from dual
 12  /

9 rows created.

SCOTT@orcl_11g> COLUMN	rns	   NOPRINT
SCOTT@orcl_11g> COLUMN	package    FORMAT A7
SCOTT@orcl_11g> WITH	befores AS
  2  	     (SELECT package, pack_ref
  3  	      FROM   test_ref
  4  	      WHERE  dependency = 'BEFORE'
  5  	      UNION
  6  	      SELECT pack_ref, package
  7  	      FROM   test_ref
  8  	      WHERE  dependency = 'AFTER')
  9  SELECT  package, MAX (rn) AS rns
 10  FROM    (SELECT  DISTINCT package, 0 AS rn
 11  	      FROM    befores
 12  	      WHERE   package NOT IN
 13  		      (SELECT pack_ref
 14  		       FROM   befores)
 15  		       UNION ALL
 16  		       SELECT  pack_ref, ROWNUM AS rn
 17  		       FROM    befores
 18  		       START   WITH package NOT IN
 19  			       (SELECT pack_ref
 20  				FROM   befores)
 21  		       CONNECT BY PRIOR pack_ref = package)
 22  GROUP   BY package
 23  ORDER   BY rns
 24  /

PACKAGE
-------
1
3
8
6
5
7
4

7 rows selected.

SCOTT@orcl_11g> 

Re: PLSQL sorting [message #325774 is a reply to message #325770] Sun, 08 June 2008 11:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
I think using numeric values as sample varchar2 data may have added to the confusion for some readers that are trying to understand the problem, so here is an example using character data. For those readers still trying to understand, the idea is to select a unique list of packages ordered in such a manner that it meets all of the criteria of before and after, where before and after don't mean the row immediately before or after, just somewhere before or after. Also, there may be more than one order of results that meets the required criteria. This may enable some of our sharper members, once the problem is understood, to come up with something even more efficient.

-- test data:
SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> COLUMN	dependency FORMAT A10
SCOTT@orcl_11g> COLUMN	pack_ref   FORMAT A15
SCOTT@orcl_11g> SELECT	* FROM test_ref
  2  /

PACKAGE         DEPENDENCY PACK_REF
--------------- ---------- ---------------
what package    BEFORE     some package
some package    AFTER      what package
that package    AFTER      who package
what package    BEFORE     some package
that package    AFTER      this package
what package    BEFORE     this package
this package    AFTER      what package
odd package     AFTER      some package
odd package     BEFORE     that package

9 rows selected.

SCOTT@orcl_11g> 


-- query:
SCOTT@orcl_11g> COLUMN	rns	   NOPRINT
SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> WITH	befores AS
  2  	     (SELECT package, pack_ref
  3  	      FROM   test_ref
  4  	      WHERE  dependency = 'BEFORE'
  5  	      UNION
  6  	      SELECT pack_ref, package
  7  	      FROM   test_ref
  8  	      WHERE  dependency = 'AFTER')
  9  SELECT  package AS packages, MAX (rn) AS rns
 10  FROM    (SELECT  DISTINCT package, 0 AS rn
 11  	      FROM    befores
 12  	      WHERE   package NOT IN
 13  		      (SELECT pack_ref
 14  		       FROM   befores)
 15  		       UNION ALL
 16  		       SELECT  pack_ref, ROWNUM AS rn
 17  		       FROM    befores
 18  		       START   WITH package NOT IN
 19  			       (SELECT pack_ref
 20  				FROM   befores)
 21  		       CONNECT BY PRIOR pack_ref = package)
 22  GROUP   BY package
 23  ORDER   BY rns
 24  /

PACKAGES
--------------------------------------------------------------------------------
who package
what package
some package
odd package
this package
that package

6 rows selected.

SCOTT@orcl_11g> 

[Updated on: Sun, 08 June 2008 11:29]

Report message to a moderator

Re: PLSQL sorting [message #325775 is a reply to message #325672] Sun, 08 June 2008 11:12 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Barbara for awesome and marvellous answer and also explaining the details around the query, which makes Oracle professionals like me coming to this forum for any complex queries. It really makes me feel, that there are people who wants to share knowledge around.

Now I feel, I wasted my 3 hrs in writing PL/SQL sorting program, which could have been done by hierarchial SQL query Embarassed

Regards
Himanshu
Re: PLSQL sorting [message #325776 is a reply to message #325672] Sun, 08 June 2008 11:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Barbara,
One more question.. if there is a cyclic loop, then we get "no rows selected" whereas we would need to achieve the contents having cyclic loop.. Is it possible?

insert all
into test_ref values ('a1','BEFORE','b5')
into test_ref values ('b5','AFTER', 'a1')
into test_ref values ('c6','AFTER', 'd3')
into test_ref values ('a1','BEFORE','d3')
into test_ref values ('e4','AFTER', 'f7')
into test_ref values ('a1','BEFORE','f7')
into test_ref values ('f7','AFTER','a1')
into test_ref values ('g8','AFTER','d3')
into test_ref values ('b5','BEFORE','c6')
into test_ref values ('c6','BEFORE','a1')
select * from dual
/


SQL> COLUMN rns    NOPRINT
SQL> COLUMN package    FORMAT A7
SQL> WITH befores AS
  2          (SELECT package, pack_ref
  3           FROM   test_ref
  4            WHERE  dependency = 'BEFORE'
  5            UNION
  6            SELECT pack_ref, package
  7            FROM   test_ref
  8            WHERE  dependency = 'AFTER')
  9  SELECT  package, MAX (rn) AS rns
 10  FROM    (SELECT  DISTINCT package, 0 AS rn
 11            FROM    befores
 12            WHERE   package NOT IN
 13             (SELECT pack_ref
 14              FROM   befores)
 15              UNION ALL
 16              SELECT  pack_ref, ROWNUM AS rn
 17              FROM    befores
 18              START   WITH package NOT IN
 19               (SELECT pack_ref
 20          FROM   befores)
 21               CONNECT BY PRIOR pack_ref = package)
 22     GROUP   BY package
 23     ORDER   BY rns
 24  /

no rows selected

PL-SQL program
SQL> test.sql
Error in loop for package (c6,a1)

PL/SQL procedure successfully completed.

Re: PLSQL sorting [message #325779 is a reply to message #325776] Sun, 08 June 2008 12:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
I don't understand what you mean by "achieve the contents". If you mean that you need to display an error message, then, as stated previously you can handle the two possible cyclic loop scenarios through some pl/sql as shown below.

SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> COLUMN	dependency FORMAT A10
SCOTT@orcl_11g> COLUMN	pack_ref   FORMAT A15
SCOTT@orcl_11g> SELECT	* FROM test_ref
  2  /

PACKAGE         DEPENDENCY PACK_REF
--------------- ---------- ---------------
a1              BEFORE     b5
b5              AFTER      a1
c6              AFTER      d3
a1              BEFORE     d3
e4              AFTER      f7
a1              BEFORE     f7
f7              AFTER      a1
g8              AFTER      d3
b5              BEFORE     c6
c6              BEFORE     a1

10 rows selected.

SCOTT@orcl_11g> COLUMN	rns	   NOPRINT
SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> DECLARE
  2    v_count	     NUMBER;
  3    e_loop_error  EXCEPTION;
  4    PRAGMA EXCEPTION_INIT (e_loop_error, -1436);
  5  BEGIN
  6    WITH    befores AS
  7  	       (SELECT package, pack_ref
  8  		FROM   test_ref
  9  		WHERE  dependency = 'BEFORE'
 10  		UNION
 11  		SELECT pack_ref, package
 12  		FROM   test_ref
 13  		WHERE  dependency = 'AFTER')
 14    SELECT  COUNT (*)
 15    INTO    v_count
 16    FROM    befores
 17    WHERE   package NOT IN
 18  	       (SELECT pack_ref
 19  		FROM   befores);
 20    IF v_count = 0 THEN
 21  	 RAISE e_loop_error;
 22    END IF;
 23    FOR r IN
 24  	 (WITH	  befores AS
 25  		 (SELECT package, pack_ref
 26  		  FROM	 test_ref
 27  		  WHERE  dependency = 'BEFORE'
 28  		 UNION
 29  		 SELECT pack_ref, package
 30  		    FROM   test_ref
 31  		  WHERE  dependency = 'AFTER')
 32  	 SELECT  package, MAX (rn) AS rns
 33  	 FROM	 (SELECT  DISTINCT package, 0 AS rn
 34  		  FROM	  befores
 35  		  WHERE   package NOT IN
 36  			  (SELECT pack_ref
 37  			   FROM   befores)
 38  		  UNION ALL
 39  		  SELECT  pack_ref, ROWNUM AS rn
 40  		  FROM	  befores
 41  		  START   WITH package NOT IN
 42  			  (SELECT pack_ref
 43  			   FROM   befores)
 44  		 CONNECT BY PRIOR pack_ref = package)
 45  	 GROUP	 BY package
 46  	 ORDER	 BY rns)
 47    LOOP
 48  	 DBMS_OUTPUT.PUT_LINE (r.package);
 49    END LOOP;
 50  EXCEPTION
 51    WHEN e_loop_error THEN
 52  	 DBMS_OUTPUT.PUT_LINE ('Error in loop for package');
 53  END;
 54  /
Error in loop for package

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: PLSQL sorting [message #325781 is a reply to message #325672] Sun, 08 June 2008 12:27 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Barbara,
I meant that the rows which are forming the cyclic loop, should be displayed in case of any inconsistency of loop...
For example - a1 before d3, d3 before c6, c6 before a1 – this is loop and we cannot build correct sorting based on this set – errors should be raised with information containing these numbers – a1, d3, c6). The normal sorted contents can be easily displayed as suggested by you but in case of cyclic loop (as we get "no rows selected" for above example in SQL OR v_count = 0 in case of PL/SQL), we would like to display the a1, d3 and c6 in the error message. Is that possible?

Regards
Himanshu
Re: PLSQL sorting [message #325786 is a reply to message #325781] Sun, 08 June 2008 12:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
You can use connect_by_iscycle:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/pseudocolumns001.htm#sthref790

to identify the offending rows:

SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> COLUMN	dependency FORMAT A10
SCOTT@orcl_11g> COLUMN	pack_ref   FORMAT A15
SCOTT@orcl_11g> SELECT	* FROM test_ref
  2  /

PACKAGE         DEPENDENCY PACK_REF
--------------- ---------- ---------------
a1              BEFORE     b5
b5              AFTER      a1
c6              AFTER      d3
a1              BEFORE     d3
e4              AFTER      f7
a1              BEFORE     f7
f7              AFTER      a1
g8              AFTER      d3
b5              BEFORE     c6
c6              BEFORE     a1

10 rows selected.

SCOTT@orcl_11g> COLUMN	rns	   NOPRINT
SCOTT@orcl_11g> COLUMN	package    FORMAT A15
SCOTT@orcl_11g> DECLARE
  2    v_count	     NUMBER;
  3    e_loop_error  EXCEPTION;
  4    PRAGMA EXCEPTION_INIT (e_loop_error, -1436);
  5  BEGIN
  6    WITH    befores AS
  7  	       (SELECT package, pack_ref
  8  		FROM   test_ref
  9  		WHERE  dependency = 'BEFORE'
 10  		UNION
 11  		SELECT pack_ref, package
 12  		FROM   test_ref
 13  		WHERE  dependency = 'AFTER')
 14    SELECT  COUNT (*)
 15    INTO    v_count
 16    FROM    befores
 17    WHERE   package NOT IN
 18  	       (SELECT pack_ref
 19  		FROM   befores);
 20    IF v_count = 0 THEN
 21  	 RAISE e_loop_error;
 22    END IF;
 23    FOR r IN
 24  	 (WITH	  befores AS
 25  		 (SELECT package, pack_ref
 26  		  FROM	 test_ref
 27  		  WHERE  dependency = 'BEFORE'
 28  		 UNION
 29  		 SELECT pack_ref, package
 30  		    FROM   test_ref
 31  		  WHERE  dependency = 'AFTER')
 32  	 SELECT  package, MAX (rn) AS rns
 33  	 FROM	 (SELECT  DISTINCT package, 0 AS rn
 34  		  FROM	  befores
 35  		  WHERE   package NOT IN
 36  			  (SELECT pack_ref
 37  			   FROM   befores)
 38  		  UNION ALL
 39  		  SELECT  pack_ref, ROWNUM AS rn
 40  		  FROM	  befores
 41  		  START   WITH package NOT IN
 42  			  (SELECT pack_ref
 43  			   FROM   befores)
 44  		 CONNECT BY PRIOR pack_ref = package)
 45  	 GROUP	 BY package
 46  	 ORDER	 BY rns)
 47    LOOP
 48  	 DBMS_OUTPUT.PUT_LINE (r.package);
 49    END LOOP;
 50  EXCEPTION
 51    WHEN e_loop_error THEN
 52  	 DBMS_OUTPUT.PUT_LINE ('Error in loop for package, offending rows:');
 53    FOR r IN
 54  	 (WITH	  befores AS
 55  		  (SELECT package, pack_ref
 56  		   FROM   test_ref
 57  		   WHERE  dependency = 'BEFORE'
 58  		   UNION
 59  		   SELECT pack_ref, package
 60  		   FROM   test_ref
 61  		   WHERE  dependency = 'AFTER')
 62  	  SELECT  DISTINCT package, pack_ref
 63  	  FROM	  (SELECT  package, pack_ref, connect_by_iscycle AS eloop
 64  		   FROM    befores
 65  		   CONNECT BY NOCYCLE PRIOR pack_ref = package)
 66  	  WHERE   eloop = 1)
 67    LOOP
 68  	 DBMS_OUTPUT.PUT_LINE
 69  	  (RPAD (r.package, 15) ||
 70  	   ' BEFORE ' ||
 71  	   RPAD (r.pack_ref, 15));
 72    END LOOP;
 73  END;
 74  /
Error in loop for package, offending rows:
a1              BEFORE d3
b5              BEFORE c6
d3              BEFORE c6
c6              BEFORE a1
a1              BEFORE b5

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

icon7.gif  Re: PLSQL sorting [message #325802 is a reply to message #325672] Sun, 08 June 2008 23:59 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Barbara for helping on this and also explaining the BEFORE/CONNECT_BY_ISCYCLE clauses.
Previous Topic: How to put source code into procedure ?
Next Topic: Execute Immediate Statement with the procedure that contain OUT Mode parameter
Goto Forum:
  


Current Time: Fri Dec 02 14:21:59 CST 2016

Total time taken to generate the page: 0.10871 seconds