My Application is running on the unix server - user oracle. My DB is in different unix box. I had created the folder called OUT with the permission of 777 in DB server. I created ORacle Directory DMPDIR for that folder /OUT. and executed grant read/write to that schema.
1) when i use the below code in sqlplus its writing the file into that folder.
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(4000);
[Code].....
But, when the same piece of code is executed from the package ( which called from the application)its throwing the ORA-29283: invalid file operation.
even i am able to copy the files into that folder with the user oracle.
We are using the below procedure to generate the files on the DB server.
SQL> SQL> create or replace procedure write_to_file(p_dir varchar2, 2 p_file varchar2, 3 p_mode varchar2, 4 p_clob clob) as 5 l_output utl_file.file_type; 6 l_amt number default 32767; 7 l_offset number default 1; 8 l_length number default dbms_lob.getlength(p_clob); 9 new_clob clob; 10 BEGIN 11 12 l_output := utl_file.fopen(p_dir, p_file, p_mode, 32767); 13 while (l_offset < l_length) loop 14 new_clob:= SUBSTR(p_clob,l_offset,l_amt); 15 utl_file.put_line(l_output, new_clob,true); 16 l_offset := l_offset + dbms_lob.getlength(new_clob); 17 end loop; 18 utl_file.new_line(l_output); 19 utl_file.fclose(l_output); 20 end write_to_file; 21 /
Procedure created
SQL>
This works fine we call this from PL/SQL developer tool. However, when this procedure is called from Java JDBC, it is giving error
java.sql.SQLException: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 2816 ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 3029 ORA-06512: at line 1
DB schema used for both are same (SYS) Verified the directory on the DB server This Procedure called from package with AUTHID CURRENT_USER option.
Windows 2003, DB 10.2.0.3...I have to read some files from remote folder, so on remote Windows machine I shared folder c:est (grant Everyone first, and Administrator then) and on DB Server I mapped the remote folder with letter T..All these operations were made with administrator privileges.
In Oracle I create a directory object with this command: CREATE OR REPLACE directory T_DIR AS 'T:';
Then I granted read and write privileges to my user GRANT READ, WRITE ON DIRECTORY T_DIR TO <user>; (I tried also to set PUBLIC and SYSTEM)
But when I try to open a file with UTL_FILE with this command
the error is Exception: ORA-29283: operazione file non valida ORA-06512: a "SYS.UTL_FILE", line 475 ORA-29283: operazione file non valida
If I change my directory object in 'C: est' (local folder) it works correctly.I tried also restart DB while shared folders were connected but with the same result.I tried, from sqlplus, the command host (dir t:) and it works (folder is accessible)
I am trying to write data to a network shared folder. When I write to a local file it works perfectly. Below is my procedure.
CREATE OR REPLACE procedure nbpsbp_file as type r_cursor is ref cursor; refr r_cursor; tab_name varchar2(20):= null; tab_name1 varchar2(20) := null; tab_name2 varchar2(20) := null;
[code]....
When I execute the above procedure, it gives me the following error
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation ORA-06512: at "NBPSBP_FILE", line 36 ORA-06512: at line 1
I have also set the parameter utl_file_dir = '\10.16.10.225 emp' When I set the utl_file_sir to a local folder, for example, c: emp, and use the same path in UTL_FILE.FOPEN, then it works fine and writes the desired output to text file. But when I give it a network address, it raises the above error.
in the following program i am submitting a request and sending concurrent program log file (.REQ file)as an attachment but getting following error
ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-06512: at "SYS.UTL_SMTP", line 44 ORA-06512: at "SYS.UTL_SMTP", line 150 ORA-06512: at "SYS.UTL_SMTP", line 383 ORA-06512: at "SYS.UTL_SMTP", line 399
The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER CONNECT TO EVP_PVE_USER IDENTIFIED BY <PWD> USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT ( DOCID, EFFECTIVE_DATE, EXPIRATION_TYPE, ALERT_PERIOD_START, ALERT_PERIOD_END, ACRONYM, [code]....
This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME is tnum number := 0; begin select count(*) into tnum from VW_PVE_CONTRACTS_INALERT; end; /
If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation ORA-02063: preceding line from PVE_SQLSERVER ORA-06512: at "EVPDBA.TESTME", line 5 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_ISCHED", line 185 ORA-06512: at "SYS.DBMS_SCHEDULER", line 486 ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.
How do you refresh a datablock after DML operation(s) - INSERT, UPDATE or DELETE? I know "EXECUTE_QUERY" retrieves records, but what trigger do I use to automatically refresh the datablock after these operations - my form only has 1 datablock.
Am running forms 10g on windows vista service pack 1 and am having problems with the FRM-40514 error. (Operation requires a database connection).
I can connect quite alright from form builder, but when i run a form it request for login credentials again. I dont understand why this is so, when am already logged on in form builder.I tried to tacke this by creating the following
This code suppreses the logon screen and runs the form without logging on to the database. I know this because when I try to enter and execute query it brings up the message: FRM-40514: Operation requires a database connection.
I got: ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31619: invalid dump file "C:P7DBfpac052912_dp01.dmp"
Done in AIX: create directory dp as '/bak' grant read, write on directory dp to public; grant exp_full_database to username;
Done in Windows: create directory dp as 'C:P7DB'; grant read, write on directory dp to public; grant exp_full_database to username; grant imp_full_database to username;
I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.
The log file has the following errors:
KUP-04021: field formatting error for field DEPTNO KUP-04023: field start is after end of record KUP-04101: record 1 rejected in file /usr/tmpclie.csv error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv ORA-01722: invalid number
i have created a procedure to open a query using a reference sys_refcursor as output parameter. i referenced the cursor in a pl/sql and is working correctly. however it throws error "ORA-01001: invalid cursor ORA-01403: no data found" when use on the oracle forms builder.
create or replace procedure mrch.prc_proj_list (p_add_clause in varchar2, p_rec_set out sys_refcursor) as begin Open p_rec_set for 'Select mp2.REGION region, '|| ' mp2.ID proj_id, '|| ' mppm2.PHASE phase, '|| ' mm2.muni muni_id, '|| ' mm2.MUNINAME muni_name, '|| ' nvl((select Sum(ma.AMOUNT) From MRCH.MRC_AGREEMENT ma '|| ' Where ma.PROJECT_ID = mp2.ID '|| ' And ma.PHASE = mppm2.phase '|| ' And ma.MUNI = mm2.MUNI),0) agreement, '|| ' nvl((Select Sum(NVL(ma.GREEN_BOOK_AMOUNT,0) + NVL(ma.GB_ADJUST,0)) From MRCH.MRC_ALLOCATION ma '|| [code]....
I am using this query in MERGE statement , result is also shows here
select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from ( select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from ( select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time, substr(data_row,15,2) Shift, substr(data_row,17,2) STatus from data_load a
[code].....
using this query in MERGE statement giving a error ora-00911 invalid character
MERGE INTO Attendance I USING ( select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from ( select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from ( select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.
I created 3 radio buttons and one list item. Different options will appear according to the radio button the user has clicked on. i've created 3 recordgroups and 3 LOV. the first two for seven_eleven and mannings work fine, but not for GNC. is it because there is only one option for list item in GNC? Must it be more than one? I've tried to another option as space but it still doesnt work.
I have created the below stored procedure and calling the procedure in when-button-pressed trigger. Problem here is that when I cancel the file selection in file open dialogue box its raising exception.
PROCEDURE TEST IS buffer_lines client_text_io.file_type; v_outputstr VARCHAR2 (32767); p_delimiter VARCHAR2 (10) := '","'; v_transaction_no VARCHAR2 (10); BEGIN [code].......
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT USING ( SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
I am using the dblink to merge the data. I am using the following merge statement.
merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE) when not matched then insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME) values ('','test','TESTxxx',null, null, null, null);
I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.
