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)
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.