Shell Programming and Scripting

I have a requirement of bash shell script calling SQL*Plus script.

Shell Script

Code:

sqlplus <user>/<pwd> @sqlscript1 parameters
sqlplus <user>/<pwd> @sqlscript2 parameters

Now I need the values generated by sqlscript1 in sqlscript2. But it is more like a huge dataset in a table and not one specific value.

But I am not allowed to create a table in the client database. Please note sqlscript cannot accept more than 240 characters in one parameter variable.

Is there any unix solution for this?

You can use SQL*Plus SPOOL command to write results to a file. Once you have the data in a file, you can process it using bash.

Not a possible solution because

1. SPOOL will write a file but I cannot read the file in sqlscript2. Do you know anyway to read a file in sqlscript? Please note I do not have PL/SQL context. I only have SQL context.
2. I cannot access a file like a table unless it is an EXTERNAL FILE which is not in this case.

Not that my requirement is get the table in sqlscript2 and not in bash only.

So it sounds like script1 and script2 are connecting to 2 different DB instances.

Since you are not allowed to create a table, writing to file, then in script2 reading each record in bash variables and using them in SQL*Plus is the only option that I can think of.

But if you are allowed to create tables, you can try one of below approaches:-

I would also suggest moving things into a here document, more like this:-

Code:

sqlplus -S <<EOSQL
   CONNECT username/password@instance ;
   sqlscript1 parameters
EOSQL

It would stop and casual observer seeing your credentials with a simple ps command. It might give you more opportunity to shovel in the parameters you require, but I'm not sure.



I hope that this helps,
Robin

Your requirement is a bit confusing for me. That's why I'd like to take one step back to evaluate possible solutions.
You say you need the results of sqlscript1 in sqlscript2.
In order to offer you good advice we need some more information. A starting point can be this questions, and based on the assumptions I mention below I'll propose a possible solutionIf my assumptions are correct there is no need for any OS-side processing or any passing of parameters to sqlscript2. You can use a construct called inline-view, which is basically the select statement from sqlscript1 built into the from-clause of sqlscript2.
Something like this:

Code:

-- sqlscript1.sql
SELECT something,
       key_for_join
  FROM huge_table
 WHERE condition = 'met'

Code:

-- sqlscript2.sql
SELECT s1.something,
       s2.somethingelse
  FROM another_table s2,
       (SELECT something,
               key_for_join
          FROM huge_table
         WHERE condition = 'met') s1,
  WHERE s2.key_for_join = s1.key_for_join;

To all of you who replied to this thread at once.

Yoda
----
So it sounds like script1 and script2 are connecting to 2 different DB instances.

My Reply:- No they are running on same instance.

Since you are not allowed to create a table, writing to file, then in script2 reading each record in bash variables and using them in SQL*Plus is the only option that I can think of.

My Reply:- Yes it is and that's what we are using.

But if you are allowed to create tables, you can try one of below approaches:-

SQL*Plus Copy command - to copy objects from source to destination DB instance.
Export Import Utilities - to transfer data objects between databases.
SQL*loader - to load data from file to database.

My Reply - This is extraction requirement at client end. We developed bash script and SQL*Plus script to get the data. We provide the scripts to the client and they run it in their environment and they send us the extracted files. We cannot use Export utilities since it is specific extraction of tables and with specific conditions applied on the tables. SQL*loader is also out of scope considering our requirements.

----------------------

cero
----

Your requirement is a bit confusing for me. That's why I'd like to take one step back to evaluate possible solutions.
You say you need the results of sqlscript1 in sqlscript2.

In order to offer you good advice we need some more information. A starting point can be this questions, and based on the assumptions I mention below I'll propose a possible solution

1. What are the results of sqlscript1? (assumption: it is a listing displayed in your sqlplus session)

2. How are the results created? (assumption: sqlscript1 is just a select statement that queries that huge table)

3. What will sqlscript2 do with the result it gets from sqlscript1? (assumption: sqlscript2 needs those results for a join to another table)

My Reply -

1. We are trying to do Order to Cash Cycle extraction for Oracle e-Business R12 for client(s).
Orders --> Deliveries --> Invoices --> SLA --> GL

SLA = Subledger Accounting

Now I can get "Invoices --> SLA --> GL" extraction done in a reasonable time frame.
If I try to get "Orders --> Deliveries --> Invoices --> SLA --> GL" together, it will take a lot of time.

Hence I am trying to populate all master Ids/primary keys by running just "Invoices --> SLA --> GL" as part of 'sqlscript1' first in a table.

2. sqlscript2 then can use the table to get "Orders" & "Deliveries" separately.

Now the challenge is our team members are suggesting is there a way that we can avoid creating a table at client end from security point of view.

This the process we have right now.

Shell Script

--> Calling SQL * Plus Script for table extraction into files
--> Performing record count on each files.

Shell Script

--> sqlscript1 (which would populate a table which would contain all master ids Invoices --> SLA --> GL)
--> sqlscript2 (extraction can take place and we can use the table data from sqlscript1 to get the Sales Orders and Deliveries).

Is there any better approach by which we can avoid creating a table? That is also going to be very helpful.


Thanks