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
ADVERTISEMENT
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
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
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
Oct 17, 2013
I'm on Oracle 11g R1. I've a requirement where user will be putting CSV files on Unix server and I've to create a job which runs periodically to check if any new file is added by user in the folder. If it finds a new file (s), then it needs to identify it and insert its name in an Oracle table. Once file's name is noted, it has to move file from that directory to another one.
View 2 Replies
View Related
May 19, 2007
I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.
int sysrun(char *command)
{
return system(command);
}
This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/
Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.
SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0
PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
[code]...
View 1 Replies
View Related
Oct 9, 2012
I have converted one function from sql to oracle through sql developer. so it's created with package name. I execute package name and function also
here is my package name and function:
CREATE OR REPLACE PACKAGE FnFetchEmployees_pkg
AS
TYPE tt_v_employees_type IS TABLE OF tt_v_employees%ROWTYPE;
END;
create or replace
FUNCTION FnFetchEmployees
(
v_user_id IN NUMBER
)
RETURN FnFetchEmployees_pkg.tt_v_employees_type PIPELINED
AS
[code]........
it's executed successfully. when i am executing this function like this:
select emp_id from fnfetchemployees_pkg.fnfetchemployees(1) from dual;
getting error: sql command is not properly ended;
View 9 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;
I got the blow error.
ORA-00905: missing keyword
View 2 Replies
View Related
Jun 25, 2012
I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);
[code]....
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11
View 12 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.
ORA-00905: missing keyword
View 5 Replies
View Related
Mar 15, 2011
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
View 2 Replies
View Related
Jun 6, 2013
I cant compile & execute this function.
create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc
[code]....
View 6 Replies
View Related
Oct 4, 2012
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
View 1 Replies
View Related
Feb 17, 2010
I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.
Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...
I am using the following command:
sqlplus u/p@<someserver> @<path_to_sqlfile>.sql
this sql file contains create procedure pl/sql code
Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?
View 8 Replies
View Related
May 20, 2013
i`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.
table1 is something like this:
ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100
table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)
[code]....
Then i wanna make a pl/sql function for:
1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1
2) change i=1 to i=2 and do that same
3) end if i=101
View 1 Replies
View Related
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
