PL/SQL Code Validation without execution [duplicate]

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.

Kaizhe Huang · Accepted Answer · 2016-03-03 20:58:03Z

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 3, 2016 at 20:58

Kaizhe Huang

1,0146 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A Sims Over a year ago

Thanks - turned it into this, which lets me validate what I need...

A Sims · Accepted Answer · 2016-03-03 21:44:11Z

1

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!

answered Mar 3, 2016 at 21:44

A Sims

1072 silver badges10 bronze badges

Collectives™ on Stack Overflow

PL/SQL Code Validation without execution [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related