|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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? ![]() |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
Hello Jiggee,
It worked! Thank you for your help ! ![]() Kelvin. |
|
#4
|
|||
|
|||
|
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. |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > procedure with cursor |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|