Re: PL/SQL issue with invalid results

Thanks!

Fails in the SPROC

AND crew=engineer VARCHAR2

Works in the SPROC

AND crew='ZZZ'

Strange and I am passing it correctly.

On Jul 9, 1:33 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Received on Wed Jul 09 2008 - 13:30:58 CDT