|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
September 20th, 2004, 11:48 AM
|
|||
|
|||
|
PL/SQL complicated dynamic cursor query produces error
Hi,
I'm a newbie to PL/SQL but have been thrown in at the deep end, having to make changes to a large piece of software. Part of this involves me changing a static cursor (which runs a huge query) to a dynamic one so that the field to order by is decided at run-time. The code is as follows: Code:
// Declarations- sorry, huge query I know
v_all_nom_list VARCHAR2(32767) :=
'SELECT n.obj_id obj_id,
n.telex_id telex_id,
f.facility_desc facility_desc,
s.shipper_desc shipper_desc,
l.lk_desc nomination_type,
n.effective_hour effective_hour,
n.quantity quantity,
NVL( n.approved_ind, 0 ) approved_ind,
NVL( n.verified_ind, 0 ) verified_ind,
NVL( n.superseded_ind, 0 ) superseded_ind,
NVL( n.agg_ind, 0 ) agg_ind,
NVL( n.rejected_ind, 0) rejected_ind
FROM t_lookup l,
t_nom n,
t_facility f,
t_shipper s,
t_contract c
WHERE p_cust_util.get_gas_day( n.effective_day, n.effective_hour )
= DECODE( :in_reference_id,
NULL, :in_gas_day,
p_cust_util.get_gas_day( n.effective_day, n.effective_hour ) )
AND c.contract_type_code = p_cust_con.con_CAPACITY
AND c.from_obj_type_code = p_cust_con.con_SHIPPER
AND c.from_obj_id = DECODE( :in_reference_id,
NULL, DECODE( :in_customer_id,
NULL, c.from_obj_id,
:in_customer_id ),
c.from_obj_id )
AND c.to_obj_type_code = p_cust_con.con_FACILITY
AND c.to_obj_id = DECODE( :in_reference_id,
NULL, DECODE( :in_site_id,
NULL, c.to_obj_id,
:in_site_id ),
c.to_obj_id )
AND UPPER( n.telex_id ) = DECODE( c_:in_reference_id,
NULL, UPPER(n.telex_id),
UPPER( :in_reference_id ) )
AND l.type_code = p_cust_con.con_NOM_TYPE
AND n.nomination_type = l.lk_code
AND c.from_obj_id = s.obj_id
AND c.to_obj_id = f.obj_id
AND c.obj_id = n.node_obj_id
AND n.scenario_id = p_cust_con.con_STORIT
AND n.scenario_type = p_cust_con.con_EOD
AND n.node_type_code = p_cust_con.con_CONTRACT
AND n.nomination_type = DECODE( :in_reference_id,
NULL, :in_nom_type,
n.nomination_type ) ORDER BY ';
TYPE t_nom_rec IS RECORD (obj_id t_nom.obj_id%TYPE,
telex_id t_nom.telex_id%TYPE,
facility_desc t_facility.facility_desc%TYPE,
shipper_desc t_shipper.shipper_desc%TYPE,
nomination_type t_lookup.lk_desc%TYPE,
effective_hour t_nom.effective_hour%TYPE,
quantity t_nom.quantity%TYPE,
approved_ind t_nom.approved_ind%TYPE,
verified_ind t_nom.verified_ind%TYPE,
superseded_ind t_nom.superseded_ind%TYPE,
agg_ind t_nom.agg_ind%TYPE,
rejected_ind t_nom.rejected_ind%TYPE);
TYPE t_nom_tab IS TABLE OF t_nom_rec INDEX BY BINARY_INTEGER;
v_nom_tab t_nom_tab;
TYPE t_nom_cur IS REF CURSOR;
c_nom_details t_nom_cur;
nom_rec t_nom_rec;
// In main body:
FOR i IN 1..v_nom_type.COUNT LOOP
htp.prn(v_user_nom_list);
OPEN c_nom_details FOR v_all_nom_list USING in_reference,
v_gas_day,
in_reference,
v_cust_obj,
v_cust_obj,
in_reference,
v_site_obj,
v_site_obj,
in_reference,
in_reference,
in_reference,
v_nom_type(i);
LOOP
v_err_point := '20.52';
FETCH c_nom_details INTO nom_rec;
EXIT WHEN c_nom_details%NOTFOUND;
v_err_point := '20.53';
v_nom_tab( v_count ) := nom_rec;
v_count := v_count + 1;
END LOOP;
CLOSE c_nom_details;
END LOOP;
This compiles fine, but produces the error 'ORA-00907: missing right parenthesis' at run-time. This seems to occur at the line that OPENs the cursor. However, I've checked through the string that contains the query several times and can't see a problem with it- there's the same number of opening and closing brackets, and they all seem to be in the right place. As this is the first time I've attempted to code something like this, I was hoping that somebody might spot something obvious that I've done wrong. Any help would be very much appreciated!
|
|
#2
September 21st, 2004, 06:19 AM
|
|||
|
|||
|
Hi,
Dont panic, its a normal situation.. Your query is having some error. I have faced this problem several times while working with ref cursors. Brackets in the query are ok i've checked them using a macro, you need to replace hardcoded values in the query and try to execute it, then only you'll be able to debug the problem. Make a habit of executing query before opening it for ref cursor. Hope you'll get it working now. Regards Ankur
|
|
#3
September 21st, 2004, 11:21 AM
|
|||
|
|||
|
Ankur,
many thanks for your reply. I've sorted the problem. In case anybody has a similar problem, the cause of it was this: When the SELECT statement in the string is executed dynamically at run-time, it cannot access the PL/SQL variables or constants, whether they are in the same package or not (unlike the case if the SELECT was just declared in a static cursor). My query included constants in another package, such as p_cust_con.con_STORIT. It still didn't work when I declared them in the current package. What you have to do is pass the values of any PL/SQL constants/variables as parameters, thus making the query in the string 'pure SQL', with no references to any PL/SQL. Working code: Code:
--In declaration section
v_all_nom_list VARCHAR2(32767) :=
'SELECT n.obj_id obj_id,
n.telex_id telex_id,
f.facility_desc facility_desc,
s.shipper_desc shipper_desc,
l.lk_desc nomination_type,
n.effective_hour effective_hour,
n.quantity quantity,
NVL( n.approved_ind, 0 ) approved_ind,
NVL( n.verified_ind, 0 ) verified_ind,
NVL( n.superseded_ind, 0 ) superseded_ind,
NVL( n.agg_ind, 0 ) agg_ind,
NVL( n.rejected_ind, 0) rejected_ind
FROM t_lookup l,
t_nom n,
t_facility f,
t_shipper s,
t_contract c
WHERE p_cust_util.get_gas_day( n.effective_day, n.effective_hour )
= DECODE( :in_reference_id,
NULL, :in_gas_day,
p_cust_util.get_gas_day( n.effective_day, n.effective_hour ) )
AND c.contract_type_code = :con_CAPACITY
AND c.from_obj_type_code = :con_SHIPPER
AND c.from_obj_id = DECODE( :in_reference_id,
NULL, DECODE( :in_customer_id,
NULL, c.from_obj_id,
:in_customer_id ),
c.from_obj_id )
AND c.to_obj_type_code = :con_FACILITY
AND c.to_obj_id = DECODE( :in_reference_id,
NULL, DECODE( :in_site_id,
NULL, c.to_obj_id,
:in_site_id ),
c.to_obj_id )
AND UPPER( n.telex_id ) = DECODE( :in_reference_id,
NULL, UPPER(n.telex_id),
UPPER( :in_reference_id ) )
AND l.type_code = :con_NOM_TYPE
AND n.nomination_type = l.lk_code
AND c.from_obj_id = s.obj_id
AND c.to_obj_id = f.obj_id
AND c.obj_id = n.node_obj_id
AND n.scenario_id = :con_STORIT
AND n.scenario_type = :con_EOD
AND n.node_type_code = :con_CONTRACT
AND n.nomination_type = DECODE( :in_reference_id,
NULL, :in_nom_type,
n.nomination_type ) ORDER BY '; --nb order by bit is added at runtime
-- In main body
IF ( p_cust_sec_shipper_***.isOperator( in_out_user ) ) THEN
v_err_point := '20.51';
FOR i IN 1..v_nom_type.COUNT LOOP
OPEN c_nom_details FOR v_all_nom_list USING in_reference,
v_gas_day,
p_cust_con.con_CAPACITY,
p_cust_con.con_SHIPPER,
in_reference,
v_cust_obj,
v_cust_obj,
p_cust_con.con_FACILITY,
in_reference,
v_site_obj,
v_site_obj,
in_reference,
in_reference,
p_cust_con.con_NOM_TYPE,
p_cust_con.con_STORIT,
p_cust_con.con_EOD,
p_cust_con.con_CONTRACT,
in_reference,
v_nom_type(i);
LOOP
v_err_point := '20.52';
FETCH c_nom_details INTO nom_rec;
EXIT WHEN c_nom_details%NOTFOUND;
v_err_point := '20.53';
v_nom_tab( v_count ) := nom_rec;
v_count := v_count + 1;
END LOOP;
CLOSE c_nom_details;
END LOOP;
Hope this of use to some of you
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > PL/SQL complicated dynamic cursor query produces error |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
