Create DB table through shell script

Shell Programming and Scripting
1

Hi,
Can anyone tell me that, How to create table in Oracle database through shell script(ksh).
Table contains 3 fields,
1] Emp ID, String, primary key
2] Name, String
3] B Date, date.

Thanks in advance.

2

You just need to connect to database. later process is same as SQL prompt.
eg, with oracle database,

sqlplus -s $user/$passwd@$sid << SQL >> session.log

-- now you are on sql prompt --
-- write sql queries --
-- create tables etc -- 
quit
SQL ## leaving sql prompt and exiting database
## shell commands ##

make sure you have set oracle environment variables.

3

Hi,

This might help you.... plz reply

$1=$uid
$2=$pwd
$3=$dbname
echo "create table emp" >> emptab.sql
echo "(" >> emptab.sql
echo "Emp ID varchar2 primary key," >> emptab.sql
echo "Name varchar2(25)," >> emptab.sql
echo "B_Date date" >> emptab.sql
)" >> emptab.sql

sqlplus -s $uid/$pwd@$dbname <<eof
@emptab.sql
exit
eof
4

But what are the contents of emptab.sql file and where it is stored?
Please elaborate on this, I am new to Database and shell scripting.
Thanks a lot in advance.

5

Its in the current directory.
more elaborated way:

#! /usr/bin/ksh
sqlplus -s $1/$2@$3 << SQL >> session.log 2>&1
CREATE TABLE t (
   emp_id  VARCHAR2(10) PRIMARY KEY,
   name    VARCHAR2(25),
   b_date  DATE
);
commit;
quit
SQL
  1. put all the above code in a file name
create_table.ksh

.
2. give executable permission to the script with

chmod +x create_table.ksh
  1. run the script
./create_table.ksh user passwd database
6

Thanks. I'll try it out.

7

Here is my code,

INICIO=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
CREATE TABLE t (
   emp_id  VARCHAR2(10) PRIMARY KEY,
   name    VARCHAR2(25),
   b_date  DATE
);
/ 
EOF)

Now I am able to create table sucessfully in Oracle database.
Could you please let me know, How can I check that if table is already exist in database?
Also let me know how can I check that many tables are in dabase?
Please help me out.