Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: Out parameter is cursor type

    Hi,

    Iam new to pl/sql, and now iam trying to create a procedure which takes 12 values as input parameters and the out parameter is a cursor type, can you send some examples how to do this, i didn't get whether i use ref cursor of any other one.and one more thing is i have to check that each in put value is not null that is i have to construct a dynamic where clause, pls tell me how to achieve this one.

    Thank you

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Out parameter is cursor type

    This a simple example about how to create a procedure that takes parameters.

    SQL> create or replace procedure the_procedure_that_takes_param
    2 ( par1 number, par2 varchar2, par3 date )
    3 as
    4 begin
    5 dbms_output.put_line(par1);
    6 dbms_output.put_line(par2);
    7 end;
    8 /

    Procedure created.

    SQL> set serveroutput on
    SQL>
    SQL> exec the_procedure_that_takes_param(1,'joel','11-11-2004');
    1
    joel

    PL/SQL procedure successfully completed.

    SQL>

    for OUTPUT parameters I recommend you to check that part in:

    PL/SQL User's Guide and Reference Contents / Search / Index / PDF
    http://download-east.oracle.com/docs...920/a96624.pdf
    Joel Pérez

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi.

    You can't do this in a procedure... following syntax *will* be bad, check out appropriate syntax yourself.

    PHP Code:
    create or replace package fred_pkg is
        type tCurType IS REF CURSOR
    ;
        
    procedure tomaParms IN table.colum%typeoutList OUT tCurtype )
    end fred_pkg;

    create or replace package body fred_pkg as
       
    procedure tomaParms IN table.colum%typeoutList tCurtype is
       Sql varchar
    (5000);
       
    begin
          Sql 
    := 'select * from ';
          if 
    aParms 'HARRY' then
             Sql 
    := Sql || ' table1 ';
          else
             
    Sql := Sql || ' table2 ';
          
    end if;
        
    open outList for Sql;
    end tom;

    end fred_pkg
    Be carefull not to concatenate string literals into into your SQL though, be sure to use bind variables if appropriate.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •