| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql parser that accepts statements on stdin?
On Wed, 02 Dec 1998 16:25:45 GMT, sweth_at_nit.gwu.edu (Sweth
Chandramouli) wrote:
> is there some tool (or hidden switch on sqlplus) that will
>connect to an oracle db in a unix environment and run commands that
>were passed to it on stdin, as opposed to being read from a file?
>i've got a lot of tasks that i would like to automate, but which
>require dynamically building many of the statements to be run and/or
>making ddl statements, so straight pl/sql won't work. i've taken a
>glance at the dbms_sql package, and would rather just use my existing
>knowledge of other scripting languages like perl to build all of the
>statements and then just execute them once, rather than learning the
>dbms_sql syntax (i'm suffering from a bit of syntax overload as it is,
>and one more won't help). i would really rather not depend on temp
>files, however, for both security and data integrity reasons, and
>can't figure out any way to get my commands to actually execute
>without writing them out to a file and then having sqlplus read them.
>
> tia for any suggestions.
I know you said that you do not want to use dbms_sql but you could write a single PL/SQL procedure that will execute all commands.
eg.
create or replace
procedure execute_it( p_statement varchar2 ) is
l_cursor number;
l_status number;
begin
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, p_statement, dbms_sql.native );
l_status := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open( l_cursor ) then
dbms_sql.close_cursor( l_cursor );
end if;
end execute_it;
/
With this procedure you can pass it a statement and execute it. No more syntax needed unless you need to bind values or if you are running a query.
But if you want to use sql*plus to accomplish this from the command line, something like this might help you.
--> echo "select * from emp where empno = 7369;" | sqlplus scott/tiger
SQL*Plus: Release 8.0.3.0.0 - Production on Wed Dec 2 13:29:42 1998
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- ---------- --------- ---------- --------- ----------
DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800
20
SQL> Disconnected from Oracle8 Enterprise Edition Release 8.0.3.0.0 -
Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
hope this helps
chris.
> -- sweth.
--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |