Sponsored Content
Top Forums Shell Programming and Scripting how to call shell script from pl/sql loop Post 302227899 by rosalinda on Friday 22nd of August 2008 07:09:04 AM
Old 08-22-2008
how to call shell script from pl/sql loop

Hello,
I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea?

Here is idea about the code:

my shell script is:

-----------------------------------------------------------------------
#!/bin/bash

OUTPUT=$(sqlplus -s '/ as sysdba' <<-EOF
set heading off feedback off serveroutput on trimout on pagesize 0
select instance_name from v\$instance;
select version from v\$instance;
declare
i number := 1;
a NUMBER(38);
b NUMBER(38);
c VARCHAR(100);
begin
while i < 4 loop
select student_id, studentaccount_id, studentaddress into a,b,c from studenttable WHERE row_id = i ;
dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
--- here i would like to call another shell script stud.sh to use the values a,b and c.
i := i+1;
end loop;
end;
/
EOF
)
INSTANCE=$(echo $OUTPUT | awk '{ print $1 }')
VERSION=$(echo $OUTPUT | awk '{ print $2 }')
ARG1=$(echo $OUTPUT | awk '{ print $3 }')
ARG2=$(echo $OUTPUT | awk '{ print $4 }')
ARG3=$(echo $OUTPUT | awk '{ print $5 }')
echo "Database: $INSTANCE"
echo "Version: $VERSION"
echo "Arg1: $ARG1"
echo "Arg2: $ARG2"
echo "Arg3: $ARG3"


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

I manage to make the shell script read it after the loop finish, but my table is too big, i want to call the stud.sh script each time the loop is searching. and how to make the other shell script read my values.
Thanks a lot, i appreciate your help.
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

how can a call shell script from pl/sql

I like to call a shell script from pl/sql proceduere and i have to use the shell script return value in that procedure. i am using oracle 9i and cygwin. can any one suggest me how can i do this (0 Replies)
Discussion started by: rajesh.P
0 Replies

2. Shell Programming and Scripting

how can i call a shell script from pl/sql

I would like to call the shell script from pl/sql and i need to uses the value returned by the shell script in pl/sql procedure. can any one suggest me how can i do that? (3 Replies)
Discussion started by: rajesh.P
3 Replies

3. Shell Programming and Scripting

call shell script from pl/sql block

Hi Experts, I want to call script_name.ksh as many time as id in customer table and also pass it as a parameter to script. someting Like below. for i in select id from customer do ./script_name.ksh $i & done I have figured out how to have ID from customer but now how to call... (3 Replies)
Discussion started by: Opamps123
3 Replies

4. 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

5. UNIX for Advanced & Expert Users

call sql through shell script

Hi i am not able to connect sqlplus my script is as follows $ORACLE_HOME/bin/sqlplus << ! > /tmp/extract/DM.txt and output is SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jan 18 02:53:54 2012 Copyright (c) 1982, 2008, Oracle. All rights reserved. Enter user-name: t175481... (1 Reply)
Discussion started by: tushar_spatil
1 Replies

6. Shell Programming and Scripting

Need to write shell script for my .sql file call

Hi Guys, I need to write a simple shell script which will generate a .csv file/report by calling .sql file inside a shell script. Can somebody help me on this. Thanks in advance! Regards, LK (7 Replies)
Discussion started by: lakshmanraok117
7 Replies

7. Shell Programming and Scripting

Need Help: Shell script to call sql session with variables stored in .txt file

Hi, I need help in writing a shell script which can read data from a text file (Cancel_ID.txt) and then calls sqlplus session (Cancel.sql) with the first line parameter of the text file ("0322600453") till all rows are not completed. ... (4 Replies)
Discussion started by: Khan28
4 Replies

8. Shell Programming and Scripting

Call sql script from UNIX shell script

I know this question is out there in many forums, but I tried all the combinations in vain. I'm basically trying to call a sql script from a shell script. Below is my sql script (plsql.sql) DELCARE v_empno NUMBER := '&empno'; BEGIN select ename,sal from emp where empno = v_empno;... (3 Replies)
Discussion started by: FName_LName
3 Replies

9. Shell Programming and Scripting

How to call SQL Loader in shell script?

HI Experts, I am pretty new to scripting and i need to create a perl or shell script which should fetch a file from local directory and insert the data into a table using sql loader. This will be later added to chron job to run daily and fetch all files and load them into the table. Also i... (1 Reply)
Discussion started by: sam1234
1 Replies

10. Shell Programming and Scripting

Shell script to call sql file

hi , the below script contains sql query and after executed it sends the output of the query (output.txt) to an email body with conditional subject line based on the output of all_counts_match.txt. i want to make this script generic so that it can accept the sql file as parameter and can... (5 Replies)
Discussion started by: itzkashi
5 Replies
SCRIPT(1)							   User Commands							 SCRIPT(1)

NAME
script - make typescript of terminal session SYNOPSIS
script [options] [file] DESCRIPTION
script makes a typescript of everything displayed on your terminal. It is useful for students who need a hardcopy record of an interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1). If the argument file is given, script saves the dialogue in this file. If no filename is given, the dialogue is saved in the file type- script. OPTIONS
-a, --append Append the output to file or to typescript, retaining the prior contents. -c, --command command Run the command rather than an interactive shell. This makes it easy for a script to capture the output of a program that behaves differently when its stdout is not a tty. -e, --return Return the exit code of the child process. Uses the same format as bash termination on signal termination exit code is 128+n. -f, --flush Flush output after each write. This is nice for telecooperation: one person does `mkfifo foo; script -f foo', and another can supervise real-time what is being done using `cat foo'. --force Allow the default output destination, i.e. the typescript file, to be a hard or symbolic link. The command will follow a symbolic link. -q, --quiet Be quiet (do not write start and done messages to standard output). -t[file], --timing[=file] Output timing data to standard error, or to file when given. This data contains two fields, separated by a space. The first field indicates how much time elapsed since the previous output. The second field indicates how many characters were output this time. This information can be used to replay typescripts with realistic typing and output delays. -V, --version Display version information and exit. -h, --help Display help text and exit. NOTES
The script ends when the forked shell exits (a control-D for the Bourne shell (sh(1)), and exit, logout or control-d (if ignoreeof is not set) for the C-shell, csh(1)). Certain interactive commands, such as vi(1), create garbage in the typescript file. script works best with commands that do not manipulate the screen, the results are meant to emulate a hardcopy terminal. It is not recommended to run script in non-interactive shells. The inner shell of script is always interactive, and this could lead to unexpected results. If you use script in the shell initialization file, you have to avoid entering an infinite loop. You can use for example the .profile file, which is read by login shells only: if test -t 0 ; then script exit fi You should also avoid use of script in command pipes, as script can read more input than you would expect. ENVIRONMENT
The following environment variable is utilized by script: SHELL If the variable SHELL exists, the shell forked by script will be that shell. If SHELL is not set, the Bourne shell is assumed. (Most shells set this variable automatically). SEE ALSO
csh(1) (for the history mechanism), scriptreplay(1) HISTORY
The script command appeared in 3.0BSD. BUGS
script places everything in the log file, including linefeeds and backspaces. This is not what the naive user expects. script is primarily designed for interactive terminal sessions. When stdin is not a terminal (for example: echo foo | script), then the session can hang, because the interactive shell within the script session misses EOF and script has no clue when to close the session. See the NOTES section for more information. AVAILABILITY
The script command is part of the util-linux package and is available from Linux Kernel Archive <https://www.kernel.org/pub/linux/utils /util-linux/>. util-linux June 2014 SCRIPT(1)
All times are GMT -4. The time now is 06:51 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy