03-16-2011
Is it possible to add "Execute procedure" script in my current existing "Create or Replace Procedure script".
I would like this to work simultaneously "create and execute the procedure"
The script to create procedure is;
HTML Code:
CREATE OR REPLACE PROCEDURE PDAC_UPDATE_ALLOW_DENY_SERV
IS
tmpVar NUMBER;
/******************************************************************************
NAME: UPDATE_ALLOW_DENY_SERV
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 11/29/2010 Mohamed.S.Rahman 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: UPDATE_ALLOW_DENY_SERV
Sysdate: 11/29/2010
Date and Time: 11/29/2010, 11:14:18 AM, and 11/29/2010 11:14:18 AM
Username: rsa11355 (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
tmpVar := 0;
UPDATE DMERCLINE
SET allow_serv = allow_serv / 1000
WHERE sadmerc_receipt_dt BETWEEN TO_DATE ('06-Oct-09')
AND TO_DATE ('30-Jun-10');
UPDATE DMERCLINE
SET deny_serv = deny_serv / 1000
WHERE sadmerc_receipt_dt BETWEEN TO_DATE ('06-Oct-09')
AND TO_DATE ('30-Jun-10');
END PDAC_UPDATE_ALLOW_DENY_SERV;
/
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi Guys,
I am trying to tun a oracle proedure throgh unix shell script but it is not running i dont know why ? i have tested this procedure in sqlplus and it was working fine.
can you see the script and sql file and let me know where is my mistake.
script:bm_chart_table_loading.sh
... (3 Replies)
Discussion started by: shary
3 Replies
2. Shell Programming and Scripting
Hi everyone!
I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB).
I have to create a shell script to execute that procedure for each txt... (5 Replies)
Discussion started by: renatoal
5 Replies
3. Shell Programming and Scripting
How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies
4. Shell Programming and Scripting
Hi,
Below is the code to execute the procedure "dbms_job.broken" from the shell script.
on executing manually, it works properly without any error.
but it is not working when scheduled using the cronjob.
#!/usr/bin/bash
user_name="oracdb"
password="ora123"
tns="localdb"... (2 Replies)
Discussion started by: milink
2 Replies
5. Shell Programming and Scripting
I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
Discussion started by: vel4ever
6 Replies
6. Shell Programming and Scripting
Hi,
I want to have a automted script to exceute 20 procedures one by one.
Suppose below is one procedure once it get executed script will write
"PL/SQL procedure successfully completed." to a log
for ex-
exec dbms_stats.gather_table_stats();
Now later procedure starts executing... (1 Reply)
Discussion started by: sv0081493
1 Replies
7. Shell Programming and Scripting
hi all,
i have a unix script where i am calling a database procedure from it. while executing the procedure i am getting an error:
but when i tried to call the same procedure manually then it ran successfully, i goggled this issue and found timezone.dat file missing at... (0 Replies)
Discussion started by: lovelysethii
0 Replies
8. Shell Programming and Scripting
below code is not executing the stored procedure,not sure what the issue.Even sqllog is blank.please help me its very urgent.
sqlplus -s $connect_str@$DB_ORACLE_SID >> ${SQL_LOG_FILE} << EOF
set serverout on
set feed off
set head off
set pages 0
set colsep ,
set tab off
set lin 150... (3 Replies)
Discussion started by: katakamvivek
3 Replies
9. Shell Programming and Scripting
i have the following code inside a shell script .prog in oracle server when i call the program
DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id);
will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i... (1 Reply)
Discussion started by: feilhk
1 Replies
10. Shell Programming and Scripting
Hi All,
I am running the below proc in unix by connecting through sqlplus and the procedure is completing successfully. But when i am trying to run through shell scripting by using function. I am getting the error as follows. Please guide me where I am going wrong.
#!/bin/sh
opera ()
{... (6 Replies)
Discussion started by: bhas85
6 Replies
LEARN ABOUT SUSE
create_constraint_trigger
CREATE CONSTRAINT
TRIGGER(7) SQL Commands CREATE CONSTRAINT TRIGGER(7)
NAME
CREATE CONSTRAINT TRIGGER - define a new constraint trigger
SYNOPSIS
CREATE CONSTRAINT TRIGGER name
AFTER event [ OR ... ]
ON table_name
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
FOR EACH ROW
EXECUTE PROCEDURE funcname ( arguments )
DESCRIPTION
CREATE CONSTRAINT TRIGGER creates a constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing
can be adjusted using SET CONSTRAINTS [set_constraints(7)]. Constraint triggers must be AFTER ROW triggers. They can be fired either at
the end of the statement causing the triggering event, or at the end of the containing transaction; in the latter case they are said to be
deferred. A pending deferred-trigger firing can also be forced to happen immediately by using SET CONSTRAINTS.
PARAMETERS
name The name of the constraint trigger. This is also the name to use when modifying the trigger's behavior using SET CONSTRAINTS. The
name cannot be schema-qualified -- the trigger inherits the schema of its table.
event One of INSERT, UPDATE, or DELETE; this specifies the event that will fire the trigger. Multiple events can be specified using OR.
table_name
The (possibly schema-qualified) name of the table in which the triggering events occur.
referenced_table_name
The (possibly schema-qualified) name of another table referenced by the constraint. This option is used for foreign-key constraints
and is not recommended for general use.
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
The default timing of the trigger. See the CREATE TABLE [create_table(7)] documentation for details of these constraint options.
funcname
The function to call when the trigger is fired. See CREATE TRIGGER [create_trigger(7)] for details.
arguments
Optional argument strings to pass to the trigger function. See CREATE TRIGGER [create_trigger(7)] for details.
COMPATIBILITY
CREATE CONSTRAINT TRIGGER is a PostgreSQL extension of the SQL standard.
SEE ALSO
CREATE TRIGGER [create_trigger(7)], DROP TRIGGER [drop_trigger(7)], SET CONSTRAINTS [set_constraints(7)]
SQL - Language Statements 2010-05-14 CREATE CONSTRAINT TRIGGER(7)