Avatar of Isaac
Isaac
Flag 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

Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Isaac

ASKER
It's a reference cursor
Sean Stuber

yes, but which type?

weak or strong?
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.
Sean Stuber

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
Sean Stuber

oops,  simul-posting

glad I could help though
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.