Re: PL/SQL issue with invalid results

On Jul 9, 2:42 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I found the problem as I did not expected it.

In the table CREW is CHAR(8) and the i_engineer is VARCHAR2.

I changed the parameter to CHAR and it worked!

Thanks for the help.

Calling these SPROC's from DOTNET code which is why I need the REF cursor. Received on Wed Jul 09 2008 - 13:55:42 CDT