creating a Cursor with selective 'SELECT' statements

[pngu123]

HI Tim

I am trying to create a cursor with selective 'SELECT' statements such as code below, I can't seem to get that going, I guess it's expecting an immediate 'SELECT' statement right after declaring a cursor, it's giving me an error, it's not telling me the exact error, but it has a red undeline on the word 'IF' right below the word 'IS' in the code below; for testing, I changed the code do only do a SELECT, no conditional testing before executing the SELECTs, then it's ok.

I've read documentation and browsed the web to see if this can be done, so far, I haven't seen anything such as what I'm trying to achieve.

I'm doing this in TOAD 10.6, Oracle 11g. Please see code below:


SAMPLE CODE:
/* I'm passing in the value for testing ONLY!!! */
IN_ENV VARCHAR2(30) := 'ORG';

CURSOR c0(IN_ENV VARCHAR2)
IS
IF (IN_ENV != ' ')
THEN
SELECT val_type_prop_type, what from GET_VALTYPE_OTHERS where val_type_prop_type = IN_ENV
ELSE
SELECT val_type_prop_type, what from GET_VALTYPE_OTHERS
END IF;


I would like to find a way to achieve the objective by running different 'SELECT' statements depending on what I pass in as an argument,
if I don't pass in anything, then do a SELECT without the 'WHERE' clause, otherwise do a SELECT with the WHERE clause.

Is there any way to do that? Thanks in advance for your help and input.

Pete.

[Tim...]

Hi.

Sounds like the perfect use-case for a REF CURSOR.

Code: Select all

DECLARE
  c_cursor SYS_REFCURSOR;
BEGIN
  IF (IN_ENV != ' ') THEN
    OPEN c_cursor FOR
      SELECT val_type_prop_type, what from GET_VALTYPE_OTHERS where val_type_prop_type = IN_ENV;
  ELSE
    OPEN c_cursor FOR
      SELECT val_type_prop_type, what from GET_VALTYPE_OTHERS;
  END IF;

  -- Do something with the cursor...
  FETCH c_cursor
  INTO  ....
   
  CLOSE c_cursor;
END;

You ca also open the cursor, then pass it out as a parameter or function return, as shown here:

http://www.oracle-base.com/articles/mis ... rdsets.php

Cheers

Tim...

[pngu123]

Thank you Tim, I will give it a try. I appreciate your time and answer.

[Tim...]