Home » SQL & PL/SQL » SQL & PL/SQL » utl_file error (oracle 9i)
utl_file error [message #584502] Tue, 14 May 2013 22:52 Go to next message
umesh seth
Messages: 5
Registered: May 2013
Location: Gurgaon
Junior Member
Hi all i have a procedure u_rtemp


problem in this procedure this is write only one line in text file .

create or replace procedure u_Rtemp as
  cursor c1(vNewLine varchar2) is
    select * from tb_sms a where a.account_nber in (vNewLine);
  vInHandle           utl_file.file_type;
  vNewLine            VARCHAR2(250);
  M_HANDLE            UTL_FILE.FILE_TYPE;
  M_CONTROL           UTL_FILE.FILE_TYPE;
  M_FILE_NAME         VARCHAR2(100) := 'SMS' || '_' || '_' ||
                                       TO_CHAR(SYSDATE, 'MMDDYYYYHHMM') ||
                                       '.txt';
  M_FILE_NAME_CONTROL VARCHAR2(100) := 'SMS_UMESH' || '_' || '_' ||
                                       TO_CHAR(SYSDATE, 'MMDDYYYYHHMM') ||
                                       '.txt';
  P_PATH_NAME         VARCHAR2(1000) := '/ods/dm/data/naps_adq';
  C_NAME              VARCHAR2(50) DEFAULT 'SMS_DATA';
  P_FREQ              VARCHAR2(5) DEFAULT 'M';
BEGIN
  vInHandle := utl_file.fopen(P_PATH_NAME, 'test.txt', 'R');
  utl_file.get_line(vInHandle, vNewLine);
  
  M_HANDLE := UTL_FILE.FOPEN(P_PATH_NAME, M_FILE_NAME, 'W', 32767);
  
  UTL_FILE.PUT_LINE(M_HANDLE, 'TITLE|FIRST_NAME');
  
  for i in C1(vNewLine) LOOP
    UTL_FILE.PUT_LINE(M_HANDLE, i.ACCOUNT_NBER || '|' || i.name);
  END LOOP;
  utl_file.fclose(vInHandle);
  utl_file.fclose(M_HANDLE);
  
end u_rtemp;


structure of table tb_sms.

create table TB_SMS
(
  ACCOUNT_NBER VARCHAR2(11),
  NAME         VARCHAR2(10)
)



insert into tb_sms values ('1234','A);
insert into tb_sms values ('1235','B);
insert into tb_sms values ('1236','C);

Read test file is also attached .

Please help.

Thanks and regards

Umesh C Seth

*BlackSwan added {code} tags. Please do so yourself in the future.
  • Attachment: Test.txt
    (Size: 0.02KB, Downloaded 31 times)

[Updated on: Tue, 14 May 2013 22:55] by Moderator

Report message to a moderator

Re: utl_file error [message #584503 is a reply to message #584502] Tue, 14 May 2013 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 21947
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

what is code supposed to do?

code only ever reads 1 line from test.txt file.

[Updated on: Tue, 14 May 2013 23:08]

Report message to a moderator

Re: utl_file error [message #584506 is a reply to message #584503] Tue, 14 May 2013 23:14 Go to previous messageGo to next message
umesh seth
Messages: 5
Registered: May 2013
Location: Gurgaon
Junior Member
sir,

problem in this code
code reading only online from (TEST.txt)and write only one line.



Re: utl_file error [message #584508 is a reply to message #584506] Tue, 14 May 2013 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 21947
Registered: January 2009
Senior Member
No error is thrown, so I don't see any problem.
Re: utl_file error [message #584511 is a reply to message #584506] Tue, 14 May 2013 23:25 Go to previous messageGo to next message
umesh seth
Messages: 5
Registered: May 2013
Location: Gurgaon
Junior Member

Dear sir.

current code nature is reading one line line and write it.
but requirement is.

cursor c1(vNewLine varchar2) is
select * from tb_sms a where a.account_nber in (vNewLine);

vnewline is used in where clause in cursor.

suppose if two account_nber matched according to cursor then generate text file with two record.

Re: utl_file error [message #584520 is a reply to message #584511] Wed, 15 May 2013 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select * from tb_sms a where a.account_nber in (vNewLine);

vNewLine is ONE value and only ONE whatever you put in it.
Search for "varying in-list".

Note that your INSERT statements are invalid. Please post VALID and TESTED code.

Regards
Michel
Re: utl_file error [message #584524 is a reply to message #584506] Wed, 15 May 2013 01:01 Go to previous messageGo to next message
umesh seth
Messages: 5
Registered: May 2013
Location: Gurgaon
Junior Member
hi michel,

find below tested and recompiled code.

create or replace procedure u_Rtemp as
  cursor c1(vNewLine varchar2) is
    select * from tb_sms a where a.account_nber in (vNewLine);
  vInHandle           utl_file.file_type;
  vNewLine            VARCHAR2(250);
  M_HANDLE            UTL_FILE.FILE_TYPE;
  M_CONTROL           UTL_FILE.FILE_TYPE;
  M_FILE_NAME         VARCHAR2(100) := 'SMS' || '_' || '_' ||
                                       TO_CHAR(SYSDATE, 'MMDDYYYYHHMM') ||
                                       '.txt';
  M_FILE_NAME_CONTROL VARCHAR2(100) := 'SMS_UMESH' || '_' || '_' ||
                                       TO_CHAR(SYSDATE, 'MMDDYYYYHHMM') ||
                                       '.txt';
  P_PATH_NAME         VARCHAR2(1000) := '/ods/dm/data/naps_adq';
  C_NAME              VARCHAR2(50) DEFAULT 'SMS_DATA';
  P_FREQ              VARCHAR2(5) DEFAULT 'M';
BEGIN
  vInHandle := utl_file.fopen(P_PATH_NAME, 'test.txt', 'R');
  utl_file.get_line(vInHandle, vNewLine);
  
  M_HANDLE := UTL_FILE.FOPEN(P_PATH_NAME, M_FILE_NAME, 'W', 32767);
  
  UTL_FILE.PUT_LINE(M_HANDLE, 'TITLE|FIRST_NAME');
  
  for i in C1(vNewLine) LOOP
    UTL_FILE.PUT_LINE(M_HANDLE, i.ACCOUNT_NBER || '|' || i.name);
  END LOOP;
  utl_file.fclose(vInHandle);
  utl_file.fclose(M_HANDLE);
  
end u_rtemp;


structure of table tb_sms.

create table TB_SMS
(
  ACCOUNT_NBER VARCHAR2(11),
  NAME         VARCHAR2(10)
)



insert into tb_sms values ('1234','A');
insert into tb_sms values ('1235','B');
insert into tb_sms values ('1236','C');

----


Thanks


[mod-edit: code tags added by bb]

[Updated on: Mon, 20 May 2013 19:17] by Moderator

Report message to a moderator

Re: utl_file error [message #584528 is a reply to message #584524] Wed, 15 May 2013 01:23 Go to previous messageGo to next message
umesh seth
Messages: 5
Registered: May 2013
Location: Gurgaon
Junior Member
i am waiting for your revert sir.

Re: utl_file error [message #584530 is a reply to message #584528] Wed, 15 May 2013 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 15 May 2013 07:26
Quote:
select * from tb_sms a where a.account_nber in (vNewLine);

vNewLine is ONE value and only ONE whatever you put in it.
Search for "varying in-list".

...


Use SQL*Plus and copy and paste your session, the WHOLE session.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Re: utl_file error [message #584977 is a reply to message #584502] Mon, 20 May 2013 20:22 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
Please see the example below, noting the comments. Note the code that loops through the input file and that the file is opened and the header written before the loop. Also note that I have used an Oracle directory object, instead of using the path directly. I used a different directory on my system and removed the time portion from the output file name for convenience. I also removed some unused variables. Everything else is similar to your original code.

SCOTT@orcl_11gR2> -- input file:
SCOTT@orcl_11gR2> host type test.txt
1234
1235
1456
1457

SCOTT@orcl_11gR2> -- create table:
SCOTT@orcl_11gR2> CREATE TABLE tb_sms
  2    (account_nber  VARCHAR2(11),
  3     name          VARCHAR2(10))
  4  /

Table created.

SCOTT@orcl_11gR2> -- insert data:
SCOTT@orcl_11gR2> INSERT ALL
  2  INTO tb_sms VALUES ('1234', 'A')
  3  INTO tb_sms VALUES ('1235', 'B')
  4  INTO tb_sms VALUES ('1236', 'C')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11gR2> -- create Oracle directory object that points to directory path:
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> -- create procedure:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE u_rtemp
  2  AS
  3    vInHandle           UTL_FILE.FILE_TYPE;
  4    -- use Oracle directory object (MY_DIR) that points to path in upper case:
  5    p_path_name         VARCHAR2(1000) := 'MY_DIR';
  6    m_handle            UTL_FILE.FILE_TYPE;
  7    m_file_name         VARCHAR2(100) := 'SMS' || '_' || '_' ||
  8                                         TO_CHAR(SYSDATE, 'MMDDYYYY') ||
  9                                         '.txt';
 10    vNewLine            VARCHAR2(250);
 11    CURSOR c1 (vNewLine VARCHAR2) IS
 12      SELECT * FROM tb_sms WHERE account_nber IN (vNewLine);
 13  BEGIN
 14    -- open input and output files:
 15    vInHandle := UTL_FILE.FOPEN (p_path_name, 'test.txt', 'R', 32767);
 16    m_handle := UTL_FILE.FOPEN (p_path_name, m_file_name, 'W', 32767);
 17    -- write header line to output file:
 18    UTL_FILE.PUT_LINE (m_handle, 'TITLE|FIRST_NAME');
 19    -- loop through data in input file, reading one line per loop
 20    -- and passing parameter to cursor:
 21    LOOP
 22      BEGIN
 23        UTL_FILE.GET_LINE (vInHandle, vNewLine, 32767);
 24      EXCEPTION
 25        WHEN NO_DATA_FOUND THEN EXIT;
 26      END;
 27      -- loop through cursor, writing one line per loop to output file:
 28      FOR i IN c1 (vNewLine) LOOP
 29        UTL_FILE.PUT_LINE (m_handle, i.account_nber || '|' || i.name);
 30      END LOOP;
 31    END LOOP;
 32    -- close input and output files:
 33    UTL_FILE.FCLOSE (vInHandle);
 34    UTL_FILE.FCLOSE (m_handle);
 35  END u_rtemp;
 36  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- execute procedure:
SCOTT@orcl_11gR2> EXEC u_rtemp

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- check results:
SCOTT@orcl_11gR2> host type sms__05202013.txt
TITLE|FIRST_NAME
1234|A
1235|B

[Updated on: Mon, 20 May 2013 20:23]

Report message to a moderator

Previous Topic: Join in Insert
Next Topic: Variable is NULL
Goto Forum:
  


Current Time: Sat Apr 19 11:18:51 CDT 2014

Total time taken to generate the page: 0.07460 seconds