Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL sorting (Oracle 10g)
PLSQL sorting [message #325672] |
Sat, 07 June 2008 08:50  |
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 546 times)
|
|
|
|
|
|
|
Re: PLSQL sorting [message #325700 is a reply to message #325699] |
Sat, 07 June 2008 11:28   |
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 #325703 is a reply to message #325672] |
Sat, 07 June 2008 12:17   |
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 #325707 is a reply to message #325706] |
Sat, 07 June 2008 14:04   |
 |
Barbara Boehmer
Messages: 9104 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 #325754 is a reply to message #325672] |
Sun, 08 June 2008 06:12   |
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   |
 |
Barbara Boehmer
Messages: 9104 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   |
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   |
 |
Barbara Boehmer
Messages: 9104 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   |
 |
Barbara Boehmer
Messages: 9104 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 #325776 is a reply to message #325672] |
Sun, 08 June 2008 11:21   |
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   |
 |
Barbara Boehmer
Messages: 9104 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 #325786 is a reply to message #325781] |
Sun, 08 June 2008 12:49   |
 |
Barbara Boehmer
Messages: 9104 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>
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:35:25 CST 2025
|