passing array as IN and OUT parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: passing array as IN and OUT parameter

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    passing array as IN and OUT parameter

    Hi Friends,
    I have a stored proc which has 5 parameters, 3 IN parameters and 3 as OUT parameter. One of the IN parameter is and array and 2 of the OUT parameter is also an array.
    Can u please help, how i can execute this procedure by passing the array as IN and OUT paramter.

    My stored proc is as follows

    CREATE OR REPLACE PROCEDURE test
    (
    p_ctr_arr IN listings_package.CharArrayTyp,
    p_listings IN INTEGER,
    p_mktplace IN VARCHAR2,
    p_kount_array OUT listings_package.CounterTabTyp,
    p_eday_array OUT listings_package.EbayTabTyp,
    p_error_code OUT NUMBER )
    IS

    i am using this pl/sql to execute the above stored proc...but it does not return any dbms_output.put_line messages in the main stored proc

    DECLARE
    p_ctr_arr listings_package.CharArrayTyp;
    p_kount listings_package.CounterTabTyp;
    p_eday_array listings_package.EbayTabTyp;
    p_err number;
    BEGIN

    for k in 1..p_ctr_arr.count loop
    p_ctr_arr(1) :='1234';
    p_ctr_arr(1) :='2484761451';
    multi_read_listings_v2 (p_ctr_arr,200,'eday',p_kount,p_eday array,p_err);
    dbms_output.put_line(p_ctr_arr(k));
    dbms_output.put_line(p_err);
    end loop;
    end;

    PL/SQL procedure successfully completed.
    anandkl

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Talking

    Do you think maybe because p_ctr_arr.count is = 0?

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    If p_ctr_arr= 0 , then i would get NO_DATA_FOUND error.

    Can u please let me know if i writtne the below pl/sql...in the correct way

    DECLARE
    p_ctr_arr listings_package.CharArrayTyp;
    p_kount listings_package.CounterTabTyp;
    p_eday_array listings_package.EbayTabTyp;
    p_err number;
    BEGIN

    for k in 1..p_ctr_arr.count loop
    p_ctr_arr(1) :='1234';
    p_ctr_arr(1) :='2484761451';
    multi_read_listings_v2 (p_ctr_arr,200,'eday',p_kount,p_eday array,p_err);
    dbms_output.put_line(p_ctr_arr(k));
    dbms_output.put_line(p_err);
    end loop;
    end;


    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Question

    Then, where are you setting the value for this statement?:

    for k in 1..p_ctr_arr.count loop

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Inside the loop..
    p_ctr_arr(1) :='1234';
    p_ctr_arr(1) :='2484761451';

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Thumbs down

    Originally posted by anandkl
    Inside the loop..
    p_ctr_arr(1) :='1234';
    p_ctr_arr(1) :='2484761451';
    Wrong! For the loop to execute as written in your code, p_ctr_arr.count must have already a value > 0 and the p_ctr_arr already be loaded with that number of items.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Did you do this in sqlplus?

    set serveroutput on

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