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)ISBEGINOPEN 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)ISBEGINOPEN thisQueryType FORSELECT 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;
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.
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;/
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