usage of rowtype in parameter list for stored proc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: usage of rowtype in parameter list for stored proc

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    How can do I run the following stored procedure in SQL/PLUS? I need to define a variable for OUT_customer_information_rec, include it in the parameter list and then print it. (I can't find the answer in Feuerstein's book )

    Code:
    create or replace procedure get_customer_info
     ( IN_customer_nbr               IN NUMBER, 
       OUT_customer_information_rec OUT customer_information%rowtype) 
    IS
    BEGIN
    SELECT  * 
      INTO OUT_customer_information_rec
      FROM customer_information  
     WHERE customer_nbr = IN_customer_nbr;
    END;

    This does NOT work:
    Code:
    variable  c1  customer_inforamtion.rowtype;
    exec  get_customer_info (123, :c1);
    print c1;

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Do it as part of a PL/SQL block then output the results using DBMS_OUPUT:

    SET SERVEROUTPUT ON
    DECLARE
    v_c1 customer_inforamtion%rowtype
    BEGIN
    get_customer_info (123, v_c1);

    -- Output the data
    DBMS_OUTPUT.PUT_LINE('v_c1.col1: ' || v_c1.col1);
    DBMS_OUTPUT.PUT_LINE('v_c1.col2: ' || v_c1.col2);
    -- etc.
    END;
    /

    Obviously, modify the column names and the numbers of columns accordingly.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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