Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to validate the syntax of a Oracle dynamic SQL before execution ?
Problem Statement:
You want to validate the syntax of a SQL before execution.
Solution: The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. We can leverage DBMS_SQL.PARSE function to validate the syntax.
We will first define a function to accept the SQL statement as the parameter and parse the SQL statement.
/* * --------------------------------------------------------------------------- * Function : check_syntax * Input : sql statement * Output : Number * --------------------------------------------------------------------------- */ FUNCTION check_syntax ( p_query IN CLOB ) RETURN INTEGER IS l_cursor NUMBER := dbms_sql.open_cursor; BEGIN BEGIN DBMS_SQL.PARSE (l_cursor, p_query, DBMS_SQL.native); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR (l_cursor); RETURN -1; END; DBMS_SQL.CLOSE_CURSOR (l_cursor); RETURN 0; END check_syntax;
In the above function, l_cursor, is opened using the open_cursor function. The given SQL statement is then parsed using DBMS_SQL.PARSE which retunr -1 if there is a Syntax issue with the SQL. We will now run the function by passing a sample SQL.
Example 1 : Passing Valid SQL to the function
DECLARE l_rc VARCHAR2(100); l_sql_stmnt CLOB; BEGIN l_sql_stmnt := 'SELECT 1 FROM DUAL' ; l_rc := my_package.check_syntax(l_sql_stmnt); IF l_rc = 0 THEN dbms_output.put_line(' ** SQL Validation Pass - ' || l_sql_stmnt); ELSE dbms_output.put_line(' ** SQL Validation Fail - ' || l_sql_stmnt); END IF; END;
Output
** SQL Validation Pass - SELECT 1 FROM DUAL
Example 2 : Passing In-Valid SQL to the function
DECLARE l_rc VARCHAR2(100); l_sql_stmnt CLOB; BEGIN l_sql_stmnt := 'SELECT 1 FROM DUALED' ; l_rc := my_package.check_syntax(l_sql_stmnt); IF l_rc = 0 THEN dbms_output.put_line(' ** SQL Validation Pass - ' || l_sql_stmnt); ELSE dbms_output.put_line(' ** SQL Validation Fail - ' || l_sql_stmnt); END IF; END;
Output
** SQL Validation Fail - SELECT 1,2,4 FROM DUALED
1K+ Views
