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 RepliesI 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]...
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
i am trying to run DBCA from unix command line. but it gives me error like this:
dbca[158]: /home/oracle/product/10.2.0/jdk/jre/bin/java: not found.
Also i tried to open some other utilites too. like netca it gives me same error.
I am facing a problem regarding the execute immediate command. I have created a procedure as given below
SQL> set echo on ;
SQL> set serveroutput on;
SQL> declare
2 l_var varchar2(50);
3 sqlstring varchar2(3000);
4 begin
[code].......
In this procedure the execute immediate command shows error ( if i avoid exception).I have tried other syntax too of this command
but it is showing error only.
I have to build a select query but its where conditions will be retrieved from a table. I was told that the execute immediate command can handle it.
lets say i have this:
string_var:= 'select field1, field2, field3
from mytable
where' ' || i.condition_selection || ';'
If the above select resuls in a single row, i could do this:
EXECUTE IMMEDIATE string_var INTO var_field1, var_field2, var_field3;
In my case the select will return multiple rows. How do I proceed ?
I have created a procedure, which should be executed on the below condition with EXECUTE IMMEDIATE COMMAND. But i am getting error.
The error shows the procedure/function name is not existing. But it is exist.
SQL>
1 Declare
2 a varchar2(20);
3 b varchar2(20);
4 c varchar2(1000);
5 begin
6 select to_char(sysdate,'day') into a
7 from dual;
8 select to_char(sysdate,'HH24') into b
9 from dual;
10 if
11 (a='friday' and b>=22)
12 or
13 (a='saturday' and b<=6)
14 or
15 (a='wednesday' and b>=9)
16 then
17 begin
18 EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;');
19 end;
20 else
21 null;
22 end if;
23* end;
SQL> /
EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;');
*
ERROR at line 18:
ORA-06550: line 18, column 32:
PLS-00222: no function with name 'BACKUP_AUTO_EXECUTE_BAT_FILE' exists in this
scope
ORA-06550: line 18, column 4:
PL/SQL: Statement ignored
I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.
The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.
Here is the Java class defined in Oracle:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static void Run(){
try
{
[code]....
And here is the procedure that calls the Java class:
create or replace
procedure run_os_command
as language java
name 'OSCommand.Run()';
I have a procedure in this procedure i use.
EXECUTE IMMEDIATE 'alter user '||use||' identified by '||modp ||' replace '||oldp;
but when i execute it show insufficient privilages but i create for this procedure as public.and grant execute facility to the user.
When working with SQL Server it's possible to execute several sql statements in one command of the DataAdapter in ADO.Net Now I try the same thing with an oracle database and recieve error ORA-00911: invalid character.
When remove ;character from query it again gives error- ORA-00933: SQL command not properly ended.
My query is like this-
update activity set activityname='Route Survey' where activityid=1;
SELECT * FROM activity where activityid=1
SO: Solaris 11 x86-64
DB: 11.2.0.3
I'm trying to install the Grid Infrastucture + Oracle db 11.2.0.3 on a Solaris 11. I'm used to work with Red-hat, so i don't remember in having such a problem. The issue is the following, when trying to run "runInstaller" as oracle user:
Checking Temp space: must be greater than 180 MB. Actual 1527 MB Passed
Checking swap space: must be greater than 150 MB. Actual 2104 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/openwin/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before continuing with the installation,Continue? (y/n) [n] n
User Selected: NoDoing some researches, would be to install the SUNWxwplt package.i have installed all the required packages:
root@sol11:/mnt/sf_Compartilhamentos# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibms SUNWpool SUNWpoolr SUNWsprot SUNWtoo SUNWlibm SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw SUNWxwplt
system SUNWarc Lint Libraries (usr)
system SUNWbtool CCS tools bundled with SunOS
system SUNWfont-xorg-core X.Org Foundation X11 core fonts
system SUNWfont-xorg-iso8859-1 X.Org Foundation X11 iso8859-1 fonts
[code]....
xhost: unable to open display "192.168.0.20:0.0"I think i made all the necessary configurations (?).
When I execute multipath -ll command that time display only 3 path (orafra2,oradata2 and oradata1), not display other 2 path (orafra1 and data1). I have configure 5 path in /etc/multipath.conf file. What is the reason behind it.
[root@reuxeuls003 ~]# multipath -ll
orafra2 (360060160a71e2100de29aae7f4f9de11) dm-10 DGC,RAID 10
size=200G features='1 queue_if_no_path' hwhandler='1 emc' wp=rw
|-+- policy='round-robin 0' prio=1 status=active
| |- 1:0:1:3 sds 65:32 active ready running
[code]....
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
View 3 Replies View RelatedWe would be moving oracle 11g unix sun solaries to oracle 11g Linux readhat OS. what would be the disadvantage and what are the item needs to be verified. Basically advantage of oracle 11g Linux readhat OS.
View 2 Replies View RelatedIam planning to upgrade my Oracle Database from 10G to 11G .
Its on HP Unix Environment 11.23 and
Oracle CRS 10.2.0.4
Oracle ASM 10.2.0.4
Oracle RDBMS 10.2.0.4
Its in RAC env...and Active-Active DB running controlled by tnsnames.ora .
I'm trying to install Oracle 11gR2 RAC on AIX, do we need to turn on Multicasting, can we install without multicasting? Also can I have the ASM disk with external redundancy for OCR and Voting Disk?
View 3 Replies View RelatedI have a UNIX shell script as following,
#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........
I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.
rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37
The oracle Procedure's SQL is as following,
SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........
The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..
Do I need to convert the Date ? ( But i_last_update_datetime is DATE)
I installed a printer(HP Laserjet 3390) in UNIX(Solaris).Followed the instructions while installing as to the path it should be present for Reports to read it etc.,Also modified the uiprint.txt file with the desired .hpd file.Logging on to the UNIX machine, I am using the below command
rwrun.sh report=xyz.rdf userid=XXXX/XXX@xxx paramform=no p_orig_org='0825' desformat=pdf destype=printer desname=xxxx batch=yes
This command invokes the printer and
^[%12345X@PJL
@PJL SET RESOLUTION=300
@PJL SET PAGEPROTECT=OFF
@PJL SET ENTER LANGUAGE=PCL
^[%1A^[E^
@PJL RESET
^[%12345X
is what it prints.Realizing that this is because of the missing parameter,tried to change paramform=yes but I am not sure how to pass the parameter.
my problem is this. I created the java classes using itext to create a document library. Rtf, or. Odt. I integrated classes in Oralce form and everything works. I created a file. Odt application server on a folder and now only remains for me to open the file. The roads are two:
1. Directly from the server, you can?
2. From the client using the API or openoffice launching the executable with cmd?
Oracle Forms is a command to transfer files on the client and run the executable swriter of openoffice?
We have recently migrated from IAS 9.0 to IAS 10.1.2 in one of our development Server hosted in UNIX B.11.11. We are using Oracle forms as front end. We have re-compiled all the existing forms and libraries using IAS 10. There were no changes in the forms and libraries. We deployed the forms in our development box and configured it, since we are using our own environment specific copy of default.env file. With few of forms we are facing the FRM-92100 error mentioning the "Connection to Server was interrupted". Most of the forms are running fine but with few forms we are facing this error. Some of details are:-
Oracle 10.1.0.4
UNIX B.11.11
IAS 10.1.2
NLS_LANG parameter in default.env is set to "american_america.WE8ISO8859P1"..We have even tried copying the libraries in our local system and then opening and saving them and ftp'ng them back in to UNIX server and then recompiling there with full permissions but still unable to resolve this error.
i need a connectivity code to connect oracle DB in UNIX.pls send me a code exactly.
View 2 Replies View RelatedI would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.
View 22 Replies View RelatedI have a file which is placed on a certain location on unix server. I want to write a code which picks up that file and emails it to certain recipients.
View 3 Replies View RelatedI want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.
E.g. : insert into tbl_daily_status values(STAT,ENDTIME);
#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done
I am trying to Push the data from Oracle which is running in HP-UNIX to SQL Server. But don't know the efficient way to connect the SQL server from oracle which is running on HP-UNIX.I have heard about oracle Heterogeneous Connectivity but don't know exactly how to implement it in Unix environment.
View 1 Replies View RelatedWe have an application that fetches and writes data into oracle database through pro c. oracle datyabase is on another server.
We are storing some secure information into oracle database so we want to encrypt the data sent by our aplication into oracle database.We do not want to use SSL(i.e certificates) and also do not want to make use of Advance Security Option available in oracle and also do not want to make any changes in sqlnet.ora file on server side.
achieve encryption of traffic between our application and Oracle database?
How to overcome the prompt user id & password to connect oracle DB from Unix environment.
View 2 Replies View RelatedI have a requirement to read a file from windows m/c and write into Unix Server. How can i do that in Oracle forms.
I tried TEXT_IO package.But that is not allowing to access Unix path.
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.
I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
export TNS_ADMIN=/usr/lib/oracle/network/admin
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/bin
export ORACLE_SID=VFIODSD1
when I try
# sqlplus
username :xxxxxxx
password : xxxxxxx
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.