Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: How to pass an Array of variable to a query and get the recordset as the output.

    Hi Guys,

    Am new to Oracle Stored procedures..
    I am looking forward to pass an array of variable values to a query and get the recordset as the output.

    Any Guidance is highly appreciated.

    Thanks for your help and time in advance

    Regards,
    Magesh

  2. #2
    Join Date
    Jul 2009
    Posts
    58
    I was able to write the below code and compile and run successfully. But i am not able to see the record set. Can somebody point out what i am missing.. The code and output is as follows
    CREATE OR REPLACE PROCEDURE GETDATA
    (
    --P_CATG_LIST IN VARRAY
    p_recordset OUT SYS_REFCURSOR
    ,p_status out VARCHAR2
    ) AS
    --
    --

    -- Variable Declaration Section
    Declare
    V_Status Varchar2(1);
    V_message varchar2(100);
    P_CATG_LIST INTEGER ;
    v_Country STG_COUNTRY_T.N_CNTRY%TYPE ;
    v_Country_Upper STG_COUNTRY_T.M_CNTRY_UPPER%TYPE;
    --
    BEGIN
    --
    -- Get the Weekly Sales
    v_Status :='S';
    P_CATG_LIST := 268435457 ;
    OPEN p_recordset FOR SELECT X.country as Country,
    X.CouNTRY_UPPER as Country_Upper
    FROM
    (
    SELECT A.N_CNTRY as Country,
    A.M_CNTRY_UPPER as Country_Upper
    FROM STG_COUNTRY_T A
    WHERE ROWNUM <100 and a.m_cntry = P_CATG_LIST ) X ;




    ---
    --- Getting to this point we know we have not had any errors, so set the Status to Sucess
    --- set fetch status
    v_Status :='S';
    v_message :='Success';
    p_Status := v_Status;
    --
    --
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN NULL;
    WHEN OTHERS
    THEN
    -- Relgradless of any error set the status to Error "E"
    --
    v_Status := 'E';
    v_message := substr(SQLERRM,1,100);
    RAISE_APPLICATION_ERROR(-20000, 'GetAllocationsRS');
    END
    GETDATA;
    Output:
    P_STATUS = S
    Process exited.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am looking forward to pass an array of variable values to a query and get the recordset as the output.
    SQL and PL/SQL are TOTALLY different languages & processed by different "engines".
    What may be a valid datatype in 1 language is not necessarily valid in the other language.
    An array is valid in PL/SQL but not in SQL.

    Please post URL to Oracle documentation where "recordset" datatype is defined.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2009
    Posts
    58
    I am using SYS_REFCURSOR for the record i want to display. I am using Oracle database. I hope my code will tell you what i am trying to do.. cheers

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But i am not able to see the record set
    PL/SQL runs deep inside Oracle RDBMS engine.
    What occurs inside PL/SQL stays inside PL/SQL unless & until you take action to make it visible;
    like using DBMS_OUTPUT

    I suggest the consider eliminating the EXCEPTION handler
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2009
    Posts
    58
    thanks for your suggestion.. I will look into it. I like to get the p_recordset (SYS_REFCURSOR) as a output. Eventhough, i have declared it as the output, i am not able to get the output. I even tried
    Return p_recordset
    statement after the open statement. But it is throwing an error as the Return statement cannot have this variable.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But it is throwing an error as the Return statement cannot have this variable.
    RETURN is ONLY valid as part of a FUNCTION & never as part of a PROCEDURE.

    When all else fails, Read The Fine Manual
    Contents
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Feb 2005
    Posts
    57
    You haven't posted how you are calling your procedure! Are you using SQL*Plus, SQL Developer, TOAD or what?? What variable have you created to accept your OUT parameter?

Posting Permissions

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