access to dbms_sql in a TOracleQuery

[dbrueckmann1]

Hi,

for my non-DOA-projects(BDE/DBExpress) I want to replace the error-position-feature using the following stored procedure:

CREATE OR REPLACE PROCEDURE dbr_error_pos(I_SQL_TEXT in varchar2,
O_ERROR_POS out NUMBER,O_ERROR_TEXT out varchar2)
AUTHID CURRENT_USER
IS
cur1 integer;
BEGIN
O_ERROR_POS := -1;
O_ERROR_TEXT := '?';
cur1:=dbms_sql.open_cursor;
dbms_sql.parse(cur1, I_SQL_TEXT,dbms_sql.native);
dbms_sql.close_cursor(cur1);
EXCEPTION
when others then
O_ERROR_POS := dbms_sql.LAST_ERROR_POSITION;
O_ERROR_TEXT := SQLERRM;
if dbms_sql.is_open(cur1) then
dbms_sql.close_cursor(cur1);
end if;
END;
/

My idea is to code the above in a TOracleQuery to avoid granting the procedure dbr_error_pos to all users. Is this psossible?

 

[Marco Kalter]

Sure. If you remove the trailing slash (which is SQL*Plus syntax) it will run fine. You will end up with a lot of dbr_error_pos procedures though.

 

[dbrueckmann1]

Sorry but I've written a misunderstoodable question.
My goal is to access dbms_sql in a similiar way the stored procedure does, without creating it (my DBA will not be amused to find many of my DBR_ERROR_POS).

 

[Marco Kalter]

That is possible too. You can execute the following PL/SQL Block:

DECLARE
  cur1 INTEGER;
BEGIN
  :o_error_pos  := -1;
  :o_error_text := '?';
  cur1          := dbms_sql.open_cursor;
  dbms_sql.parse(cur1, :i_sql_text, dbms_sql.native);
  dbms_sql.close_cursor(cur1);
EXCEPTION
  WHEN OTHERS THEN
    :o_error_pos  := dbms_sql.last_error_position;
    :o_error_text := SQLERRM;
    IF dbms_sql.is_open(cur1) THEN
      dbms_sql.close_cursor(cur1);
    END IF;
END;

You need to declare i_sql_text, o_error_pos and o_error_text as variables, so that you can pass the SQL text in and get the error info out.

 

[dbrueckmann1]

Marco, that's great.
Many thanks for your help.