| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Problems with UTL_FILE
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01BFC4F0.86355320
Content-Type: text/plain
> Hi,
>
> I'm having some difficulty using UTL_FILE to write some records out to a
> pipe delimited flat file. The file is opened correctly and the first row
> is written to the file. The loop continues and after the second fetch the
> script bombs with a UTL_FILE.WRITE_ERROR. I have tested the cursor SQL and
> it is extracting many rows (248) to be exact. The init.ora has the correct
> entry and the database has been bounced and, since the file is being
> written to at least once I'm assuming the UTL_FILE requirements are being
> met. Any ideas?
>
> <<postings.sql>>
>
> Thanks you,
>
> Steve Barlow
>
>
------_=_NextPart_000_01BFC4F0.86355320
Content-Type: application/octet-stream;
name="postings.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="postings.sql"
CREATE OR REPLACE PROCEDURE RUSA_POSTINGS(
i_file_type IN VARCHAR2,
i_log_dir IN VARCHAR2
)
AS
/***********************************************************************=**
File: rusa_post.sql
Pupose: This procedure was created to generate a file of job
postings that is to be FTP'd out to our internet postings
vendor, Recruit USA.
The procedure accepts inbound file type definition in the
form of weekly or daily.
It will be run once a day during the work week to generate
any new or modified postings that have a status of open or
re-open. On Sundays it will completely refresh the entire
list of job postings.
************************************************************************=/
/***********************************************************************=
*
Cursors
************************************************************************=
*/
' ' as contact_phone, ' ' as contact_title, substr(location,1,instr(location,',')-1) as job_city, 'USA' as job_country, descriptions.description as job_description, postings.reqcode_pk as job_number, education.description as = education,
requirements.description as job_qualifications, substr(location,(instr(location,',')+2)) as job_state,
' ' as job_status, rtrim(title) as job_title,' ' as salary_level,
' ' as start_date, ' ' as close_date, ' ' as Monster_category,
' ' as Monster_locations,' ' as newsgroups,
'function code' as function_code,'HEA' as industry_code
FROM bsc_post_new postings, reqdescriptions descriptions,
reqdescriptions education, reqdescriptions requirements
WHERE descriptions.reqcode_pk=3Dpostings.reqcode_pk
AND education.reqcode_pk=3Dpostings.reqcode_pk
AND requirements.reqcode_pk=3Dpostings.reqcode_pk
AND descriptions.descriptor_vk=3D1292
AND education.descriptor_vk=3D1291
AND requirements.descriptor_vk=3D1293;
/***********************************************************************=
*
Programmatic Variables
************************************************************************=
*/
v_req_email VARCHAR2(20) :=3D 'BSC - Recruit USA';
v_fax VARCHAR2(15) :=3D ' ';
v_contact_name VARCHAR2(61);
v_contact_phone VARCHAR2(15) :=3D ' ';
v_contact_title VARCHAR2(255) :=3D ' ';
v_job_city VARCHAR2(35);
v_job_country VARCHAR2(3) :=3D 'USA';
v_job_number CHAR(10);
v_job_description VARCHAR2(2000);
v_education VARCHAR2(500);
v_job_qualifications VARCHAR2(1000);
v_job_state VARCHAR2(35);
v_job_status VARCHAR2(15) :=3D ' ';
v_job_title VARCHAR2(255);
v_salary_level VARCHAR2(15) :=3D ' ';
v_start_date VARCHAR2(15) :=3D ' ';
v_close_date VARCHAR2(15) :=3D ' ';
v_Monster_category VARCHAR2(15) :=3D ' ';
v_Monster_locations VARCHAR2(15) :=3D ' ';
v_newsgroups VARCHAR2(15) :=3D ' ';
v_function_code VARCHAR2(15) :=3D 'function code';
v_industry_code VARCHAR2(3) :=3D 'HEA';
l_file UTL_FILE.file_type;
loop_cnt INTEGER :=3D 1;
err_num NUMBER;
err_msg VARCHAR2(100);
/***********************************************************************=
*
Program Start
************************************************************************=
*/
DBMS_OUTPUT.put_line ('Open the cursor');
LOOP
DBMS_OUTPUT.put_line ('Inside the loop');
FETCH get_job_cur into v_req_email,v_fax,v_contact_name,v_contact_phon=
e,
v_contact_title,v_job_city,v_job_country,v_job_description,
v_job_number,v_education,v_job_qualifications,v_job_state,
v_job_status,v_job_title,v_salary_level,v_start_date,
v_close_date,v_Monster_category,v_Monster_locations,v_newsgroups,
v_function_code,v_industry_code;
DBMS_OUTPUT.put_line ('Done fetching record');
EXIT WHEN get_job_cur%NOTFOUND;
 |
 |