creating a Cursor with selective 'SELECT' statements

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

creating a Cursor with selective 'SELECT' statements

Postby pngu123 » Wed Feb 27, 2013 1:27 pm

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.
pngu123
Member
 
Posts: 2
Joined: Tue Feb 26, 2013 4:49 pm

Re: creating a Cursor with selective 'SELECT' statements

Postby Tim... » Wed Feb 27, 2013 6:47 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: creating a Cursor with selective 'SELECT' statements

Postby pngu123 » Tue Mar 05, 2013 2:21 pm

Thank you Tim, I will give it a try. I appreciate your time and answer.
pngu123
Member
 
Posts: 2
Joined: Tue Feb 26, 2013 4:49 pm

Re: creating a Cursor with selective 'SELECT' statements

Postby Tim... » Tue Mar 05, 2013 5:00 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 7 guests