Home » Other » Client Tools » SQLPlus command parser
SQLPlus command parser [message #591754] Wed, 31 July 2013 08:33 Go to next message
sabaka1391
Messages: 4
Registered: July 2013
Location: Sevastopol
Junior Member

Hello everyone!
Please help me. I need to parse .sql file to tree and I can't find how to do it.
(We apply sql files by sqlplus and we have some problems with '/': after anonymous blocks - it haven't applied without '/'; Also not-anonymous block applied twice when we have both ';' and '/'. I need to report about problems before file will apply.)
I've tried to use ANTLR with it grammar but it build awful tree.
Re: SQLPlus command parser [message #591756 is a reply to message #591754] Wed, 31 July 2013 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQLPlus command parser [message #591765 is a reply to message #591754] Wed, 31 July 2013 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you want to do is clear as mud.

Post an example.

Regards
Michel
Re: SQLPlus command parser [message #591776 is a reply to message #591765] Wed, 31 July 2013 09:56 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
I'm guessing he wants a program to syntax-check a SQL script before bundling it up into a release, for example.

sabaka, you should have a testing environment to sanity check scripts/releases. They should be thoroughly tested before they get anywhere near a production environment.

[edit: typo]

[Updated on: Wed, 31 July 2013 10:54]

Report message to a moderator

Re: SQLPlus command parser [message #591793 is a reply to message #591776] Wed, 31 July 2013 10:53 Go to previous messageGo to next message
sabaka1391
Messages: 4
Registered: July 2013
Location: Sevastopol
Junior Member

gazzag, yes, you are right
Unfortunately I can't to test scripts thoroughly. I need to create check tool.

My problem:
DECLARE
	begin
		INSERT INTO T1 VALUES(3);
	end;

SQL> @path/first.sql
6  

But with /
DECLARE
	begin
		INSERT INTO T1 VALUES(3);
	end;
/

SQL> @path/first.sql

PL/SQL procedure successfully completed.


For non-anonymous:
 select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          2          3


INSERT INTO T2 VALUES(4,5,6);
/

SQL>  @path/first.sql

PL/SQL procedure successfully completed.


1 row created.


1 row created.

SQL> select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          2          3
         4          5          6
         4          5          6


I have to report when I catch / after the not-anonymous block or when it omitted after the anonymous block.
Re: SQLPlus command parser [message #591795 is a reply to message #591776] Wed, 31 July 2013 10:55 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm guessing he wants a program to syntax-check a SQL script before bundling it up into a release, for example.


If this is the case then DBMS_SQL can do it easily.
Here's a SQL parser:
create or replace procedure checkSQL (p_sql varchar2) 
authid current_user
is 
   handle int := dbms_sql.open_cursor; 
begin 
   dbms_sql.parse (handle, p_sql, dbms_sql.native); 
   dbms_sql.close_cursor (handle); 
exception
  when others then 
    if dbms_sql.is_open(handle) then dbms_sql.close_cursor (handle); end if;
    raise;
end; 
/ 

SQL> exec checkSQL('select * from dual');

PL/SQL procedure successfully completed.

SQL> exec  checkSQL('select michel from dual');
BEGIN checkSQL('select michel from dual'); END;

*
ERROR at line 1:
ORA-00904: "MICHEL": invalid identifier
ORA-06512: at "MICHEL.CHECKSQL", line 11
ORA-06512: at line 1

SQL> exec  checkSQL(q'[update dba_users set username='FOO']')
BEGIN checkSQL(q'[update dba_users set username='FOO']'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MICHEL.CHECKSQL", line 11
ORA-06512: at line 1

Regards
Michel
Previous Topic: sqlplus spool and substitution parameters
Next Topic: How to handle errors in sql files by SQLPlus
Goto Forum:
  


Current Time: Thu Mar 28 11:48:39 CDT 2024