1

I have a loop in Bash, it was working like a charm until today. The loop looks like:

while read line1 ; do
    while read line2 ; do
      stringArray=($line2)
      string=$line1.${stringArray[1]}

        sqlplus /nolog <<EOF
        connect sysuser/syspassword@db_instance
        alter system flush shared_pool;
        quit
        EOF

        sqlplus -s /nolog > /dev/null 2>&1  <<EOF
        connect user/password@db_instance
        variable rc refcursor;
        SPOOL ${line1}_${stringArray[0]}.DATA
        exec :rc := $string;
        print rc;
        spool off
        exit
        EOF

    done < file2.txt
done < file1.txt

To clarify one thing, the contents of the variable $string are Oracle packages with functions and parameters:

SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',numbers)

Now - Until the package was doing what it should do in just one parametrized run - everything was perfectly fine. But now I have encountered a package that does its job in 5 steps, steps determined by the last parameter. It looks like this:

SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',1)
SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',2)
SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',3)
SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',4)
SOMEPACKAGE.SOMEFUNCTION(some,'parameters',here,'sometimes',NULL,'or',5)

The first one, with the 1 on end, is producing a TEMPORARY_TABLE on which the other jobs (2 to 5) are working. The Job number 5 is supposed to give the results of the whole chain too.

Until I had packages doing their Job in 1 step, running this loop was just perfect. But the TEMPORARY_TABLE is dissappearing after disconnecting, and I cannot just add a special section in my bash script just for this one package, cause there could be much more packages, with different number of steps. This has to be done automatically, without too much effort from the users that will consume this script.

So is there a way of keeping the session alive? Or is there any other way to do this?

hc0re
  • 1,806
  • 2
  • 26
  • 61
  • Run `sqlplus` in a coprocess. – Barmar Dec 11 '14 at 10:43
  • Creating a new temporary table for each run is not the right way to use temporary tables in Oracle. In Oracle temporary tables are more permanent than in some other databases. See e.g. http://stackoverflow.com/q/2671518/272735 Temporary refers to the lifespan of rows, not to the lifespan of the table. – user272735 Dec 11 '14 at 10:59
  • If you really need different SQL sessions, why not make a regular table, that serves as a temporary table ? You would need to manually drop it afterwards, but there'll be no issue with usage of different sessions. Do know that you must assure that the name of the table is very specific. – tvCa Dec 14 '14 at 14:30
  • Don't use hardcoded passwords in scripts. – tvCa Dec 14 '14 at 14:31

1 Answers1

0

Instead of storing just a single procedure call in $string try writing an anonymous block to the variable and executing that. The block can contain multiple calls to to the procedure (along with other things) and won't return until they have all completed.

Mark Wagoner
  • 1,729
  • 1
  • 13
  • 20
  • Seems to be a good idea, but I have no clue where to start. I should store a whole procedure, or more procedures like `variable rc refcursor; SPOOL ${line1}_${stringArray[0]}.DATA exec :rc := $string; print rc; spool off` under `$string`? – hc0re Dec 11 '14 at 14:46
  • You can write any PL/SQL statements needed within a `BEGIN...END` block. And since this block is technically just a string you can generate it dynamically on the client and then send it to the server for execution. Something to remember, however, is that it is running on the server so you can't use `SPOOL` statements (which is a SQL*PLUS statement anyway). You will have to use `DBMS_OUTPUT` – Mark Wagoner Dec 12 '14 at 12:43