Re: PL/SQL issue with invalid results

From: <fitzjarrell_at_cox.net>
Date: Wed, 9 Jul 2008 10:33:38 -0700 (PDT)
Message-ID: <8c5f9822-7ceb-4be3-bd13-99a32525e226@l64g2000hse.googlegroups.com>

On Jul 9, 11:43 am, JAW <jwill..._at_aglresources.com> wrote:
> I can run a query in SQPLUS and get results I need.
>
> Select col1
>
>                From tab1 sch, tab2 cmn
>
>                where
>                  and cmn.joinid = sch.joinid
>
>                  AND completion_dttm > TO_DATE('06/30/2008', 'MM/DD/
> YYYY')
>
>                  AND completion_dttm < TO_DATE('06/30/2008', 'MM/DD/
> YYYY') + 1
>
>                  and crew = 'ZZZ'
>
>                ORDER BY  completion_dttm;
>
> I have the same query in  a procedure that I pass parmss and I get no
> rows (It has a REF CUROSR).
>
> var x refcursor
> exec proc1('ZZZ','06/30/2008',:x);
> print x
>
> Below are the changes for the PL/SQL including the parameter
>
> When executed no rows are found.
>
> proc1(engineer IN VARCHAR2, whichday IN VARCHAR2, cur_out OUT
> oracle_pkg.listCursor)
>
> AND completion_dttm > TO_DATE(whichday, 'MM/DD/YYYY')
>                  AND completion_dttm < TO_DATE(whichday, 'MM/DD/YYYY')
> + 1
>                  AND crew = engineer

Did you open the ref cursor with the defined cursor:

create or replace procedure proc1(engineer IN VARCHAR2, whichday IN VARCHAR2, cur_out IN OUT oracle_pkg.listCursor) as

                            l_query varchar2(500);
begin
                            l_query := 'Select col1 From tab1 sch,

tab2 cmn where cmn.joinid = sch.joinid AND completion_dttm > TO_DATE('||whichday||', 'MM/DD/YYYY') AND completion_dttm < TO_DATE('|| whichday||', 'MM/DD/YYYY') + 1 and crew = '||engineer||' ORDER BY completion_dttm';

                            open cur_out for l_query;

end;
/

You can try that and see if it returns what you expect. A similar example using the EMP table would be:

SQL> create or replace package my_package is   2 type refcursor is ref cursor;   3
  4 procedure proc1(p_eno in number, p_cur in out refcursor);   5 end;
  6 /

Package created.

SQL>
SQL> create or replace package body my_package is   2 procedure proc1(p_eno in number, p_cur in out refcursor) as

  3                  l_query varchar2(255);
  4          begin
  5                  l_query := 'select empno, ename, deptno, sal,
hiredate from emp where empno = '||p_eno;
  6                  open p_cur for l_query;
  7          end;

  8 end;
  9 /

Package body created.

SQL>
SQL> show errors
No errors.

SQL>
SQL> variable x refcursor
SQL>
SQL> exec my_package.proc1(7369, :x)

PL/SQL procedure successfully completed.

SQL>
SQL> print x

     EMPNO ENAME DEPTNO SAL HIREDATE ---------- ---------- ---------- ---------- ---------

      7369 SMYTHE 20 800 17-DEC-80 SQL> David Fitzjarrell Received on Wed Jul 09 2008 - 12:33:38 CDT