What if it were DDL?
Scott, May 03, 2002 - 9:41 am UTC
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
May 03, 2002 - 11:45 am UTC
Correct -- good point. Should probably add:
if substr(upper(ltrim(p_query)),1,6) in ( 'SELECT', 'INSERT', 'UPDATE', 'DELETE' )
the
parse it.
syntax checking for stored procedure without creation...
Ramasubbu, May 04, 2002 - 12:51 am UTC
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
May 04, 2002 - 1:07 pm UTC
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.
Where did i commit mistake?
perumal, May 17, 2004 - 8:45 am UTC
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.
May 17, 2004 - 9:22 am UTC
first error because of your typo -- sqlScheck
neither of the second are "in error". select ename sal from emp -- it is legal, you just renamed ename to sal.
Kindly leave the error.Since it has been taken from SPOOL file..
A reader, May 17, 2004 - 9:03 am UTC
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
Thank you. TOM
perumal, May 18, 2004 - 12:39 am UTC
Does PL/SQL do only partial SQL checking?
Jon Waterhouse, May 12, 2005 - 12:08 pm UTC
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;
May 12, 2005 - 1:34 pm UTC
because that is a runtime semantic error, not a syntax error.
the plsql parser is just doing syntax checking at this point - finding the binds, setting it up. it is not until runtime that it is actually "executed"
Want more information
Martin Lourduswamy, August 06, 2018 - 3:30 pm UTC
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
August 06, 2018 - 4:26 pm UTC
There are lots of problems with the PL/SQL you're trying to test:
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;
You have at least the following problems:
- You don't create or replace packages within a PL/SQL block; declare/begin is unnecessary
- Spaces in P ROC EDURE
Errors screen
Martin Lourduswamy, August 06, 2018 - 3:34 pm UTC
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
Need a sample code for parsing PL/SQL packages
Martin Lourduswamy, August 06, 2018 - 8:04 pm UTC
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,
August 07, 2018 - 1:00 pm UTC
Why are you doing that? Why not just deploy them?
Pro *C
Martin Lourduswamy, August 06, 2018 - 8:13 pm UTC
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
Followup
Martin Lourduswamy, August 07, 2018 - 3:41 pm UTC
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,
August 08, 2018 - 9:41 am UTC
Parsing PL/QSL will create the object:
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
It sounds like you need a pre-prod/QA database to test your release scripts...
Or you could look into the parsers J. Laurindo Chiappa suggested below.
To Martin Lourduswamy
J. Laurindo Chiappa, August 07, 2018 - 4:04 pm UTC
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
August 08, 2018 - 9:43 am UTC
There is a PL/SQL parser in Oracle Database! But it will also create the object at the same time...
Thanks for sharing the other options.
Obvious....
J. Laurindo Chiappa, August 08, 2018 - 2:16 pm UTC
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
August 08, 2018 - 3:39 pm UTC
Yes, that is true.