Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> LAST_ERROR_POSITION and Stored-Procedure-Context

LAST_ERROR_POSITION and Stored-Procedure-Context

From: Dietmar Brueckmann <brueckmann.nixspam_at_kzvth.de>
Date: Tue, 20 Aug 2002 11:49:57 +0200
Message-ID: <ajt3c5$e6i$04$1@news.t-online.com>


Hi,

If I start in my application a select with some more statement lines and get one of these errors

ORA-00918: column ambiguously defined
ORA-00942: table or view does not exist
ORA-00904: invalid column name

it would be nice to know what column, table or view is concerned. Now I've found some PL/SQL-statements for "LAST_ERROR_POSITION" and I've build the following stored procedure to obtain column, table or view:

CREATE OR REPLACE
procedure ERROR_POS(I_SQL_TEXT in varchar2,   O_ERROR_POS out NUMBER,O_ERROR_TEXT out varchar2)   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.v7);     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;
/

and putted into a routine that shows me the word at error-position in the
SQL-Statement in red color - that's nice and it wasn't so hard to do.

But to realize it in all my projects I get a new problem: procedure ERROR_POS uses the schema-right's where it is defined - but all my users log in with there own names and have special access-rights for tables and views by grant and synonyms. If I grant ERROR_POS it is accessed based on the rights of the data-schema and doesn't find a not granted view for example.

Now my question

Is there an oracle trick to change the context in which a stored procedure is executed.

My only solution is to create the procedure for all users - what a pity.

We have Oracle 8.1.7 and my admin's say me that's a new feature of version 8.

Best regards
Dietmar Received on Tue Aug 20 2002 - 04:49:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US