Re: Cursor Parameters?
Date: Wed, 24 Jan 2001 01:54:25 GMT
Message-ID: <lBqb6.300677$U46.9618798_at_news1.sttls1.wa.home.com>
Thad,
You are correct the statement is being interpreted as 1 item in a list not
as a list of items. To do what you want to do you will have to use execute
immediate (Oracle 8i ) or the dbms_sql package (8i and earlier) so that at
runtime it will resolve your select statement. Otherwise what you are
really saying is
select ... from ... where UNIT_UNIC in (' some string value that has one or
more commas in it and is in "'s') which is one value.
HTH,
Jim
"Thad Noles" <thadnoles_at_hotmail.com> wrote in message
news:Bpmb6.296$DB1.19906_at_e420r-atl2.usenetserver.com...
> I am writing a stored procedure in which I attempt to pass in a list of
> values (as a VARCHAR2) to be used inside of an IN construct (like this ->
> WHERE UNIT_UIC IN ('XXXXXX', 'YYYYYY', 'ZZZZZZ') ) in a cursor
declaration.
> If there is only one value, it works fine. If there is more than 1 data
> value, it returns no rows (as if maybe it is interpreting the entire list
as
> a single data value?). Has anyone tried this before, or is this something
> that just will not work inside a stored procedure? I have included the
> procedure to illustrate what I am talking about. (Note: this procedure
> doesn't have exception clauses, etc. yet. I am just seeing if this could
> work first).
>
> The procedure could be called like this:
>
> execute load_strength_prep('''WE1AAA'', ''W45VAA''', 'tjn',
> 'OFFICER_TOTALS');
>
> And here is the procedure:
> PROCEDURE LOAD_STRENGTH_PREP
> (v_unitlist IN VARCHAR2,
> v_userid IN VARCHAR2,
> v_whichtable in VARCHAR2) IS
>
>
/***************************************************************************
> ****
> NAME: LOAD_STRENGTH_PREP
> PURPOSE: To load the STRENGTH_PREP table with the relevant rows (based on
a
> list of UICs) from
> either the OFFICER_TOTALS or WO_TOTALS tables prior to
> calculating the assigned and projected values for strength
> reports.
>
> REVISIONS:
> Ver Date Author Description
> --------- ---------- --------------- ------------------------------------
> 1.0 01/19/2001 Thad Noles 1. Created this procedure.
>
> PARAMETERS:
> INPUT: v_unitlist - The list of desired unit UICs
> v_userid - The current user
> v_whichtable - Designates either OFFICER_TOTALS, WO_TOTALS, or
> BOTH (for combined reports)
> OUTPUT: v_return - Returns 0 if OK, 1 if an error occurs
> CALLED BY: OMB Application
> NOTES:
>
> ***/
>
> /* Officer Totals */
> CURSOR OffTot_cur (unitlist VARCHAR2) IS
> SELECT UNIT_UIC, CS, AUTH_COL, ODP_COL, AUTH_LTC, ODP_LTC, AUTH_MAJ,
> ODP_MAJ, AUTH_CPT, ODP_CPT, AUTH_LT, ODP_LT
> FROM OFFICER_TOTALS
> WHERE UNIT_UIC IN (unitlist);
>
> /* Warrant Officer (WO) Totals */
> CURSOR WOTot_cur (unitlist VARCHAR2) IS
> SELECT UNIT_UIC, CS1, AUTH_CW5, ODP_CW5, AUTH_CW4, ODP_CW4, AUTH_CW3,
> ODP_CW3, AUTH_CW2, ODP_CW2, AUTH_CW1, ODP_CW1
> FROM WO_TOTALS
> WHERE UNIT_UIC IN (unitlist);
>
> /* Records */
> OffTot_rec OffTot_cur%ROWTYPE;
> WOTot_rec WOTot_cur%ROWTYPE;
>
> BEGIN
> /* First clear the table for this userid */
> DELETE FROM STRENGTH_PREP WHERE USER_ID = v_userid;
> COMMIT;
> DBMS_OUTPUT.PUT_LINE('Records deleted.');
> DBMS_OUTPUT.PUT_LINE('Unit list = ' || v_unitlist);
>
> IF v_whichtable = 'OFFICER_TOTALS' OR v_whichtable = 'BOTH' THEN
> /* Open and fetch data for officer totals */
> DBMS_OUTPUT.PUT_LINE('About to open officer cursor.');
> OPEN OffTot_cur(v_unitlist);
> DBMS_OUTPUT.PUT_LINE('Officer cursor opened.');
> FETCH OffTot_cur INTO OffTot_rec;
>
> IF OffTot_cur%FOUND THEN
> DBMS_OUTPUT.PUT_LINE('First record located.');
> ELSE
> DBMS_OUTPUT.PUT_LINE('No records found.');
> END IF;
>
> WHILE OffTot_cur%FOUND
> LOOP
> /* Load STRENGTH_PREP */
> DBMS_OUTPUT.PUT_LINE(OffTot_rec.UNIT_UIC || ' ' || OffTot_rec.CS);
> INSERT INTO STRENGTH_PREP (USER_ID, UNIT_UIC, CSMOS, AUTH_COL,
> ODP_COL,
> AUTH_LTC, ODP_LTC, AUTH_MAJ, ODP_MAJ,
> AUTH_CPT, ODP_CPT, AUTH_LT, ODP_LT)
> VALUES (v_userid, OffTot_rec.UNIT_UIC, OffTot_rec.CS,
> OffTot_rec.AUTH_COL, OffTot_rec.ODP_COL, OffTot_rec.AUTH_LTC,
> OffTot_rec.ODP_LTC, OffTot_rec.AUTH_MAJ, OffTot_rec.ODP_MAJ,
> OffTot_rec.AUTH_CPT, OffTot_rec.ODP_CPT, OffTot_rec.AUTH_LT,
> OffTot_rec.ODP_LT);
> FETCH OffTot_cur INTO OffTot_rec;
> END LOOP;
> END IF;
>
> IF v_whichtable = 'WO_TOTALS' OR v_whichtable = 'BOTH' THEN
> /* Open and fetch data for warrant officer totals */
> OPEN WOTot_cur(v_unitlist);
> FETCH WOTot_cur INTO WOTot_rec;
> WHILE WOTot_cur%FOUND
> LOOP
> DBMS_OUTPUT.PUT_LINE(WOTot_rec.UNIT_UIC || ' ' || WOTot_rec.CS1);
> /* Load STRENGTH_PREP */
> INSERT INTO STRENGTH_PREP (USER_ID, UNIT_UIC, CSMOS, AUTH_CW5, ODP_CW5,
> AUTH_CW4, ODP_CW4, AUTH_CW3, ODP_CW3,
> AUTH_CW2, ODP_CW2, AUTH_CW1, ODP_CW1)
> VALUES (v_userid, WOTot_rec.UNIT_UIC, WOTot_rec.CS1,
> WOTot_rec.AUTH_CW5, WOTot_rec.ODP_CW5, WOTot_rec.AUTH_CW4,
> WOTot_rec.ODP_CW4, WOTot_rec.AUTH_CW3, WOTot_rec.ODP_CW3,
> WOTot_rec.AUTH_CW2, WOTot_rec.ODP_CW2, WOTot_rec.AUTH_CW1,
> WOTot_rec.ODP_CW1);
> FETCH WOTot_cur INTO WOTot_rec;
> END LOOP;
> END IF;
>
> END LOAD_STRENGTH_PREP;
>
>
>
>
>
Received on Wed Jan 24 2001 - 02:54:25 CET