I use a table trigger to limit DML actions on one (or more) columns of the table by a user who is not the owner of the table. (I cannot use views). To add to the complexity, the trigger is defined in a separate schema with create any trigger privilege and this user does not have any other special privileges. Simply put, I am maintaining column level privileges to user tables with a PL/SQL APIs to grant and revoke privileges on some special columns. The triggers are maintained in a special schema along with the dictionary (privilege) information.

In the current implementation, I am using SYS_CONTEXT('USER_ENV','CURRENT_USER') to check if the user modifying the content in the column has the privilege to do so. This works fine as long as the end user is performing direct INSERT/UPDATE into this table. But this approach fails if the owner of the table creates a procedure to perform the DML and grants execute privileges on this procedure to other users. In this case, no matter who invokes the procedure, the insert into the column should go through as the owner (of the procedure and the table) always has the full access to the column. But, the use of
SYS_CONTEXT('USER_ENV','CURRENT_USER') is restricting this operation because it always computes to the original caller of the procedure and not the owner of the procedure (or the invoker of the DML statement).

So, to mimic the typical privilege model in the presence of user defined function/procedures, I would like to get the user who "issued" the INSERT/UPDATE statement (dynamic/static) within the trigger body. Is the who_am_i (like) procedure my only option ? Is there any trigger variable (like UPDATING) that can give this information ?

Your help is greatly appreciated.

Thanks,
-Anil.

Yes, I could have used grant privileges on the columns. But when the user does grant insert on the table, I still want to restrict privileges on some columns (based on the datatype). The content of these columns is special and this is required to ensure that the other users do not get privileges on them when the owner simply does table level grant. I want the owner to take some explicit action so that he knows what he is doing.

Thanks for the response.
-Anil

Tom,

I have your sample MY_CALLER function from your book. Does this work on Oracle 9i?

Hi Tom

I was trying to use the who_called_me procedure.
For some reason it falls up with the following error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error-6502
(I have pasted my code below which calls this.)
I have traced the error and it comes up on the following line in who_called_me

lineno := to_number(substr( line, 13, 6 ));
I put debug messages just before this line and I get
the following values for
lineno=c0000000197b5a30 11 anonymous block
lineno= <BLANK>


I am sure I am doing something wrong here.
Can you please help on this

Thanks
Ashwin N.



PACKAGE BODY measure_time
AS
PROCEDURE set_time IS
BEGIN
time_before := DBMS_UTILITY.GET_TIME();
END;
PROCEDURE get_time(process_name IN VARCHAR2) IS
time_after NUMBER;
time_elapsed NUMBER;
prog_name VARCHAR2(30) ;
prog_owner VARCHAR2(30);
lineno VARCHAR2(30);
caller_t VARCHAR2(30);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_OUTPUT.PUT_LINE(1);
who_called_me(prog_owner,prog_name, lineno, caller_t);
DBMS_OUTPUT.PUT_LINE(2);
time_after := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(3);
time_elapsed := (time_after-time_before)/100;
DBMS_OUTPUT.PUT_LINE(4);
INSERT INTO measure_time_table VALUES(prog_name,process_name,time_elapsed,SYSDATE);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||SQLCODE);
END;
END;

Just a small change

It Should have been

line=c0000000197b5a30 11 anonymous block
lineno= <BLANK>

Regards
Ashwin N

In the last follup you said
Followup:
the address is different then I anticipated -- just adjust the code a bit to
parse it correctly on your server.

I am also getting the same pl/sql numeric value error.
Can u pls elaborate how to adjust the parse statement ?What
should be the substr.
lineno := to_number(substr( line, 13, 6 )); is giving the error.
To correct the problem i used substr(line,11,9).
Did i do the correct thing. How do I the "handle"address wont increase.

Do i need to adjust some more place

handle number name
0x51125dd8 6 procedure SCOTT.WHO_CALLED_ME
0x527b1d1c 12 SCOTT.TRG_AIU_TEST

I tried using your same example but did the insert on table t2 from a remote DB procedure p2_remote and it is not giving me the remote procedure name nor the owner/schema name.
but it works if i am in the same DB but different schema
Any clues to this one ?

The remote DB doesnot see that.I printed the stack to confirm.I think its tied to its own DB and stack info cannot cross from one DB to another remote DB

So what is the solution here to know who called the trigger in case of RPCs.
Thanx much

This looks like a tough one.
Thanx

Can we use dbms_pipe ,abms_alert , AQ on a Distributed DB for the above issue.

Can we sen a message to a pipe to be retrieved by a remote DB.
Still struggling with this issue of knowing who fired if the firing is done from a Remote DB.
Thanx

I made the following changes to correct the problem:

Line 30
FROM lineno := to_number(substr( line, 13, 6 ));
TO lineno := to_number(substr( line, 21, 6 ));

Line 31
FROM line := substr( line, 21 );
TO line := substr( line, 29 );

I'm on Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production

Excellent!
I've been looking for this for I don't know how long.
Thanks!

Does not work with Oracle Forms and db links - at least on Oracle 8i:

</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=197738.1 <code>

Is there a way of telling a particular PL/SQL stored program unit is being called by an Oracle GUI tool for sure.
Last metalink post suggests Forms uses RPC, is that a hint for the above problem?
Tried testing that a Null value for format_call_stack as an indicator, but this value is incorrect as it is nulls between PL/SQL calls across databases.
Any suggestions?

Tom,

The link at the top (who_called_me) seems to be invalid.

</code> http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>


Very useful bit of code if I remember well.

Change osi to asktom
</code> http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>

Tom,

dbms_utility.format_call_stack and who_am_i is excellent!

If we are in a function/procedure inside a package, is there a way to know the function/procedure also? Using dbms_utility.format_call_stack I could only get the package name as shown below.


create or replace procedure p(lcString in Clob, lnLength in number default 255)
as
temp Clob;
lnLength1 number;
begin
if lnLength > 255 then
   lnLength1 := 255;
else
   lnLength1 := nvl(lnLength,255);
end if;
temp := lcString;
WHILE( LENGTH( temp  ) > 0 )
LOOP
   dbms_output.put_line(substr(temp,1, lnLength1));
  temp  := SUBSTR( temp , lnLength1+1  );
END LOOP;
end;
/
create or replace package test_pkg
as
procedure test_pro;
end;
/

create or replace package body test_pkg
as
procedure test_pro
as
begin
p(dbms_utility.format_call_stack);
p('who_am_i:'||who_am_i);
end;
end;
/

SQL> exec test_pkg.test_pro;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7D7814F0         6  package body INTERSPC.TEST_PKG
7D645A24         1  anonymous block

who_am_i:INTERSPC.TEST_PKG
PL/SQL procedure successfully completed.

Is there a way to get TEST_PKG.test_pro ? . If it is there, then that would be very much useful in error logging functionality.

Thanks,
Shiju 

Tom,
I had been using the who_called me procedure without a problem, then started getting the numeric error that others had mentioned. Obviously there had been a system change which caused the object handle string size to change. Here is a fix that will handle this possibility:

Replace lines 30-31:
lineno := to_number( substr( line,instr(line,' ',-1,2)+2,
instr(line,' ',-1,1)-instr(line,' ',-1,2) ));
line := substr( line, instr(line,' ',-1,1)+2 );

Tom,

Is there any view/table for me to know when the trigger was last fired - if at all it fired ?

We have a trigger like following in our database -
create table t_LOGGER ( msg varchar2(4000) );
create public synonym t_LOGGER for t_LOGGER ;
grant INSERT,SELECT on t_LOGGER to PUBLIC ;


create or replace trigger
t_FAILED_TO_EXTENT_TEMP
after servererror on database
declare
begin
if ( is_servererror(1652) )
then
insert into t_LOGGER values ( 'ora_sysevent = ' || ora_sysevent );
insert into t_LOGGER values ( 'ora_login_user = ' || ora_login_user );
insert into t_LOGGER values ( 'ora_server_error = ' || ora_server_error(1) );
insert into t_LOGGER
select 'Sorts:'||SORTS||
'! DISK_READS:'||DISK_READS||
'! BUFFER_GETS:'||BUFFER_GETS||
'! LOADS:'||LOADS||
'! INVALIDATIONS:'||INVALIDATIONS||
'! PARSE_CALLS:'||PARSE_CALLS||
'! SQL_Text:'||SQL_TEXT
from v$sql
where (HASH_VALUE,ADDRESS) in
(SELECT HASH_VALUE,ADDRESS from v$open_cursor where sid
= (select sid from v$mystat where rownum=1) );
end if ;
end ;

In this trigger am capturing the SQL when Temp tablespace error comes.
We have been notified by our application team that last night they had problem with temp tablespace cant extend, and verified with the logs from application team.

However the above trigger was expected to fill in rows of a table regarding the SQL,but it didnt.

I have checked this complete scenario in a seperate DB and all worked fine there giving me proper SQLs.
But in this database, I didnt get any rows populated.
Any idea why ?

With Regards,
Shalini.

Hi Tom,

Wanted to know the trigger performance on a heavily updatable table.

I have a fairly big table (2 million) & I'm going to write a trigger (after update) on it.
the trigger looks like this

---------------------------------------
CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE ON tab1
FOR EACH ROW
DECLARE


IF (:NEW.prov_status_id = 73 AND :NEW.action_type_id =2 )THEN

<Do the needful>

end if;

end;

----------------------------------------
the table is heavily updatable as well.
but my IF condidion (status_id = 73) is going to occur hardly e.g. 20 times a day.

so will it hamper the performace of the trigger??

thanks in advance.

Tom,

I'm trying to call a procedure from a trigger.
And the procedure is doing some commit/rollback/savepoint.

so the trigger is throwing the error back as ORA 4092.

So can I not do any commit/savepoint in the procedure that is being called by the trigger??

I faced a strange problem few times
while populating some tables in a new database
created from the legacy database.
I may not be able to repeat / recreate that,
but would just describe the situation
as per my perception.

current login / Session user-name is PRMS

insert /*+ APPEND */ into PRMS.table1
select t1.*, 'VRO' from VRO.table1
where NOT exists
(select null from PRMS.table1 where RO_Abb = 'VRO');

It gave me error saying *something like*
the invalid trigger MRO.table_xxxx.trigger_xxxx
could not be re-validated.

After dropping
the trigger trigger_xxxx
on table_xxxx
of user MRO,
I could execute the statement successfully.

I wonder how come a trigger on a table
of some third user got fired ?!

I have created / defined an FGAC-policy
on table PRMS.table1
to restrict the row-selection by "RO_ABB = User".

There was (and is) no trigger on PRMS.table1.
There was (and is) no trigger on VRO.table1.

Well, the client was toad;
but, this time I could not doubt the client.

I feel there might be something
that I am missing.

This happened nearly 20 times
while populating various tables
in schema PRMS
selecting from 150 tables
from 12 different schemas / users.

This did not happen for PRMS.table1
for some users before it occured for user VRO.
i.e.,

insert /*+ APPEND */ into PRMS.table1
select t1.*, 'VRO' from VRO.table1
where NOT exists
(select null from PRMS.table1 where RO_Abb = 'VRO');

gave error, but

insert /*+ APPEND */ into PRMS.table1
select t1.*, 'DRO' from DRO.table1
where NOT exists
(select null from PRMS.table1 where RO_Abb = 'DRO');

did not give any error though it (DRO) was executed
prior to VRO.

I had dropped all the triggers
of all the tables
of the schema PRMS.

select * from User_Triggers;

gave me "No rows returned".

After that only I started population further.

I dropped so many (ALL the) triggers
because I did not want them to be fired at any cost
since this was loading of
legacy (static - read-only) data.
It was a precess of creating / building
of a new instance
used solely by me.
If need be there,
I can re-create (re-import) the triggers later on.

This is new to me : "By the way part 2 - triggers make /*+ append */ a big "no-op", it doesn't append when triggers are there. "

All the details in the discussions were very useful but for some reason when i try to compile my trigger (using proc who_called_me) it says identifier who_called_me needs to be declared. I am using oracle 8i ....... why is this proc not available to me???

On this page I asked a query on March 11/12 2006.
I did not have any copy-paste and therefore I could not give much details.
Recently similar problem on a different server (hardware) and therefore different instance (SID).
After I imported a table form Live User (LU) to a dummy user (DU) (same instance)
The column Table_Owner of the User_Triggers view of the DU now shows as "LU";
hence the LU table insert fails, since the DU table trigger is INVALID.

The details (copy/paste) are as under.

(1)
exp sys/mymm06dbsys full=Y file=g:\oraexp\ears_full.dmp log=g:\oraexp\ears_full.log

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export the entire database ...
<Deleted Several Lines Here>
. about to export DBSNMP's tables via Conventional Path ...
. about to export IPCLTOS's tables via Conventional Path ...

<Delete some lines here>

. . exporting table PUNCHDATASAP 2023922 rows exported

<Delete some lines here>

. . exporting table SAP_PUNCHDATA 6163542 rows exported
. . exporting table SAP_PUNCHDATA_23FEB2006 3486333 rows exported
. . exporting table SAP_PUNCHRECORD 1688605 rows exported

<Deleted several lines here>

. exporting statistics
Export terminated successfully without warnings.

(2)
imp system Full=n FromUser=ipclTos ToUser=Portal Tables=(PUNCHDATASAP,SAP_PUNCHDATA) ignore=n file=ears_full.dmp log=temp_20061220_2.log

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by SYS, not by you

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing IPCLTOS's objects into PORTAL
. . importing table "PUNCHDATASAP" 2023922 rows imported
. . importing table "SAP_PUNCHDATA" 6163542 rows imported
IMP-00041: Warning: object created with compilation warnings
"CREATE TRIGGER "PORTAL"."DB_TR_PUNCHDATASAP" BEFORE INSERT ON "IPCLTOS"."PU"
"NCHDATASAP" FOR EACH ROW "
"DECLARE"
" CNT NUMBER(10):=0;"
"BEGIN"
" SELECT PUNCH_SAP.NEXTVAL INTO CNT FROM DUAL;"
" :NEW.SRNO := CNT ;"
"END;"
About to enable constraints...
Import terminated successfully with warnings.

(3)
12:33:27 portal@ >SELECT Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner, Table_Name, Description
12:33:36 2 FROM User_Triggers
12:33:36 3 WHERE Table_Name = UPPER ('PunchDataSap')
12:33:36 4 /

TRIGGER_NAME TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ --------------------
DESCRIPTION
--------------------------------------------------------------------------------
DB_TR_PUNCHDATASAP BEFORE EACH ROW
INSERT
IPCLTOS PUNCHDATASAP
"PORTAL"."DB_TR_PUNCHDATASAP" BEFORE INSERT ON "IPCLTOS"."PUNCHDATASAP" FOR EACH
ROW


real: 63
12:33:37 portal@ >spo off

This is in continuation to the previous query from me.
Since I used the same singe SQL*Plus Session (i.e., same window)
for different Oracle Sessions / connections,
I spooled them on separate files and then merged / edited.
Here, Live User (LU) is actually IPCLTOS
and the Dummy User (DU) is PORTAL.

(4)

12:17:16 ipcltos@ >INSERT INTO ipcltos.PunchDataSap
12:19:14 2 SELECT -1, PUNCHDATE, PUNCHTIME, INOUT, MACHINEID, LOCCODE, PUNCHST, CMPLXCD, SAPUPFLG, ERRMSG, SRNO
12:19:14 3 FROM PunchDataSap WHERE 1=1 AND EmpCode = 16207625
12:19:14 4 AND ROWNUM < 11
12:19:14 5 /

10 rows created.

real: 16
12:19:16 ipcltos@ >roll
Rollback complete.
12:19:17 ipcltos@ >spo off

(5)

12:19:31 portal@ >Input truncated to 14 characters
set termout on
12:19:31 portal@ >alter trigger DB_TR_PUNCHDATASAP enable;

Trigger altered.

real: 31
12:19:56 portal@ >spo off

(6)

12:20:48 portal@ >set heading on echo off verify off termout on

USER UID TERMINAL ENTRYID
------------------------------ ---------- ---------------- ----------
SESSIONID DATE_AND_TIME
------------- -----------------
IPCLTOS 19 PC6093 0
27,21,015 23/12/06 12:21:23


real: 0



real: 16
12:20:48 ipcltos@ >Input truncated to 14 characters
set termout on
12:20:48 ipcltos@ >INSERT INTO ipcltos.PunchDataSap
12:21:02 2 SELECT -1, PUNCHDATE, PUNCHTIME, INOUT, MACHINEID, LOCCODE, PUNCHST, CMPLXCD, SAPUPFLG, ERRMSG, SRNO
12:21:02 3 FROM PunchDataSap WHERE 1=1 AND EmpCode = 16207625
12:21:02 4 AND ROWNUM < 11
12:21:02 5 /
INSERT INTO ipcltos.PunchDataSap
*
ERROR at line 1:
ORA-04098: trigger 'PORTAL.DB_TR_PUNCHDATASAP' is invalid and failed
re-validation


real: 32
12:21:03 ipcltos@ >roll
Rollback complete.
12:21:08 ipcltos@ >spo off

(7)

12:21:31 portal@ >Input truncated to 14 characters
set termout on
12:21:31 portal@ >alter trigger DB_TR_PUNCHDATASAP disable
12:21:53 2 /

Trigger altered.

real: 16
12:21:54 portal@ >spo off

12:29:27 ipcltos@ >Input truncated to 14 characters
set termout on
12:29:27 ipcltos@ >INSERT INTO ipcltos.PunchDataSap
12:29:41 2 SELECT -1, PUNCHDATE, PUNCHTIME, INOUT, MACHINEID, LOCCODE, PUNCHST, CMPLXCD, SAPUPFLG, ERRMSG, SRNO
12:29:41 3 FROM PunchDataSap WHERE 1=1 AND EmpCode = 16207625
12:29:41 4 AND ROWNUM < 11
12:29:41 5 /

10 rows created.

real: 16
12:29:42 ipcltos@ >roll
Rollback complete.
12:29:44 ipcltos@ >
12:31:01 ipcltos@ >

12:31:02 ipcltos@ >SELECT Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner, Table_Name, Description
12:31:12 2 FROM User_Triggers
12:31:12 3 WHERE Table_Name = UPPER ('PunchDataSap')
12:31:12 4 /

TRIGGER_NAME TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DB_TR_PUNCHDATASAP BEFORE EACH ROW
INSERT
IPCLTOS PUNCHDATASAP
"IPCLTOS"."DB_TR_PUNCHDATASAP" BEFORE INSERT ON "IPCLTOS"."PUNCHDATASAP" FOR EAC
H ROW


real: 31
12:31:13 ipcltos@ >desc PunchDataSap
Name Null? Type
------------------------------- -------- ----
EMPCODE NOT NULL NUMBER(8)
PUNCHDATE NOT NULL DATE
PUNCHTIME NOT NULL VARCHAR2(6)
INOUT VARCHAR2(1)
MACHINEID NUMBER(4)
LOCCODE VARCHAR2(3)
PUNCHST VARCHAR2(1)
CMPLXCD VARCHAR2(2)
SAPUPFLG VARCHAR2(1)
ERRMSG VARCHAR2(40)
SRNO NUMBER(10)

12:32:12 ipcltos@ >@InD11
12:32:29 ipcltos@ >COLUMN table_name format A20
12:32:29 ipcltos@ >COLUMN index_name format A20
12:32:29 ipcltos@ >COLUMN index_type format A20
12:32:29 ipcltos@ >COLUMN column_name format A20
12:32:29 ipcltos@ >break ON table_name skip ON index_name skip
12:32:29 ipcltos@ >
12:32:29 ipcltos@ >SELECT I.Table_Name Table_Name, I.Index_Name Index_Name, Uniqueness, Index_Type,
12:32:29 2 Column_name
12:32:29 3 FROM user_indexes I, user_ind_columns C
12:32:29 4 WHERE I.index_name = C.index_name (+)
12:32:29 5 AND I.Table_name LIKE UPPER ('&Tbl_Nam')
12:32:29 6 ORDER BY I.Table_name, I.Index_Name, C.Column_Position
12:32:29 7
12:32:31 ipcltos@ >/
Enter value for tbl_nam: PunchDataSap
old 5: AND I.Table_name LIKE UPPER ('&Tbl_Nam')
new 5: AND I.Table_name LIKE UPPER ('PunchDataSap')

TABLE_NAME INDEX_NAME UNIQUENES INDEX_TYPE
-------------------- -------------------- --------- --------------------
COLUMN_NAME
--------------------
PUNCHDATASAP PK_PUNCHDATASAP UNIQUE NORMAL
EMPCODE

UNIQUE NORMAL
PUNCHDATE

UNIQUE NORMAL
PUNCHTIME


real: 62
12:32:41 ipcltos@ >spo off

(8)

12:33:09 portal@ >desc PunchDataSap
Name Null? Type
------------------------------- -------- ----
EMPCODE NUMBER(8)
PUNCHDATE DATE
PUNCHTIME VARCHAR2(6)
INOUT VARCHAR2(1)
MACHINEID NUMBER(4)
LOCCODE VARCHAR2(3)
PUNCHST VARCHAR2(1)
CMPLXCD VARCHAR2(2)
SAPUPFLG VARCHAR2(1)
ERRMSG VARCHAR2(40)
SRNO NUMBER(10)

12:33:17 portal@ >@InD11
12:33:21 portal@ >COLUMN table_name format A20
12:33:21 portal@ >COLUMN index_name format A20
12:33:21 portal@ >COLUMN index_type format A20
12:33:21 portal@ >COLUMN column_name format A20
12:33:21 portal@ >break ON table_name skip ON index_name skip
12:33:21 portal@ >
12:33:21 portal@ >SELECT I.Table_Name Table_Name, I.Index_Name Index_Name, Uniqueness, Index_Type,
12:33:21 2 Column_name
12:33:21 3 FROM user_indexes I, user_ind_columns C
12:33:21 4 WHERE I.index_name = C.index_name (+)
12:33:21 5 AND I.Table_name LIKE UPPER ('&Tbl_Nam')
12:33:21 6 ORDER BY I.Table_name, I.Index_Name, C.Column_Position
12:33:21 7 /
Enter value for tbl_nam: PunchDataSap
old 5: AND I.Table_name LIKE UPPER ('&Tbl_Nam')
new 5: AND I.Table_name LIKE UPPER ('PunchDataSap')

no rows selected

real: 16
12:33:27 portal@ >SELECT Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner, Table_Name, Description
12:33:36 2 FROM User_Triggers
12:33:36 3 WHERE Table_Name = UPPER ('PunchDataSap')
12:33:36 4 /

TRIGGER_NAME TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ --------------------
DESCRIPTION
--------------------------------------------------------------------------------
DB_TR_PUNCHDATASAP BEFORE EACH ROW
INSERT
IPCLTOS PUNCHDATASAP
"PORTAL"."DB_TR_PUNCHDATASAP" BEFORE INSERT ON "IPCLTOS"."PUNCHDATASAP" FOR EACH
ROW


real: 63
12:33:37 portal@ >spo off

I had asked a query in the last review (23-Dec-2006).
Since in was quite huge, I split it in 2 reviews.

The main point is that, after EXPort from userA and IMPort in (/ to) userB, the userB.User_Triggers.Table_Owner for one trigger shows "userA", though it shows "userB" for all other triggers (new IMPorted tables as well).

I understand it is too difficult to go through huge examples when you alone are attending so many queries.

This type of problems do not occur frequently, only rarely.

I could not get a small example; so we need to wait for such opportunity.

Thanks for as usual.

Hello Tom,

I was implementing your who_called_me function and I was getting some errors. After some debuging I replaced lineno := to_number(substr( line, 13, 6 )); with lineno := to_number(substr( line, 13, 7 )); and it works now. I was really happy cause I thought I found a bug in your code. Then I got back to the post and saw your replies to other people that thought the same thing as me. Naive us, of course there is no bug in Tom's code ;)

Can you please explain why does the line format vary?

Thank you very much,

Hi Tom,
I was wondering if there is a way to find the name of the view that calls a program. I have a lot of views that have a pipeline function in them . one of the parameter of the function is the view name to ensure we know which view called it, in event of an error.
Is there any way to dynamically pass the caller view name into the function(which is actually in the view text?). I tried something with RLS, but was wondering if there's a simpler way.

thank you very much!

Tom,

The so called number 1 database doesnot have easy way to find the current procedure name?

What a shame!

Shame shame puppy shame!

At least give some easy online option to find the current package.procedure name.

ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_output.put_line( 'I am ' || $$PLSQL_UNIT || ' on ' || $$PLSQL_LINE);
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> exec p
I am P on 4

PL/SQL procedure successfully completed.