This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 4, 2012 9:04 AM by user346369 RSS

In general, how do I put code into a loop?

MrGibbage Newbie
Currently Being Moderated
I have access to a procedure that takes a parameter and spits out a single result. I need to figure out how to use that procedure and spit out many results. This example should clear things up.
VARIABLE enum NUMBER;
EXEC :enum := 7566;
-- my procedure. Cannot change code here.
-- Prints out a formatted name and job, given an empno stored in :enum
SELECT ename || ' (' || job || ')' AS "myrow"
FROM scott.emp
WHERE empno = :enum;
-- end of my procedure
Here, I can't change anything from the SELECT to the semi-colon. Now I would like to put this into a PL/SQL LOOP, but I can't figure it out. Let's say I want to print out all of the employees with a job of manager. Somehow I will need enum to take the value of the current iteration from within the loop.

something like this:
BEGIN
--
-- first get the list of employees
CURSOR e_cur IS
SELECT empno
FROM scott.emp
WHERE job="MANAGER';
END;
--
BEGIN
--
FOR r_cur IN e_cur LOOP
:enum = r_cur.empno
-- my procedure. Cannot change code here.
-- Prints out a formatted name and job, given an empno stored in :enum
SELECT ename || ' (' || job || ')' AS "myrow"
FROM scott.emp
WHERE empno = :enum;
-- end of my procedure
END LOOP;
END;
{code}

of course, this code does not work, and I don't know what else to try.

Thanks in advance for any help!

Skip                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 1. Re: In general, how do I put code into a loop?
    748098 Newbie
    Currently Being Moderated
    Hello MrGibbage,

    I think you have to give more information.
    Why can't you modify the select - statement?
    if you want to use your statement within a pl/sql-procedure, you need to declare a
    variable with into, in which you write the result.

    Your example works with some corrections:
    VARIABLE enum NUMBER;
    exec :enum := 7566;
    
    declare
    --
    -- first get the list of employees
    CURSOR e_cur IS
    SELECT empno
    from emp
    where job='MANAGER';
    dummy varchar2(100);
    --
    BEGIN
    --
    for r_cur in e_cur loop
    :enum := r_cur.empno;
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
    select ename || ' (' || job || ')' as "myrow"
    into dummy
    FROM  emp
    WHERE empno = :enum;
    -- end of my procedure
    END LOOP;
    END;
    Can you write stored procedures/functions?
  • 2. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    The reason I can't make a change to the select statement is simply that this example actually represents a stored procedure that I do not have access to. The stored procedure actually takes an ID and spits out a nice, formatted result. The procedure was never meant to be run inside a loop, but it is perfect for my needs, other than that.

    So this example was meant to show that given some code that does one thing, how can it be placed into a loop to do that one thing, many times.

    Anyway, your answer helped me a lot. Here's what I've got so far:
    SET SERVEROUTPUT ON SIZE unlimited
    EXEC DBMS_OUTPUT.ENABLE(null)
    declare
    --
    -- first get the list of employees
    CURSOR e_cur IS
    SELECT empno
    from scott.emp
    where job='MANAGER';
    dummy varchar2(100);
    --
    BEGIN
    --
    for r_cur in e_cur loop
    :enum := r_cur.empno;
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
    select ename || ' (' || job || ')' as "myrow"
    into dummy
    FROM  scott.emp
    WHERE empno = :enum;
    -- end of my procedure
    dbms_output.put_line(dummy);
    END LOOP;
    END;
    I was surprised that the out put was
    CLARK (MANAGER)
    CLARK (MANAGER)
    CLARK (MANAGER)

    When I was expecting to get
    JONES (MANAGER)
    BLAKE (MANAGER)
    CLARK (MANAGER)


    Can you explain why this isn't working the way I was expecting?

    Skip
  • 3. Re: In general, how do I put code into a loop?
    748098 Newbie
    Currently Being Moderated
    I didn't output the results, so I have not seen the problem.
    This lies in the cursor, which defines a static result set, so that the last result defines the value for the bind variable:
    set serveroutput on
    declare
    --
    -- first get the list of employees
    CURSOR e_cur IS
    SELECT empno
    from emp
    where job='MANAGER';
    --and empno = :enum;
    dummy varchar2(100);
    --
    BEGIN
    --
    for r_cur in e_cur loop
    :enum := r_cur.empno;
    dbms_output.put('empno from cursor '||r_cur.empno);
    dbms_output.put_line('/ value for enum '||:enum);
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
    select ename || ' (' || job || ')' as "myrow"
    into dummy
    from  emp
    WHERE empno = :enum;
    -- end of my procedure
    dbms_output.put_line(dummy);
    END LOOP;
    end;
    /
    IF you uncomment the --and empno = :enum;+
    you can see the coding compiles anyway.
    perhaps there is a possibilty in using the using parameter,
  • 4. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    Oh shoot. I don't know how I missed this the first time, but we have made a change in the unmodifiable section of code ("into dummy"). I know you even pointed it out, but I overlooked it. Your solution isn't going to work for me at all. :(

    I apologize for taking additional time from you. But I really appreciate your help. You have given me some insight with how loops work.

    I am still very much looking for a solution to my original question, if anyone is willing to help me.
  • 5. Re: In general, how do I put code into a loop?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Skip,
    MrGibbage wrote:
    The reason I can't make a change to the select statement is simply that this example actually represents a stored procedure that I do not have access to. The stored procedure actually takes an ID and spits out a nice, formatted result. The procedure was never meant to be run inside a loop, but it is perfect for my needs, other than that.

    So this example was meant to show that given some code that does one thing, how can it be placed into a loop to do that one thing, many times.

    Anyway, your answer helped me a lot. Here's what I've got so far:
    SET SERVEROUTPUT ON SIZE unlimited
    EXEC DBMS_OUTPUT.ENABLE(null)
    declare
    --
    -- first get the list of employees
    CURSOR e_cur IS
    SELECT empno
    from scott.emp
    where job='MANAGER';
    dummy varchar2(100);
    --
    BEGIN
    --
    for r_cur in e_cur loop
    :enum := r_cur.empno;
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
    select ename || ' (' || job || ')' as "myrow"
    into dummy
    FROM  scott.emp
    WHERE empno = :enum;
    -- end of my procedure
    dbms_output.put_line(dummy);
    END LOOP;
    END;
    Never write, let alone post, unformatted code.
    I was surprised that the out put was
    CLARK (MANAGER)
    CLARK (MANAGER)
    CLARK (MANAGER)

    When I was expecting to get
    JONES (MANAGER)
    BLAKE (MANAGER)
    CLARK (MANAGER)
    That's the output I get when I run your code.
    Can you explain why this isn't working the way I was expecting?
    How is the bind variable enum declared? What happens when you don;t use empno, but use r_cur.empno instead?

    Are you sure you're running exactly what you posted?
  • 6. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    Hi, Frank.

    I just confirmed that this code:
    SET SERVEROUTPUT ON SIZE unlimited
    EXEC DBMS_OUTPUT.ENABLE(null)
    DECLARE
    --
    -- first get the list of employees
     CURSOR e_cur IS
      SELECT empno
      FROM scott.emp
      WHERE job='MANAGER';
     dummy varchar2(100);
    --
    BEGIN
    --
     FOR r_cur IN e_cur LOOP
      :enum := r_cur.empno;
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
      SELECT ename || ' (' || job || ')' AS "myrow"
      INTO dummy -- this line is not allowed to be here.
      FROM  scott.emp
      WHERE empno = :enum;
    -- end of my procedure
      dbms_output.put_line(dummy);
     END LOOP;
    END;
    outputs:
    CLARK (MANAGER)
    CLARK (MANAGER)
    CLARK (MANAGER)
    In any case, that solution won't work for me because we have made a change in my "unmodifiable" code section by adding the "INTO dummy" clause. Like I said, that code represents a stored procedure that I cannot modify in any way. The procedure was written to solve a different problem, and works perfectly, but it was not designed to be put into a loop. My current project needs to use that stored procedure (it is way to complicated to re-write), but it needs to be in a loop.

    I welcome your suggestions on solving the question as posed in the OP.

    Regards,
    Skip

    Edited by: MrGibbage on Dec 3, 2012 1:04 PM
  • 7. Re: In general, how do I put code into a loop?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Like I said, that code represents a stored procedure that I cannot modify in any way
    I'm confused...

    You say here that the unmodifiable code is a stored procedure. But the unmodifiable code you posted is a SQL*Plus script. It's going to be impossible to construct a loop in PL/SQL using a SQL*Plus script because PL/SQL runs on the server and SQL*Plus runs on the client and sends SQL & PL/SQL blocks to the database after preprocessing.

    If you really have a stored procedure, we can almost certainly help you work with that. Post the actual stored procedure (or, at least, an actual stored procedure-- a CREATE OR REPLACE PROCEDURE statement) and the actual signature of that stored procedure.

    Justin
  • 8. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    The stored procedure is actually upwards of a thousand lines of code. The procedure expects that bind variable to be set before running and after it is done running, it outputs the desired output. I cannot make any changes to the stored procedure because it is used in other functions. In my mind, all I need to do is put the code inside of a loop where I assign that bind variable to the ID for each iteration. Then when the procedure is called, the variable will be set.

    Maybe what I am asking really cannot be done (as Justin is suggesting). I thought it sounded really simple, but maybe I have bitten off more that I can chew.

    I'll see if I can come up with a different solution, or maybe I can get the DBA to give me the code for the procedure, and I will modify it to meet my needs.
  • 9. Re: In general, how do I put code into a loop?
    Justin Cave Oracle ACE
    Currently Being Moderated
    But a stored procedure (which runs on the database server and can be invoked by an client) cannot depend on a SQL*Plus variable that may or may not be set in a particular client's specific SQL*Plus session. If you really have a stored procedure then that stored procedure takes an argument, it does not depend on a SQL*Plus variable. If you have a stored procedure that takes an argument, we can almost certainly help you execute that procedure in a loop in a PL/SQL block. That is endlessly easier than trying to make a SQL*Plus script loop which appears to be what you are trying to do.

    As I said in my last reply-- post a stored procedure (a CREATE OR REPLACE PROCEDURE statement) that has the same signature as the stored procedure you want to call-- and we can probably help you out. No need to post 1000 lines of code, just a simplified dummy procedure with the same signature (or even a simplified signature if you want to work on an example where you pass only 1 parameter to the stored procedure).

    Justin
  • 10. Re: In general, how do I put code into a loop?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Skip,
    MrGibbage wrote:
    Hi, Frank.

    I just confirmed that this code:
    SET SERVEROUTPUT ON SIZE unlimited
    EXEC DBMS_OUTPUT.ENABLE(null)
    DECLARE
    --
    -- first get the list of employees
    CURSOR e_cur IS
    SELECT empno
    FROM scott.emp
    WHERE job='MANAGER';
    dummy varchar2(100);
    --
    BEGIN
    --
    FOR r_cur IN e_cur LOOP
    :enum := r_cur.empno;
    -- my procedure. Cannot change code here.
    -- Prints out a formatted name and job, given an empno stored in :enum
    SELECT ename || ' (' || job || ')' AS "myrow"
    INTO dummy -- this line is not allowed to be here.
    FROM  scott.emp
    WHERE empno = :enum;
    -- end of my procedure
    dbms_output.put_line(dummy);
    END LOOP;
    END;
    outputs:
    CLARK (MANAGER)
    CLARK (MANAGER)
    CLARK (MANAGER)
    I just confirmed that it displays:
    JONES (MANAGER)
    BLAKE (MANAGER)
    CLARK (MANAGER)
    I still don't know jow you're setting the bind variable enum. Did you try using r_cur.empno instead of :enum? What happened?
    Once again, are you really running the code above, or are you running something that actually calls another procedure?
    In any case, that solution won't work for me because we have made a change in my "unmodifiable" code section by adding the "INTO dummy" clause. Like I said, that code represents a stored procedure that I cannot modify in any way.
    So you're saying the procedure can be modified? If so, what are you trying to gain by calling it "unmodifiable"?
    The procedure was written to solve a different problem, and works perfectly, but it was not designed to be put into a loop. My current project needs to use that stored procedure (it is way to complicated to re-write), but it needs to be in a loop.

    I welcome your suggestions on solving the question as posed in the OP.

    Regards,
    Skip
    I can get the results you want ('JONES', 'BLAKE', 'CLARK') with or without calling a procedure. Just do what I'm doing.
  • 11. Re: In general, how do I put code into a loop?
    user346369 Expert
    Currently Being Moderated
    Skip, if it is a stored procedure, then you MUST be passing "enum" in as as input parameter, and the result of the stored procedure is coming back to you as an out parameter. So all you need is something like this:
    DECLARE
      text_returned  VARCHAR2(30);
    BEGIN
      -- first get the list of employees
      FOR r_cur in
       ( SELECT empno FROM scott.emp
         WHERE job='MANAGER' )
      LOOP
        -- Call the procedure:
        Unchanged_procedure ( R.empno, text_returned );
      END LOOP;
    END;
    All you need is to use the correct list of input and output parameters required to call the stored procedure, and it should be good to go.

    If this is not correct, then what ARE the parameters used to call that stored procedure?
  • 12. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    Oh boy, do I owe you guys an apology. Absolutely no excuse, but I screwed up big time yesterday. I am working on a couple of projects and got some wires crossed. I would like to start over. Forget everything I asked yesterday. I wouldn't be surprised if no one answered, but I could still use some help. I am embarrassed because I am usually pretty good at these things, and again, I humbly apologize.

    Still with me? Here we go:

    I have a SQL script that is quite long. It has 25 select statements, 4 unions, 7 minus'es, lots of joins, sub-queries, etc. The script expects two bind variables to be set before running. The code was written to execute once (like a report). But the code does exactly what I need, but I need to run it on a much larger set. So I would like to wrap it in a loop. Now, I am pretty good at other programming languages (I know, hard to believe given yesterday's exchange). If I have some java code that I want to wrap in a FOR-NEXT loop, often all I need to do is put one line at the top of the code and one line at the bottom. Then I make sure the variables that need t be updated through each iteration are properly updated. Bada-bing, bada-boom, it's done.

    It doesn't seem as easy here. I know that I will have to convert my SQL script to PL/SQL. But I am wrestling with the DECLAREs, BEGINs and LOOPs, and changes to the existing code to ensure the bind variables are updated each iteration. I have tried all of what I thought would be the simple solutions as I described with my java example above, but it has not worked. So, as my subject line indicates, I was wondering if there was a general methodology to LOOP-ifying existing code. I know this is a pretty nebulous question, and I probably blew it yesterday, but I really do need some help. Even if it is "read this and that website/book/etc". That's really it. sigh All that stuff yesterday about "unmodifiable code"--forget about that. I should never have said that. Anything goes.

    Maybe there isn't a simple solution here. SQL isn't Java. And that's OK. At least then I will know I will need to look for a different solution or I will have to get a LOT smarter with SQL and PL/SQL. I thought my super-simple example yesterday was a good enough substitute for what I am faced with, but maybe that isn't a good assumption.
  • 13. Re: In general, how do I put code into a loop?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Whenever you have a problem, please post a complete test script that poeple can run to re-create the problem and test their ideas.
    Simplify the problem as much as possible. For example, if you have a script that's 1000 lines long, but the part you don't understand is just how to call it with parameters, then substitute a very simple script with parameters, perhaps as simple as "SELECT * FROM scott.emp WHERE empno = :target_empno;".
    Post the outptut you want from whatever sample data and arguments you post.
    See the forum FAQ {message:id=9360002}

    So you have a script that runs in SQL*Plus, and you want to run it in a loop. I see 2 ways you could do this:
    (1) Re-write the SQL*Plus script as a PL/SQL procedure. This way, you'll be able to use all the PL/SQL flow-of-control features, such as LOOP. However, you won't be able to use SQL*Plus's output features.
    (2) Write a SQL*Plus script that simulates a loop. For example:
    -- Turn off SQL*Plus output that will look like commands
    SET   FEEDBACK        OFF
    SET   PAGESIZE        0
    SET   VERIFY          OFF
    
    
    SPOOL  c:\foo\run_all.sql
    
    SELECT  '@my_script '
    ,       empno
    FROM    scott.emp
    WHERE   job    = 'MANAGER'
    ;
    
    SPOOL  OFF
    
    -- Turn on user-helpful features again
    SET   FEEDBACK        ON
    SET   PAGESIZE        50
    SET   VERIFY          ON
    
    -- Run the script created above:
    @c:\foo\run_all
    Use the SQL*Plus substitution variables &1 (&2, &3, ...) in my_script.sql. If you really want to use bind variables, you can use the substitution variables to set bind variables in my_script.sql. For example:
    EXEC  :enum := &1;
  • 14. Re: In general, how do I put code into a loop?
    MrGibbage Newbie
    Currently Being Moderated
    Frank,
    First, thank you very much for giving me a second chance.

    I like the second suggestion that you gave. I use SQL*Plus and often use the SPOOL command to catch the output to a text file. But your example has the output going to a sql file?? I am having a hard time figuring out what that will do. Are we dynamically creating a SQL script? Is there a general name for what you call running a procedure like this? Something that I could look up and read about?

    I am also puzzled by the SELECT '@my_script ' (by the way, was the trailing space there important, irrelevant, or just a typo?) I run my SQL scripts by typing @c:\scripts\my_sql_script.sql (and I do know that the trailing .sql is optional). But since it is in quotes, won't it just write @my_script in that column?

    If I understand this correctly, you have two scripts here: run_all.sql and my_script.sql. I think the first code you posted is the run_all.sql. But since it spools to itself, it would be overwritten as soon as it was executed. And then it executes itself at the end?

    Sorry, Frank, your solution looks intriguing, and I think I will learn a lot by trying it out, but I am lost as to figuring out how to actually implement it.

    Skip
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points