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
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
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
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
May 17, 2010
why commit is not allowed in trigger?
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Feb 15, 2011
How to call a procedure by passing the db link dynamically.
View 1 Replies
View Related
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
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
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