Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor problem

Re: Cursor problem

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 18 Jun 2004 16:00:36 -0700
Message-ID: <1087599659.174492@yasure>

Murray Sobol wrote:

> I am trying to compile the following code, but get the following error (see
> below):
>
> Here is the query:
>
> DECLARE
> v_freight1 VARCHAR2(20);
> v_freight2 VARCHAR2(20);
> v_freight3 VARCHAR2(20);
> v_freight4 VARCHAR2(20);
> v_freight5 VARCHAR2(20);
> v_freight6 VARCHAR2(20);
> v_bol VARCHAR2(20);
> dt_bol_date DATE;
> v_carrier VARCHAR2(10);
> c_flag CHAR(1);
> d_rate NUMBER(18,4);
> v_uom VARCHAR2(10);
> i_agn NUMBER;
> v_io CHAR(1);
> i_group_nbr NUMBER;
>
> CURSOR curs_group IS
> SELECT DISTINCT qe.bill_of_lading,
> qe.bill_of_lading_date,
> qe.freight_carrier_id,
> qe.rate_per_ticket_flag,
> qe.freight_charge_rate,
> qe.freight_charge_uom_code,
> fs.freight1,
> fs.freight2,
> fs.freight3,
> fs.freight4,
> fs.freight5,
> fs.freight6,
> qe.scale_ticket_inbound_outbound,
> qea.group_nbr
> FROM s1_quantity_entry qe,
> s1_quantity_entry_accrual qea,
> (SELECT qe.quantity_entry_nbr,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 1
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight1,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 2
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight2,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 3
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight3,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 4
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight4,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 5
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight5,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 6
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
> s1_quantity_entry.quantity_entry_nbr),'-1') AS freight6
> FROM s1_quantity_entry
> WHERE qe.quantity_entry_source = 'IE'
> ) AS fs
> WHERE qe.quantity_entry_source = 'IE'
> AND qe.quantity_entry_nbr = fs.quantity_entry_nbr
> AND qe.bill_of_lading IS NOT NULL
> AND qe.freight_carrier_id IS NOT NULL
> AND qe.quantity_entry_nbr = qea.quantity_entry_nbr(+)
> AND ((qe.freight_charge_rate IS NOT NULL) OR
> (fs.freight1 <> '-1') OR
> (fs.freight2 <> '-1') OR
> (fs.freight3 <> '-1') OR
> (fs.freight4 <> '-1') OR
> (fs.freight5 <> '-1') OR
> (fs.freight6 <> '-1') OR
> qea.group_nbr IS NOT NULL
> )
> ORDER BY 1,2,3,4,5,6,7;
>
> BEGIN
>
> OPEN curs_group;
>
> LOOP
> FETCH curs_group INTO v_bol,
> dt_bol_date,
> v_carrier,
> c_flag,
> d_rate,
> v_uom,
> v_freight1,
> v_freight2,
> v_freight3,
> v_freight4,
> v_freight5,
> v_freight6,
> v_io,
> i_group_nbr;
>
> EXIT WHEN curs_group%NOTFOUND;
>
> SELECT accrual_group_nbr + 1
> INTO i_agn
> FROM s1_max_key;
>
> UPDATE s1_max_key
> SET accrual_group_nbr = i_agn;
>
> UPDATE s1_quantity_entry
> SET freight_group_nbr = i_agn
> WHERE quantity_entry_nbr IN
> (SELECT qe.quantity_entry_nbr
> FROM s1_quantity_entry qe,
> (SELECT qe.quantity_entry_nbr,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 1
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight1,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 2
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight2,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 3
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight3,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 4
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight4,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 5
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight5,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 6
> AND
> s1_quantity_entry_freight_seal.quantity_entry_nbr =
> qe.quantity_entry_nbr),'-1') AS freight6
> FROM s1_quantity_entry qe
> WHERE qe.quantity_entry_source = 'IE'
> ) AS fs
> WHERE qe.quantity_entry_nbr = fs.quantity_entry_nbr
> AND qe.quantity_entry_source = 'IE'
> AND qe.bill_of_lading IS NOT NULL
> AND qe.freight_carrier_id IS NOT NULL
> AND ((i_group_nbr IS NULL
> AND NOT EXISTS (SELECT 1
> FROM s1_quantity_entry_accrual
> WHERE
> s1_quantity_entry_accrual.quantity_entry_nbr = qe.quantity_entry_nbr
> )
> ) OR (EXISTS (SELECT 1
> FROM s1_quantity_entry_accrual
> WHERE
> s1_quantity_entry_accrual.quantity_entry_nbr = qe.quantity_entry_nbr
> AND
> s1_quantity_entry_accrual.group_nbr = i_group_nbr
> )
> )
> )
> AND ((qe.freight_charge_rate IS NOT NULL) OR
> (fs.freight1 <> '-1') OR
> (fs.freight2 <> '-1') OR
> (fs.freight3 <> '-1') OR
> (fs.freight4 <> '-1') OR
> (fs.freight5 <> '-1') OR
> (fs.freight6 <> '-1') OR
> i_group_nbr IS NOT NULL
> )
> AND qe.bill_of_lading = v_bol
> AND qe.freight_carrier_id = v_carrier
> AND (dt_bol_date IS NULL OR qe.bill_of_lading_date = dt_bol_date)
> AND qe.rate_per_ticket_flag = c_flag
> AND (d_rate IS NULL OR qe.freight_charge_rate = d_rate)
> AND (v_uom IS NULL OR qe.freight_charge_uom_code = v_uom)
> AND (v_freight1 = '-1' OR fs.freight1 = v_freight1)
> AND (v_freight2 = '-1' OR fs.freight2 = v_freight2)
> AND (v_freight3 = '-1' OR fs.freight3 = v_freight3)
> AND (v_freight4 = '-1' OR fs.freight4 = v_freight4)
> AND (v_freight5 = '-1' OR fs.freight5 = v_freight5)
> AND (v_freight6 = '-1' OR fs.freight6 = v_freight6)
> AND (qe.scale_ticket_inbound_outbound = v_io)
> );
>
> END LOOP;
>
> CLOSE curs_group;
>
> END;
> /
>
> Here is the error:
>
> NVL((SELECT freight_seal_tag
> *
> ERROR at line 36:
> ORA-06550: line 36, column 18:
> PLS-00103: Encountered the symbol "SELECT" when expecting one of the
> following:
> ( - + mod not null others <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> avg
> count current exists max min prior sql stddev sum variance
> execute forall time timestamp interval date
> <a string literal with character set specification>
> <a number> <a single-quoted SQL string>
> ORA-06550: line 39, column 113:
> PLS-00103: Encountered the symbol "," when expecting one of the following:
> ; return returning and or
>
> If I am reading the above error correctly, it seems to object to the inline
> view in the cursor.
>
> Any suggestions??
>
> Thanks
> Murray Sobol
> dbcSMARTsoftware inc.

The first thing I'd suggest with respect to that cursor is that you find another approach. I can not imagine the CBO is going to handle this particularly well.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Fri Jun 18 2004 - 18:00:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US