Sponsored Content
Top Forums Shell Programming and Scripting UNIX Solution - Calling SQL*Plus scripts Post 303005620 by uuuunnnn on Friday 20th of October 2017 08:53:17 AM
Old 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

Communitcating between UNIX and SQL Scripts

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

Calling SQL scripts through Shell Script

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

Calling SQL LDR and SQL plus scripts in a shell script

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

any possible solution on sql calling scripts

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

Calling unix script from sql or plsql

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

Calling oracle package Unix from shell scripts.

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

Execute multiple SQL scripts from single SQL Plus connection

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

Call parallel sql scripts from shell and return status when both sql are done

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

Behavior of Unix in calling 2 scripts simultaneously

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

Calling multiple scripts from another scripts

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
SHELL-QUOTE(1p) 					User Contributed Perl Documentation					   SHELL-QUOTE(1p)

NAME
shell-quote - quote arguments for safe use, unmodified in a shell command SYNOPSIS
shell-quote [switch]... arg... DESCRIPTION
shell-quote lets you pass arbitrary strings through the shell so that they won't be changed by the shell. This lets you process commands or files with embedded white space or shell globbing characters safely. Here are a few examples. EXAMPLES
ssh preserving args When running a remote command with ssh, ssh doesn't preserve the separate arguments it receives. It just joins them with spaces and passes them to "$SHELL -c". This doesn't work as intended: ssh host touch 'hi there' # fails It creates 2 files, hi and there. Instead, do this: cmd=`shell-quote touch 'hi there'` ssh host "$cmd" This gives you just 1 file, hi there. process find output It's not ordinarily possible to process an arbitrary list of files output by find with a shell script. Anything you put in $IFS to split up the output could legitimately be in a file's name. Here's how you can do it using shell-quote: eval set -- `find -type f -print0 | xargs -0 shell-quote --` debug shell scripts shell-quote is better than echo for debugging shell scripts. debug() { [ -z "$debug" ] || shell-quote "debug:" "$@" } With echo you can't tell the difference between "debug 'foo bar'" and "debug foo bar", but with shell-quote you can. save a command for later shell-quote can be used to build up a shell command to run later. Say you want the user to be able to give you switches for a command you're going to run. If you don't want the switches to be re-evaluated by the shell (which is usually a good idea, else there are things the user can't pass through), you can do something like this: user_switches= while [ $# != 0 ] do case x$1 in x--pass-through) [ $# -gt 1 ] || die "need an argument for $1" user_switches="$user_switches "`shell-quote -- "$2"` shift;; # process other switches esac shift done # later eval "shell-quote some-command $user_switches my args" OPTIONS
--debug Turn debugging on. --help Show the usage message and die. --version Show the version number and exit. AVAILABILITY
The code is licensed under the GNU GPL. Check http://www.argon.org/~roderick/ or CPAN for updated versions. AUTHOR
Roderick Schertler <roderick@argon.org> perl v5.8.4 2005-05-03 SHELL-QUOTE(1p)
All times are GMT -4. The time now is 03:06 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy