DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: problem with procedure

  1. #1
    Join Date
    May 2002
    Posts
    193

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    May 2002
    Posts
    193
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    May 2002
    Posts
    193
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you didnt set serveroutput on

  7. #7
    Join Date
    May 2002
    Posts
    193
    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
  •  


Click Here to Expand Forum to Full Width