| column select in ref cursor [message #38223] |
Wed, 03 April 2002 07:04  |
Judy
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
I am getting a PLS-00103 error (Encountered the symbol 'select'...) when using the following code. It works find when executed in sql*plus. I am using ref cursors.
Can you please help me out !
CURSOR C_LS IS
SELECT mpo.div, hi.hr_id, mpo.mpo_number mpo, hi.mhr_qty mqty, ss.ship_sum_id, mpo.order_qty ord_qty, ss.carton_count ord_ctn,
RTRIM(mpl.po_header_po_number) cpo, ss.ship_mode smode, TO_CHAR(ss.eta_date, 'DD-MON-RR') eta,
TO_CHAR(mpo.anticipated_ndc, 'DD-MON-RR') ndc, TO_CHAR(mpo.cust_cancel_date, 'DD-MON-RR') can,
mpo.country, ss.hawb, ss.arriving_mawb mawb, ss.arriving_obol obol, ss.vessel, ss.dock_receipt_num drn, ss.fcr,
ss.last_event_code lev,
(SELECT SUM((ending_carton_num-beginning_carton_num+1)*log_item.qty)
FROM hl.log_event, hl.log_carton, hl.log_item
WHERE log_event.LOG_EVENT_TYPE_CODE In ('EV3','EV4')
AND log_event.SHIP_SUM_ID <> 1
AND log_event.LOG_EVENT_ID = log_carton.log_event_id
AND log_carton.LOG_CARTON_ID = log_item.log_carton_id
AND log_event.SHIP_SUM_ID = ss.ship_sum_id
GROUP BY log_event.MPO_id, log_event.ship_sum_id, log_item.color_code) shp_qty
FROM hl.ship_sum ss, mastdbo.vw_mpo2 mpo, hl.hr_mpo hi, mastdbo.vw_mpoline mpl
WHERE ss.div = upper(p_div)
AND ss.div = mpo.div
AND ss.mpo = mpo.mpo_number
AND ss.div = hi.div(+)
AND ss.mpo = hi.mpo(+)
AND ss.div = mpl.div_code
AND ss.mpo = mpl.mpo_number ;
TYPE T_LS IS REF CURSOR RETURN C_LS%ROWTYPE ;
|
|
|
|
| Re: column select in ref cursor [message #38228 is a reply to message #38223] |
Wed, 03 April 2002 08:48  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
This is one of those cases where PL/SQL does not support all of the SQL syntax (in 9i, PL/SQL fully supports all SQL syntax).
Specifically, it is the (SELECT ...) in your SELECT list that is complaining. Here's a demo using a simpler case:
sql>select (select sysdate from dual) d from dual;
D
---------
03-APR-02
sql>declare
2 d date;
3 begin
4 select (select sysdate from dual)
5 from dual
6 into d;
7 end;
8 /
select (select sysdate from dual)
*
ERROR at line 4:
ORA-06550: line 4, column 11:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the
following:
The alternative is to use dynamic SQL (which is not subject to the PL/SQL limitations):
declare
type rc is ref cursor;
x rc;
d date;
begin
open x for 'select (select sysdate from dual) from dual'; -- note the single quotes around the statement
fetch x into d;
dbms_output.put_line( d );
close x;
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
