PL/SQL ? How to declare cursor based on condition

[SJSFoxPro]

I'm trying to write a procedure that uses a cursor in a loop to execute a statement (I am not using it to manipulate data.) When I declare the cursor, I need to assign the cursor based on a parameter meeting a specific condition (of which I have two now, possibly more later). I want to code for an exception, in case an invalid parameter is sent to the procedure. The exception is what I don't know how to code. If the parameter does NOT meet one of the conditions and my cursor is not declared, an error will raise when I try to use the cursor in my loop.

I could write two procedures, but the only thing that changes is the result set for the cursor. I thought of sending the where clause as the parameter, but I prefer not to complicate the code calling the procedure. Also, if I did so, I would still need to code for an invalid where clause being passed.

HELP!

It would seem like I could code a simple IF statement, but I can't find any reference or example for doing so:

PROCEDURE run_process(parameter_in VARCHAR2)
IS
-- Declare cursor variable
IF parameter_in = condition1
THEN
CURSOR company_cur IS
SELECT field
FROM table
WHERE condition1;
ELSIF parameter_in = condition2
THEN
CURSOR company_cur IS
SELECT field
FROM table
WHERE condition2;
ELSE
[This is where I need help!]
END IF;

BEGIN
BEGIN
FOR i IN company_cur
LOOP
<execute statements>
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001,'Error message.',True);
END;
END run_process;

Thanks for any help!

 

[SantaMufasa]

SJS,

You can do the following:

PROCEDURE run_process(parameter_in VARCHAR2)
begin
IF parameter_in = condition1
THEN
   FOR i IN (SELECT field
               FROM   table
              WHERE  condition1) loop
          <execute statements>
   END LOOP; 
ELSIF parameter_in = condition2
THEN
   FOR i IN (SELECT field
               FROM   table
              WHERE  condition2) loop
          <execute statements>
   END LOOP; 
ELSE
   RAISE_APPLICATION_ERROR(-20001,'Error message.');
END IF;
END run_process;

So you see, the code is actually much simpler that the origianl code set. Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

Santa,

As usual, your code is very straight forward! Thanks!

The only reason I was trying to use a cursor is because the select statement is the ONLY thing changing (in fact just the WHERE clause). I wanted to avoid duplicating the <execute statements> section two (and likely many more) times.

I can code it the way you showed, but ultimately I would like to find a way to NOT grow my procedure with the redundant code.

Any thoughts?

Regards, Susan

 

[SantaMufasa]

Susan,

Perhaps we can do what you want depending upon the nature of your SELECT statement and especially your WHERE clause. Could you please post at least two actual code samples of your SELECT statement?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

Santa,

Certainly... thanks for looking at this. The SELECT statements are simple, but I'd prefer not to send the WHERE clause as a parameter (it could get lengthy for some). Here are two:

CURSOR ghrs_cur IS
SELECT table_name
FROM user_tables
WHERE tablespace_name like '%ODW%'
AND table_name not like 'MINIPAY_DETAIL';

CURSOR ghrs_cur IS
SELECT table_name
FROM user_tables
WHERE table_name in ('FULL_TIME_POSITION_DETAIL','POSITION_EMPLOYEE_CURRENT','POSITION_EMPLOYEE_CURRENT_ORG');

 

[SantaMufasa]

Susan,

Since I'm not aware of a way of doing an "EXECUTE IMMEDIATE <cursor definition>...", I don't know of a way around my alternative, above. Sorry.

Anyone else have ideas for Susan?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

Santa,

Do you see any issues with this? Any way I could use a bind variable?

PROCEDURE run_process(parameter_in VARCHAR2)
IS
-- Declare cursor variable
CURSOR ghrs_cur IS
SELECT table_name
FROM user_tables
WHERE (parameter_in = 'condition1' and
tablespace_name like '%ODW%' and
table_name not like 'MINIPAY_DETAIL') or
(parameter_in = 'condition2' and
table_name in 'FULL_TIME_POSITION_DETAIL','POSITION_EMPLOYEE_CURRENT','POSITION_EMPLOYEE_CURRENT_ORG');

BEGIN
BEGIN
FOR i IN company_cur
LOOP
<execute statements>
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001,'Error message.',True);
END;
END run_process;

 

[SantaMufasa]

Looks pretty good after a desk check except that your "table_name in ..." needs a set of parentheses around your argument list.

Let us know how things go.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

Thanks for your help. I think I just needed to think it through with someone who knows what they're doing ;-)

I'll try to remember to post the outcome.

Have a great day!

 

[SJSFoxPro]

Dave,

I just wanted to confirm that it worked. I was finally able to implement the solution and get the whole process working. The WHERE clause for the cursor could grow, but overall, I'm pleased to have it as minimal and simple as it is. Certainly better than writing the same execute statement over and over for each of my tables. My procedure (in its generic form) is:

PROCEDURE run_process(parameter_in VARCHAR2)
IS
-- Declare cursor variable
CURSOR ghrs_cur IS
SELECT table_name
FROM user_tables
WHERE (parameter_in = 'condition1' and
tablespace_name like '%XYZ%' and
table_name not like 'table5') or
(parameter_in = 'condition2' and
table_name in ('table1','table2','table3')) or
(parameter_in not in
('condition1', 'condition2') and 1 = 2);

BEGIN
BEGIN
FOR i IN ghrs_cur
LOOP
<execute statements using i.table_name>
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001,'Error message.',True);
END;
END run_process;

Thanks for your help and support!

Susan