Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 21st, 2004, 10:31 AM
kelvin-c kelvin-c is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 57 kelvin-c User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 25 m 54 sec
Reputation Power: 5
procedure with cursor

Code:
CREATE OR REPLACE PROCEDURE oppdatering
IS

v_kostnad NUMBER(9);

CURSOR c1 IS
SELECT timeloenn, timer, pnr, pnavn
FROM ansatt, arbeid, prosjekt;

v_c1 c1%ROWTYPE;

BEGIN 
DELETE FROM prokostnad;

OPEN c1;
LOOP
	FETCH c1 INTO v_c1;
	EXIT WHEN c1%NOTFOUND;

	IF v_c1.timeloenn IS NULL THEN
	v_c1.timeloenn := 700;
	END IF;
	
	v_kostnad := v_c1.timeloenn * v_c1.timer * 1.5;

INSERT INTO prokostnad VALUES(v_c1.pnr, v_c1.pnavn, v_kostnad);

END LOOP;
CLOSE c1;

END;
//


i dont know whats wrong in this code.
i get this which i dont understand :
Code:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/8      PLS-00341: declaration of cursor 'C1' is incomplete or malformed
7/1      PL/SQL: SQL Statement ignored
7/26     PL/SQL: ORA-00918: column ambiguously defined
10/6     PL/SQL: Item ignored
17/2     PL/SQL: SQL Statement ignored
17/16    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

20/2     PL/SQL: Statement ignored
20/5     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

LINE/COL ERROR
-------- -----------------------------------------------------------------

24/2     PL/SQL: Statement ignored
24/15    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

26/1     PL/SQL: SQL Statement ignored
26/41    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

26/46    PL/SQL: ORA-00984: column not allowed here


anyone help?

Reply With Quote
  #2  
Old October 21st, 2004, 01:02 PM
jiggee jiggee is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 27 jiggee User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 m 27 sec
Reputation Power: 0
In your cursor declaration there are 3 tables after the 'FROM' ...make the columns unambigous.. i think that might be the cause

eg : match the table aliases with the appropriate column names.
CURSOR c1 IS
SELECT at.timeloenn, at.timer, ad.pnr, pt.pnavn
FROM ansatt at, arbeid ad, prosjekt pt;

let me know if it works.

Jiggee

Reply With Quote
  #3  
Old October 21st, 2004, 01:35 PM
kelvin-c kelvin-c is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 57 kelvin-c User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 h 25 m 54 sec
Reputation Power: 5
Hello Jiggee,

It worked!
Thank you for your help !

Kelvin.

Reply With Quote
  #4  
Old April 5th, 2005, 11:46 AM
phyxsly phyxsly is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2005
Posts: 2 phyxsly User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 40 m 11 sec
Reputation Power: 0
I am happy for kevin. At least it worked for him. In my case it doesn't work. What if (please refer to kelvin's sample code) the 3 tables are from 3 different databases?

Let me give you a code snippet..
-------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE assess(STUD_ID IN VARCHAR2, STUD_YEAR IN NUMBER, STUD_TYPE IN CHAR) IS
CURSOR subj_cur IS
SELECT e.student_id, s.subject_id, s.subj_code, s.subject_title, s.units, s.pay_units,
d.department_name, s.tuition_fee_type, s.lab_fee_type, f.year, f.amount
FROM regist.enrol e, regist.offering o, regist.subject s,
system.fee_details f, regist.student st, regist.department d
WHERE (e.student_id = STUD_ID) AND (e.offering_id = o.offering_id) AND
(o.subject_id = s.subject_id) AND (f.fee_no = s.tuition_fee_type) AND
(st.student_id=e.student_id) AND ((f.year = STUD_YEAR) OR (f.year=0)) AND
(s.department_id = d.department_id);
subj_rec subj_cur%ROWTYPE;
fee_rec fee_details%ROWTYPE;
lab_rec fee_details%ROWTYPE;
------------------------------------------------------------------

I do get the same error. I couldn't fix this kind of problem. Do help me please.

Thanks.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > procedure with cursor


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway
Stay green...Green IT