sql output from multiple rows and columns as variables in a script

This is for an Oracle journal import. I was using a pl/sql package and oracle API's. Oracle added invoker rights to their API's and now my package won't run. I didn't want to use their API's anyway. The only reason i was using pl/sql and the API's (just a package) was to utilize a cursor. How do I do this in a shell script?
I need to run the process using variables from a table I update. I'll have to figure that one out too, but this is the one that has me stumped.

variables and constants:

    x_appl_id         number  default 101;
    x_user_id         number  default 1234;
    x_resp_id         number  :=  NULL;
    x_conc_id         number  := NULL;
    x_table_name    varchar(255) default 'gl.gl_ugh_interface';
    run_id              number  :=NULL;
    sob_id        number  :=NULL;

How do I recreate the following cursor in a shell script and run the process as many times as there are rows in the table using the column values from each row?

CURSOR sob_run_id_cur IS
    SELECT
      set_of_books_id
      ,interface_run_id
      ,je_source_name
      FROM gl.gl_interface_control
      where interface_table_name= 'gl.gl_ugh_interface';
  BEGIN
    FOR rec1 in sob_run_id_c
    LOOP
  -- set the responsibility ID based on the set_of_books_id
  IF (rec1.set_of_books_id in ('2023','2043','2047') )
         THEN x_resp_id := '50638';
  ELSIF (rec1.set_of_books_id in ('2045'))
         THEN x_resp_id := '50617';
  ELSIF (rec1.set_of_books_id in ('2041'))
         THEN x_resp_id := '50660';

 
 END IF
run_id       := to_char(rec1.interface_run_id);
sob_id       := to_char(rec1.set_of_books_id);

I then run the process using the variables from the cursor.
I won't put the original api's here, but the process i have to run is the following. For each combination of ledger_id, source_names there is a $run_id. (the api needed the sourceId, this doesn't)

x_conc_id =`CONCSUB apps/apps SQLGL '"$x_resp_id"' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL $RUN_ID 1 '"N"' '""' '""' '"N"' '"W"'

For each conc_id I wait for the request to finish and email the output to the users.

Maybe you could redirect the output of your SELECT statement to a temporary file and then process that file in your shell script ?

If you print the resultset of the SELECT statement to a file then iterating through the file in your shell script is the equivalent of Oracle's cursor loop.

Here's a short testcase -

$
$ # show the content of the shell script
$ cat -n gl_interface.sh
     1  #!/usr/bin/bash
     2
     3  sqlplus -s test/test <<EOF >gl_interface.tmp
     4  set feed off pages 0 trimspool on
     5  SELECT set_of_books_id ||','||
     6         interface_run_id ||','||
     7         je_source_name
     8  FROM gl_interface_control
     9  where interface_table_name= 'gl.gl_ugh_interface';
    10  EOF
    11
    12  # now process the temp file
    13  IFS=","
    14  while read SOB_ID RUN_ID SOURCE_NAME
    15  do
    16    case "$SOB_ID" in
    17      "2023"|"2043"|"2047") X_RESP_ID="50638" ;;
    18      "2045") X_RESP_ID="50617" ;;
    19      "2041") X_RESP_ID="50660" ;;
    20      *) X_RESP_ID="" ;;
    21    esac
    22    X_CONC_ID="CONCSUB apps/apps SQLGL '$X_RESP_ID' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL $RUN_ID 1 'N' '' '' 'N' 'W'"
    23    echo "SOB_ID      = $SOB_ID"
    24    echo "RUN_ID      = $RUN_ID"
    25    echo "SOURCE_NAME = $SOURCE_NAME"
    26    echo "X_RESP_ID   = $X_RESP_ID"
    27    echo "x_CONC_ID   = $X_CONC_ID"
    28    echo "==================================================================================================================="
    29  done <gl_interface.tmp
$
$
$ # the shell script dumps the results of the SELECT statement in the file "gl_interface.tmp" which is processed thereafter
$ # now run the script
$
$
$ . gl_interface.sh
SOB_ID      = 2023
RUN_ID      = 100
SOURCE_NAME = source_1
X_RESP_ID   = 50638
x_CONC_ID   = CONCSUB apps/apps SQLGL '50638' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 100 1 'N' '' '' 'N' 'W'
===================================================================================================================
SOB_ID      = 2043
RUN_ID      = 200
SOURCE_NAME = source_2
X_RESP_ID   = 50638
x_CONC_ID   = CONCSUB apps/apps SQLGL '50638' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 200 1 'N' '' '' 'N' 'W'
===================================================================================================================
SOB_ID      = 2047
RUN_ID      = 300
SOURCE_NAME = source_3
X_RESP_ID   = 50638
x_CONC_ID   = CONCSUB apps/apps SQLGL '50638' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 300 1 'N' '' '' 'N' 'W'
===================================================================================================================
SOB_ID      = 2045
RUN_ID      = 400
SOURCE_NAME = source_4
X_RESP_ID   = 50617
x_CONC_ID   = CONCSUB apps/apps SQLGL '50617' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 400 1 'N' '' '' 'N' 'W'
===================================================================================================================
SOB_ID      = 9999
RUN_ID      = 500
SOURCE_NAME = source_5
X_RESP_ID   =
x_CONC_ID   = CONCSUB apps/apps SQLGL '' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 500 1 'N' '' '' 'N' 'W'
===================================================================================================================
SOB_ID      = 2041
RUN_ID      = 600
SOURCE_NAME = source_6
X_RESP_ID   = 50660
x_CONC_ID   = CONCSUB apps/apps SQLGL '50660' JOURNALIMPORT WAIT=N CONCURRENT SQLGL GLLEZL 600 1 'N' '' '' 'N' 'W'
===================================================================================================================
$
$
$ # just for reference, here's how the "gl_interface.tmp" file looked like
$ cat gl_interface.tmp
2023,100,source_1
2043,200,source_2
2047,300,source_3
2045,400,source_4
9999,500,source_5
2041,600,source_6
$
$
$

HTH,
tyler_durden

You're a genius. That's exactly what I needed and beautiful code as well. Thank you.