Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Unanswered: PL/SQL procedure problem

    Hello,

    I am using Oracle 9i on Win2K. Here is the stored procedure:

    CREATE OR REPLACE PROCEDURE SCREENTAGS (CurrentPage in varchar2, CurLanguage in varchar2)
    IS
    BEGIN
    DECLARE

    CURSOR c1 IS
    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.Screenid = TRIM(CurrentPage) and
    b.Lang = TRIM(CurLanguage) and
    a.refno = b.refno;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Begin the procedure: 2 input parameters are: ' || CurrentPage || ',' || CurLanguage || '.');
    open c1;
    loop
    FETCH c1 into v_tempRecord;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
    ('Screentags for ' || v_tempRecord.screenid || ' are ' ||
    v_tempRecord.tagtext || ' .');
    end loop;
    DBMS_OUTPUT.PUT_LINE('Retrieved number of records is: ' || c1%ROWCOUNT || '.');
    DBMS_OUTPUT.PUT_LINE('end cursor loop');
    close c1;

    End;
    End SCREENTAGS;
    /
    SHOW ERRORS

    Can anyone please tell me where am I wrong? It is compiled with no error, and execute with no error, just there is no record retrieved, while it should be. Because if I hardcode the parameters, it works.

    I'd appreciate your ideas.

    christine

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: PL/SQL procedure problem

    I think there are a few possiblities for your problem.
    If you use a named procedure, do not use the DECLARE keyword

    Try this procedure and see if it works

    CREATE OR REPLACE PROCEDURE SCREENTAGS (CurrentPage in varchar2, CurLanguage in varchar2)
    IS
    BEGIN

    -- Define cursor. Use local variable
    CURSOR c1(c_Page Varchar, c_Lang varchar) IS
    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno
    and a.Screenid = TRIM(c_Page)
    and b.Lang = TRIM(c_Lang);

    -- Define Record to fetch cursor into
    v_tempRecord c1%rowtype;

    BEGIN
    DBMS_OUTPUT.PUT_LINE('Begin the procedure: 2 input parameters are: ' ||
    CurrentPage || ',' || CurLanguage || '.');

    OPEN c1(CurrentPage,CurLanguage);
    LOOP
    FETCH c1 into v_tempRecord;
    EXIT WHEN c1%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE ('Screentags for ' ||
    v_tempRecord.screenid || ' are ' ||
    v_tempRecord.tagtext || ' .');
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Retrieved number of records is: ' || c1%ROWCOUNT || '.');
    DBMS_OUTPUT.PUT_LINE('end cursor loop');

    CLOSE c1;

    END;

  3. #3
    Join Date
    Jul 2003
    Posts
    78

    Unhappy but it does not work

    thank you for your reply. Actually I've almost tried every combination, including pass parameters to the cursor. None of them work. What else possibility could be?

    thanks again,
    christine

  4. #4
    Join Date
    Aug 2003
    Posts
    41
    Is it displaying the first line?


    DBMS_OUTPUT.PUT_LINE('Begin the procedure: 2 input parameters are: ' ||
    CurrentPage || ',' || CurLanguage || '.');


    Also, what are the messages you get when you execute the procedure?

    -Sunil

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: but it does not work

    if you run the sql statement from the cursor(in sql-plus) hard coding the parameters

    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno
    and a.Screenid = TRIM('XXX')
    and b.Lang = TRIM('YYY');

    and you get data back

    but when you pass the values into the cursor you get no rows then there is something wrong with the way you are passing in the values. Can you show how you are passing values into the procedure, and how they are passed into the cursor.

    Originally posted by christine2003
    thank you for your reply. Actually I've almost tried every combination, including pass parameters to the cursor. None of them work. What else possibility could be?

    thanks again,
    christine

  6. #6
    Join Date
    Jul 2003
    Posts
    78

    one of the test is just like what you did

    CREATE OR REPLACE PROCEDURE SCREENTAGS (CurrentPage in varchar2,CurLanguage in varchar2)
    IS
    CURSOR c1(v_curPage varchar, v_curLang varchar) IS
    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno and
    a.Screenid = TRIM(v_curPage) and
    b.Lang = TRIM(v_curLang);


    v_tempRecord c1%ROWTYPE;

    BEGIN
    DBMS_OUTPUT.PUT_LINE('Begin the procedure: 2 input parameters are: ' || CurrentPage || ',' || CurLanguage || '.');
    -- DBMS_OUTPUT.PUT_LINE('Parameters passed to cursor are : ' || v_curPage || ',' || v_curLang || '.');

    -- open c1;
    open c1(CurrentPage, CurLanguage);

    loop
    DBMS_OUTPUT.PUT_LINE('we are inside loop...');
    FETCH c1 into v_tempRecord;
    DBMS_OUTPUT.PUT_LINE('after fetch c1 into v_tempRecord...');
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
    ('Screentags are ' || v_tempRecord.tagid || ' , ' ||
    v_tempRecord.tagtext || ' .');
    end loop;
    DBMS_OUTPUT.PUT_LINE('Retrieved number of records is: ' || c1%ROWCOUNT || '.');
    DBMS_OUTPUT.PUT_LINE('end cursor loop');
    close c1;

    End SCREENTAGS;
    /
    SHOW ERRORS

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: one of the test is just like what you did

    I still see the DECLARE keyword in your procedure. When you name a Stored procedure, you do not use DECLARE.

    Declare is the keyword for creating anonyomous blocks.

    So remove the Declare, and then call your procedure with this block of code.

    replacing the XXX and YYY with your input values

    Begin
    SCREENTAGS('XXX','YYY');
    End;

  8. #8
    Join Date
    Jul 2003
    Posts
    78

    Question where did I use 'DECLARE'?

    I'm sorry, but I didn't use 'DECLARE' in the above test???

  9. #9
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: where did I use 'DECLARE'?

    My bad, I must have been looking at a different version of your code.

    are you calling the procedure passing in your values like the example I provided?

  10. #10
    Join Date
    Jul 2003
    Posts
    78

    Unhappy Here is what I did in SQL*PLUS

    SQL> EXEC SCREENTAGS('logon', 'E')

    That's it.
    You know, one thing very strange, I tried to debug the procedure in JDeveloper, when it steps into the cursor, the type displayed in the debug window is unknown. I am so confused.

    thanks,
    christine


    Originally posted by carloa
    My bad, I must have been looking at a different version of your code.

    are you calling the procedure passing in your values like the example I provided?

  11. #11
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Here is what I did in SQL*PLUS

    So when you run

    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno and
    a.Screenid = TRIM('logon') and
    b.Lang = TRIM('E');

    you get the correct data?

  12. #12
    Join Date
    Jul 2003
    Posts
    78

    Re: Here is what I did in SQL*PLUS

    correct, except I didn't put TRIM before the string.

    Originally posted by carloa
    So when you run

    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno and
    a.Screenid = TRIM('logon') and
    b.Lang = TRIM('E');

    you get the correct data?

  13. #13
    Join Date
    Jul 2003
    Posts
    78

    I also tried to put '''' to enclose the variables

    a.Screenid = '''' ||v_curPage|| '''' and
    b.Lang = '''' ||v_curLang|| ''''


    Originally posted by christine2003
    correct, except I didn't put TRIM before the string.

  14. #14
    Join Date
    Jul 2003
    Posts
    78

    Talking I make it!!!

    seems tricky, only add TRIM to both side will work, no other way to work around!

    TRIM(a.Screenid) = TRIM('''' ||v_curPage|| '''')


    thank you guys,
    christine

    Originally posted by christine2003
    a.Screenid = '''' ||v_curPage|| '''' and
    b.Lang = '''' ||v_curLang|| ''''

  15. #15
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: I make it!!!

    Do you need to use the TRIM()?

    Unless you pass in spaces the varchar2 will take care of different length strings and you don't need to trim them.

    CURSOR c1(v_curPage varchar, v_curLang varchar) IS
    Select a.screenid, a.tagid, b.tagtext
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno and
    a.Screenid = v_curPage and
    b.Lang = v_curLang;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •