-
problem with procedure
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure xcal_test1.missingrecords
2 is
3 clipsessionid_temp clipsession.clipsessionid%type;
4 articleno_temp mediaitem.articleno%type;
5 Cursor c2 is select cs.clipsessionid, mi.articleno
6 FROM mediaitemelectronicpage mip,
7 mediaitem mi,
8 clipsession cs,
9 clipping c,
10 media m
11 WHERE cs.clipsessionid= c.clipsessionid
12 AND c.mediaitemid= mi.mediaitemid
13 AND mi.mediaitemid= mip.mediaitemid(+)
14 AND mi.mediano= m.mediano
15 AND cs.clipsessionid in(select CLIPSESSIONID from clipsession where sessionstatusid in (9,18,2
16 and mip.SOURCEPATH is null
17 order by mi.articleno;
18 begin
19 open c2;
20 while c2%found
21 loop
22 fetch c2 into clipsessionid_temp, articleno_temp;
23 -- dbms_output.put_line('Cursor2');
24 dbms_output.put_line('select count(*) from missingarticles;');
25 End Loop;
26 dbms_output.put_line('C2');
27 while c2%Notfound
28 loop
29 fetch c2 into clipsessionid_temp, articleno_temp;
30 dbms_output.put_line('select count(*) from missingarticles;');
31 end loop;
32 Close c2;
33* End missingrecords;
SQL> /
Procedure created.
SQL> exec procedure xcal_test1.missingrecords
BEGIN procedure xcal_test1.missingrecords; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the
following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe
The symbol "PROCEDURE" was ignored.
SQL>
--------------------------------
Could anyone correct me where Iam wrong in the above code.
Iam basically trying to create a cursor and depending upon the output/result, do the two different activities!
Regards,
K.Diwakar
-
exec xcal_test1.missingrecords
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
Thank you for the reply that has helped me to go to the next step!
Please clarify the same again in the code below:
create or replace procedure missingrecords
is
c_missingarticles number;
clipsessionid_temp clipsession.clipsessionid%type;
articleno_temp mediaitem.articleno%type;
Cursor c2 is select cs.clipsessionid, mi.articleno
FROM mediaitemelectronicpage mip,
mediaitem mi,
clipsession cs,
clipping c,
media m
WHERE cs.clipsessionid= c.clipsessionid
AND c.mediaitemid= mi.mediaitemid
AND mi.mediaitemid= mip.mediaitemid(+)
AND mi.mediano= m.mediano
AND cs.clipsessionid in(select CLIPSESSIONID from clipsession where sessionstatusid in (9,18,21,23,29,33,34,35,36))
and mip.SOURCEPATH is null
order by mi.articleno;
begin
open c2;
while c2%found
loop
fetch c2 into clipsessionid_temp, articleno_temp;
Dbms_output.put_line(clipsessionid_temp); -- dbms_output.put_line('Cursor2');
--dbms_output.put_line('select count(*) from missingarticles;');
End Loop;
-- dbms_output.put_line('C2');
while c2%Notfound
loop
fetch c2 into clipsessionid_temp, articleno_temp;
-- dbms_output.put_line('select count(*) from missingarticles;');
dbms_output.put_line(clipsessionid_temp);
end loop;
Close c2;
End missingrecords;
/
Now the line in bold does not give me the value when I execute the procedure:
SQL> exec missingrecords;
-------Dont see the value of clipsessionid_temp
PL/SQL procedure successfully completed.
SQL>
Thank you very much.
Regards,
K.Diwakar
Last edited by diwakar; 12-07-2007 at 07:28 AM.
-
Your WHILE statement has several problems.
1- You are testing for NotFound instead of testing for Found.
2- Even if you test for Found, since you have not attempted yet to Fetch a row the returned value would be NULL, not TRUE as you want it to be.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
Thank You for the reply.
Now I think I should give the whole scenario.
I also suppose I need to declare a cursor for
select CLIPSESSIONID from clipsession where sessionstatusid in (9,18,21,23,29,33,34,35,36)
as the outer select statement(cursor C2) executes for every value of the above select statement in bold!
Now comes my doubt:
How can you pass the value returned by one cursor to another?
And also as you have mentioned and for the two results: %found and %notfound, more calculations need to be done - I have just tried to see if the loop works!
Kindly suggest a way out for:
passing every value returned by the cursor for the statement:
select CLIPSESSIONID from clipsession where sessionstatusid in (9,18,21,23,29,33,34,35,36)
to the cursor
C2
Thank you,
Regards,
K.Diwakar
-
you didnt set serveroutput on
-
This has been set to the on position:
set serveroutput on
so could you please clarify on how to pass one cursor value to another please?
Regards,
K.Diwakar
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|