Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why
Prev   Next
# 3  
Old 05-13-2017
What you are trying to do won't work with this method of file reading:
Code:
while read line
do
    # something
done < myfile

because the next line from "myfile" will be fed as a response to the "accept" command in the sqlplus script within the loop.

You will have to read your file using the file descriptor method.
Change your "x_main.ksh" to something like this:

Code:
#!/bin/ksh
sqlfile="x_main.sql"
env="TESTDB.TESTDB.COM"
FILENAME="x_schema.txt"
# open file for reading; assign descriptor
exec {FD}<${FILENAME}
while read -u ${FD} LINE
do
    username=`echo ${LINE} | awk -F"/" '{ print $1 }'`
    password=`echo ${LINE} | awk -F"/" '{ print $2 }'`
    echo "- username => $username // pasword => $password"
    sqlplus /nolog @${sqlfile} $username $password $env
done
# close file
exec {FD}<&-

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calling Oracle stored procedure from ksh script

Friends, I'm newbie with ksh so wanting some help.... 1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure. 2. Put name1 and name2 value from script run replacing $3 & $4 I'm trying to put name1 in... (4 Replies)
Discussion started by: homer4all
4 Replies

2. Shell Programming and Scripting

How to accept arguments in shell script when calling in perl

I have a shell script like this: #!/bin/sh $PYTHON MetarDecoder.py < ../data/mtrs/arg1/arg2 And I'm calling it with this in perl: my $output = `./metar_parse.sh --options`; It's successful when I put in actual values for arg1 and arg2 in the shell script, but I'd like to pass arguments... (1 Reply)
Discussion started by: civilsurfer
1 Replies

3. Shell Programming and Scripting

Calling SQL script from ksh job and send mail on some error.

Hi, I am trying to call sql script from ksh job with parameters.The parameters passed from ksh job will be used in SELECT query in sql file to SPOOL the data in extract file.My questions are: 1) How to call a sql script from ksh job with parameters? 2) How to use the parameter in sql file to... (1 Reply)
Discussion started by: anil029
1 Replies

4. Shell Programming and Scripting

calling a sql file in my shell script

Hi, I want to call a sql file in my shell script. see the below code:- if ] then ( isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://" ${S_PWD} set nocount on go use ${S_DB} go // need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies

5. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

6. UNIX for Advanced & Expert Users

Calling sql file from shell script

Hi I have a shell script that call a sql file. The sql file will create a spool file. My requirement is, when ever i get an OS error like file not found. I have to log it in a log file. Could some who worked in a like scenario help me by giving the code sample. Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies

7. Shell Programming and Scripting

calling a PL/SQL stored procedure from KSH

Hi I have a stored procedure which should be called from KSH. Could ayone please help me with this. Thanks (1 Reply)
Discussion started by: BlAhEr
1 Replies

8. Shell Programming and Scripting

calling sql file from shell script

Hello everybody I need help calling sql file from shell script. Can anyone help me creating a small shell script which calls an sql file . The .sql file should contain some select statements like select emp_no from emp_table; select emp_id from emp_table; And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies

9. Shell Programming and Scripting

Problem with Calling sql file from shell script

I have created abc.sh file which will set the environment variables (UNIX env variables as well as ORACLE required variables like ORACLE_SID,ORACLE_HOME etc) and then calls a function file which checks for starts some logs and then it will try to execute the .sql file. The .sh, function file are as... (1 Reply)
Discussion started by: sskc
1 Replies

10. UNIX for Advanced & Expert Users

Problem while calling Oracle 10g SQLPLUS files

Hi all, Iam facing a lot of problem while calling Oracle 10g SQLPLUS files from shell. What is the standard procedures to be taken care. Any help would be useful for me. Thanks in advance, Ganapati. (2 Replies)
Discussion started by: ganapati
2 Replies
Login or Register to Ask a Question

LEARN ABOUT DEBIAN

sql::translator::parser::oracle

SQL::Translator::Parser::Oracle(3pm)			User Contributed Perl Documentation		      SQL::Translator::Parser::Oracle(3pm)

NAME

       SQL::Translator::Parser::Oracle - parser for Oracle

SYNOPSIS

	 use SQL::Translator;
	 use SQL::Translator::Parser::Oracle;

	 my $translator = SQL::Translator->new;
	 $translator->parser("SQL::Translator::Parser::Oracle");

DESCRIPTION

       From http://www.ss64.com/ora/table_c.html:

	CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...)
	    [ON COMMIT {DELETE|PRESERVE} ROWS]
		[storage_options | CLUSTER cluster_name (col1, col2,... )
		   | ORGANIZATION {HEAP [storage_options]
		   | INDEX idx_organized_tbl_clause}]
		      [LOB_storage_clause][varray_clause][nested_storage_clause]
			  partitioning_options
			     [[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause]
				[ENABLE enable_clause | DISABLE disable_clause]
				    [AS subquery]

       tbl_defs:
	  column datatype [DEFAULT expr] [column_constraint(s)]
	  table_ref_constraint

       storage_options:
	  PCTFREE int
	  PCTUSED int
	  INITTRANS int
	  MAXTRANS int
	  STORAGE storage_clause
	  TABLESPACE tablespace
	  [LOGGING|NOLOGGING]

       idx_organized_tbl_clause:
	  storage_option(s) [PCTTHRESHOLD int]
	    [COMPRESS int|NOCOMPRESS]
		[ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

       nested_storage_clause:
	  NESTED TABLE nested_item STORE AS storage_table
	     [RETURN AS {LOCATOR|VALUE} ]

       partitioning_options:
	  Partition_clause {ENABLE|DISABLE} ROW MOVEMENT

       Column Constraints (http://www.ss64.com/ora/clause_constraint_col.html)

	  CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY} constrnt_state

	  CONSTRAINT constrnt_name CHECK(condition) constrnt_state

	  CONSTRAINT constrnt_name [NOT] NULL constrnt_state

	  CONSTRAINT constrnt_name REFERENCES [schema.]table[(column)]
	     [ON DELETE {CASCADE|SET NULL}] constrnt_state

       constrnt_state
	   [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
	      [RELY | NORELY] [USING INDEX using_index_clause]
		 [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
		     [EXCEPTIONS INTO [schema.]table]

       Note that probably not all of the above syntax is supported, but the grammar was altered to better handle the syntax created by
       DDL::Oracle.

AUTHOR

       Ken Youens-Clark <kclark@cpan.org>.

SEE ALSO

       SQL::Translator, Parse::RecDescent, DDL::Oracle.

perl v5.14.2							    2012-01-18				      SQL::Translator::Parser::Oracle(3pm)