Say the contents of p_query was a DDL statement. As I understand the current implementation of dbms_sql it executes DDL a part of the parse() call.

DROP TABLE terribly_important CASCADE CONSTRAINTS;
CREATE OR REPLACE PROCEDURE malicious_intent AS ...
GRANT EXECUTE ON malicious_intent TO wimpy_everyday_user;
etc.

Hence, if the application was one where the app user is logged onto the database as a rather privileged db user (an unfortunately common enough occurance), then this opens up quite a security hole.

cheers,
Scott

I understood that dbms_sql.parse does parse as well as execution for ddl statements.But I am interested to asking is there any way to syntax checking for stored procedure without creation.
Thank you
muthu

Hi TOM,
       this is what i execute in my SQL*PLUS (ORACLE 8.1.7)

SQL> CREATE OR REPLACE PROCEDURE sqlcheck(p_query varchar2) AS
  2     l_cursor number := dbms_sql.open_cursor;
  3  begin
  4      execute immediate 'alter session set cursor_sharing=force';
  5      dbms_sql.parse( l_cursor,P_query, dbms_sql.native );
  6      execute immediate 'alter session set cursor_sharing=exact';
  7  exception
  8      when others then
  9          execute immediate 'alter session set cursor_sharing=exact';
 10          dbms_sql.close_cursor( l_cursor );
 11          RAISE;
 12* end;
 /

Procedure created.

SQL> execute sqlcheck('SELECT * FROM EMP');

PL/SQL procedure successfully completed.

SQL> execute sqlscheck('SELECT FROM EMP');
BEGIN sqlscheck('SELECT FROM EMP'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7: 
PLS-00201: identifier 'SQLSCHECK' must be declared 
ORA-06550: line 1, column 7: 
PL/SQL: Statement ignored 

SQL> execute sqlcheck('SELECT ename,sal FROM EMP');

PL/SQL procedure successfully completed.

SQL> execute sqlcheck('SELECT ename sal FROM EMP');

PL/SQL procedure successfully completed.

 - It didn't throw error,even i commit the error. why?
 - If i have made mistake, kindly show me.
 

Hi TOM,
In above question Error message needs to be ignored.since it's my mistake. I have sent spool file without editing. Ignore the error.kindly bear with me.SORRY.

Thank you

Using 9.2.0.6

Most SQL syntax errors are picked up in PL/SQL compilation. The dbms_sql.parse picks up the error in the cursor statement below, but the procedure will compile just fine and report the GROUP BY error only when you run it. Is there a reason for this?

CREATE OR REPLACE procedure getrows AS
--
CURSOR table_cursor IS select table_name,tablespace_name,sum(num_rows) AS rowcount
from user_tables
group by tablespace_name; -- missing table_name
--
BEGIN
FOR x in table_cursor LOOP
dbms_output.put_line(x.table_name || ' ' || x.tablespace_name || ' ' || x.rowcount);
END LOOP;
end;

Nope, you either create it or not.

Or, maybe you can do this


declare

procedure that_you_want_to_create
is
...
begin
....
end;

begin
null;
end;


and parse that block -- that'll do it.

I followed your example and parsing for select,delete and et.all statements works fine.
I could not get PL/SQL procedures to be parsed by your example, I still get errors.Could you help,
------------------------------------
set serveroutput on;
declare
l_theCursor number := dbms_sql.open_cursor;
q CLOB := 'select 1 from dual;';
v CLOB := q'{
declare
begin
create or replace PACKAGE "test"
AS
----------------------------------------------------------------------------------------------------
P ROCE DURE test1(
p_user_id IN NUMBER,
p_page_name IN VARCHAR2,
p_profile_name IN VARCHAR2,
p_operation_type IN VARCHAR2,
p_operation IN VARCHAR2,
p_err_code OUT VARCHAR2,
p_err_msg OUT VARCHAR2);
----------------------------------------------------------------------------------------------------
END test;
end;
begin
null;
end;

}';
begin
execute immediate 'alter session set cursor_sharing=force';
dbms_sql.parse( l_theCursor, v, dbms_sql.native );
execute immediate 'alter session set cursor_sharing=exact';
exception
when others then
execute immediate 'alter session set cursor_sharing=exact';
dbms_sql.close_cursor(l_theCursor);
RAISE;
end;
/* dbms_sql.return_result( l_theCursor ); */
/
---------------------------------------------
thanks

declare 
begin 
create or replace PACKAGE "test" 
AS 
---------------------------------------------------------------------------------------------------- 
P ROCE DURE test1( 
p_user_id IN NUMBER, 
p_page_name IN VARCHAR2, 
p_profile_name IN VARCHAR2, 
p_operation_type IN VARCHAR2, 
p_operation IN VARCHAR2, 
p_err_code OUT VARCHAR2, 
p_err_msg OUT VARCHAR2); 
---------------------------------------------------------------------------------------------------- 
END test; 
end; 
begin 
null; 
end; 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 declare
*
ERROR at line 1:
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
ORA-06512: at line 34

Hi,
Thanks for the reply. My requirement is that, I get SQL statements, PL/SQL blocks, packages for deployment and all sort of code associated with Oracle. I want to check syntax errors only for them before deployment to DB. I thought I can use your example
--
declare
l_cursor number := dbms_sql.open_cursor;
begin
execute immediate 'alter session set cursor_sharing=force';
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
execute immediate 'alter session set cursor_sharing=exact';
exception
when others then
execute immediate 'alter session set cursor_sharing=exact';
dbms_sql.close_cursor( l_cursor );
RAISE;
end;
/
---
and see if just parsing them will be sufficient. Please share your thoughts for this requirement.

Thanks,

I have already tried Pro *C
EXEC SQL PREPARE xyz from :sql_from_a_file

This also does not work. It gives errors only for the outer-most statement not the package body

Individual statements work but not packages,
Thanks

Hi,
I can just deploy them but since I deploy over 2000 files(object and statements) if there is a failure after say 1000 files then we need to go to the developer and ask to be corrected and re-deploy them. If I can parse all files before deployment it speeds up the productivity as well as a clean build-release process.
Please let me know if this is doable,
Thanks,

select created from user_objects
where  object_name = 'P';

no rows selected

declare 
l_cursor number := dbms_sql.open_cursor; 
p_query varchar2(4000) := 'create or replace procedure p ( p int ) as
begin
  null;
end p;';
begin 

  dbms_sql.parse( l_cursor, p_query, dbms_sql.native ); 

exception 
  when others then 
    dbms_sql.close_cursor( l_cursor ); 
    RAISE; 
end; 
/ 

select created from user_objects
where  object_name = 'P';

CREATED                
08-AUG-2018 02:40:07 

Yes, Martin - the Oracle docs already says :


"152.9.26 PARSE Procedures

This procedure parses the given statement in the given cursor. "

See, the PARSE routine parses ONLY ONE SQL STATEMENT, and just SQL : no PL/SQL block or unit, just one SQL statement....

To validate the syntax for all of a given PL/SQL source code without executing it, you have two options :

a. deploy the PL/SQL program - if it is a package or procedure or function or trigger, create it with another name or (if it is an anonymous block), encapsule it in a 'dummy' procedure

OR

b. write/use a PL/SQL PARSER : https://github.com/porcelli/plsql-parser , https://github.com/raverkamp/plsql-parser , http://www.semdesigns.com/Products/FrontEnds/PLSQLFrontEnd.html?Home=FrontEnd and https://docs.oracle.com/middleware/12211/jdev/api-reference-esdk/oracle/javatools/db/plsql/class-use/PlSqlToken.html are possible examples/references...
Point is, afaik inside Oracle RDBMS there is NO PL/SQL Parser available, ONLY a SQL Parser conatined in DBMS_SQL package...

Regards,

J. Laurindo Chiappa

Yes, obviously the RDBMS contains a PL/SQL parser of course, but it is not available to be used directly by we, mere mortals : the correct phrase must be "NO PL/SQL Parser available TO end-users, it is to be used only for the RDBMS"....

Regards,

Chiappa