Passing the unix variable to sqlplus

Shell Programming and Scripting
1

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

You can try like:

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

And call the script as ./script table_name

3

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

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

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

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.

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.