How to Pass PL/SQL Table as Input Parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to Pass PL/SQL Table as Input Parameter

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Does any one Know How to Execute a Procedure in SQL *Plus That takes a PL/SQL Table as Input Parameter.

    The Records was Declatred as
    Myrec (Id emp.empno%Type, name emp.ename%Type);

    PL/SQL Table of this Record Type.

    Please let me know how this Proc be Executed.

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    use this:
    create or replace package package_demo
    is
    type EmpRecType is RECORD (ID EMP.EMPNO%type,
    NAME EMP.ENAME%type);

    type EmpTabType is TABLE of EmpRecType;

    procedure call_getRecords;

    procedure getRecords(emp_table EmpTabType);

    end package_demo;
    /

    create or replace package body package_demo
    is
    employees EmpTabType;

    procedure call_getRecords
    is
    begin
    getRecords(employees);
    end call_getRecords;

    procedure getRecords(emp_table in EmpTabType)
    is
    begin
    null;
    -- perform all you want here...
    end getRecords;

    end package_demo;

    now you can use in SQL*Plus:
    execute package_demo.call_getRecords;
    or use just package_demo.call_getRecords in a begin-end block.

    Good Luck.

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Thanks Rotem,

    It was really help ful, but my question is how to Execute the Procedure getRecords That you have mentioned in the package.

    I don't want to use the Other Procedure just for execution of the Main.

    Please let me know how would I be able to execute the Procedure getRecords That takes parameter Emp_Tab as Input parameter...

    Declare
    What ?
    Begin
    Package_Demo.getRecords(WHAT????)
    End;


    Please let me know.... Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hey,
    In order to call the package_demo.getRecords procedure simply declare a PL/SQL table type and send it as parameter to the procedure.
    Just use :

    declare
    employees EmpTabType;
    begin
    package_demo.getRecords(employees);
    end;

    you can fill the PL/SQL Table with values before you send it to the procedure in these matters:

    1) if you are using 8i version use a select clause with bulk collect :

    select empno,ename bulk collect into employees from emp;

    2) if you are using 8.0.x version loop over the emp table with a cursor and insert values into employees table.

    for i in 1..count(emp) LOOP
    fetch cur into employees(i);
    end LOOP;

  5. #5
    Join Date
    Nov 2009
    Posts
    1

    Hi

    sorry, im new to this area.

    can u guide me how to retrive data from the TYPE ?

    procedure getRecords(emp_table in EmpTabType)
    is
    begin
    null;
    -- perform all you want here...end getRecords;


    wat all operations can be performed here

Posting Permissions

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



Click Here to Expand Forum to Full Width