Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

[ORACLE] CREATE TABLE in bash

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [ORACLE] CREATE TABLE in bash
# 1  
Old 10-28-2008
Data [ORACLE] CREATE TABLE in bash
Hey,
I want to create a table in bash, my db server is oracle. You can find my script below:

Quote:
current_path=`pwd`
login=user
passwd=passwd
db=db
table=TABLE
file=$current_path"/Fields.dat"
login1=login1
passwd1=passwd1
table1=TABLE1

for i in `cat $file`
do

exec_query=`sqlplus -S "$login/$passwd@$db"<<EOF
DROP TABLE IF EXISITS $table1;
CREATE TABLE $table1 AS SELECT \* FROM $login.$table WHERE $i IN (SELECT $i FROM $login.$table MINUS SELECT $i FROM login1.$table);
exit
EOF`
done
I am very confused. I tried to run this script many times without any luck. Please help!
# 2  
Old 10-28-2008
The parenthesis in your subquery are probably being interpreted by the shell. Backquote them like you did the *.
# 3  
Old 10-28-2008
I don't think you need to give backslash before '*' and also in ur script brefore login1 '$' is missing. Also post what error you are getting.
# 4  
Old 10-28-2008
PHP
Quote:
Originally Posted by ranjithpr
I don't think you need to give backslash before '*' and also in ur script brefore login1 '$' is missing. Also post what error you are getting.
I removed backslash before *. I put backslashes before parenthesis. I put $ sign before login1. Unfortunately it did not help. I wish to put here any error message but I did not get any info. Any ideas?

I appreciate your help!
# 5  
Old 10-28-2008
in your script, before the sql loop, add this:
Code:
sqlplus () {
 echo >&2 "$*"
 cat >&2
}

Then you can see on STDERR what is actually getting to the sqlplus command.
# 6  
Old 10-29-2008
Quote:
Originally Posted by otheus
in your script, before the sql loop, add this:
Code:
sqlplus () {
 echo >&2 "$*"
 cat >&2
}

Then you can see on STDERR what is actually getting to the sqlplus command.
Hey. I finally resolved the problem. I am put here correct code, maybe someone will need it:

Quote:
current_path=`pwd`
login=login
passwd=passwd
db=db
table=table
file=$current_path"/Fields.dat"
login1=login1
passwd1=passwd1
table1=table1

#sqlplus () {
# echo >&2 "$*"
# cat >&2
#}

for i in `cat $file`
do

exec_query=`sqlplus -s "$login/$passwd@$db"<<EOF
DROP TABLE IF EXISITS $table1;
CREATE TABLE $table1 AS \
SELECT * FROM $login.$table \
WHERE $i IN \
(SELECT $i FROM $login.$table \
MINUS \
SELECT $i FROM $login1.$table);
exit
EOF`

done
I thought that the maybe my query is too long to put it in one line. I decided to split it - just like above. It is working fine now.

Thanks a lot for help!
# 7  
Old 10-29-2008
It is not necessary or desirable to execute the sqlplus command within backticks and put the result into an environment variable. This will hide errors and give you issues with line breaks.
The SQL "DROP TABLE IF EXISITS " is a SQL syntax error.

sqlplus -s "$login/$passwd@$db"<<EOF
DROP TABLE IF EXISITS $table1;
CREATE TABLE $table1 AS SELECT * FROM $login.$table WHERE $i IN (SELECT $i FROM $login.$table MINUS SELECT $i FROM $login1.$table);
exit
EOF
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to create a for loop statement for removing files listed in Oracle table?

Hello Frens, I am a newbie to shell scripting. I need a help on creating a for loop script (shell script) for removing files. I have a table called a_table with column name fil_name which contains all the files that need to be removed. Thank you in advance (6 Replies)
Discussion started by: manisha_singh
6 Replies

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

3. UNIX and Linux Applications

Help in copying table structure to another table with constraints in Oracle

hi, i need to copy one table with data into another table, right now am using create table table1 as select * from table2 i want the constraints of table1 to be copied to table2 also , can anyone give me some solution to copy the constraints also, now am using oracle 10.2.0.3.0... (1 Reply)
Discussion started by: senkerth
1 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. 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

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

7. Shell Programming and Scripting

Check the record count in table (table in oracle)

I have requirement: 1) Check the record count in table (table in oracle) 2) If records exists generate the file for existing records and wait for some time then Go to sleep mode and Again check the record count after 10 min.......... (Loop this process if record count >0). 3) Generate touch... (1 Reply)
Discussion started by: kamineni
1 Replies

8. Shell Programming and Scripting

Check the record count in table (table in oracle)

I have requirement: 1) Check the record count in table (table in oracle) 2) If records exists generate the file for existing records and wait for some time (Go to sleep mode) and Again check the record count after 10 min.......... (Loop this process if record count >0). 3) Generate touch... (1 Reply)
Discussion started by: kamineni
1 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