Visit Our UNIX and Linux User Community


Passing the unix variable to sqlplus


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing the unix variable to sqlplus
# 1  
Old 09-04-2008
Java Passing the unix variable to sqlplus

Hi,

I am writing a script which creates an external table using a shell script. My requirement is like this.

Usage: . ./r.ksh <table_name> - this should create an external table.
e.g . ./r.ksh abc - this should create an external table as abc_external.

How do i achieve this? Please help me.

Regards
Anaramkris
# 2  
Old 09-04-2008
You can try like:

Code:
var=$1;
sqlplus -s '/ as sysdba'  <<EOF
set feedback off;
select * from $var;
exit;
EOF

And call the script as ./script table_name
# 3  
Old 09-04-2008
var=$1;
sqlplus -s '/ as sysdba' <<EOF
set feedback off;
select * from $var;
exit;
EOF

The above works. but i want it like this.

. ./script abc -it should create a table as "abc_Ext".

How to get to this?
# 4  
Old 09-04-2008
var=$1;
sqlplus -s <user name >/<passwd> @./prefix.sql "$1"


prefix.sql
set long 1024
set longchunksize 1024
set pagesize 0
set linesize 1024
set trimspool on
set verify off
set feedback off
set termout off
spool &1
select * from &1;
exit;
# 5  
Old 09-04-2008
Yeah.i got it.

I did like this. i have assigned two variables $1 and $2 to do this.

$1 -normal Table name
$2 - Externabl table to be created from normal table with _ext.

so, . ./script abc abc_ext creates the external table i want. The below one works perfect.

echo "CREATE TABLE SCHEMA.$EXT_TAB
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR1
LOCATION('$EXT_TAB.dmp1','$EXT_TAB.dmp2','$EXT_TAB.dmp3','$EXT_TAB.dmp4')
)
PARALLEL 4
AS SELECT * FROM SCHEMA.$TABLE;" > file.sql
sqlplus / < file.sql

Thanks for your help.
anaramkris
# 6  
Old 09-04-2008
You can tack on the _ext if you just take care to use {} around the variable name.

You don't need a temporary file, either.

Code:
sqlplus -s <<____HERE
CREATE TABLE SCHEMA.${1}_TAB
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR1
LOCATION('${1}_TAB.dmp1','${1}_TAB.dmp2','${1}_TAB.dmp3','${1}_TAB.dmp4')
)
PARALLEL 4
AS SELECT * FROM SCHEMA.${1}_TAB;" 
___HERE

I have a very vague idea about the SQL part so I might have adapted it incorrectly.

Previous Thread | Next Thread
Test Your Knowledge in Computers #869
Difficulty: Medium
Fortran introduced the concept of automatic garbage collection.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sqlplus variable UNIX

hi guys i have a sqlplus : sqlplus -s username/password << EOF @mysql.sql EOF in mysql.sql there is a count of a table, i want to write in a variabile unix. how can i do? Thanks a lot Regards Francesco. (3 Replies)
Discussion started by: Francesco_IT
3 Replies

2. Shell Programming and Scripting

Passing sqlplus output to shell variable

Hi , I am using below code : for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'` do ORACLE_SID=$i export ORACLE_SID; dest=`sqlplus "/ as sysdba" <<EOF set heading off feedback on verify off select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Discussion started by: admin_db
5 Replies

3. Shell Programming and Scripting

How to print huge values in sqlplus variable in UNIX?

Hi, I ahve a unix code as below. sqlTxt=$* sqlReturn=`echo "set feedback off; set heading off; set term off; set verify off; set serveroutput on size unlimited set lin 1000; VARIABLE GV_return_val NUMBER; VARIABLE GV_script_error varchar2(4000); EXEC :GV_return_code := 0; EXEC... (6 Replies)
Discussion started by: bhaski2012
6 Replies

4. Shell Programming and Scripting

passing an unix variable to an XML

I need help I have a unix command : VERSION=$(ls -d /vsn/v12.??.??.?? | sort | tail -1) when i do echo $VERSION, i get the exact value, i want. Now i want to use this variable and pass it to an xml. How can i do that? (1 Reply)
Discussion started by: samk
1 Replies

5. UNIX for Dummies Questions & Answers

Passing a Unix parameter to SQLPlus login command

hi All, i m trying to pass a user choice paramter from unix to sqlplus connect command here i want the user to enter the username and password he wants to connect in sql plus through read in unix and then automatically connect to that instance. sqlplus -s $1/$2 where $ 1 and $2 will b... (2 Replies)
Discussion started by: Jcpratap
2 Replies

6. Shell Programming and Scripting

error in passing a variable to sqlplus from a shell script

hi, I am using a shell script from where i will be conecting to sqlplus.. i am having a problem in passing a variable to sqlplus query.. i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Discussion started by: kripssmart
3 Replies

7. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies

8. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

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

10. Programming

Passing C Variable To Unix

I want to pass a variable set in my c program to a shell script (which will also be invoked or initiated from the same C program using the C's system command). Is it possible ? :confused: (3 Replies)
Discussion started by: kapilv
3 Replies

Featured Tech Videos