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.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Two Different Package Can Contain Same Procedure?

Mar 31, 2011

is it possible that two different package can contain same procedure?

View 1 Replies View Related

SQL & PL/SQL :: Package And Procedure?

Jul 17, 2012

In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?

View 14 Replies View Related

SQL & PL/SQL :: Obtaining REMOTE ADDR Using Owa_util Package In After Logon Trigger

Nov 16, 2011

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

[Code].....

View 3 Replies View Related

Call A Procedure From Another Package?

Dec 10, 2007

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.

View 6 Replies View Related

SQL & PL/SQL :: Running Procedure Within A Package?

May 24, 2011

I have a package which has two procedures in it.

The second of which was put in just as a test:

PROCEDURE DST_RPT_INVOICE_REPRINT(refCur OUT Dsti_Rpt_Init_Pkg.RC, param_locationid VARCHAR2,
param_companycode VARCHAR2, param_frominvdate DATE, param_toinvdate DATE, param_project VARCHAR2,
param_invtype VARCHAR2, param_printed NUMBER) AS.....
Dsti_Rpt_Init_Pkg.PRINT_OUTPUT(strSql);
END DST_RPT_INVOICE_REPRINT

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?

View 3 Replies View Related

SQL & PL/SQL :: Call A Procedure In A Different Package?

Sep 13, 2010

I am wrinting a procedure. I want to call a procedure in a different package and get its out value to a variable.

how can I do that in PL SQL?

View 10 Replies View Related

SQL & PL/SQL :: Call Private Procedure Outside Of The Package?

Mar 2, 2010

There is any way to call private procedure out side of the package.

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Oracle Procedure And Package?

Jan 7, 2013

difference between oracle procedure & package.

View 1 Replies View Related

SQL & PL/SQL :: Scheduling A Stored Procedure / Package?

Mar 9, 2010

I need to schedule a stored procedure and a stored package every day @6:30 AM.

I have also a merge statement that I need to execute before stored procedure is executed.

View 3 Replies View Related

PL/SQL :: How To Test Procedure / Function / Package

Sep 16, 2012

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 .

View 6 Replies View Related

PL/SQL :: Get A Called Procedure / Function Name Within Package?

Jan 11, 2013

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:

11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_2*

I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.

View 7 Replies View Related

Export / Import Of Procedure - Function / Package

Jun 9, 2012

Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name

View 2 Replies View Related

SQL & PL/SQL :: Mail Reading / Saving Through Package Or Procedure

Nov 14, 2011

sample code to read attachments in inbox(from POP3 mail server) using plsql package or procedure.

View 6 Replies View Related

SQL & PL/SQL :: Capture And Reference Parameter Value Of Procedure In Another Package

Jun 8, 2011

I need to know how to capture the Parameter Value of a Procedure in a Variable and reference that variable in another Procedure or Package.

View 12 Replies View Related

Trace Particular Object (procedure / Package) On Database?

Dec 13, 2012

How do I trace an particular object (procedure/package) on the database. my database version is Oracle 11g(11.2.0.2)

View 2 Replies View Related

PL/SQL :: Counting The Number Of Lines For Each Procedure In A Package

Jul 31, 2012

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]....

View 14 Replies View Related

How To Extract DDL Of Table / Package / Procedure Using SQL Code

Jan 5, 2007

how to extract the ddl of a table/package/procedure using SQL Code?

I found a method, but it's only supported from Oracle9i and obove, im using Oracle8i

View 3 Replies View Related

PL/SQL :: Call VB Function From A Package Stored Procedure

Apr 30, 2013

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.

View 5 Replies View Related

How To Audit Execute On Specified Procedure Or Function In Package

Oct 4, 2012

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 ???

View 1 Replies View Related

SQL & PL/SQL :: Procedure Within Procedure In Package

Mar 16, 2012

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?

View 5 Replies View Related

Application Express :: Creating Procedure Using Database Package

Oct 23, 2012

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.

View 4 Replies View Related

SQL & PL/SQL :: Export Table Data Into Text File Through Procedure / Package

Oct 8, 2012

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?

noted that the data does exist 32k.

View 39 Replies View Related

Calling Procedure Using Trigger

Aug 26, 2013

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;

[Code]....

View 2 Replies View Related

Modify Trigger Into Procedure

Sep 12, 2011

Moidfy trigger into procedure

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

[Code]...

-------------------

Don't play what's there, play what's not there

View 2 Replies View Related

Creating Trigger Through Procedure

Jul 24, 2009

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.

View 2 Replies View Related

SQL & PL/SQL :: Create A Trigger And Procedure To Go Along With DDL

May 11, 2010

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]....

View 9 Replies View Related

SQL & PL/SQL :: Difference Between In Procedure And Trigger?

Jul 7, 2010

what is difference between in procedure and trigger?

View 2 Replies View Related

Calling Procedure With Commit From Trigger

Dec 8, 2006

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.

View 5 Replies View Related

SQL & PL/SQL :: Procedure Call Inside Trigger?

Aug 8, 2011

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 ..

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved