DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2001
    Excuse my COBOL-ese, but what are my options for "performing" common chunks of code in PL/SQL? If I have the same block of code I want to execute several times in a procedure, I know I can do a "goto" and branch to a named routine. My problem is, I need to do this while in a cursor FOR loop, and as soon as I do a "goto" from there, my cursor gets closed so I only process 1 row of the cursor.
    How do most people handle this? Do you just write another procedure or function and call it? If so, how do you pass your cursor row to it for processing? Or are there other options I don't know about. THis seems so basic.

    Thanks for your input.

  2. #2
    Join Date
    Oct 2000
    You could create a function or procedure in your PL/SQL DECLARE section and then you can use it in the loop and not be kicked out.

    Hope this clear, if not, say and I could put some more info.

  3. #3
    Join Date
    Jan 2001
    Thank you. Do you mean create a package that includes the procedure as well as the common block as a function or procedure? Then I can send that function/procedure cursor rows/variables for processing? Or are you thinking of calling another external function or procedure? If so, I need to know how to pass cursor rows to external functions/procedures.
    I appreciate your help very much.

  4. #4
    Join Date
    Sep 2000
    Sao Paulo,SP,Brazil, Earth, Milky Way
    I suggest something like :

    DECLARE -- main declaring of the PROG
    v_1 number;
    v_2 table of ...
    v_3 varchar2(20);
    -- do something ...
    BEGIN -- main program starting point
    ... do something
    v_1 := nnnn
    v_2 := zzzzz
    ... PL/SQL will return here after P_SUBPROC executing
    do anotherthing
    v_1 := yyyyy
    v_2 := kdkdk
    and so on ...
    END; -- main program end here

    ===> In this example, you can call P_SUBPROC n times, each time it will exec P_SUBPROC (and P_SUBPROC will "see" the current value of v_1, v_2, and the execution will be like a PERFORM : you go to another point, exec something there and return exactly after the starting point.



  5. #5
    Join Date
    Mar 2001
    Cologne, Germany
    If you create a package containing the calling as well as the called procedure, you can define global variables, cursors, records in that package, which are accessible to all procedures.
    The less dirty way is of course to pass the parameters to called functions/procedures.


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.