1 2 Previous Next 15 Replies Latest reply: Nov 29, 2012 7:14 AM by Paul Horth RSS

    Procedure Doesn't return values

    user20090209
      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
          Keith Jamieson
          You need to read the FAQ. Specifically point no 7.


          SQL and PL/SQL FAQ
          • 2. Re: Procedure Doesn't return values
            BluShadow
            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
              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
                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
                  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
                    >

                    >
                    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
                      >

                      >
                      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
                             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
                          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
                            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
                              I do have exception blocks to capture exceptions. The fact is no exceptions are raised.
                              • 12. Re: Procedure Doesn't return values
                                Solomon Yakobson
                                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
                                  Keith Jamieson
                                  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
                                    >
                                    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