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 DEBIAN
grass-sqlite
grass-sqlite(1grass) Grass User's Manual grass-sqlite(1grass)
NAME
grass-sqlite - SQLite driver
SQLite driver SQLite driver in GRASS
Creating a SQLite database
GRASS is automatically creating the SQLite database if it is not yet existing when the first table is created in the SQLite database. It is
sufficient to define the connection (see next step).
Connecting GRASS to SQLite
The database name 'sqlite.db' is at user's choice. Also the file storage location can be freely chosen. If the database does not exist, it
will be automatically created when database content is created:
# example for storing DB in mapset directory (keep single quotes):
db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db'
db.connect -p
Supported SQL commands
All SQL commands supported by SQLite (for limitations, see SQLite help page: SQL As Understood By SQLite and Unsupported SQL).
Operators available in conditions
All SQL operators supported by SQLite.
Browsing table data in DB
A convenient SQLite front-end is sqlitebrowser. To open a DB file stored within the current mapset, the following way is suggested (corre-
sponds to above database connection):
eval `g.gisenv`
# use double quotes:
sqlitebrowser "$GISDBASE/$LOCATION_NAME/$MAPSET"/sqlite.db
SEE ALSO
db.connect, db.execute, db.select
SQL support in GRASS GIS
SQLite web site, SQLite manual, sqlite - Management Tools
Last changed: $Date: 2011-02-07 18:59:50 +0100 (Mon, 07 Feb 2011) $
Help Index
GRASS 6.4.2 grass-sqlite(1grass)