SQL & PL/SQL :: Create 2 Triggers And Each Trigger Will Call Only One Procedure

Feb 14, 2011

What is more efficient?

- To create one trigger on the table and make it call two unrelated procedures
OR
- To create 2 triggers and each trigger will call only one procedure.

View 13 Replies


ADVERTISEMENT

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

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 :: Call Procedures And Functions In Database Triggers?

Oct 6, 2010

Is it possible to call procedures and functions in Database Triggers?

View 1 Replies View Related

Create Trigger To Execute A Stored Procedure During Source Code Compile?

Nov 9, 2009

I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.

View 1 Replies View Related

Forms :: Call One Trigger Of Item In Trigger Of Form?

Jul 1, 2011

How can "call one trigger of item in trigger of form"

View 5 Replies View Related

SQL & PL/SQL :: Procedure Functionality As Triggers

Oct 26, 2010

I am creating triggers for audit operation when any insert/update/delete happens on a table automatically.Now i need the same functionality in procedure.

My requirement is to create procedure for all those triggers.

View 2 Replies View Related

Server Administration :: Complete Content Of Trigger Body In User Triggers

Aug 2, 2010

Column Trigger_body in table user_trigger is long data type.I want to find out the certain 'table name' was using intrigger.

Below is my sql statement:
select trigger_name
from user_triggers
where TRIGGER_BODY like '%tb_test%' or TRIGGER_BODY like '%TB_TEST%';

then it show me a error :
where TRIGGER_BODY like '%tb_test%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes

I had set long 32767 before execute above statement

View 7 Replies View Related

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 Replies View Related

SQL & PL/SQL :: ORA-04089 / Cannot Create Triggers On Objects Owned By SYS

Oct 10, 2011

now i can log in but here i faced with another erorr, while i am creating a trigger it gives the following erorr:

SQL> ed
Wrote file afiedt.buf
1 create or replace trigger trgr
2 before insert on myTable
3 for each row
4 begin
5 select mySequence.nextval into :new.ID from dual;
6* end;
SQL> /
create or replace trigger trgr
*
ERROR at line 1:ORA-04089: cannot create triggers on objects owned by SYS

note: i tried and gave different names for the trigger, still it is the same.

View 22 Replies View Related

SQL & PL/SQL :: How To Call Trigger In Another Trigger

Mar 10, 2010

Can we call trigger in another trigger,if it is possible any example.

View 1 Replies View Related

Forms :: Call A Function On Key-next-item Trigger?

May 20, 2011

how to call a function on key-next-item trigger. atlst the syntax.

View 2 Replies View Related

Forms :: Oracle 10g Key Combination To Call Form Trigger

Nov 11, 2009

I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.

80 : 1 : "Shift+P" : 64 : "Duplicate Record"

It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.

View 2 Replies View Related

SQL & PL/SQL :: How To Call Stored Procedures Or Functions From A Database Trigger

Oct 14, 2011

How to call stored procedures or functions from a database trigger?

View 1 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 :: How To Call A Procedure In Cursor

Sep 11, 2012

I have a procedure(used to delete the data from all tables) which taking two parameters as input. like EXCS_kiwldate(1,'keepitems')[/color][/size] but it taking only one parameter at a time if i want to delete all data using that procedure by calling it in a cursor

how to write a cursor by giving loop function to delete the data once.

View 3 Replies View Related

SQL & PL/SQL :: How To Call Outside Function In Procedure

Dec 3, 2011

How to call outside function in procedure

View 11 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

PL/SQL :: How To Call Procedure In Jasper

Sep 16, 2012

How to call plsql in jasper. i have try this code in jasper -> select query langiuage = plsql

{CALL STD03_MERIT_PROCESSING($P{std03_studentVal},$P{std03_studentVal})}

but there is error taht said no data found.

View 1 Replies View Related

PL/SQL :: How To Call Procedure From Sql Prompt

Jun 12, 2012

I have a procedure with signature as proc_temp(deptno in number, empdetails out sys_refcursor);

how can i call this procedure from sql prompt.

View 2 Replies View Related

PL/SQL :: Syntax To Call Procedure?

Apr 8, 2013

I have 2 procedure defined in a package.Procedure is defined so that, it has 3 varchar2, which is used to store the insert statement.

ex:
CREATE OR REPLACE PACKAGE PK_LOGIC AS
PROCEDURE MOVE_table1_TO_table2;
PROCEDURE MOVE_table2_TO_table3;
END PK_LOGIC ;
/
[code]....

My problem is how can we execute this procedure in SQL DEVELOPER tooL?I tried using,

CALL PK_LOGIC .MOVE_table1_TO_table2(); or EXEC PK_LOGIC .MOVE_table1_TO_table2;

View 9 Replies View Related

SQL & PL/SQL :: How To Call A Procedure From Anonymous Blocks

Jul 12, 2010

I have a written a script which doesnt take any parameter. this is an anonymous block. I run my script as below: SQL> @filename.sql

I would like to convert this into a procedure which should be called in an anonymous block. run a procedure from an anonymous block.

I would also like to know how to run the same from the SQL prompt.

View 5 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 :: How To Call Procedure Dynamically Using Execute Immediate

Jan 2, 2011

I have a procedure p1 in schema s1.i want to call this procedure in schema s2 dynamically.how can i do?

View 4 Replies View Related

PL/SQL :: Call Java From Stored Procedure

Aug 21, 2012

We have a stored procedure named proc_java_test . From this stored proc, we need to call a java class named java_test.java

This class posts messages to a queue hosted on an app server running on a separate box.

create or replace
PROCEDURE PROC_JAVA_TEST
(
num IN NUMBER
)
AS LANGUAGE JAVA
NAME 'java_test.post(int) ';

Is it mandatory to place java files in oracle_home/bin ? I tried placing the file on some other location and then calling it from stored proc. It did not work. Can we point oracle to read java file from location other than oracle_home/bin?

Oracle version used: 11.2.0.2r3

View 4 Replies View Related

Forms :: How To Call Procedure From One To Another Object Library

Jan 29, 2013

I have one procedure "PROC_A" in LibraryA, Is it possible to call this procedure PROC_A from LibraryB.

View 1 Replies View Related

SQL & PL/SQL :: Procedure To Call User Defined Function?

Apr 12, 2012

I have made a function to add two number create or replace function add_num(a in number, b in number) return number

as
begin
return a+b;
end;
/

i run it via
select add_num(2,5) from dual;

my question is how to call add_num in procedure.

View 9 Replies View Related

Windows / .NET :: Call External Dll From Stored Procedure

Jun 6, 2013

I have function in stored procedure which call function from another .net dll, is a first step. And this dll call function from unmanaged dll, is a second step.I do not have any problem in the first step, but when i do second step i have error:

ORA-20100: System.Security.SecurityException
System.Security.Permission.SecurityPermission
at ...
at ...
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: ...
ORA-06512: ...

when i am deploying proceduse i set security levels "external".

View 0 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

PL/SQL :: How To Create Unique Number On Each Call

Apr 10, 2013

i want to insert unique number with out gaps in a col of a table every time my query runs . it shouel generate numbers from 1 to n. I can do it using sequence , but sequence need reseting and other user can reset it also and it creats gaps too.

Any function/technique which can give me session or transaction specific unique number on each call to sp.

i want to inserting these numbers in a table and insertion is going on in a loop.

View 6 Replies View Related







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