This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 29, 2012 5:13 AM by KeithJamieson RSS

Procedure Doesn't return values

user20090209 Newbie
Currently Being Moderated
Hello,

I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
CREATE OR REPLACE PROCEDURE check_values (
     my_values            IN          emp.dept_no%TYPE,
     p_cursor        OUT sys_refcursor
)
AS
     quoteValues     VARCHAR2 (256);
BEGIN
     SELECT     '''' || REPLACE(my_values, ',', ''',''') || ''''
       INTO     quoteValues
       FROM     DUAL;

     OPEN p_cursor  FOR
          SELECT          emp_no,
                         emp_name,
            FROM     emp
           WHERE     dept_no IN (quoteValues);
END check_values;
/
The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving
my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.

How can I resolve this issue?

Thanks
  • 1. Re: Procedure Doesn't return values
    KeithJamieson Expert
    Currently Being Moderated
    You need to read the FAQ. Specifically point no 7.


    SQL and PL/SQL FAQ
  • 2. Re: Procedure Doesn't return values
    BluShadow Guru Moderator
    Currently Being Moderated
    user20090209 wrote:
    Hello,

    I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         SELECT     '''' || REPLACE(my_values, ',', ''',''') || ''''
           INTO     quoteValues
           FROM     DUAL;
    
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (quoteValues);
    END check_values;
    /
    The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving
    my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.

    How can I resolve this issue?
    By not trying to do what your doing. I know that sounds flippant, but it's just the wrong way to go about it.

    You cannot just use a single string of comma delimited values inside an IN clause, as it just sees that as a single string value, not multiple values.
    You could write the query dynamically and open the refcursor based on that dynamic string, but still that's a poor way of doing it.
    Ideally the individual values should be stored in a table (global temporary table perhaps?) and the query should simply make a join on that table to reference the values.
  • 3. Re: Procedure Doesn't return values
    ranit B Expert
    Currently Being Moderated
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         SELECT     '''' || REPLACE(my_values, ',', ''',''') || ''''
           INTO     quoteValues
           FROM     DUAL;
    
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (quoteValues);
    END check_values;
    /
    1} Please remove the extra comma from OPEN p_cursor... statement

    2} I assume DEPT_NO is number, then why are you putting the numbers in quotes??
    dept_no IN (1,2,3,4)
    /* OR */
    dept_no IN ('1','2','3','4')
    Please fix accordingly.

    Most Important !!! - Seems you haven't executed this code even once before posting.

    Hope this Helps.
    Ranit B.

    Edited by: ranit B on Nov 29, 2012 6:09 PM
  • 4. Re: Procedure Doesn't return values
    Paul Horth Expert
    Currently Being Moderated
    user20090209 wrote:
    Hello,

    I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         SELECT     '''' || REPLACE(my_values, ',', ''',''') || ''''
           INTO     quoteValues
           FROM     DUAL;
    
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (quoteValues);
    END check_values;
    /
    The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving
    my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.

    How can I resolve this issue?

    Thanks
    Read the FAQ {message:id=9360009}
  • 5. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    where condition which is dept_no is character, that is the reason why I have put single quotes.

    Thanks
  • 6. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    >

    >
    Most Important !!! - Seems you haven't executed this code even once before posting.

    Hope this Helps.
    Ranit B.

    Edited by: ranit B on Nov 29, 2012 6:09 PM
    I have executed.
  • 7. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    >

    >
    You cannot just use a single string of comma delimited values inside an IN clause, as it just sees that as a single string value, not multiple values.
    You could write the query dynamically and open the refcursor based on that dynamic string, but still that's a poor way of doing it.
    Ideally the individual values should be stored in a table (global temporary table perhaps?) and the query should simply make a join on that table to reference the values.
    When I print before the where clause, it has correctly formatted the way I want. e.g.
    '2345','1245','9076'
    So why then it is not getting executed? if I hard code the above values in where condition it executes fine.
  • 8. Re: Procedure Doesn't return values
    ranit B Expert
    Currently Being Moderated
         OPEN p_cursor FOR
              SELECT          emp_no,
                             emp_name,
              FROM     emp
              WHERE     dept_no IN (quoteValues);
    Here, the quoteValues is actually coming as a string and not a set of values.

    Please try...
    v_INSERT_stmt VARCHAR2(250Char);
    
    v_INSERT_stmt := '
    OPEN p_cursor  FOR
               SELECT          emp_no,emp_name,
                 FROM     emp
                WHERE     dept_no IN (:1)';
    
    EXECUTE IMMEDIATE v_INSERT_stmt USING quoteValues;
    Edited by: ranit B on Nov 29, 2012 6:34 PM
  • 9. Re: Procedure Doesn't return values
    Paul Horth Expert
    Currently Being Moderated
    user20090209 wrote:
    >
    You cannot just use a single string of comma delimited values inside an IN clause, as it just sees that as a single string value, not multiple values.
    You could write the query dynamically and open the refcursor based on that dynamic string, but still that's a poor way of doing it.
    Ideally the individual values should be stored in a table (global temporary table perhaps?) and the query should simply make a join on that table to reference the values.
    When I print before the where clause, it has correctly formatted the way I want. e.g.
    '2345','1245','9076'
    So why then it is not getting executed? if I hard code the above values in where condition it executes fine.
    Have you actually bothered reading the FAQ?
  • 10. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    ranit B wrote:
         OPEN p_cursor FOR
              SELECT          emp_no,
                             emp_name,
              FROM     emp
              WHERE     dept_no IN (quoteValues);
    Here, the quoteValues is actually coming as a string and not a set of values.
    When I print it comes has
    WHERE dept_no IN  ('2345','1245','9076')
    But no results when procedure is executed, and no excpetions raised.
  • 11. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    I do have exception blocks to capture exceptions. The fact is no exceptions are raised.
  • 12. Re: Procedure Doesn't return values
    Solomon Yakobson Guru
    Currently Being Moderated
    user20090209 wrote:
    So why then it is not getting executed? if I hard code the above values in where condition it executes fine.
    Look at your WHERE clause:
    WHERE     dept_no IN (quoteValues);
    How many expressions there are in IN list? Right, only one. So no matter what the value of quoteValues is, that WHOLE value will be compared to dept_no. What you are trying to do can be done via dynamic SQL, LIKE operator or collection.

    Dynamic SQL:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              'SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (' || my_values || ')';
    END check_values;
    / 
    LIKE operator:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     ',' || my_values || ',' LIKE '%,' || dept_no || ',%';
    END check_values;
    / 
    Collection:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          sys.OdciVarchar2List,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (SELECT * FROM TABLE(my_values));
    END check_values;
    / 
    and pass my_values as sys.OdciVarchar2List('2345','1245','9076').

    SY.
  • 13. Re: Procedure Doesn't return values
    KeithJamieson Expert
    Currently Being Moderated
    When I print it comes has
    WHERE dept_no IN  ('2345','1245','9076')
    But no results when procedure is executed, and no excpetions raised.
    Because what you are passing to the parser is a single string:
    SQL> set serverout on
    SQL>
    SQL> declare
      2  v_string varchar2(100) := '2345'||','||'1245'||','||'9076';
      3  begin
      4  dbms_output.put_line(v_string);
      5  end;
      6  /
    2345,1245,9076
    
    PL/SQL procedure successfully completed.
    
    SQL>
    So the string '2345,1245,9076' is what the parser is looking for:

    What you want the parser to look for is:

    '2345','1245','9076'

    If you put the value '2345,1245,9076' in your data as a dept_no , it will "magically" return a result.
  • 14. Re: Procedure Doesn't return values
    user20090209 Newbie
    Currently Being Moderated
    >
    WHERE     dept_no IN (quoteValues);
    How many expressions there are in IN list? Right, only one. So no matter what the value of quoteValues is, that WHOLE value will be compared to dept_no. What you are trying to do can be done via dynamic SQL, LIKE operator or collection.

    Dynamic SQL:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              'SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (' || my_values || ')';
    END check_values;
    / 
    LIKE operator:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          emp.dept_no%TYPE,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     ',' || my_values || ',' LIKE '%,' || dept_no || ',%';
    END check_values;
    / 
    Collection:
    CREATE OR REPLACE PROCEDURE check_values (
         my_values            IN          sys.OdciVarchar2List,
         p_cursor        OUT sys_refcursor
    )
    AS
         quoteValues     VARCHAR2 (256);
    BEGIN
         OPEN p_cursor  FOR
              SELECT          emp_no,
                             emp_name,
                FROM     emp
               WHERE     dept_no IN (SELECT * FROM TABLE(my_values));
    END check_values;
    / 
    and pass my_values as sys.OdciVarchar2List('2345','1245','9076').

    SY.
    Thanks a lot, dynamic sql has worked like a charm, I will try other options you have mentioned. Much appreciated.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points