SQL & PL/SQL :: Trigger Impacting A Procedure In The Package
Jun 27, 2012
Recently I created a trigger in my production environment which affected a procedures execution.
Trigger code is as below
CREATE OR replace TRIGGER chk_fresh_lead_time
BEFORE INSERT ON location_refnum
REFERENCING NEW AS NEW
FOR EACH ROW
WHEN (NEW.location_refnum_qual_gid = 'FRESH_LEAD_TIME'
[code].......
This trigger will check if a value is inserted on Location ref num table for the qualifier LEAD_TIME only if the value exist in Fresh template table else it will throw an error message as Quote: Add the fresh template and then add the LEAD_TIME value
We have a procedure in one of the package which inserts values on the same Location ref num table but for a different qualifier say PENDING and not LEAD_TIME as above, but still the procedure is not being executed due to this trigger. How this trigger is affecting the procedures execution.
I am working in the company which is using PL/SQL to create web interface. Of course we are using mod_plsql. I was using OWA_UTIL get_cgi_env ('REMOTE_ADDR') in the packages and it was always properly returning the IP address of the client. But Now I want to use it in after logon on schema trigger and it is never initialized.
Maybe some other way to obtain the IP of the client machine just before calling the package which is showing the interface?
Here is the simplified code of the trigger:
CREATE OR REPLACE TRIGGER dev_debug_trg AFTER LOGON ON SCHEMA DECLARE l_ip VARCHAR2(200); BEGIN
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS PROCEDURE proc_1 IS BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;" SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT -------------------------------------------------- PACKAGE BODY PKG_TEST IS /************************************************* ****/ PROCEDURE proc1 IS BEGIN update t1 set EDITION_NAME = 'AAAAAAA'; commit; END proc1; [code]....
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
i need to clarify that, we can call a procedure inside a procedure, when i am using inside a package,whether i have specify the called procedure in the Package specification?
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'
Here is my procedure:
create or replace PROCEDURE INSERTINTOPHYSLOG AS prec_num long; srec_num long;
I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.
create or replace TRIGGER TRG_RATING_EXP_DLS BEFORE DELETE OR UPDATE OR INSERT OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID ON RATING REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE
I am trying to create trigger through Procedure due to following reasons-
1. The name of the column on which trigger will execute, is to be fetched dynamically. 2. The name of the column to be updated, is to be fetched dynamically.
Here is the sample code-
CREATE OR REPLACE PROCEDURE test2 IS var VARCHAR2 (4000); uname VARCHAR2 (30); attribtask VARCHAR2 (100); mapcol VARCHAR2 (100); BEGIN [code].........
On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.
In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.
I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:
update housing set group_num = 1 where room_num = 10
ERROR at line 1: ORA-00937: not a single-group group function ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6 ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'
/* DDL */ create table violation_type (violation_num number(1) primary key, violation_def varchar2(100) not null) organization index tablespace mi257_data; [code]....
I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.
Trigger
Create or replace trigger owner_name.table1_trg2 after update on table1 for each row call owner_name.procedure1;
procedure Code (psuedo):
Create or replace procedure1 as begin update table1 set col1 = 'whatever'; commit; exception when others then rollback;
Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.
I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry as select * from emp where 1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER , v_ename VARCHAR2, v_job VARCHAR2) is insert into emp_hstry (empno,ename,job) values (v_empno,v_ename,v_job); COMMIT; end;
create or replace trigger emp_del_hstry1 after insert or delete on emp for each row begin if deleting then emp_del_hstry(:old.empno,:old.ename,:old.job); end if; end;
delete from emp where empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
