Link to home
Create AccountLog in
Avatar of Isaac
IsaacFlag for United States of America

asked on

PLS-00382: expression is of wrong type

I have an oracle package, see below, and I keep getting the error "PLS-00382: expression is of wrong type" but can't figure out why.  The error keeps pointing to the 2nd procedure.  The first one works fine when I compiled it by itself.  The second one does not compile when I compile it by itself or with other procedures in a package.  What am I doing wrong?
Please help.

I'm using oracle 10g

Thanks.
CREATE OR REPLACE PACKAGE BODY "SP_SELECT_DOCLOG_RPT_PKG" AS 
PROCEDURE SP_SELECT_DOCLOG_RPT_PROC (
    thisQueryType IN OUT SP_SELECT_DOCLOG_RPT_PKG.thisQuery,
    PF_STARTING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_ENDING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_STATUS IN VARCHAR2,
    PF_ASSIGNED IN OGC_TBL.ASSIGNED%TYPE
)
IS
BEGIN
OPEN thisQueryType FOR
    SELECT CASE_NUMBER, CASE_TYPE, ORIGINATOR, SUBJECT,
         DESCRIPTION, DATE_IN, DATE_OUT, DUE_DATE, ASSIGNED,
         POC_LAST_NAME||'  '||POC_FIRST_NAME FULL_NAME,
         COMMENTS, CATEGORY_CD
      FROM OGC_TBL
      WHERE (DATE_IN >= PF_STARTING_DATE AND DATE_IN <= PF_ENDING_DATE) AND
            (ASSIGNED LIKE PF_ASSIGNED)  AND ((PF_STATUS = 'A')  OR (PF_STATUS = 'C' AND DATE_OUT IS NOT NULL) OR (PF_STATUS = 'O' AND DATE_OUT IS NULL))
      ORDER BY ASSIGNED, SUBJECT, CASE_NUMBER;
END SP_SELECT_DOCLOG_RPT_PROC;
PROCEDURE SP_SUB_CONS_RPT_PROC (
    thisQueryType IN OUT SP_SELECT_DOCLOG_RPT_PKG.thisQuery,
    PF_STARTING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_ENDING_DATE IN OGC_TBL.DATE_IN%TYPE
)
IS
BEGIN
OPEN thisQueryType FOR
SELECT T1.CASE_NUMBER, T1.DATE_IN, T1.DATE_OUT, T1.SUBJECT,    
         T1.CATEGORY_CD
   FROM OGC_TBL T1
   WHERE ((T1.DATE_IN BETWEEN PF_STARTING_DATE AND PF_ENDING_DATE AND   
                     T1.DATE_OUT IS NOT NULL) OR
                    (T1.DATE_OUT BETWEEN PF_STARTING_DATE AND PF_ENDING_DATE))   
                  AND
                    (T1.SUBJECT LIKE 'CONSEN%' OR T1.SUBJECT LIKE 'SUBP%' OR 
                     T1.SUBJECT LIKE 'NON%' OR T1.CATEGORY_CD = 'CONSE' OR   
                     T1.CATEGORY_CD = 'NCONS' OR
                     T1.CATEGORY_CD = 'SUBPO')
      ORDER BY CATEGORY_CD, CASE_NUMBER, DATE_IN;
END SP_SUB_CONS_RPT_PROC;
END SP_SELECT_DOCLOG_RPT_PKG;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Isaac

ASKER

It's a reference cursor
Avatar of Sean Stuber
Sean Stuber

yes, but which type?

weak or strong?
Avatar of Isaac

ASKER

My bad...It's strong
CREATE OR REPLACE PACKAGE "SP_SELECT_DOCLOG_RPT_PKG" AS TYPE thisquery is REF CURSOR RETURN OGC_STAT_TBL%ROWTYPE;
TYPE thisquery1 is REF CURSOR;
PROCEDURE SP_SELECT_DOCLOG_RPT_PROC(
    thisQueryType IN OUT SP_SELECT_DOCLOG_RPT_PKG.thisQuery,
    PF_STARTING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_ENDING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_STATUS IN VARCHAR2,
    PF_ASSIGNED IN OGC_TBL.ASSIGNED%TYPE);
PROCEDURE SP_SUB_CONS_RPT_PROC(
    thisQueryType IN OUT SP_SELECT_DOCLOG_RPT_PKG.thisQuery,
    PF_STARTING_DATE IN OGC_TBL.DATE_IN%TYPE,
    PF_ENDING_DATE IN OGC_TBL.DATE_OUT%TYPE);                              
END SP_SELECT_DOCLOG_RPT_PKG;
/

Open in new window

Avatar of Isaac

ASKER

I see what you are saying now.  I think the solution would be to create a different package or independent stored procdeure.  Thanks.
that's the problem.

you have two different query structures the strong type can only be used with one of them.

you have to either use a weak type, or declare a second strong type for the other query
oops,  simul-posting

glad I could help though