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


ADVERTISEMENT

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

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

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

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

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

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

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

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

PL/SQL :: Passing Variables From Shell Script To Separate Sqlplus Script

Apr 5, 2013

I am having issues passing variables to a separate sqlplus script invoked by the shell script, e.g.

#!/bin/sh
DB_NAME=TEST
PWD1=PA55W0rd

echo exit | sqlplus / as sysdba @${DB_NAME}.sql ${DB_NAME} $PWD1 >> ${DB_NAME}.sql

exit 0

The script picks up the $DB_NAME variable fine, and therefore invokes the required sql script. However, when I pass the variable $PWD1 to the sql script I get an error. The script creates a database link:

create or replace procedure new.link
is
begin
execute immediate 'create database link TEST
connect to TESTSCH identified by '$PWD1'
using ''TEST''';
end;
/
exec new.link;
/

output is:

ERROR at line 1: ORA-00911: invalid character ORA-06512: at "NEW.LINK", line 4 ORA-06512: at line 1

View 13 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Find Parent / Host Procedure Name Within Procedure

Sep 6, 2013

I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin

find_the_proc_name();
end;

In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.

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

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 Replies View Related







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