Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sql output from multiple rows and columns as variables in a script
# 1  
Old 06-04-2010
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:

Code:
    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?

Code:
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)

Code:
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.

Moderator's Comments:
Mod Comment Please use code tags, ty
Last edited by pludi; 06-04-2010 at 04:45 AM..
# 2  
Old 06-04-2010
Quote:
Originally Posted by lmu
...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?
Maybe you could redirect the output of your SELECT statement to a temporary file and then process that file in your shell script ?

Quote:
... but this is the one that has me stumped.

variables and constants:

Code:
    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?

Code:
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)

Code:
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.
...
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 -

Code:
$
$ # 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
# 3  
Old 06-05-2010
You're a genius. That's exactly what I needed and beautiful code as well. Thank you.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

Transposing rows to columns with multiple similar lines

Hi, I am trying to transpose rows to columns for thousands of records. The problem is there are records that have the same lines that need to be separated. the input file as below:- ID 1A02_HUMAN AC P01892; O19619; P06338; P10313; P30444; P30445; P30446; P30514; AC Q29680; Q29837;... (2 Replies)
Discussion started by: redse171
2 Replies

3. Shell Programming and Scripting

Reading multiple values from multiple lines and columns and setting them to unique variables.

Hello, I would like to ask for help with csh script. An example of an input in .txt file is below, the number of lines varies from file to file and I have 2 or 3 columns with values. I would like to read all the values (probably one by one) and set them to independent unique variables that... (7 Replies)
Discussion started by: FMMOLA
7 Replies

4. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

5. Shell Programming and Scripting

Format output into columns, variables with multiple entries

Hello all, I've got a script that collects data on file systems and prints out specific data about each. I've formatted headers w/ printf like so. printf "\033 and I had the content of the varibles printed out beneath those columns like so: printf... (5 Replies)
Discussion started by: awreneau
5 Replies

6. UNIX for Dummies Questions & Answers

Getting values of 2 columns from sql query in UNIX variables

Hi, I have connected to oracle database with sqlplus -s / <<EOF select ename, age from emp where empid=1234; EOF I want to save the values of ename and age in unix shell variables. Any pointers would be welcome.. Thanks in advance!!1 Cheers :):):):) (1 Reply)
Discussion started by: gonchusirsa
1 Replies

7. Shell Programming and Scripting

How to split the sql output into two different variables

Hi, How to set as variable from sql output. Query: select aa.serial, ao.name from ann_amit aa JOIN ann_object ao on (aa.classid=ao.classid); I got two values from aa.serial and ao.name, I wanna make two different variable for aa.serial and ao.name. The value of aa.serial should be in... (2 Replies)
Discussion started by: KarthikPS
2 Replies

8. Shell Programming and Scripting

Arrange output based on rows into columns

Hi All, I would like to ask help on how can i achieve below output. Inputfile: Oct11,apa1-daily,01:25:01 Oct11,apa2-daily,01:45:23 Oct12,apa1-daily,02:30:11 Oct12,apa2-daily,01:55:01 Oct13,apa1-off,01:43:34 Oct13,apa2-off,01:22:04 Desired output: Clients ... (3 Replies)
Discussion started by: mars101
3 Replies

9. Shell Programming and Scripting

want to print output if u have rows and columns

U have a file File 1 0.3 0.2 0.4 0.3 0.8 0.11 0.22 0.4 0.23 0.45 0.56 0.78 0.98 0.11 0.32 0.2 0.4 0.45 0.54 0.2 0.33 0.44 0.21 0.22 0.98 0.8 0.2 0.34 0.54 0.98 0.12 0.1 0.22 0.32 0.34 0.89 0.22 File 2 rows columns 3 1 2 3 4 2 5 ... (8 Replies)
Discussion started by: cdfd123
8 Replies

10. Shell Programming and Scripting

Pass multiple variables to SQL script

I am trying to close of multiple users in an Oracle database. Each users has records in multiple tables what I need to do is use a script that call each SQL seperately passing either CLI arguments or gathered arguments from the users during run time. ## Accept variable(s) from the command line... (1 Reply)
Discussion started by: jagannatha
1 Replies
Login or Register to Ask a Question