1 2 Previous Next 16 Replies Latest reply: Dec 4, 2012 11:04 AM by user346369 Go to original post RSS
      • 15. Re: In general, how do I put code into a loop?
        Frank Kulash
        Hi, Skip,
        MrGibbage wrote:
        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??
        Exactly!
        What is a sql file? It's a text file, where the text is all SQL and/or SQL*Plus code. In this case, SPOOL will be generating a file that consists entirely of SQL*Plus @ commands.
        I am having a hard time figuring out what that will do. Are we dynamically creating a SQL script?
        Exactly! We're creating and running 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've heard the term SQL from SQL where the ouptut of a query is a SQL statement, or a fragment of a SQL statement, as in {message:id=3227388} I suppose you could call this SQL*PLus from SQL.
        I am also puzzled by the SELECT '@my_script ' (by the way, was the trailing space there important, irrelevant, or just a typo?)
        Irrelevant. You want to generate a file that looks like:
        @my_script  7566
        @my_script  7698
        @my_script  7782
        There has to be some whitespace between '@my_script' and the number, but it doesn't matter how much. If COLSEP is supplying a space, then you don't need to include a space in the literal '@my_script'.
        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?
        Write whatever path- and file names you want to use.
        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?
        There are actually 3 scripts:
        (1) my_script.sql which does the work for 1 set of parameters
        (2) run_all.sql which calls my_script.sql N times
        (3) a script like the one I posted. Give it a unique name. When I write scripts like this, I sometimes use names like run_all_cr.sql , where the _cr at the end reminds me that this is the script that creates and runs another script.  Again, you can give it any unique name you like.
        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
        I can understand if the answer isn't clear to you. I know how it feels, becuse the question isn't clear to me. If you would post a complete test script that re-creates the problem, then I could post a comple script that solves the problem. Again, simplify the problem as much as possible. Include as little as you can to show the part you don't already know how to do.
        • 16. Re: In general, how do I put code into a loop?
          user346369
          MrGibbage wrote:
          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 your script has two input variables. What is the output? Does it spool anything to a file, or what.

          Examples or descriptions of what/how those select statements deliver their output is crucial to giving you any help.

          In your first post, you said "spits out a single result". Where is that result -- in a SQL Plus output screen? spooled to a file? Please explain that.
          1 2 Previous Next