A shell script that login to sqlplus [message #98205] |
Tue, 11 May 2004 08:03 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
I'm trying to write a shell script that does followings.
1. log into sqlplus
2. Run a sql script (test.sql) in the current directory.
3. commit
4. exit from sqlplus
Can anyone do this?
|
|
|
|
Re: A shell script that login to sqlplus [message #98208 is a reply to message #98206] |
Tue, 11 May 2004 10:57 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
Yes it does work perfect.
Thank you.
What about this one?
I have a bunch of insert statements in test.sql.
every time insertions is done for a specific table, I want to leave a message in the directory.
For example,
insert into target1
select * from source1;
echo target1 is done > log.txt
insert into target2
select * from source2;
echo target2 is done >> log.txt
bla bla...
I think I need to exit from sqlplus temporarily
to leave a message and come back to sqlplus for more insertion.
is there better way of doing it without going out and coming back?
|
|
|
Re: A shell script that login to sqlplus [message #98209 is a reply to message #98208] |
Tue, 11 May 2004 11:35 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
set echo off
set verify off
set serveroutput on size 100000
column fname new_value fname noprint
SELECT 'mylog_' || to_char(sysdate, 'yymmdd_sssss') ||'.log' fname FROM dual;
spool &fname
begin
insert into T (select * from cat);
dbms_output.put_line('===========================================');
dbms_output.put_line('First insert: inserted '||sql%rowcount||' rows.');
dbms_output.put_line('===========================================');
end;
/
prompt ===========================================
prompt Second insert just using SQL
prompt ===========================================
insert into T (select * from cat);
spool off
|
|
|
Re: A shell script that login to sqlplus [message #98210 is a reply to message #98209] |
Tue, 11 May 2004 11:53 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
That is brilliant idea.
I think I understand everything you wrote except one thing.
column fname new-value fname noprint
what is this? it looks like you are declaring a variable for a file name.
Can you explain or tell me where to go to know more?
|
|
|
Re: A shell script that login to sqlplus [message #98211 is a reply to message #98210] |
Tue, 11 May 2004 12:41 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
It just gets a dynamic value into a variable. To make it more readable you could use different names:
set serveroutput on
--set scan on
COLUMN XXX new_val YYY noprint
SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI') XXX FROM dual;
prompt The date is &YYY
|
|
|
Re: A shell script that login to sqlplus [message #98213 is a reply to message #98211] |
Wed, 12 May 2004 06:21 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
From your example, I'm trying to figure out the purpose of XXX and YYY.
It looks like XXX is column name and YYY is a holder that will hold a value from the column.
Here are two examples that tell me my assumption is wrong. Why they don't work?
example 1
SQL> column branchname new_val ZZZ noprint
SQL> select branchname from employee where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is
example 2
SQL> column b_name new_val ZZZ noprint
SQL> select branchname b_name from employee where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is
|
|
|
|
Re: A shell script that login to sqlplus [message #98217 is a reply to message #98214] |
Thu, 13 May 2004 05:24 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
Yours works fine.
My examples don't. Can you take a look at them below?
example 1
SQL> column branchname new_val ZZZ noprint
SQL> select branchname from employee where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is
example 2
SQL> column b_name new_val ZZZ noprint
SQL> select branchname b_name from employee where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is
|
|
|
Re: A shell script that login to sqlplus [message #98218 is a reply to message #98217] |
Thu, 13 May 2004 08:50 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
it works fine for me. Try re-typing your statements. Maybe you have an undisplayed character somewhere.
SQL> -- I set scan off in my login.sql, so I'm setting it on again here
SQL> set scan on
SQL> create table emp_tst(branchname varchar2(10), name varchar2(10));
Table created.
SQL> insert into emp_tst values ('HQ', 'Charlie');
1 row created.
SQL> -- test the queries in isolation
SQL> select branchname from emp_tst where name = 'Charlie';
BRANCHNAME
----------
HQ
SQL> select branchname b_name from emp_tst where name = 'Charlie';
B_NAME
----------
HQ
SQL> -- ex1
SQL> column branchname new_val ZZZ noprint
SQL> select branchname from emp_tst where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is HQ
SQL> -- ex2
SQL> column b_name new_val ZZZ noprint
SQL> select branchname b_name from emp_tst where name = 'Charlie';
SQL> prompt the value is &ZZZ
the value is HQ
|
|
|
|
|
Re: A shell script that login to sqlplus [message #98324 is a reply to message #98307] |
Wed, 21 July 2004 19:45 |
Jai Vrat Singh
Messages: 205 Registered: September 2002 Location: Singapore
|
Senior Member |
|
|
when you use > , the file is overwritten if it already exists , i.e you loose previos contents.
when you use >> , the file is appended. This means that the output you generate is inserted to the end of already existing contents.
In both the cases, if the file does not already exist, then it is recreated?
Is that what you wanted?
|
|
|