PL/SQL :: Executing A Shell Script From A Trigger

Sep 4, 2012

I have to write a script to do the following requirement. There is a file called BUSINESS_DATE.TXT. This file get updated once the oracle partition created. In Oracle, artition will be created every day. There is a seperate script scheduled to take care ORACLE partition creation. The above file will have only one row. i.e. 03092012

If Oracle partition creation job failed, the above file won't be updated. My requirement is, I have to check whether the BUSINESS_DATE file is updated today or not. If yes, I will have to move the files from common area to input file directory to process those files. All file name will amend with current date. i.e .

LIDDIFD03092012.TXT

The key part is to check the BUSINESS_DATE.TXT file is updated properly or not. We don't know what time the file will get updated. So we are planning to schedule the new script to run for every 15 mins to check whether the file is updated or not

But...I just thought instead of writing a shell script to do the above one, Why shouldn't I capture the date in a table (New table needs to be created) and use ORACLE TRIGGER to run the shell script to move the files from common area to input file directory to process those files?

My Proposal in ORACLE :

Create table business_date
(
rep_date varchar(15),
curr_timestamp timestamp
)

Once the oracle partition created , one row will be inserted into the above table. This adjustment needs to be implemented partition script. Once this table get record, TRIGGER should call SHELL SCRIPT to move the files from common area to input area.

If I implement ORACLE TRIGGER, The script which will check whether the file got updated or not for 15 mins is not required.

View 3 Replies


ADVERTISEMENT

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

Oracle 7 Shell Script?

Sep 6, 2011

find the sample script and the error i face while running from shell. Actually i want to check the health of the database.

Sample script:
--------------------
export ORACLE_SID=GMX
export ORACLE_HOME=/opt/app/oracle/product/9.2.0
export PATH=/home/oracle/dbgen/PM/bin:/usr/bin:/opt/ansic/bin:/usr/ccs/bin:/usr/contrib/bin:/opt/nettladm/bin:/opt/pd/bin:/op

[code]....

error messege:
Oracle Server Manager Release 2.3.4.0.0 - Production

Message 4505 not found; No message file for product=SVRMGR, facility=MGR

Error while trying to retrieve text for error ORA-12545
Error while trying to retrieve text for error ORA-12545

Attached File(s)

 Script.txt ( 997bytes )
Number of downloads: 3

View 1 Replies View Related

SQL & PL/SQL :: Run Shell Script In Procedure

Feb 10, 2012

I've a requirement of running shell script in pl/sql procedure. I thought of doing this by using dbms_scheduler package. I believe while executing the jobs using dbms_scheduler the owner has to be part of DBA group, but as per my company policy (security issue) only DBA users has to be part of this group and i'm not able to . So is there anyway i can execute shell script in pl/sql procedure other than using dbms_scheduler.

View 2 Replies View Related

How To Transfer Parameter Between Procedure And Shell

Jul 25, 2013

I created a procedure with cursor to inquiry a table. There is more than 1 million records on the table. For each record, I would like to transfer two columns on table as parameter to shell and feedback to procedure as varcahr2.

This is my sample(I only use 10 records for the testing).

create or replace procedure task_file(task_check in varchar2,file_name out varchar2) is
k number :=0;
v_task_number number :=0;
v_task_filename varchar2(30) := null;
cursor jobchk is select task_number,task_filename into v_task_number,v_task_filename from my.tasktable
where task_filename=task_check and rownum <=10;
[code]........

View 1 Replies View Related

Shell Script For RMAN Backup?

Apr 12, 2011

I was trying to take/schedule a RMAN backup of database using crontab in REDHAT Linux 5. I was able to execute the script manually and the backup completed successfully with out any issue. (execute ./script.sh from command prompt) But, this script is not getting executed on scheduled time from crontab..

daily_backup.sh
#!/bin/bash
export ORACLE_SID=traxmp
export ORACLE_HOME=$ORACLE_HOME
# Connect to the database. Change this to Sys logon if not using /
rman target sys/*******@traxmp @'/backup/rman_script/daily_backup.rman'

[code].....

View 4 Replies View Related

SQL & PL/SQL :: DBMS_SCHEDULER Calling Shell Script

Sep 13, 2011

I am facing issues with DBMS_SCHEDULER..I am receiving error while executing the "DBMS_SCHEDULER.run_job"..The OS folder has full permission.

OS : UNix
Database: 10g
====================================================================
Error Received:

ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit 274664
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
THE CODE IS LISTED BELOW:
[code]....

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

Shell Script For Automatic AWR Report

Apr 17, 2013

I have a requirement to generate the AWR report daily between (10AM - 06PM , 10AM - 01PM , 01PM - 10PM) . I have the below script for this . But the issue is this script is working only when i run it two times and before running i have to delete snap_list.lst file.

Script :
-------------
dt=`date +%d%m%Y
cd /orabkp/awr_report
chmod 777 *
rm -rf snap_list.lst
touch snap_list.lst
[code]....

View 6 Replies View Related

Enterprise Manager :: How To Add Shell Scripts

Dec 24, 2009

I have written a shell script for block corruption checks. I want to run the script from OEM grid control, so that I can get an email alert if there is anything wrong. how to add the shell script at OEM Grid?

View 1 Replies View Related

Execute UNIX Shell Script From Function?

Apr 27, 2012

I have a requirement where i need to execute a unix shell script "a.sh" having permissions user1,group1 and no access to public. This should be executed from Oracle function.

So i wrote a routine in Java and it works fine when i execute a command like Date etc.

But when i execute "a.sh" from oracle client, its returning error that "could not be executed-permission denied". This is because once login happened through the "sqlplus" the shell takes user as "oracle" and group as "dba" but the script "a.sh" having only permissions user1,group1. Unfortunately we are not allowed to change the permission for "a.sh" to give execute permission to all(public).

After searching in internet, i understand that one way is to make the password (/etc/passwd) for the user "user1" as no password and can use "su" command so that it wont ask password while invoking it.

Is there any other way apart from this doing changes in password file at UNIX level when executing a script from oracle client?

if further information is required.

Oracle: version: 11.2.0.1
UNIX : AIX -5.9

View 3 Replies View Related

Enterprise Manager :: Scheduling A Shell Script As Job In OEM 12c

Sep 12, 2012

I have a shell script which triggers a batch in our application (an application which is not integrated with any other scheduler and is trying to integrate with OEM for the first time).

Is it possible to schedule a job to run this shell script in OEM ?

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Shell Script Values To Procedure

May 30, 2012

I have one procedure , which calls the DBMS_scheduler.run_job , which calls the Shell Script. now based on the shell execution i need to return the message lines to procedure.

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

RMAN :: Using Variables - Unix Shell Script?

Apr 18, 2013

I am new to Oracle and my requirement below.

Environment:

Oracle DB Version     11.2.0.3.0
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production with 2 nodes RAC.

Requirement :

Daily at morning 5:00, Flashback the database to the yesterday restore point.It has to be scheduled to be done automatically. Now it being performed manually.

Psuedo code:

1. Shutdown the RAC database
2. Bring up the RAC database with mount mode
3. Flashback the database to yesterday restore point
4. Create new restore point to be used on next day
5. Bring up other instance of the RAC database.

#!/bin/ksh
#######################################################################

# Shutdown databases and bring up one instance in NOMOUNT mode.
#
#######################################################################

[code]...

eofQuestions:

1. How can i get the laterst restore point for the database and pass it on the RMAN FLASHBACK command?
2. How can I create new restore point with todays date suffixed with lable like BEFORE_TEST_20130417_5AM, with in RMAN ?

View 3 Replies View Related

Security :: Create User Using Java Shell

Mar 29, 2012

How to create user using java shell basis to using sysdba or sysoper access on isplplus and what is the location of java home in linux.

View 2 Replies View Related

Application Express :: Call OS Shell Script

Jun 13, 2012

I have a batch script "backup.bat" . How can I call this script in my Apex page? I have search the forum but their explanation is complicated. I can understand it better using sample apps. Add a sample call script in:

[URL]........

username:test01
password:abc123
workspace: dev_ws_100

View 4 Replies View Related

How To Pass Values From Bash Shell Script To Sqlplus

May 3, 2012

I want to run parallel queries of 16 sqlplus jobs through shell script.i don't know to pass values dynamically from shell script to sqlplus.

here i am hard coded the values but i want to pass the values dynamically. and how i will know each job is running successfully.if any job is fail the it will exit from script.

my shell script is :

sqlplus -s username/password @script_file.sql 1001 1004 &
sqlplus -s username/password @script_file.sql 1006 1010 &
sqlplus -s username/password @script_file.sql 1011 1015 &
sqlplus -s username/password @script_file.sql 1016 1025 &

my sql file is:

CLEAR BUFFER
SET SQLBLANKLINES ON
SET ECHO OFF
SET FEEDBACK OFF
SET FLUSH OFF
SET HEADING OFF
[code]........

View 1 Replies View Related

Client Tools :: Set Echo On Is Not Working In Shell Scripts?

Jun 11, 2012

SET ECHO ON is not working in shell script. It works in command prompt and also shell prompt. whenever i run the shell scripts, set echo on is not working.

I want output and its query in spool file.

set -e
set +x
#Executes SVR parameter scripts Sets all the application specific properties
. $SCRIPTS_PATH/svrparam.sh
$ORACLE_HOME/bin/sqlplus -s ${SOLV_DB_USER}/${SOLV_DB_PASS}@${SOLV_DB_SERVER}<<EOF
set echo on
set termout on

[code].......

View 9 Replies View Related

Write Shell Script To Parse Parameter File

Mar 29, 2013

I'm working on trying to write a shell script to parse a parameter file, but at the same time I want to be able to overwrite the parameter file settings with other command line settings. For instance if my par file had export/import settings for the username, password, schema, etc;

I wanted to run the same export/import with those settings for a different schema. I want to be able to put the schema=<different_than_par_file> after the parfile=<parfile.par and have the parfile be read and applied for everything except the different schema.

Right now I'm storing the cmd line and parsing it again looking for other parameters besides the parfile.

View 4 Replies View Related

Develop Shell Script Which Will Do Monitoring Of Alert Log Daily

Nov 29, 2012

Oracle on 11gR2 on hp-ux 11.23.Need to develop a shell script which will do a monitoring of the alert log daily.

It should do a audit trail (only check for warnings/errors) for the last 1 day daily till collection time and output to a file.if no errors should output "No Errors/Warnings", else ouput the relevant.

View 12 Replies View Related

PL/SQL :: Scheduling - Create Oracle Job That Will Execute Shell Script?

Jul 2, 2012

I need to create an oracle job that will execute a shell script. i need to run it only when I call it(so not to be scheduled).

View 5 Replies View Related

Application Express :: Execute Remote Shell Scripts Through 4.1?

Jan 3, 2013

Is there a way to execute remote shell scripts/perl script through APEX 4.1?I use Application Express 4.1.1.00.23

View 3 Replies View Related

Shell Script Throwing Permission Denied Error Message?

Nov 1, 2012

I am learning oracle DBA training course. I tried to find disc space and mail alert .I got permission denied error message.I was running this script in my own pc.

CODE    #!/bin/sh
    # Shell script to monitor or watch the disk space
    # It will send an email to $ADMIN, if the (free avilable) percentage
    # of space is >= 90%
  
[code]...

View 14 Replies View Related

Server Administration :: Automated Shell Script For Renaming Oracle DB 10gR2

Nov 8, 2011

Provide automated shell script for renaming oracle DB 10gR2. Now it is a daily manual job for us.

View 4 Replies View Related

SQL & PL/SQL :: Executing SP Using Jobs

Sep 3, 2010

how to execute one stored procedure for every 10 minutes using oracle jobs

sp name: abc

using anonymous block with sample code.

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

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

Executing Function From Ksh Script?

Mar 5, 2009

I don't know how to control if a call to a PL function return errors.

I have this ksh:

executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction

And the code of "ejecutaFuncion.sql" is:

executeusers.startProcessing();
EXIT

How i can controle if the "startProcessing" function has any problems from the ksh?

View 1 Replies View Related

Getting Errors While Executing Procedure

Oct 4, 2010

I am getting errors while executing the following block.

create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
DECLARE
-- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
TYPE c_collection IS TABLE OF c_Rec;
l_coll c_collection := c_collection();
BEGIN
[code]........

error

06530. 00000 - "Reference to uninitialized composite"

View 1 Replies View Related







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