Re: PL/SQL issue with invalid results

On Jul 9, 1:30 pm, JAW <jwill..._at_aglresources.com> wrote:
> 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 -- Hide quoted text -
>
> - Show quoted text -

Please note how I constructed the query string in my examples.

David Fitzjarrell Received on Wed Jul 09 2008 - 13:42:07 CDT