10-20-2017
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
Last edited by uuuunnnn; 10-20-2017 at 10:04 AM..
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
Hello all,
With UNIX, they don't come any greener than me.
Here's my question:
I've written a UNIX script in which I execute SQL Scripts.
In the last SQL Script, processing errors can occur that are not SQL problems and therefore the SQL script ends successfully. However, I want to be able... (0 Replies)
Discussion started by: andrewa1
0 Replies
2. Shell Programming and Scripting
Oracle and Scripting gurus,
I need some help with this script...
I am trying to add the query
SELECT * FROM
ALL_SYNONYMS
WHERE SYNONYM_NAME = 'METADATA'
in the current script....
Read the result set and look for the TABLE_NAME field. If the field is pointing to one table eg.... (18 Replies)
Discussion started by: madhunk
18 Replies
3. Shell Programming and Scripting
Hi-
I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment...
I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies
4. Shell Programming and Scripting
hi all,
i have a function which will take i/p as a ddl sctipt as i/p and execute it,
let
function execute_sql
{
db_var="$1"
v_cnt=`sqlplus -s XXXXX/XXXXX@aXXX << ENDSQL | sed -e "s/Connected\.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off serveroutput on size... (4 Replies)
Discussion started by: manas_ranjan
4 Replies
5. UNIX for Advanced & Expert Users
Hi
Can anyone please let me know how to call unix scripts from sql or plsql ASAP. (2 Replies)
Discussion started by: ksailesh
2 Replies
6. Shell Programming and Scripting
Hi,
Can anyone tell me how to call a oracle package from a Unix shell script? I want to pass some input parameters to package and it will return me the output which I want to use further in my shell script. I want to know the way to capture the output values in my shell script. Please send some... (1 Reply)
Discussion started by: anil029
1 Replies
7. Shell Programming and Scripting
Hi!
I would like to do a single connection to sqlplus and execute some querys.
Actually I do for every query one connection to database
i.e
echo 'select STATUS from v$instance;
exit' > $SQL_FILE
sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT
echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies
8. UNIX for Advanced & Expert Users
Hi Experts:
I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies
9. Shell Programming and Scripting
Hi,
I wanted to know the exact behavior of the shell scripts in the below scenario.
I have 2 scripts - a.ksh and b.ksh
Both these scripts call a 3rd script xyz.ksh.
What will happen if both a.ksh and b.ksh run at the same time?
What will happen if a.ksh starts 2-3 seconds before b.ksh?... (3 Replies)
Discussion started by: aster007
3 Replies
10. Shell Programming and Scripting
Dear all,
I am working on script which call other shell scripts in a loop but problem is from second script am not able to come out.
Here is the snippet:-
#!/bin/bash
HSFILE=/root/Test/Components.txt
LOGFile=/opt/domain/AdminDomain/application/logs... (3 Replies)
Discussion started by: sharsour
3 Replies
LEARN ABOUT REDHAT
create_table_as
CREATE TABLE
AS(7) SQL Commands CREATE TABLE AS(7)
NAME
CREATE TABLE AS - create a new table from the results of a query
SYNOPSIS
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ]
AS query
DESCRIPTION
CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types asso-
ciated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column
names).
CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query
just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast,
a view re-evaluates its defining SELECT statement whenever it is queried.
PARAMETERS
[LOCAL] TEMPORARY or [LOCAL] TEMP
If specified, the table is created as a temporary table. Refer to CREATE TABLE [create_table(7)] for details.
table_name
The name (optionally schema-qualified) of the table to be created.
column_name
The name of a column in the new table. Multiple column names can be specified using a comma-delimited list of column names. If col-
umn names are not provided, they are taken from the output column names of the query.
query A query statement (that is, a SELECT command). Refer to SELECT [select(7)] for a description of the allowed syntax.
DIAGNOSTICS
Refer to CREATE TABLE [create_table(7)] and SELECT [select(7)] for a summary of possible output messages.
NOTES
This command is functionally equivalent to SELECT INTO [select_into(7)], but it is preferred since it is less likely to be confused with
other uses of the SELECT ... INTO syntax.
COMPATIBILITY
This command is modeled after an Oracle feature. There is no command with equivalent functionality in SQL92 or SQL99. However, a combina-
tion of CREATE TABLE and INSERT ... SELECT can accomplish the same thing with little more effort.
HISTORY
The CREATE TABLE AS command has been available since PostgreSQL 6.3.
SEE ALSO
CREATE TABLE [create_table(7)], CREATE VIEW [create_view(l)], SELECT [select(l)], SELECT INTO [select_into(l)]
SQL - Language Statements 2002-11-22 CREATE TABLE AS(7)