Re: LAST_ERROR_POSITION and Stored-Procedure-Context

Lookup invoker rights in documentation - namely AUTHID CURRENT_USER. You define your procedure this way:

create or replace procedure p (...) AUTHID CURRENT_USER ...

and then GRANT EXECUTE ON P TO PUBLIC. All your users will be able to execute this procedure and it will run in their own security context.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Dietmar Brueckmann" <brueckmann.nixspam_at_kzvth.de> wrote in message
news:ajt3c5$e6i$04$1_at_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

>

>

>