Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009

    Red face Unanswered: Associative array problem


    I am facing one problem in Oracle PL/SQL. I have to pass some string values in a stored procedure. These string values will be used in the WHERE clause of a SQL query inside this stored procedure. For E.g.

    string[] strArr = new string[3] {"1","2","3"};

    SP is GetEmpNameFromID(strArr)

    In the SP we are accepting this array as associative array. The problem is we want to create a SQL query inside the SP as

    Select EMP_NAME from EMP_MASTER
    Where EMP_ID IN (Select EMP_ID from "the string array we have passed in the parameter of this SP")

    We tried accepting the strArr values in associative array in the SP, but associative array can't be used in a SQL statement.

    Is there any other way we can accept the string array passed from the application in some temporary table which can be directly used in the WHERE clause of the query.

    Please note that i cannot pass the EMP_ID as a varchar2 in the SP directly as the EMP_ID can be in thousands and may cross the 4000 limit of varchar2. Hence i am using an array to pass these varchar2 values in the SP.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >may cross the 4000 limit of varchar2
    In PL/SQL, a VARCHAR2 can be 32000+ in length.
    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.

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    But not if it is used in a sql command. then it is a max of 4000 for varchar2
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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