Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to pass variable to SQLPLUS in a ksh script?
Top Forums Shell Programming and Scripting How to pass variable to SQLPLUS in a ksh script? Post 302074768 by tmarikle on Thursday 25th of May 2006 04:09:31 PM
Old 05-25-2006
One way is to use a co-processor in KSH.

test.sql
Code:
set serveroutput on
set feedback off verify off

variable input_number number;

begin 
    :input_number := &input_number;
    dbms_output.put_line ('your number divided by 5 = ' || :input_number / 5);
end;
/

test.sh
Code:
#! /usr/bin/ksh

# Create coprocessor
sqlplus -s /nolog |&

# Send log on message
print -p connect user/password@sid

# Call SQL
print -p @test.sql

# Answer prompts
print -p 37

# Plant a known message to allow break from loop
print -p PROMPT done

# Loop through message from co-processor
while read -p LINE
do
    # Print them; filter them; test for error message, etc.
    print - "${LINE}"

    # Break from loop when planted message is found
    [[ ${LINE} = done ]] && break
done

# Terminate co-processor
print -p quit

exit 0

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help with ksh script that uses sqlplus, called from PHP

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file... (7 Replies)
Discussion started by: j2owilson
7 Replies

2. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies

3. Shell Programming and Scripting

How to pass parameter from sqlplus(procedure completed) to your shell script

if then # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com echo "FILE does not exist" exit 1 fi echo "FILE EXIST" size=-1 set $(du /export/home/oracle/nas/scott21.dmp.gz) while do echo "Inside the loop" size=$1 set $(du... (1 Reply)
Discussion started by: sanora600
1 Replies

4. Shell Programming and Scripting

ksh variable pass to awk

I'm trying to store the response from a nawk command inside of a ksh script. The command is: text=$(nawk -F: '$1 ~ /${imgArray}/ {print $2}' ${etcDir}/captions.txt) From what I can tell, the imgArray variable is not being expanding when it is inside the single quote ('). Is there something I... (4 Replies)
Discussion started by: meman1188
4 Replies

5. Shell Programming and Scripting

AIX ksh: how to pass variable to host shell

I have a script that "runs" a script. For example: ./runscript.ksh pcnmc01.ksh runscript puts pcnmc01.ksh into the background with log output going to the logfile. After executing the command, I get this output: Running script in the background: pcnmc01.ksh Logfile:... (2 Replies)
Discussion started by: Eben Yong
2 Replies

6. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

7. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

8. Shell Programming and Scripting

ksh script trying to pass a variable to edit a file

I'm trying to create a ksh script that will ask the user for the port number. $PORT1 is the variable I want to use that will contain whatever numbers the user inputs. The script would edit ports.txt file, search and delete "./serv 110.1.0.1.$PORT1 200;=3" . So if the user types 50243 then the... (5 Replies)
Discussion started by: seekryts15
5 Replies

9. Shell Programming and Scripting

Line break in sqlplus output through ksh script

Hi, I am new to shell script programming. I have written a ksh script to run the sql File placed in server directory and spool the output in destination directory. Below Command: $ORACLE_HOME/bin/sqlplus -s $ora_uid @$sqlfile_loc$testquery.sql > $opfiledirectory It is generating the output... (6 Replies)
Discussion started by: Sumit Arora
6 Replies

10. Shell Programming and Scripting

Pass a VARIABLE to sqlplus script

Hi Team, I am trying to run a sqlplus script against several databases via a FOR/LOOP and also passing the loop variable to a sqlplus script I am calling, as follows: #!/bin/bash export ORACLE_SID=plgc1 export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1 export... (1 Reply)
Discussion started by: jonnyd
1 Replies

LEARN ABOUT DEBIAN

vuname

VIEW-OS(1)						      General Commands Manual							VIEW-OS(1)

NAME

       vuname - print view information

SYNOPSIS

       vuname [OPTION]...

DESCRIPTION

       Print certain view information.	With no OPTION, same as -s.

OPTIONS

       -a, --all
	   print all information, in the following order, except omit -p and -i if unknown.

       -s, --kernel-name
	   print the kernel name

       -n, --nodename
	   print the network node hostname

       -r, --kernel-release
	   print the kernel release

       -v, --kernel-version
	   print the kernel version

       -m, --machine
	   print the machine hardware name

       -p, --processor
	   print the processor type or "unknown"

       -i, --hardware-platform
	   print the hardware platform or "unknown"

       -o, --operating-system
	   print the operating system

       -U, --serverid
	   print the view-os server id

       -V, --viewid
	   print the view id

       -N, --viewname
	   print the view name

       -x, --nouname
	   do not use uname (without this flag the command behaves like uname		when it runs on a non View-OS enabled environment).

       -q, --quiet
	   quiet mode: error messages suppressed

       --help
	   display a help message and exit

       --version
	   output version information and exit

SEE ALSO

       umview(1),uname(1)

AUTHORS

       View-OS is a project of the Computer Science Department, University of Bologna. Project Leader: Renzo Davoli.
       <http://www.sourceforge.net/projects/view-os>

       Howto's and further information can be found on the project wiki <wiki.virtualsquare.org>.

VIEW-OS: a process with a view					   June 11, 2007							VIEW-OS(1)
All times are GMT -4. The time now is 01:42 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy