Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Create DB table through shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create DB table through shell script
# 1  
Old 10-05-2010
Create DB table through shell script
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  
Old 10-05-2010
You just need to connect to database. later process is same as SQL prompt.
eg, with oracle database,

Code:
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  
Old 10-05-2010
Create DB table through shell script
Hi,

This might help you.... plz reply
Code:
$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

Last edited by Franklin52; 10-06-2010 at 03:34 AM.. Reason: Please use code tags, thank you!
# 4  
Old 10-06-2010
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  
Old 10-06-2010
Its in the current directory.
more elaborated way:

Code:
#! /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
Code:
create_table.ksh

.
2. give executable permission to the script with
Code:
chmod +x create_table.ksh

3. run the script
Code:
./create_table.ksh user passwd database

# 6  
Old 10-06-2010
Thanks. I'll try it out.
# 7  
Old 10-08-2010
Here is my code,
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.
Last edited by Poonamol; 10-08-2010 at 05:28 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to create the SQLLDR control file from Oracle table.

I need to create the shell script load the few 100 table using the SQLLDR. Need to generate the control file for each table using oracle table. table has "table names" and "column names" using this need create the control file. example table rows below table_nme column_nme DEPT ... (2 Replies)
Discussion started by: pimmit22043
2 Replies

2. Shell Programming and Scripting

Shell Script Table

Hi, i need a bit help. I must write a script with shell- and sed-commands, which reads a table from stdin and writes a html-table on stdout (so i can open it with a web browser). The number of columns must be a parameter for the script, so i can start it for example with: "./htmltab.sh 3... (3 Replies)
Discussion started by: scruffytramp
3 Replies

3. Shell Programming and Scripting

Create Script from table

I have a table ABC with 4 columns and below data Col1,Col2,Col3,Col4 prod,10,12,joba prod,10,11,jobb qa,10,12,jobc I want to create an output file like this Server:prod StartTime:10 EndTime:12 JobName:joba Server:prod StartTime:10 EndTime:11 JobName:jobb (3 Replies)
Discussion started by: traininfa
3 Replies

4. Shell Programming and Scripting

How can we create a string table in bash shell ?

Hello, How can we write the pseudo code below using Bash shell scripting? table = ; i=0; do{ grep table h.txt; }while(i<3); or table = ; i=0; for(i=0;i<3;i++) grep table h.txt; (4 Replies)
Discussion started by: dbgork
4 Replies

5. Shell Programming and Scripting

Create a table using shell scripting

Hi Can we create a rectangular table as i have attached in the query . This is primarily for populating the created table with data gathered . Hope I made myself clear ... Pls suggest Thanks (1 Reply)
Discussion started by: ultimatix
1 Replies

6. UNIX and Linux Applications

create table via stored procedure (passing the table name to it)

hi there, I am trying to create a stored procedure that i can pass the table name to and it will create a table with that name. but for some reason it creates with what i have defined as the variable name . In the case of the example below it creates a table called 'tname' for example ... (6 Replies)
Discussion started by: rethink
6 Replies

7. Shell Programming and Scripting

help with a bash script to create a html table

Hi guys as the title says i need a little help i have partisally written a bash script to create a table in html so if i use ./test 3,3 i get the following output for the third arguement in the script i wish to include content that will be replace the A characters in the... (2 Replies)
Discussion started by: dunryc
2 Replies

8. Shell Programming and Scripting

Truncating table from a shell script

I am trying to truncate a table using below script. When I ran the script it runs fine but table was not truncated and the spool is empty. I am not sure what is wrong with pl/sql block. #!/bin/ksh # ---------------------------------------------------------------------- # # Created by: XXXX... (2 Replies)
Discussion started by: gunaah
2 Replies

9. HP-UX

to create a oracle table in perl script

Hi all, I have to create table for each month inside a perl script. tablename_monthnameyear. megh_aug2008 for august 2008. megh_sep2008 for september 2008. just like the logfiles created on date basis. thanks megh (1 Reply)
Discussion started by: megh
1 Replies
Login or Register to Ask a Question