I have a requirement of bash shell script calling SQL*Plus script.
Shell Script
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
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:-
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 solution
select statement that queries that huge table)If 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:
-- sqlscript1.sql
SELECT something,
key_for_join
FROM huge_table
WHERE condition = 'met'
-- 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:-
SQLPlus Copy command - to copy objects from source to destination DB instance.
Export Import Utilities - to transfer data objects between databases.
SQLloader - to load data from file to database.
My Reply - This is extraction requirement at client end. We developed bash script and SQLPlus 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. SQLloader 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
What are the results of sqlscript1? (assumption: it is a listing displayed in your sqlplus session)
How are the results created? (assumption: sqlscript1 is just a select statement that queries that huge table)
What will sqlscript2 do with the result it gets from sqlscript1? (assumption: sqlscript2 needs those results for a join to another table)
My Reply -
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.
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
First off: please do not use your (admittedly inventive) markup for quotes - there is a QUOTE-tag, which can be used for that and it makes what you say stand out from what someone else said even better than what you did above. Thanks.
You haven't said which database you use, but from your mentioning "SQL*Plus" i suppose it is Oracle. You can - depending on what exactly your query consists of - perhaps use a view or maybe even a stored procedure.
I hope this helps.
bakunin