Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> LAST_ERROR_POSITION and Stored-Procedure-Context
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;
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