Validating PLSQL statement in vb.net program

Question

I have a TextEdit in my GUI which takes PlSql statement, which further can be saved by user in DB. The requirement is to validate this PlSql statement before saving. In Oracle we can do this with the following code:

DECLARE
    cursor_name INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,'select * from schema.TableName',DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
        dbms_output.put_line('Invalid PlSql');
END;

Hence when given PlSql or SQL is not a valid statement then I get the error message 'Invalid PlSql'. Can anyone suggest how can I catch this exception in my vb.net code? I mean I can excute this query in my code but I can not make any stored procedure in DB in oder to get the exception as return value.

Is there any other way we can validate/parse PlSql statements in vb.net?

rather than catching the exception and using DBMS_OUTPUT to inform the client you should simply let Oracle raise the error and catch it client side. Additionally there is nothing stopping you having the code above ss an anonymous PL/SQL block passed from your application but it would better implemented as a procedure. — Owen S
– Owen S, Commented Jan 17, 2015 at 18:40

Atul Kr Dey · Accepted Answer · 2018-06-04 16:14:05Z

2

create procedure p_return_type(v_resp in out varchar2)
is
    cursor_name INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,'select * from schema.TableName',DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
        v_resp:='Invalid PlSql';
        dbms_output.put_line('Invalid PlSql');
END;

answered Jun 4, 2018 at 16:14

Atul Kr Dey

1601 silver badge13 bronze badges

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

Collectives™ on Stack Overflow

Validating PLSQL statement in vb.net program

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related