I am trying read a File to find out the error message through UTL_FILE.m. But the loop used inside is not getting closed.
CREATE OR REPLACE procedure alert_mail_A
as
v_flag varchar2(10);
mesg varchar2(100);
c1 utl_smtp.connection; -- passing the datatype to a variable.
c varchar2 (1000);
B utl_file.file_type;
[code]........
My DB Version: 10.2.0
OS : Solaris
I have 2 Database installed on the server one with the name of orcl and one with the name of mz50. When i am trying to connect from client side using toad to orcl i am able to connect to that db successfully but when i try to connect to mz50 DB i getting above mentioned error.
I check the tnsnames file as well as listener file and when i am trying to do tnsping both DB reply
My tnsname file is
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
[code]....
I Installed Oracle forms 10g after which I Installed Oracle Database Express Edition.
I have added the following entry in tnsnames
XEXDB=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))
(CONNECT_DATA=
(SERVICE_NAME=XEXDB)
)
)
When I am trying to connect Forms with the Database am getting the below error:ORA - 12537: TNS connection closed
I need to make any changes to sqlnet.ora
I am gettting error message databse connection closed itried
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
[code]...
I Got a scnario like a upload file contains
1.Header(Contains the File Name,Branch Name,MIS date)
2.Body(Customer Details)
3.Footer (File Name,Contians Total Number of Records and Number of Customers)
Like
CustomerDet_11-12-2013.csv,Chennai,11-12-2013
101,AAA,Savings,'M',10000
102,BBB,Current,'O',2000
103,CCC,Deposit Acct,'F',3000
CustomerDet_11-12-2013.csv,3,15000
How to handle this scnario through UTL_File
I have an application that creates files using the utl_file package. It works fine but one of the lines in the file should hold the number of bytes in the file (on a line formatted like 'FileSize: 2104'). Unfortunately this line is not the last line of the file and the lines that follow it are variable in length.
My approach therefore is as follows:
1. Write the 'FileSize: ' line during file creation.
2. Write the remaining lines of data to the file (but don't close it).
3. Use utl_fgetattr to find the file size.
4. Go back and find the 'FileSize' line I need to update, using get_line to read it into the buffer.
5. Append the filesize (plus the number of characters that the variable I use to store the filesize value) to the buffer string.
6. Write the line back to the file using put_line.
However I keep getting invalid file operation errors however I try to re-access the file...
PROCEDURE setUtlFileSize (pFileName IN VARCHAR2,
pFileHandle IN utl_file.file_type) IS
vbFileExists BOOLEAN;
viFileLen NUMBER;
viFileBlockSize NUMBER;
vsFileRecord VARCHAR2(2000);
[code].......
This gives me the following output.... (for two files)... currently I am using some generic exception handling just to show me the error.
File Length: 2106
File Position: 0
-29283,ORA-29283: invalid file operation
File Length: 497
File Position: 0
-29283,ORA-29283: invalid file operation
Process exited.
Two things here concern me: The File Position suggests that the current offset position is set to the start of the file... but since I had not closed or performed any other operation since the last put_line and fflush (which are used to add lines to the file) I had expected that the offset position would be the same as the file length?
Secondly: Even if the position had been reset to the start of the file I don't understand why the get_line gives me the oracle error.
I'm using
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
I'm creating a file using UTL_FILE.FOPEN and UTL_FILE.PUTF
But I don't know the file I created is in which encoding ASCII, UTF-8, EBCDIC etc.
1.) How can I create files in my desired encoding using UTL_FILE?
2.) Does UTL_FILE use database encoding? If yes then how to find out database encoding?
3.) Which encoding is used by UTL_FILE by default?
I have make utl_file. Its run in oracle 10 g. But when i run oracle 9i it give some error.
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "HR.EXP_DATA", line 9
ORA-06512: at line 1.
[code]......
How to spool the Japanese characters in table using UTL_FILE. I tried with utl_file.fopen it's general,it's spooling. but i am not sure it this right way or not. in this case we need to change any character.
We can't see this characters in TOAD. Only possible in PLSQL developer
create table test1(name varchar2(1000),fname varchar2(1000))
insert into test1 values ('名を入力してください。','姓を入力してください。')
commit;
oracle 9i, I am reading about UTL_FILE package.make this in Hr Schema.Where hire date between date 1 and date 2.
View 8 Replies View RelatedI am working on Forms [32 Bit] Version 11.1.1.4.0 (Production). OS is Win 7 (32 bit). Internet Explorer 8. My Requirement is I need to raise an alert when user close the browser window before closing the Form.
View 2 Replies View RelatedI have Multi-threaded application using pro*c to connect to oracle.
I am using the following piece of code to create a connection to oracle database and preserver the context of created connection.
struct sqlca * tempSqlca = new sqlca;
struct sqlca & sqlca = * tempSqlca;
EXEC SQL BEGIN DECLARE SECTION;
SQL_CONTEXT localContext;
[code].....
I am using the following piece of code to close the connection to the oracle database, I am using the context created in the previous step and passing it to the below code(connection.context)to release the connection.
if (connection.context == 0 || connection.ca == 0)
return true;
// Multithreading: get the communication area of the context
struct sqlca & sqlca = *(connection.ca);
EXEC SQL BEGIN DECLARE SECTION;
[code].....
When the above code is executed there is no exception thrown but the connection is not getting close. I am using the V$SESSION system views to monitor the connection created.
I there anything I am missing in my code for the connection closing to fail.
I am trying to create a job that will execute a shell script on a remote server (Server B) when an existing job, on the local server (Server A), completes.
I have spent the last couple of days combing through Metalink, Oracle Documentation, AskTom, and Google. I know this is a relatively "new" feature/ability - released with 11.2 - but this particular error stack is no where to be found. I have attempted to be completely thorough with the information I provided, along with being diligent about masking important configuration information.
While the calling Job is starting the second job, the second job appears to timeout at 60 seconds and the job status is reported as failed with this error stack:
ORA-12537: TNS:connection closed
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 6853
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29261: bad argument
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3975
ORA-06512: at line 1
Configuration information:
Server A and Server B OS Version/Kernal Information:
Oracle Linux Server release 5.8
x86_64
2.6.32-300.24.1.el5uek
Database Version, on all databases, is:
Oracle Database 11g Enterprise Edition
11.2.0.3.0 - 64bit
Remote Scheduler Agent version, on both servers is: 11.2.0.3.1
Here is the listener status on Server B for db2:
serverb $$ lsnrctl status listdb2
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2013 11:39:27
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=***)))
STATUS of the LISTENER
[code]...
I have configured both servers with remote Scheduler agents and both databases have been configured and registered with the agents.
I have executed the shell script on Server B and it performs as expected.
I have the primary job calling the remote job, and that is performing as expected.
I have tested the connection from DB1 on Server A to DB2 on Server B with this
SQL> set serveroutput on
SQL> DECLARE
2 versionnum VARCHAR2(30);
3 BEGIN
4 versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('ServerB.someplace.net');
[code]...
and I have tested the connection from DB2 on Server B to DB1 on Server A with this
SQL> set serveroutput on
SQL> DECLARE
2 versionnum VARCHAR2(30);
3 BEGIN
4 versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('ServerA.someplace.net');
[code]...
One user complained that, his session is closing automatically when his session exceeds exactly 01 hour 1 minute.
default profile is assigned for this user. I checked the idle_time value of this default profile. it is UNLIMITED only. What else I need to check to find out is there any timeout parameter has been set or not?
I am getting the error TNS-12537. In the SQLnet.ora file, if no IP Address is listed everything works fine and the listener starts without any issue. But one of the Security requirements we have is, we need to list all the client IP address on the SQLNet.ora file. I checked the Windows Firewall services and it is disabled.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
M:>tnsping proditg
TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 01-OCT-
013 10:31:14
Copyright (c) 1997, 2011, Oracle. All rights reserved.
[code].....
I am facing ora - 12537 TNS connection closed issue while I am trying to login into database.I have installed Oracle database 11g on windows 7 premium OS.
View 13 Replies View Relatedwhen I am running a cursor and printing its data into an excel file using utl_file, the file size is nearly 50mb. But if I run the cursor and copy its data manually into an excel sheet the file size is only 22mb. I am unable to undersatnd why there is difference in file size.
View 6 Replies View RelatedWe have a p/slq procedure that reads a *.txt file using the UTL_FILE package. The contents of the file are then inserted into a database table.
At the end of the procedure we close the open file using UTL_FILE.FCLOSE.
There is a program (non-oracle)that attempts to move the file to a new location after being read into Oracle. The problem is that the application cannot move the file as the file is locked. ie message displays that the file is open and cannot be moved to a new location.
Is there anything else that we are missing besides the UTL_FILE.FCLOSE.
I'm trying to read the data from flat file and write data into multiple files based on the condition. The value of each line is checked with the Flag Table (Id NUMBER, FlagType VARCHAR(25), Flag CHAR(1)), If the Flag is True then new file has to be created and corresponding line has to be moved into new file otherwise it has to continue with the same file.
CREATE OR REPLACE PROCEDURE rw_demo (File_In VARCHAR2, File_out VARCHAR2) IS
InFile utl_file.file_type;
OutFile utl_file.file_type;
vNewLine VARCHAR2(4000);
i PLS_INTEGER;
j PLS_INTEGER := 0;
SeekFlag BOOLEAN := TRUE;
[code].........
I want to use UTL_FILE package to create OS file. How to resolve this error. Oracle11g under XP.
SQL> create directory my_dir as 'c: emp';
Directory created.
1 create or replace procedure test_1(md in varchar2)
2 is
3 file utl_file.file_type;
4 begin
5 file := utl_file.fopen(md,'abc.log','w');
6 utl_file.put_line(file,'EMPLOYE REPORT');
7 utl_file.fclose(file);
8* end;
SQL> /
Procedure created.
SQL> execute test_1('MY_DIR');
BEGIN test_1('MY_DIR'); END;
RROR at line 1:
RA-06510: PL/SQL: unhandled user-defined exception
RA-06512: at "SYS.UTL_FILE", line 98
RA-06512: at "SYS.UTL_FILE", line 157
RA-06512: at "SCOTT.TEST_1", line 5
RA-06512: at line 1
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
I'm working on a java program connected to a Oracle 9i db. I'm inexperienced about Oracle specific capabilities. The experienced Oracle users
I would like to know if there's a way to create a temporary table so that:
- the table will be deleted automatically when a specific db connection closes (obligatory)
- the table is visible for just one specific connection (optional)
What I would like to do is:
1 - Get the result set for query A.
1a - process the results
1b - store the results in a performance friendly way ( I thought about a temp table)
2 - Run query B over result set A.
3 - Run query C over result set A.
X - The stored result set A has to be removed before/when the program ends (ideally when the db connection closes).
The problem isn't how to do this in java. The problem is to do it in a way so that the table will be automatically removed when the db connection used to create it will be closed so that's unimportant if the java program crashes or ends normally.
I'm using Oracle 11. I'm currently having some problem to start my Oracle database after database restore, hence unable to start my SAP application too.
> lsnrctl start
LSNRCTL for HPUX: Version 10.2.0.4.0 - Production on 25-AUG-2010 23:49:20
Copyright © 1991, 2007, Oracle. All rights reserved.
Starting /oracle/PRD/102_64/bin/tnslsnr: please wait...
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
HPUX Error: 29: Illegal seek
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;
can i use forall instead of 'for loop ..end loop'
i am using oracle 10g enterprise edition.whether its possible to read international characters from the text file using the utl_file function..?
View 1 Replies View RelatedOracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Is there a way to read Excel file using UTL_FILE package ?
Like our usual method:
l_utlfile := utl_file.fopen(p_dir, p_filename, 'R',2000);
p_filename is event.xls
Or do we need to convert that file to .csv or .txt ?
I want to extract data from a table and write into a txt file using UTL_FILE utility.I have written the below query. I am able to see query is returning data.But it is not creating file and writing data into it.
CREATE OR REPLACE DIRECTORY DOC_PATH AS '/apps/orarpt/SJDEV/utl'
CREATE OR REPLACE PROCEDURE xxcfi_outbound_test (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
AS
CURSOR emp_cur
[code].....
i have create database link with msaccess2000 its working fine in forms 6i i am trying to upload data by using forms 6i while compiling form is automatically closed my code is this
begin
insert into baba1
(select *
from baba1@access_db
where dte between to_date(:block3.fdate,'dd-mon-yyyy')
and to_date(:block3.ldate,'dd-mon-yyyy')
[code]....
I've created report to generate letters,my query contains records more than 1000 while generating report it shows report progress
"Formatting Page 1000" after that report builder gets closed without any error message,I've tried for 10 records it is working fine,
I am getting below error while tnsping from client
C:Program FilesMicrosoft Visual Studio 9.0VC>tnsping xpcdev
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-JAN-2011 21:35:03
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.102)(PORT = 1524))) (C
xpcdev)))
TNS-12537: TNS:connection closed
on server side listener is up also I am able to tnsping without any error.
contents from sqlnet.ora
Quote:NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10i
I am getting below error on listener log file
Quote:12-JAN-2011 10:04:56 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied