Home » SQL & PL/SQL » SQL & PL/SQL » Repeat Cursor (12.1.0.2.0)
Repeat Cursor [message #664510] Thu, 20 July 2017 09:42 Go to next message
SteveShephard
Messages: 41
Registered: August 2012
Member
Hi all,

I have a cursor within a procedure (below). Is it possible to run through the cursor and if PART_STATUS equals a certain value, run through the cursor again but changing the value of PART_NO_ to the value of SUPERSEDED_PART

cursor get_details is 
SELECT 
SUPERSEDED_PART,
PART_STATUS
FROM PART_INO
WHERE PART_NO = PART_NO_;

Many thanks

Steve







Re: Repeat Cursor [message #664511 is a reply to message #664510] Thu, 20 July 2017 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65169
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it is not possible.
But you can directly create a cursor that does what you want.

Again:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Repeat Cursor [message #664512 is a reply to message #664511] Thu, 20 July 2017 09:59 Go to previous messageGo to next message
SteveShephard
Messages: 41
Registered: August 2012
Member
Basically the idea is we run through the cursor to give us the value of STATUS. If the value is 1 return the PART_NO. If the value is 2 then go through the cursor again until we get a 1 using swapping the PART_NO parameter for the result of SUPERSEDED_PART

cursor get_details is 
SELECT 
SUPERSEDED_PART,
PART_STATUS
FROM PART_INO
WHERE PART_NO = PART_NO_;

BEGIN

for get_details_a in get_details loop
if get_details_a.STATUS = '1' then
return_value_ := part_no
end if
if get_details_a.STATUS = '2' then
part_no_ := get_details_a.SUPERSEDED_PART
--REPEAT THROUGH LOOP AGAIN until the STATUS = 1

Does that make more sense?


return return_value_

Re: Repeat Cursor [message #664514 is a reply to message #664512] Thu, 20 July 2017 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 12876
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looping through a cursor till you find a particular value makes no sense unless the cursor has order, yours doesn't. What should it be ordered by?

If you give us a test case, along with the result you expect it's going to be a lot easier to work out what you are trying to achive
Re: Repeat Cursor [message #664515 is a reply to message #664512] Thu, 20 July 2017 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
What business problem are you trying to solve?
Re: Repeat Cursor [message #664516 is a reply to message #664515] Thu, 20 July 2017 11:09 Go to previous messageGo to next message
SteveShephard
Messages: 41
Registered: August 2012
Member
We have a table which stores unique part numbers. Each part number can be active or inactive. If it is inactive then there will be a SUPERSEDED_PART value which again can be active or inactive. This superseded part is also a part number which is displayed in the table.

So for example

PART_NO|STATUS|SUPERSEDED_PART
PART_A|ACTIVE|NULL
PART_B|INACTIVE|PART_C
PART_C|ACTIVE|NULL
PART_D|INACTIVE|PART_E
PART_E|INACTIVE|PART_F
PART_F|ACTIVE|NULL

If the Part is inactive I would like to find the status of the superseded part. If the superseded part of that is also inactive, find the next part (so keep going until you find the active part)
Re: Repeat Cursor [message #664517 is a reply to message #664516] Thu, 20 July 2017 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65169
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is NOT a test case.
Please read the link we gave you and post what we requested.

From your previous topic:

Michel Cadot wrote on Fri, 19 June 2015 13:54

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
Also you did not feedback in your previous topics, do you think we are your servants just good to solve your problems but do not deserve any gesture of gratitude from you not even an answer to our requests in order to help you?

[Updated on: Thu, 20 July 2017 11:16]

Report message to a moderator

Re: Repeat Cursor [message #664519 is a reply to message #664517] Thu, 20 July 2017 12:08 Go to previous messageGo to next message
Bill B
Messages: 1687
Registered: December 2004
Senior Member
this screams for using a simple query using the connect by clause. can you give a table create command and some sample data in insert commands. Thanks
Re: Repeat Cursor [message #664539 is a reply to message #664519] Fri, 21 July 2017 02:20 Go to previous messageGo to next message
SteveShephard
Messages: 41
Registered: August 2012
Member
Thank you all. I was not after a full solution, just some example syntax or clause for me to look further into it (I was never expecting anyone to provide the full required code). I will explore the connect by clause.

Many thanks
Re: Repeat Cursor [message #664544 is a reply to message #664539] Fri, 21 July 2017 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65169
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But maybe future readers would be interested to have a complete definition of the problem and its solutions.

Re: Repeat Cursor [message #664545 is a reply to message #664539] Fri, 21 July 2017 14:20 Go to previous message
Barbara Boehmer
Messages: 8737
Registered: November 2002
Location: California, USA
Senior Member
-- test environment:
SCOTT@orcl_12.1.0.2.0> -- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> -- statements to create table with primary key and insert sample data like we expected you to provide:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE part_ino
  2    (part_no 	 VARCHAR2(15) PRIMARY KEY,
  3  	status		 VARCHAR2(8),
  4  	superseded_part  VARCHAR2(15))
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2    INTO part_ino VALUES ('PART_A', 'ACTIVE', 'NULL')
  3    INTO part_ino VALUES ('PART_B', 'INACTIVE', 'PART_C')
  4    INTO part_ino VALUES ('PART_C', 'ACTIVE', 'NULL')
  5    INTO part_ino VALUES ('PART_D', 'INACTIVE', 'PART_E')
  6    INTO part_ino VALUES ('PART_E', 'INACTIVE', 'PART_F')
  7    INTO part_ino VALUES ('PART_F', 'ACTIVE', 'NULL')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_12.1.0.2.0> -- resultng sample data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM part_ino
  2  /

PART_NO         STATUS   SUPERSEDED_PART
--------------- -------- ---------------
PART_A          ACTIVE   NULL
PART_B          INACTIVE PART_C
PART_C          ACTIVE   NULL
PART_D          INACTIVE PART_E
PART_E          INACTIVE PART_F
PART_F          ACTIVE   NULL

6 rows selected.

-- method using recursive subquery:
SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT p2.part_no, p2.status, p2.superseded_part,
  2  	    (SELECT  MAX (p1.part_no) KEEP (DENSE_RANK LAST ORDER BY LEVEL)
  3  	     FROM    part_ino p1
  4  	     START   WITH p1.part_no = p2.part_no
  5  	     CONNECT BY PRIOR p1.superseded_part = p1.part_no
  6  		    AND PRIOR p1.status != 'ACTIVE') AS active_part
  7  FROM   part_ino p2
  8  /

PART_NO         STATUS   SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A          ACTIVE   NULL            PART_A
PART_B          INACTIVE PART_C          PART_C
PART_C          ACTIVE   NULL            PART_C
PART_D          INACTIVE PART_E          PART_F
PART_E          INACTIVE PART_F          PART_F
PART_F          ACTIVE   NULL            PART_F

6 rows selected.

-- method using recursive function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION active_part
  2    (part_no_       IN part_ino.part_no%TYPE)
  3    RETURN		  part_ino.part_no%TYPE
  4  AS
  5    superseded_part_  part_ino.superseded_part%TYPE;
  6    status_		 part_ino.status%TYPE;
  7    return_value_	 part_ino.part_no%TYPE;
  8  BEGIN
  9    SELECT superseded_part, status
 10    INTO   superseded_part_, status_
 11    FROM   part_ino
 12    WHERE  part_no = part_no_;
 13    IF status_ = 'ACTIVE' THEN return_value_ := part_no_;
 14    ELSIF status_ = 'INACTIVE' THEN return_value_ := active_part (superseded_part_);
 15    END IF;
 16    RETURN return_value_;
 17  END active_part;
 18  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT part_no, status, superseded_part,
  2  	    active_part (part_no) AS active_part
  3  FROM   part_ino
  4  ORDER  BY part_no
  5  /

PART_NO         STATUS   SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A          ACTIVE   NULL            PART_A
PART_B          INACTIVE PART_C          PART_C
PART_C          ACTIVE   NULL            PART_C
PART_D          INACTIVE PART_E          PART_F
PART_E          INACTIVE PART_F          PART_F
PART_F          ACTIVE   NULL            PART_F

6 rows selected.
Previous Topic: Solution for ora-00020 maximum number of processes
Next Topic: oracle external table load
Goto Forum:
  


Current Time: Mon Sep 25 21:08:46 CDT 2017

Total time taken to generate the page: 0.06558 seconds