Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sql parser that accepts statements on stdin?

Re: sql parser that accepts statements on stdin?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 02 Dec 1998 19:28:55 GMT
Message-ID: <36658472.17360453@inet16.us.oracle.com>


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.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 02 1998 - 13:28:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US