Is this possible???

[desNotes]

I am new to progamming in Oracle with past experience in M$ SQL server and am having problems doing the following:

I have a cursor query running that takes summary data, formats it and writes to a table. I also have another query that runs several small queries that put data into variables.

What I want to do is include the variable queries before running the cursor query but when attempting to do it, I keep getting an error that 'cursor' is found and unexpected.

any help??

thanks,

desNotes

 

[sem]

Most probably you have a syntax error (you placed executable code into declaration section). Read about pl/sql program unit structure.
Could you provide your code? As well as exact message code/text, because your retelling is quite far from original

Regards, Dima

http://seminihin.narod.ru

 

[desNotes]

Here is a shortened version of the code with the accompanying error message. I agree it is probably a syntax error...

Error Message:
ORA-06550: line 143, column 1 (SELECT COUNT(*)):
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
The symbol "begin" was substituted for "SELECT" to continue.

Query:

DECLARE
v_holidayHours0507 NUMBER := 0;



/* Holiday hours for Jul 05 */
SELECT COUNT(*)
INTO v_holidayHours0507
FROM retainedev.lei_holiday_view

WHERE dnd_date >= TO_DATE('07/01/2005','MM/DD/YYYY')
AND dnd_date <= TO_DATE('06/30/2006','MM/DD/YYYY'));



DECLARE

/* Cursor Declaration */
CURSOR c_Hours IS
SELECT res_staffno,
res_descr,
CONCAT(TO_CHAR(sbk_date, 'yy'),TO_CHAR(sbk_date, 'mm')) year_month,
crd_descr,
ROUND(sum((sbk_time/60)),1) hours


FROM booking_view
INNER JOIN resource_view ON bkg_res_id = res_id
INNER JOIN sub_booking_view ON bkg_id = sbk_bkg_id
INNER JOIN job_view ON bkg_job_id = job_id
INNER JOIN charge_code_view ON job_crd_id = crd_id


WHERE sbk_date >= TO_DATE('7/1/2005','mm/dd/yyyy')
AND sbk_date <= TO_DATE('6/30/2006','mm/dd/yyyy')

AND (bkg_res_id >= to_number('30003')
AND bkg_res_id <= to_number('30008'))

GROUP BY res_staffno,
res_descr,
CONCAT(TO_CHAR(sbk_date, 'yy'),TO_CHAR(sbk_date, 'mm')),

crd_descr

ORDER BY res_staffno,
res_descr,
year_month,

crd_descr;


BEGIN

OPEN c_Hours;

LOOP

FETCH c_Hours INTO v_staffNo, v_staffName, v_YearMonth, v_CC, v_Hours;

/* more code below*/

 

[desNotes]

I found it...amazing how things come to you when one has to present the problem to someone else. What I was doing trying to insert a query in the middle of a DECLARE. Once I moved my queries after the Cursor DECLARE statment, it runs fine.

Thank you Dima for forcing me to think

desNotes

 

[sem]

If read my answer more careful you may find that I didn't force you but rather

(you placed executable code into declaration section)

pinpointed your error

Regards, Dima

http://seminihin.narod.ru