SEARCH YOUR SOLUTION HERE  

Utl

Hi all,
I have a bug to be fixed in Utl_file .Utl file writes to the most
recent open file .
My proc will open the file, write the data from the cursor into it
and will set the flag to 'y'such that the file remains open .And at the
end of the loop the file gets closed.

> Sample data:
>
> ID PROG_ID FILENAME
>
>
>
> 1245 Cmmm M (MORE THAN 1)
>
> 2444 Cdr D
>
> 7878 SOp A
>
> 7899 SOp A
>
> 1245 PROS M (MORE THAN 1)
>
> 2344 PINT B
>
>
>
According to my procedure , first data goes to M file, followed by
D, A, A,next record (5th record) should go to 'M' file but
utl_file writes the data to the most recent open file where the file is 'A'.
How can i avoid this and these files names are generated during the process.

Posted On: Thursday 15th of November 2012 08:19:10 PM Total Views:  317
View Complete with Replies




Related Messages:

outlook file DBX file extract using vb.net   (121 Views)
hi i want to read email in dbx file using vb.net plase help me ....
Please advice on utlbstat/estat results   (154 Views)
DBA's, I ran utlb/estat on my production server bacusae performance came down a bit. Here are some relevant sections: Statistic Total Per Transact Per Logon Per Second --------------------------- ------------ ------------ ------------ ------------ bytes received via SQL*Net 5784845 18721.18 26354.65 3869.46 bytes sent via SQL*Net to c 9284694 30047.55 42299.29 6210.5 WAIT EVENTS: Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- rdbms ipc message 2042 629261 308.16 smon timer 5 153602 30720.4 Does this indicate some sort of Net 8 issues And what is this RDBMS IPC message and SMON timer the session is waiting for
Outlines not used   (161 Views)
Oracle 8.1.7.3.50 EE, MVS, OS3/90 Z/OS Hi In Production after rebuild of partitioned indexes we have some bad execution-plans. Now i want to take the good working Executuin-plans from another instance. Steps Instance 'good' : * give application-user create any outline -Sys-priv * create the outlines from the SQL in V$sql of the 'bad' instance export the outln-tables Instance 'bad' * give application-user create any outline -Sys-privs * alter the system : use_stored_outlines = TRUE * import the outln-tables from good db * flush the shared_pool But on 'bad' instance : select * from user_outlines show still 'UNUSED' in v$sql the columns OUTLINE_CATEGORY is still NULL What i have do wrong both instances are the same Version the *enabled-init.ora is the same Orca
Outlook to mysql database   (137 Views)
Any ideas how I would go about automatically saving the contents of an e-mail (in Outlook) into a MySql database
utl_smtp / utl_tcp   (131 Views)
Hi , I am trying to get e-mail from Oracle to work and seem to be having trouble with the above packages. I have the JSERVER option installed. I have run the $ORACLE_HOME/javavm/install/initjvm.sql. I am having trouble loading the plsql.jar in the $ORACLE_HOME/plsql/jlib directory. I am using the installer to point at this file. Each time I do it says that the source is invalid. Is this step necessary and also is this the correct way of trying to install this java class Many
utl_file   (130 Views)
Can anybody tell me if it's possible for Oracle to write out a file from a PL/SQL proc, (utl_file or whatever) without Oracle being the owner of the file created cheers chris.
utl_file   (148 Views)
Hi all, I set utl_file_dir=remote location. From init.ora utl_file_dir=\\Snap52207\info\info\Datawarehouse\SOURCE_EXTERNES When i execute a simple procedure using utl_file I hve Following error: The following error has occurred: ORA-06510: PL/SQL : exception definie par l'utilisateur non traitee ORA-06512: a "SYS.UTL_FILE", ligne 98 ORA-06512: a "SYS.UTL_FILE", ligne 157 ORA-06512: a "DWH.UTL_FILE_TEST_INSERT", ligne 13 ORA-06512: a ligne 2 PS: This procedure excecute normally under another Oracle server
utl_file   (130 Views)
Hi all, I want to read in a loop lines of flat file and want to ignore the first line(Heading of the columns). How can i do Thank's all
using utl_http pl/sql package   (204 Views)
HI, When I fire below query at our development database SqlPlus prompt it works fine but same query when fired on production gives me error select utl_http.request('http://www.yahoo.com') from dual; The error given on production DB is ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.UTL_HTTP", line 174 ORA-06512: at line 1 What is stopping me on production database to use utl_http.request please reply sandeep
usage of symbolic link in utl_file_dir   (227 Views)
How to use symbolic link in utl_file_dir. The environment is oracle 10g on solaris 10g. Early help is appreciated.
utl_inaddr.get_host_name   (119 Views)
The following query can run on 9i successfully, but it gave error on 8i that wrong number of argument call. How should we run the following query on 8.1.7 Or what its replacement select utl_inaddr.get_host_name(sys_context('USERENV','IP_ADDRESS')) from dual;
utl_file problem on Solaris 9i database   (152 Views)
I'm experiencing problem with utl_file database package on 9.0.1.4.0 Sun Solaris database I use a database stored package to write several thousands of files to server machine using utl_file database package. It worked fine for few months, but after last database restart, strange things begin to happen: files are truncated ! I can't find the reason why files are truncated (number of lines in files are not the same, nor their length in bytes). When I rewrite program not to write several first lines, some missing lines from the end of file appears ! I try to use dbms_output package to debug program and it seems to work fine (every utl_file.put_line is followed by corresponding dbms_output.put_line: all dbms_output.put_line are executed, but the file is still truncated for several lines) I'm not able to restart the database for some time, and I'm not even sure if that will help Can someone help please
utl_file   (98 Views)
Hi , I am using utl_file.fremove to remove some files from the OS side. To say it simple it just creates 2 temp tables, one with the archivelog listing and one with the datafile listing. Then I wrote a little sql query that compares the archivelog dates to the datafiles dates en gives a list of the archivelogs that can be deleted. The problem is with the loop part. I make a cursor of above query and then make a loop. The part which gives an error is: begin utl_file.fremove('DIRECTORY',...); end loop; end; The DIRECTORY is the directory pointing to the archivelog directory on the OS. The ... is the error. When I use the cursor parameter like 'filename' it searches for the string filename and not for the name coming out of the cursor. When I do filename without the quotes it says I have a code fault. So either way he doesn't seem to recognize the file part. Is there any way around it or am I doing something wrong The problem in short is the fact the cursor works but the utl_file part doesn't recognize the filename. The directory is right, I can do a utl_file fremove without a loop, just single OS file remove with the hard coded filename. So I have rights etc. Anyone have any ideas.
Using Stored Outlines   (97 Views)
We just (finally!) swapped from Rule to Cost-mode. Theres some statements that do daft things that will need tuning but it takes our developers a damn long tmie to change the code as we suggest. In the short term im going to create stored outlines for bits of code that run worse under CBO when compared to RBO and have tested it and it works; Alter session user optimizer_mode=rule; create outline fred for select yada yada from etc; alter system set use_stored_outlines=true; Now in another session running CBO, I do the select and the stored outline is used! Fantastic! Ok, now to the problem.... take a bit of code from oracle Forms - "Select col from table where column1=:my_field_on_screen" When you look at this in v$SQL for example the code is now apearing as "Select col from table where column1=:b1" -ie Oracle tidies up the bind variable names and strips out the cariage returns. So, bottom line is which of the sql layouts do I use to create the outline I have tried - using the sql that appears in the form - outline never used. - using the sql that OEM shows - outline never used - using the sql formatted as it appears in V$SQL - outline not used. If I run a bit of sql in another sqlplius window, that matches the select I used to create the outline then the outline is used! Im at a loss - which 'version' of the sql should i use to creae the outline -B
utl_file / nfs   (120 Views)
Got a file on an NFS mounted directory which I am trying to use utl_file on to rename it to another NFS mounted directory e.g. source directory Code: [xx1@xx1 in]$ ls -l total 24 -rw-r--r-- 1 550 its 4033 Jul 7 16:04 file1.xml [xx@xx1 in]$ ls -ld drwxrwxrwx 2 550 its 8192 Jul 11 12:25 . Trying to move it to this directory Code: [xx@xx1 in]$ cd ../success/ [xx@xx1 success]$ ls -ld drwxrwxrwx 2 550 its 4096 Jul 11 12:22 . So the directories have full permissions on them. I am not the owner of the file nor am I in the same group but from the command line I can move the file using mv and the file gets moved however when I try to do it using utl_file I get an error e.g. Code: declare v_file varchar2(100); v_direc constant varchar2(80) := 'IN_DIR'; v_direc2 constant varchar2(80) := 'SUCCESS_DIR'; begin v_file := 'file1.xml'; utl_file.frename(v_direc, v_file, v_direc2, v_file); end; / declare * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 18 ORA-06512: at "SYS.UTL_FILE", line 1108 ORA-06512: at line 7 Why cant utl_file move the file If i give the file group write permission (the oracle user is in that group) then it works What is utl_file.frename doing that is stopping this working unless the group permission is set. Cheers (9.2.0.5, redhat 2.1)
utl_file   (87 Views)
hai in windows for the utl_file package to work i gave utl_file_dir=* in the init.ora file what should i give like the same in the linux machine ie permission for all files
utlbstat utlestat   (91 Views)
I have attached a report after running UTLBSTAT AND UTLESTAT when the system was slow. Can somebody help me out interpreat the results.
Specifying spaces with utl_file.put_line   (98 Views)
The following how could replace my commas with a designated number of spaces, it 10 spaces, or 5 spaces for simply readability of the output utl_file.put_line(v_output_file1, cursor_emp.empno || ',' || cursor_emp.ename || ',' || cursor_emp.deptno);
sending an attachment by using utl_smtp   (176 Views)
i have created the following procedure for sending email its working fine but i want to send an attachment file with email and i didn't find any help Can anybody help me in this regard CREATE OR REPLACE PROCEDURE email_from_plsql IS tmpVar NUMBER; c UTL_SMTP.CONNECTION; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF); END; BEGIN -- Open connection to SMTP gateway c := UTL_SMTP.OPEN_CONNECTION('192.168.0.12',25); UTL_SMTP.HELO(c, '192.168.0.12'); UTL_SMTP.MAIL(c, 'mshakeel@nctex.com'); UTL_SMTP.RCPT(c, 'mshakeel@nctex.com'); UTL_SMTP.RCPT(c, 'afzaal@nctex.com'); UTL_SMTP.OPEN_DATA(c); send_header('From','mshakeel@nctex.com'); send_header('To','mshakeel@nctex.com'); send_header('Cc','afzaal@nctex.com'); send_header('Subject','Automated Database Email'); UTL_SMTP.WRITE_DATA(c, utl_tcp.CRLF || 'This is an automated email from the Oracle database.'); UTL_SMTP.WRITE_DATA(c, utl_tcp.CRLF || 'The database is working for you!'); UTL_SMTP.CLOSE_DATA(c); UTL_SMTP.QUIT(c); EXCEPTION WHEN NO_DATA_FOUND THEN Null; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END email_from_plsql; /
outlook express to oracle -- automation   (178 Views)
Hi I am getting some client details in formated mail.(say outlook express (excel formated) or outlook). I need to transfer that data into my database. How to insert the details automatically into my oracle 8i database. ( We want to automate that process).