PL/SQL Code Validation without execution

Question

I would like to be able to validate that a piece of code is correct using only PL/SQL.

For example, I have the following code in a variable, I would like to validate it:

my_code:='BEGIN
           package1.get_wordlist_keywords_PG;
           package1.get_wordlist_keyword_cat_PG;
          END;
'

I would like to do something like:

result:=validate_code(my_code);

and result would be "valid" or "invalid" (or with error message etc.)

I am sure this would be possible, as Oracle Apex allows users to enter PL/SQL and runs a validation on the code when it is saved.

I am on Oracle 12c.

Thanks!

P.S. - I do not need to validate a simple sql select, it needs to be a PL/SQL code block.

score 1 · Accepted Answer · answered Mar 03 '16 at 20:58

1

Probably put your code in a procedure instead of anonymous block.

CREATE OR REPLACE PROCEDURE p1 AS
BEGIN
  package1.get_wordlist_keywords_PG;
  package1.get_wordlist_keyword_cat_PG;
END;
/
show errors;

After the execution, the procedure may or may not be created successfully. Though the procedure is created successfully, the code inside the procedure is not executed.

answered Mar 03 '16 at 20:58

Kaizhe Huang

990
5
11

Thanks - turned it into this, which lets me validate what I need... – A Sims Mar 03 '16 at 21:43

score 1 · Answer 2 · answered Mar 03 '16 at 21:44

CREATE OR REPLACE PROCEDURE VALIDATE_PLSQL 
(
  PLSQL IN VARCHAR2 
, MY_RESULT OUT VARCHAR2 
) AS 
 my_code varchar2(31900);
 validate_code varchar2(32000);
 status varchar2(4000);
BEGIN
  my_code:=PLSQL;
  validate_code:='create or replace procedure testp1 AS '||my_code;

  BEGIN
   status:='PASSED';
   EXECUTE IMMEDIATE validate_code;
  exception 
   when OTHERS then
   status:='FAILED';
  END;

  EXECUTE IMMEDIATE 'drop procedure testp1';

  MY_RESULT:=status;

END VALIDATE_PLSQL;

Pass in the block to be validated in "PLSQL" get the results in "MY_RESULT"

Clunky, but works!

PL/SQL Code Validation without execution

2 Answers2