i have created an file in my local system (sys name : System1). i have created a directory as below
SQL>CREATE DIRECTORY test_dir AS '\System1TEMP';
I gave rights as
SQL>GRANT ALL ON DIRECTORY TEST_DIR TO PUBLIC;
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
IF utl_file.is_open(fileHandler) THEN
utl_file.fclose_all;
END IF;
[code]......
but while executing the above procedure, Invalid File Operation error occurs.
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.
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.
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
file_handler := UTL_FILE.fopen( 'T_DIR', 'Dati.ini', 'r' ) ;
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)
Is it an Oracle bug? Is it a Windows bug?
This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.
The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.
The procedure uses utl_file to create an .xls file
We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges drwxrwxrwx.
We have a job scheduler as the attachment which in turn triggers a procedure(check the code attachment for the scheduler and the procedure).
In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.
The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.
We capture the exceptions in a table. In the table the following exception is logged :ORA-29283: invalid file operation
The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..
Also the files are created when we manually run the same procedure in the path.
The Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
The OS flavour is SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V490 -------
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
[code]....
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 :
select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
[code]....
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.
My Login is System
Already created Directory MY_DIR in c:mydir
Showing Following error
1 DECLARE
2 src_lob BFILE := BFILENAME('MY_DIR','C:
mi2Blue.jpg');
3 dest_lob BLOB;
4 BEGIN
5 INSERT INTO lob_table VALUES(2, EMPTY_BLOB())
6 RETURNING doc INTO dest_lob;
[code].....
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.
Is it possible or not.
I need to recreate/ clone my database to a new machine. The two machine are not connected in the network.
Step 1. (Oracle 10.2.0.5 AIX 64-bit)
expdp username/password@db1 full=y dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_expdp.log job_name=full_exp
Step 2.
FTP dump files to Windows
Step 3. (Oracle 10.1.0.2 Windows 32-bit)
impdp username/password@db1 dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_impdp.log full=y
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
This is the script for the table:
create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
[code]....
And this is csv:
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
[code]....
I restore Dump File in Oracle 10g .
my Command is :
impdp DUMPFILE=BAK910830.DMP NOLOGFILE=Y
An error message is as follows:
"Invalid argument value"
"Bad dump file specification"
"Dump File may ba an original export Dump file "
I think the dump File is in Oracle 11g Format .
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.
What are all the DML operation can be done in DUAL table.?
View 5 Replies View RelatedI have a source view where I have some invalid records and those should be found based on codes present in another table.
For eg. from source the records come like
****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********
and if my reference table has values
SIINNSFDFD
TABLE
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
I want to create one sql script with the followings-
1.Show the values before the operation is performed
2.Display the values after the operation is performed.
How it can be done in a proper sql script format.
Frequently getting below error in alert log file. to sort out this issue.
Database: Active standby database
OS: Windows Server
Db Version: 11.2.0.2
Error:
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
[code]...
I have a car table with the following attribute sale_code,model_car etc. the format of sale_code is year/number example when a client buy a car(model_car=mercedes) her code is 2012/0125.
I want to calculate the number of cars that were sold in the year of 2011 grouped by the model_car attribute.
select count (sale_code),model_car
from car
where sale_code like '2011%'
when I tested this query i received an error.
My question is :
how to resolve the issue when we use an attribute in where and we use the same attribute in count operation ?
I am working on an SAP application migration project using Oracle 10.2.0.2 database. We are migrating the application from Windows to Solaris.
During the process we are facing problem with very slow insert operation on a particular table.The server's capacity is very good and so no resource bottleneck.
The table contains around 2,70,000 rows and inserting at around 100 rows per 10 seconds.
The table contains following data types.
SQL> desc SAPDATDB.CAF_GP_VALDEF;
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL_UUID NOT NULL NVARCHAR2(34)
VAL_GUID NOT NULL NUMBER(10)
VAL_CLOB NCLOB
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.
View 3 Replies View RelatedWhen I try to call a database procedure written in Oracle 8.1.7.4.0 with OUT parameter and COMMIT statement from my Oracle 10g environment, I am getting error like "ORA-02064: distributed operation not supported".
I cannot omit OUT/COMMIT statement from the procedure because it is also updating another table from called procedure. I have tried some solutions from my end, but it is not working and same error generating. Like:
1) Moved the update statement with COMMIT statement to another procedure and calling that procedure from main called procedure
2) Creating a job to run the newly created procedure and submit the job from called procedureetc.
I had added a new disk into disk group DATA1 with rebalance power of 5, it ran as estimated for about 2 days long, I can see the estimated time down to zero but the process never end.
The command used:
SQL> alter diskgroup DATA1 add disk '<path>' rebalance power 5;
Below is the disk status:
SQL> select group_number, disk_number, total_mb, free_mb, mount_status, header_status, state
from v$asm_disk where group_number=1 order by disk_number;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB MOUNT_S HEADER_STATU STATE
------------ ----------- ---------- ---------- ------- ------------ --------
1 0 1191626 314236 CACHED MEMBER NORMAL
1 1 1191626 314230 CACHED MEMBER NORMAL
1 2 1191626 314232 CACHED MEMBER NORMAL
1 3 1191602 314229 CACHED MEMBER NORMAL
1 4 1191626 314210 CACHED MEMBER NORMAL
1 5 1191626 314218 CACHED MEMBER NORMAL
1 6 1191626 314218 CACHED MEMBER NORMAL
1 7 1191602 314223 CACHED MEMBER NORMAL
1 8 1191626 314216 CACHED MEMBER NORMAL
1 9 1191626 314202 CACHED MEMBER NORMAL
1 10 1191626 314230 CACHED MEMBER NORMAL
[code]....
I've checked and follow the status for a few days more, it still running with EST_MINUTES=0. I think it was hang and never end.During the next few weeks, I've manually restarted the rebalance process by modifying its power to 6, 8, 10
SQL> alter diskgroup DATA1 rebalance power 10;
After every restart, the diskgroup rebalance again but has the same issue, never end.Here is the content in the alert log
NOTE: starting rebalance of group 1/0x9d2529a0 (DATA1) at power 5
Starting background process ARB0
Fri Sep 16 12:01:11 2011
ARB0 started with pid=15, OS id=21066
Starting background process ARB1
Starting background process ARB2
Fri Sep 16 12:01:11 2011
[code]....
As you can see, the rebalance process started 3 days ago, hanging till now
SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
1 REBAL RUN 5 0
I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
l_fun_ret_value NUMBER;
v_src_id NUMBER;
rec_jci_contract_dim jci_contract_dim%ROWTYPE;
rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE;
err_row VARCHAR2(4000);
ind NUMBER(1) := 1;
[code]....
How do I perform Undo a drop table operation?
View 12 Replies View RelatedI am trying to use on trigger to get the code of an DDL operation, I use the code supplied on orafaq (orafaq.com/scripts/plsql/auditdll.txt) as base code and I added a collumn objcode and a call to DBMS_METADATA.GET_DDL to save the object code, however I am receiving an Oracle error ORA-00604.See code below.
This error is raised when I try to create a new view on the schema, on objects that I already have and I changed something usually the error did not happens, how to get the object code of one object when it is created, or changed.
I am using Oracle 11g
SQL> select banner
2 from sys.v_$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
cl scr;
DROP TRIGGER audit_ddl_changes
/
DROP TABLE dll_audit_log
[Code]....
CREATE FORCE VIEW V3 AS SELECT * FROM ALL_OBJECTS
ORA-00604: error occurred at recursive SQL level 1
ORA-31603: object "V3" of type VIEW not found in schema "XXISV_V11R01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
ORA-06512: at line 2
I have created the 5 Level Hierarchy Tree using the Tree example
[URL]........
how-to-s/ forms- how- to- create - a- hierarchical - tree-form".
My requirement are
1. I wish to refresh the Hierarchy Tree after DML Operation.
2. I wish to Query the Record when i will select the node.
I have 5 Master Detail Record as:
Master - Detail
Detail Master -- Detail
Detail Master -- Detail
Detail Master -- Detail
Detail Master -- Detail
3. Is there any way to find out which Hierarchy Level currently selected
4. Is there any way to find out which value is for what block.
I am getting the below exception when i access the WebPage.This only happens sometimes, so if i try again to hit the URL, it works. Not getting why this is behaving unusual.
StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception
(wt.fc.fcResource/0) wt.util.WTException: The operation: "getPrincipal" failed.
Nested exception is: wt.util.WTRemoteException: Unable to invoke remote method; nested exception is:
java.lang.OutOfMemoryError
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1