Home » SQL & PL/SQL » SQL & PL/SQL » File write error
File write error [message #318464] Tue, 06 May 2008 23:11 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear ,
CREATE OR REPLACE PROCEDURE process_report
AS

ft UTL_FILE.FILE_TYPE;
f VARCHAR2 (30) := 'MNJ' || TO_CHAR (SYSDATE, 'ddmmyyyy')|| '.csv';
BEGIN
ft := UTL_FILE.FOPEN ('UTL_PATH_C', f, 'w');
UTL_FILE.PUT_LINE
(ft,
'"Sno.","Emp Code","Name","Relation","Birth Date"'
);

FOR i IN 1 .. medicarelist.COUNT
LOOP
UTL_FILE.PUT_LINE (ft,
i
|| ','
|| medicarelist (i).med_comp_name
|| ','
|| medicarelist (i).relation_name
|| ','
|| medicarelist (i).relation
|| ','
|| medicarelist (i).birth_dt
|| ','
);
END LOOP;
UTL_FILE.FCLOSE (ft);
END;

In medicarelist data's will come more than 50,000.
When am executing this procedure am getting file write error.
Kindly suggest me.
Re: File write error [message #318479 is a reply to message #318464] Wed, 07 May 2008 00:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why don't you just use spool for this instead of UTL_FILE. It's seems that you are writing data in a file by pulling data from a table.
Re: File write error [message #318481 is a reply to message #318479] Wed, 07 May 2008 00:31 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Yes right.Am taking the data from a table.Can you please advice me how to use spool.
Re: File write error [message #318483 is a reply to message #318481] Wed, 07 May 2008 00:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
subbu_tce wrote on Wed, 07 May 2008 07:31
Can you please advice me how to use spool.

Open browser
Go to http://www.google.com
type oracle spool
hit enter
Re: File write error [message #318491 is a reply to message #318483] Wed, 07 May 2008 00:50 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Open SQL* Plus:-

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
       123 Bob 1 2 1  Sales            555      10000         14         11
       321 Sue        Finance          555      42000         12         33
       234 Mary       Account          555      33000         12         22
       289 Test 1     Manager          555      45000         12         23
       888 Bob 1 2 1  NOZOMI           555      10000         12         11
       123 Bob 1 2 1  Sales            555      10000         12         11

6 rows selected.

SQL> spool c:\test.csv
SQL> select empno||','||ename||','||job||','||mgr||','||sal||','||comm||','||deptno from emp;

EMPNO||','||ENAME||','||JOB||','||MGR||','||SAL||','||COMM||','||DEPTNO
--------------------------------------------------------------------------------
123,Bob 1 2 1,Sales,555,10000,14,11
321,Sue,Finance,555,42000,12,33
234,Mary,Account,555,33000,12,22
289,Test 1,Manager,555,45000,12,23
888,Bob 1 2 1,NOZOMI,555,10000,12,11
123,Bob 1 2 1,Sales,555,10000,12,11

6 rows selected.

SQL> spool off;


Find Data in test.csv file in C:\directory.
Re: File write error [message #318501 is a reply to message #318491] Wed, 07 May 2008 01:01 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Thanks.........
Re: File write error [message #318512 is a reply to message #318491] Wed, 07 May 2008 01:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@rajatratewal
From the forum guidelines:
Quote:
Responding to Posts

When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


The OP could have easily found out how to use spool by using Frank's advice. Try not to spoon feed learners, you are not doing them any favours in the long run.
Re: File write error [message #318575 is a reply to message #318491] Wed, 07 May 2008 03:33 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
if aim to load data in excell through csv,
spooled data makes no sense since itignores formating
concepts.
yours
dr.s.raghuanthan
Re: File write error [message #318577 is a reply to message #318575] Wed, 07 May 2008 03:36 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
csv files contain no excel formatting anyway.
The way the OP wrote his records can just as well be done by spooling
Previous Topic: Reg. cursor based delete! Little Urgent!
Next Topic: Irregular nature of Order By Clause
Goto Forum:
  


Current Time: Thu Dec 08 04:22:22 CST 2016

Total time taken to generate the page: 0.13592 seconds