Home » SQL & PL/SQL » SQL & PL/SQL » Code Errors- Please assist
Code Errors- Please assist [message #185428] Tue, 01 August 2006 12:37 Go to next message
anxusnewbie
Messages: 4
Registered: August 2006
Junior Member
College intern needs help!! I want to put the output into the variables that I created into a spooled file
DECLARE
pv_sales_data_seq  NUMBER(13);
pv_facility_seq   NUMBER(7);

-- v_facility_seq    NUMBER(7);
--Extra variable for second and third cursor

CURSOR c_sales_data IS
    select distinct s.facility_seq, count(s.sales_data_seq) from
    facility f, group_membership g, sales_data s
    where g.facility_seq = f.facility_seq
    and f.facility_seq = s.facility_seq
    and s.facility_seq in ('212031','275983','311777');

BEGIN
FETCH (sales_data_seq INTO pv_sales_data_seq,
  	facility_seq INTO pv_facility_seq);
IF s.facility_seq = pv_facility_seq
THEN
  DBMS_OUTPUT.ENABLE(100000);
  dbms_output.put_line('FACILITIES WITH SALES DATA' , 'pv_sales_data_seq, pv_facility_seq');
  CLOSE c_facility_sd;
 END IF;
 END;
/

[Updated on: Tue, 01 August 2006 14:00]

Report message to a moderator

Re: Code Errors- Please assist [message #185448 is a reply to message #185428] Tue, 01 August 2006 16:17 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You better explain in more detail what you are trying to do here because your code makes no sense at the moment (cursor is never opened, there is no loop, only a single fetch, comparison logic unclear, etc.).
Re: Code Errors- Please assist [message #185449 is a reply to message #185428] Tue, 01 August 2006 16:27 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First make it work, then make it fancy.

This code wouldn't compile, for beginning. Obviously, you encountered some errors. Why didn't you say which ones, but left us to guess and debug your code?

Cursor SELECT statement is invalid; did you try to run it in SQL*Plus? I guess not; it has an aggregate function (COUNT) which requires a GROUP BY clause (which you don't have). Furthermore, this fact makes use of DISTINCT unnecessary. By the way, it would be simpler to use cursor FOR loop here; you wouldn't have to declare, open, fetch and close cursor - Oracle would do it all for you.

You never opened the cursor, but are trying to fetch data from it. This won't work, not the way you put it. Also, CURSOR is to be fetched into variables, not elements from the SELECT column list.

I guess you wanted to loop through the whole cursor data set, but there's no LOOP here either.

DBMS_OUTPUT.ENABLE won't do much good if you didn't SET SERVEROUTPUT ON earlier.

DBMS_OUTPUT.PUT_LINE has wrong number of arguments.

You are closing an unopened cursor.

So, these were errors I found looking at your code. My suggestion would be: go step by step. Don't rush, as this won't work. Educate yourself; almost every line you wrote is incorrect.
Previous Topic: Range value
Next Topic: WAT IS BASIC USE OF
Goto Forum:
  


Current Time: Sat Dec 03 14:26:31 CST 2016

Total time taken to generate the page: 0.06228 seconds