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


ADVERTISEMENT

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

SQL & PL/SQL :: Trigger Calling An Autonomous Stored Procedure

Jul 7, 2011

I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?

View 18 Replies View Related

SQL & PL/SQL :: Commit In Trigger In Oracle

Aug 3, 2013

I am using commit in a trigger as given :

create or replace
trigger comt after insert on tbl_city
declare
pragma autonomous_transaction;
begin
commit;
dbms_output.put_line('Value is committed');
end;

Now when I perform an insert in tbl_city---->trigger fires properly and gives output stream. But If I perform rollback now --->there are the data rollbacked in table.

why?I think after commit(which is in trigger associated at insert to table)there should no any rollback in table.

View 17 Replies View Related

SQL & PL/SQL :: Why Commit Not Allowed In Trigger

May 17, 2010

why commit is not allowed in trigger?

View 3 Replies View Related

Forms :: 10g Win XP 32 Bit - Key-commit Trigger?

Oct 10, 2013

I have a problem with key-commit trigger. I have written some validations and computations criteria on the block level (lines level). Actually there are a number of loops involved in it.

Problem is that the computations are performed twice. (may be the validations would also be performed twice, which couldn't be felt ). Since i read somewhere that key-commit is fired on different events, which i infered to be firing only just before database commit.

View 1 Replies View Related

Oracle Trigger On Transaction Commit

Nov 29, 2007

I am working with an oracle table that is populated by a trigger on another table.. So Table A is an audit of table B. The trigger also uses sysdate to populate a modification Date column on the Audit table.

I was using this modification Date column in a query interface to get changes that happened on the main table after a certain date/time.

The problem is that there is an application that uses transactions to write to table B and sometime this transaction may not be committed for over a minute so the modification Date is not a reliable way to query the table for changes after a certain time.

Is there a way to update the trigger/create a new one where the sysdate that gets written to the audit table is from when the transaction is committed, not when the transaction starts?

View 2 Replies View Related

SQL & PL/SQL :: Invoke Procedure With DML And COMMIT Through DBLINK?

May 29, 2012

A simple procedure is created as follows:

Create procedure p1(a varhar2, b varchar2, c_err out varchar2) is
begin
insert into t1 values(a,b);
commit;
exception
when others then
c_err:= substr(sqlerrm,1,250);
end;

It is executed fine.But if it is executed from a dblink as

declare
c varchar2(200);
begin
t1@remote_link('testa','testb',c);
dbms_output.put_line(c);
end;

It is giving error "ORA-02064: distributed operation not supported". How can I pass DML through this procedure and commit from a dblink?

View 5 Replies View Related

Forms :: Calling Validation In Pre-insert Trigger

Nov 10, 2010

my purpose is when PRE-INSERT trigger fires validation should be done like date format , primary key in table if validation is ok then a value of text box should be set to sequence no . else it should generate message

View 9 Replies View Related

Calling PL/SQL Procedure From JavaScript?

Oct 8, 2007

The application I am working on uses Oracle HTP procedures to generate HTML pages for our application. I am trying to perform some cleanup when a user closes a web application by clicking the 'X' button in there browser. I am thinking of using the onUnload trigger to call a javascript function, which in turn would call a procedure in my PL/SQL pacakge for cleaning up logical locks on records.

The body tag delaration looks like this code fragment:

htp.print('<BODY onUnload="clear_locks(' || p_user_id || ')"

Here is some psuedo code for the function i would like to use:

<SCRIPT>function clear_locks(p_user_id){
call clear_locks(p_user_id);
return true;
}</SCRIPT>

Note that clear_locks is a pl/sql stored procedure.So far I have gotten it to pass in the user ID correctly.Is there a way I can call my clear_locks procedure?

View 1 Replies View Related

SQL & PL/SQL :: Procedure Calling Methods

Oct 14, 2013

I have created this procedure for printing ename,sal,job as output using empno as input:-

CREATE OR REPLACE PROCEDURE p_get (
p_empno NUMBER,
p_name OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2
)
IS
BEGIN
SELECT ename, sal, job
INTO p_name, p_sal, p_job
FROM emp
WHERE empno = p_empno;
END;

Now My Requirement I want to call this procedure using Positional,Named and Mix Methods...I am calling this procedure using Positional Method:-

declare
p_name varchar2(20);
p_sal number;
p_job varchar2(20);
begin
p_get(7369,p_name,p_sal,p_job);
dbms_output.put_line('Name='||p_name||' Salary='||p_sal||' Job='||p_job);
end;

how to call the same procedure using NAMED and MIX methods.how to call same procedure using NAMED and MIX methods.

View 11 Replies View Related

SQL & PL/SQL :: Calling Procedure Into Function?

Aug 1, 2011

for my r&d purpose i create function which call procedure,

create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)

[Code]...

but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.

but when i complied fucntion it doesnt gv me any error.

View 15 Replies View Related

PL/SQL :: Calling Procedure In Loop

Oct 2, 2013

I am calling a procedure with the following parameters 

DECLARE     PROFIT_CENTER NUMBER;   BEGIN   PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111

one by one by passing values manually, it is taking almost a minute for each profit center

)Prc_test_calc (  PROFIT_CENTER);  COMMIT;   END;
For each profit_center proc is taking 1 mins (Approx) time.  

But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop  

DECLARE     PROFIT_CENTER NUMBER;  cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --

Plng_pc is a table from where profit_center information is getting fetched  

BEGIN For pc_rec in c_profit loop   Prc_test_calc (  pc_rec .PROFIT_CENTER );  COMMIT; End loop;   END;

View 9 Replies View Related

PL/SQL :: Calling Dbms_Scheduler From Forms Trigger To Invoke SqlPlus Script In Linux

Mar 25, 2013

I would like to use Dbms_Schedule to call SqlPlus script in Linux .

BEGIN     
sys.Dbms_Scheduler.Create_Job (
job_name => 'TEST_SQL_JOB',

[Code]....

This is giving me the error: ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

BUT when I issue the command: /usr/local/bin/sqlplus -s s/ibs @/usr2/dms/run/lkp_sql_test.sql
it works fine.

View 4 Replies View Related

Calling Web Service From A Procedure Using SOAP API

Mar 5, 2013

I am trying to call a Web service using the SOAP API and I get the following error.

ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
ORA-06512: at "SYS.XMLTYPE", line 48
ORA-06512: at "MyConnection.SOAP_API", line 121
ORA-06512: at "MyConnection.MyFunction", line 28
31020. 00000 - "The operation is not allowed, Reason: %s"
*Cause: The operation attempted is not allowed
*Action: See reason and change to a valid operation.

However we do have another procedure that is able to make an HTTP call successfully within the same domain. Hence the ACLs that are required are for making an HTTP call are in place. Is there any other security setting to be tweaked to make the to enable me to make a Webservice call using the SOAP package?

View 1 Replies View Related

SQL & PL/SQL :: Calling Exe File From Stored Procedure

Jun 15, 2011

I want to call an exe file through DB Procedure. How it is possible?

1create or replace procedure proc3

2is
3begin
4 host('calc');
6* end;

from FORMS it works fine but it does not work through DB. I found same question through this link

[URL].........

but no answer is given.

View 2 Replies View Related

SQL & PL/SQL :: Calling Stored Procedure With Default Value?

Apr 3, 2012

I have the following Stored Procedure:

CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS

[Code]....

I dont know what I need to pass in order to give it the default value.

View 3 Replies View Related

SQL & PL/SQL :: Calling Shell Script Within Procedure?

Jul 22, 2013

I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.

Below is my procedure:

create or replace
PROCEDURE RD_ODS_REFRESH_LOG
IS
BEGIN
FOR i IN
(
SELECT RESULT FROM RD_REFRESH_LOG

[code].....

And here is CALL_SHELL job that my procedure calls:

BEGIN
SYS.dbms_scheduler.create_job (
job_name => 'CALL_SHELL',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/load_semantic.sh',

[code].....

My ELSE statement never gets executed, why?

View 18 Replies View Related

Precompilers, OCI & OCCI :: Calling Procedure From PRO C?

Nov 26, 2012

I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL

like this

my_procedure( q=>a1 , b=>'abc )

View 1 Replies View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

Feb 26, 2013

It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.

Scenario is like below:

create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;

[code]....

Not getting why the schema is not changing... and how to resolve the error.

View 8 Replies View Related

PL/SQL :: How To Run Report / Calling Web URL Into Stored Procedure

Dec 17, 2012

I have a requirement to run below given URL by Stored Procedure. I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

[URL]

View 4 Replies View Related

PL/SQL :: Calling Oracle Procedure With Two OUT Parameters

Jun 20, 2012

I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.

proc_Test (p_resultset=> My_cursor)

How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.

View 5 Replies View Related

PL/SQL :: Calling Procedure From Another And Get Combined Output

Jun 3, 2013

I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:

CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....

I am not able to compile tis proc.

View 5 Replies View Related

PL/SQL :: Differences Between Calling SQL Statement / Procedure?

Apr 22, 2013

I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?

Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;

b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);

create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
   begin
    open cur for select * from employees where department_id = dep_id;   
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.

View 10 Replies View Related

SQL & PL/SQL :: Error In Calling Collection Procedure

Jun 4, 2013

In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are

"UpdateRelatedOrders" and "SetGeoFraudScore"

with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures

"UpdateRelatedOrders" and "SetGeoFraudScore".

I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....

View 19 Replies View Related

Out Of Process Exception On Calling Java Procedure?

Sep 28, 2011

In my application, we are getting the following exception on calling the Java procedure.

java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-04030: out of process memory when trying to allocate 10804020 bytes (joxp heap,f:OldSpace)

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: Calling Database Link Dynamically For A Procedure

Feb 15, 2011

How to call a procedure by passing the db link dynamically.

View 1 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 :: Calling Shell Script Through External Procedure

Nov 26, 2006

I am trying to call a shell script through external procedure. To summarize the steps that I followed:

Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.

Then I logged into the sqlplus. Created one library pointing this .so file.

create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';

After this created the external procedure. This got compiled without any errors.

SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2)
as external
library ext_proc
NAME "execute_cmd"
parameters (in_script string);
/
Procedure created.

I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.

However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows

SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>

View 5 Replies View Related

Application Express :: How To Set Request When Calling Procedure

Apr 23, 2013

I use the procedure gReport.navigate.paginate. If there is the possibility to set a value to REQUEST when calling this procedure?

View 7 Replies View Related







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