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


ADVERTISEMENT

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

Reports & Discoverer :: How To Call Oracle Reports Inside A Stored Procedure

Oct 14, 2010

Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:

a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?

[code]...

Now, i want to convert this into oracle,plsql? Is it possible or not?

View 3 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 :: Call Function Generates ORA-14551 /cannot Perform A DML Operation Inside Query

Aug 9, 2011

Calling function

select PACK.MAIN('blah') from dual

generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245

Unfortunately the Body is not accessible to see.The spec of the function is:

FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:

var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar

But this generates: ORA-01008: not all variables bound

View 3 Replies View Related

Possible To Create Sequence Inside DML Trigger

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '

end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 9 Replies View Related

Executing A Job Using DBMS_SCHEDULER Inside A Trigger

Oct 10, 2013

I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.

The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by  (and as far as I understand, supposed to be used with) SYS user. what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users? 

View 11 Replies View Related

SQL & PL/SQL :: Populate Mypo Inside Trigger?

Apr 15, 2011

CREATE TABLE TBL_LTEST
(
ID NUMBER,
MYPO NUMBER
);

alter table tbl_ltest add constraint pk_tbl_ltest primary key (id, mypo);

I want the data in this table to be like this:

ID MYPO
1000 401
1000 402
1000 403
2000 401
2000 402
2000 403

Rule of MYPO column population:

1. It should always start with 401 (I can handle that with Oracle check constraint that mypo should be > 401)
2. For each Id, the first insert has to be 401, then it should add one from there within the Id

(something like, mypo is a sequence within the ID.)

I am trying to populate mypo inside the trigger in this way:

CREATE OR REPLACE TRIGGER trigTBL_LTEST
BEFORE INSERT OR UPDATE
ON TBL_LTEST
REFERENCING NEW AS NEW OLD AS OLD

[code]...

View 3 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

SQL & PL/SQL :: Invoking Statement Inside The Trigger Body

Jan 11, 2012

I need to find out the exact DML statement which invoked a particular trigger inside a trigger body.

View 7 Replies View Related

SQL & PL/SQL :: Insert With Select And Subquery Inside Trigger

May 5, 2011

I hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:

Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))

Step# 2 manipulated the tables:
Inserted 3 records in Table_C.

Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:

CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......

Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')

I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.

View 4 Replies View Related

SQL & PL/SQL :: Usage Of Context Variables Inside The Trigger?

Feb 17, 2010

Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.

Select Username from User_Users

Will any of the below variables return the trigger owner when used inside the trigger code

sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')

View 13 Replies View Related

PL/SQL :: Dynamic Execution Of Block Inside Trigger?

Jan 3, 2013

Im using the following oracle database.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

I have one problem in trigger execution. I have a small plsql block in trigger and, I want to execute it as a dynamic way. but it is giving the error. Please find the trigger code. Here my intension is that, the column name used in trigger should be dynamic. In future, if I want to switch the column name, I have to do without modification in trigger.
The error im getting is "ORA-01008: not all variables bound".

CREATE OR REPLACE TRIGGER ETM_AR_IU
AFTER UPDATE ON
EXTERNAL_MAPPING
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

[code]...

View 17 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

View Inside The Procedure

Jun 12, 2013

I am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on

usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views

how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.

View 1 Replies View Related

SQL & PL/SQL :: How To Set Environment Variable Inside Procedure

Nov 24, 2010

I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:

before the select query i need to insert this environment variable : "set linesize 200"

create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Cursor In DML Statement Inside A Procedure

Sep 12, 2011

I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.

The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.

when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.

Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.

select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed
from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r
where t.set_id= d.set_id
and d.depot_gid=r.location_gid
AND D.SERVPROV_GID=''R_LOC.LOCATION_GID''
and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING''
and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'')
and t.default_set=''Y''

View 21 Replies View Related

SQL & PL/SQL :: Bind Variable Inside Procedure?

Jun 18, 2013

I have two procedure , from first procedure having some ref cursor output.

from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .

can I define bind variable inside the procedure , then how can I define it .

SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN

[code].....

View 10 Replies View Related

SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure

Apr 28, 2011

Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.

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

SQL & PL/SQL :: Declaring Arrays Inside Stored Procedure?

Jun 17, 2010

how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.

procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)

[code]....

When I try to compile it, I get the error "component EXISTS must be declared".

View 14 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

View 4 Replies View Related

SQL & PL/SQL :: Create And Drop Table Inside The Procedure

Jun 10, 2013

I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.

I cant track the error. Why is it so?

CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........

View 18 Replies View Related

PL/SQL :: How To Get Some Optional Parameters Inside A Stored Procedure

Oct 1, 2013

Can we call one or more parameters inside a stored procedure call (like func_get_args in PHP) ? Example : 

create or replace PROCEDURE test_proc (
val1 in varchar DEFAULT NULL,
val2 in varchar DEFAULT NULL,
val3 in varchar DEFAULT NULL,
[code]..........

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

PL/SQL :: Calling A Function In Remote Database Inside A Stored Procedure

Apr 9, 2013

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN

[Code]...

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

where my mistake is?

View 7 Replies View Related

Procedure To Generate Crosstable From Data Inside Calves_per_breed Table

Dec 11, 2006

I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.

I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:

PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....

Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:

Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)

select *from kalwers_per_ras_crosstable:

TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....

View 5 Replies View Related

Creating And Dropping Global Temp Tables Inside A Single Procedure

Aug 20, 2010

I am writing a procedure that will be called from a java wrapper.

The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.

Create or replace proc_name ()

update table........

delete from ..........

CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)

insert into............

drop table TSAAG;

End;

creating a global temp table inside a procedure is expensive...

Do we have anything like creating table before and calling the instanse of it in procedure.

Do we have any alternatives to this..

View 3 Replies View Related







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