ORA-06510: PL/SQL: unhandled user-defined exception

[FunkMaster]

Hello

ARRggh

i get this error message below for the script important points below that, as far i see its all in order and i have swaped lots of bits about:-

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 143
ORA-06510: PL/SQL: unhandled user-defined exception


OPEN Cur_Job_Data;

v_stmt := 'FETCH CURSOR Cur_Job_Data';

LOOP

FETCH Cur_Job_Data
INTO
V_JOB_WINDOW_START,
V_JOB_WINDOW_END,
V_JOBSTATUS_DESC,
V_ABS_APPT_TYPE,
V_MPAN,
V_JOBCODE_DESC,
V_DISP_NOTES,
V_ENQ_NO,
V_APPT_NO,
V_RESP_UNITS;

IF Cur_Job_Data%NOTFOUND THEN

EXIT;

END IF;


utl_file.put_line(v_output_file, V_JOB_WINDOW_START||','||
V_JOB_WINDOW_END||','||
V_JOBSTATUS_DESC||','||
V_ABS_APPT_TYPE||','||
V_MPAN||','||
V_JOBCODE_DESC||','||
Replace(Replace(Replace(Replace(Replace(Replace(V_DISP_NOTES,',',' ') ,chr(13),' '),'*',' '),chr(10),' '),chr(8),' '),chr(9),' ')||','||
V_ENQ_NO||','||
V_APPT_NO||','||
V_RESP_UNITS);

END LOOP;

Close Cur_Job_Data ;

anyone help

oh v_job_window_start and END is an date and defined as date
and this is the cursor below:-

CURSOR Cur_Job_Data is
SELECT
to_char(APPOINTMENT.JOB_WINDOW_START,'dd/mm/yyyy HH24:mi:ss'),
to_char(APPOINTMENT.JOB_WINDOW_END,'dd/mm/yyyy HH24:mi:ss'),
JOBSTATUS.DESCRIPTION,
APPOINTMENT.ABS_APPT_TYPE,
APPOINTMENT.MPAN,
JOBCODE.DESCRIPTION,
APPOINTMENT.DISPATCHER_NOTES,
APPOINTMENT.ENQUIRY_NUMBER,
APPOINTMENT.APPOINTMENT_NO,
RESPONSIBILITY_UNITS.DESCRIPTION
FROM
SRC.APPOINTMENT APPOINTMENT,
SRC.DEPOTS DEPOTS,
SRC.JOBCODE JOBCODE,
SRC.JOBSTATUS JOBSTATUS,
SRC.RESPONSIBILITY_UNITS RESPONSIBILITY_UNITS
WHERE
JOBSTATUS.REC_ID = APPOINTMENT.JOBSTATUS_REC_ID
AND JOBCODE.REC_ID = APPOINTMENT.JOBCODE_REC_ID
AND DEPOTS.REC_ID = APPOINTMENT.DEPOT_REC_ID
AND DEPOTS.RESPONSIBILITY_UNITS_REC_ID = RESPONSIBILITY_UNITS.REC_ID
AND APPOINTMENT.JOB_WINDOW_START >= v_start_Report_date
And APPOINTMENT.JOB_WINDOW_START < v_end_Report_date
AND JOBSTATUS.DESCRIPTION='Unassigned'
;

5% is not an rise, its an poke in the eye.

 

[sem]

As you may see the error is raised in UTL_FILE package. There's a number of exceptions declared in its specification. The only thing you need is to handle them. In fact the error may be raised in the part of your code where file is opened or anywhere else.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Funk,

I believe you have not posted all your PL/SQL code that we need to see. Your statement, "utl_file.put_line(v_output_file,..." has two problems if you truly posted all your code: 1) where did you define, "v_output_file" and 2) where did you OPEN your file with a "utl_file.fopen" function?

If you post the code that resolves these two issues, then we can make more progress in resolving your need. Also, it would be helpful to know the contents of your Oracle instance parameter "utl_file_dir". Please post that information by issuing this query:

select value from v$parameter
where name = 'utl_file_dir';

Looking forward to your post,

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:37 (06May04) UTC (aka "GMT" and "Zulu"), 02:37 (06May04) Mountain Time)

 

[FunkMaster]

okay

its:-

DECLARE

/* Insert script variables here */
v_start_Report_date date;
v_end_Report_date date;
V_JOB_WINDOW_START date;
V_JOB_WINDOW_END date;
V_JOBSTATUS_DESC SRC.JOBSTATUS.DESCRIPTION%Type;
V_ABS_APPT_TYPE SRC.APPOINTMENT.ABS_APPT_TYPE%Type;
V_MPAN SRC.APPOINTMENT.MPAN%Type;
V_JOBCODE_DESC SRC.JOBCODE.DESCRIPTION%Type;
V_DISP_NOTES SRC.APPOINTMENT.DISPATCHER_NOTES%Type;
V_ENQ_NO SRC.APPOINTMENT.ENQUIRY_NUMBER%Type;
V_APPT_NO SRC.APPOINTMENT.APPOINTMENT_NO%Type;
V_RESP_UNITS SRC.RESPONSIBILITY_UNITS.DESCRIPTION%Type;


/**** From here to the cursor declarations are standard variables for ****/
/**** Reports programs. The only change that should be need is setting ****/
/**** the value for v_prog_id. ****/

/* Declare the variables for the start, end and file datetime */
/* As all these dates are to be to_char, they are declared as varchar */
v_start_date varchar2(21);
v_file_date varchar2(21);
v_end_date varchar2(21);

/* Declare the script/report id*/
/* The 'constant' ensures that the variable cannot be changed */
v_prog_id constant varchar2(20):='MOUnass05';

/* Declare output file */
v_output_file utl_file.file_type;

/* Declare the log and error files */
v_log_file utl_file.file_type;
v_error_file utl_file.file_type;

/* Declare the variables for the error codes and message */
v_error_code varchar2(30);
v_error_msg varchar2(300);

/* Declare the statement variable to identify the statement with the error */
/* Each statement must have an unique description variable assigned to it before it is excuted */

v_stmt varchar2(50);

/**** Cursors can be inserted here ****/

CURSOR Cur_Job_Data is
SELECT
to_date(to_char(APPOINTMENT.JOB_WINDOW_START,'dd/mm/yyyy HH24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss'),
to_date(to_char(APPOINTMENT.JOB_WINDOW_END,'dd/mm/yyyy HH24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss'),
JOBSTATUS.DESCRIPTION,
APPOINTMENT.ABS_APPT_TYPE,
APPOINTMENT.MPAN,
JOBCODE.DESCRIPTION,
APPOINTMENT.DISPATCHER_NOTES,
APPOINTMENT.ENQUIRY_NUMBER,
APPOINTMENT.APPOINTMENT_NO,
RESPONSIBILITY_UNITS.DESCRIPTION
FROM
SRC.APPOINTMENT APPOINTMENT,
SRC.DEPOTS DEPOTS,
SRC.JOBCODE JOBCODE,
SRC.JOBSTATUS JOBSTATUS,
SRC.RESPONSIBILITY_UNITS RESPONSIBILITY_UNITS
WHERE
JOBSTATUS.REC_ID = APPOINTMENT.JOBSTATUS_REC_ID
AND JOBCODE.REC_ID = APPOINTMENT.JOBCODE_REC_ID
AND DEPOTS.REC_ID = APPOINTMENT.DEPOT_REC_ID
AND DEPOTS.RESPONSIBILITY_UNITS_REC_ID = RESPONSIBILITY_UNITS.REC_ID
AND APPOINTMENT.JOB_WINDOW_START >= v_start_Report_date
And APPOINTMENT.JOB_WINDOW_START < v_end_Report_date
AND JOBSTATUS.DESCRIPTION='Unassigned'
;

/*********** START of PROGRAM LOGIC ************/

BEGIN

/*** Initialisation Processing ***/

/* Select the start time of the script, which is also used in the file name */
v_stmt := 'Start Time';

v_start_date := to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS');
v_file_date := to_char(sysdate, 'YYYYMMDDHH24MISS');
v_start_Report_date := to_date(to_char(sysdate-1, 'dd/mm/yyyy')||' 00:00:00', 'dd/mm/yyyy hh24:mi:ss');
v_end_Report_date := to_date(to_char(sysdate-1, 'dd/mm/yyyy')||' 23:59:59', 'dd/mm/yyyy hh24:mi:ss');

/* Assign v_log_file using the start date of the script and print the start time to the log file*/
v_log_file := utl_file.fopen('/sqt',v_prog_id||'.'||v_file_date||'.log','w');
utl_file.put_line(v_log_file,v_prog_id||' Run Start Time = '||v_start_date);

/*Assign the output_file */
v_output_file := utl_file.fopen('/sqt',v_prog_id||'.'||v_file_date||'.csv','w');

/* Format and output report header record */
v_stmt := 'Title and report Header';

utl_file.put_line(v_output_file,v_prog_id||',Run Date: '||v_start_date);
utl_file.new_line(v_output_file,1);
utl_file.put_line(v_output_file,'Unassigned Jobs in SABRE between '||v_start_Report_date||' and '||v_end_Report_date);
utl_file.new_line(v_output_file,1);
utl_file.put_line(v_output_file,'JOB_WINDOW_START,JOB_WINDOW_END,DESCRIPTION,ABS_APPT_TYPE,MPAN,DESCRIPTION,DISPATCHER_NOTES,ENQUIRY_NUMBER,APPOINTMENT_NO,DESCRIPTION');


/******* The main body of the PL\SQL program goes here *******/
/** The first row has been fetched from the cursor before the loop **/
/** is entered. The next row is fetched from the cursor as the last **/
/** step inside the loop. **/
/** When NOTFOUND condition is encountered the loop is not entered, **/
/** instead the program flow goes to the termination section **/


v_stmt := 'OPEN CURSOR Cur_Job_Data';
OPEN Cur_Job_Data;

v_stmt := 'FETCH CURSOR Cur_Job_Data';

LOOP

FETCH Cur_Job_Data
INTO
V_JOB_WINDOW_START,
V_JOB_WINDOW_END,
V_JOBSTATUS_DESC,
V_ABS_APPT_TYPE,
V_MPAN,
V_JOBCODE_DESC,
V_DISP_NOTES,
V_ENQ_NO,
V_APPT_NO,
V_RESP_UNITS;

IF Cur_Job_Data%NOTFOUND THEN

EXIT;

END IF;


utl_file.put_line(v_output_file, V_JOB_WINDOW_START||','||
V_JOB_WINDOW_END||','||
V_JOBSTATUS_DESC||','||
V_ABS_APPT_TYPE||','||
V_MPAN||','||
V_JOBCODE_DESC||','||
Replace(Replace(Replace(Replace(Replace(Replace(V_DISP_NOTES,',',' ') ,chr(13),' '),'*',' '),chr(10),' '),chr(8),' '),chr(9),' ')||','||
V_ENQ_NO||','||
V_APPT_NO||','||
V_RESP_UNITS);

END LOOP;

Close Cur_Job_Data ;

/****** Termination Process *******/


/* Select the script completion time into v_end_date from dual and print to the log file */

v_stmt := 'SELECT END DATE';
SELECT to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into v_end_date
from dual;
utl_file.put_line(v_log_file,v_prog_id||' Run End Time = '||v_end_date);

/* Close all files on successful completion */
utl_file.fclose_all;


/***** END of MAIN PROGRAM Logic *****/


/*** GENERAL EXCEPTION HANDLING BLOCK ***/
/* This should be placed at the end of the main processing block */
/* This writes the error code and message to the error file */

EXCEPTION
WHEN OTHERS
THEN

/* Assign the error code and text to the variables */
v_error_code := to_char(SQLCODE);
v_error_msg := SQLERRM;

/* Assing a file to the error file */
v_error_file := utl_file.fopen('/sqt',v_prog_id||'.'||v_file_date||'.err','w');

/* Print the Start time, error code and text to the error log */
utl_file.put_line(v_error_file,v_prog_id||' Run Start Time = '||v_start_date);
utl_file.put_line(v_error_file,'* ERROR at Statement label: '||v_stmt);
utl_file.put_line(v_error_file,'SQLCODE = '||v_error_code||' SQLERRM = '||v_error_msg);


/* Select the script completion time into v_end_date from dual and print to the error file */
select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
into v_end_date
from dual;
utl_file.put_line(v_error_file,v_prog_id||' Run Error Time = '||v_end_date);

/* Close all files on erroneous completion */
utl_file.fclose_all;

END;

/


the error file thing also falls over

5% is not an rise, its an poke in the eye.

 

[SantaMufasa]

Funk,

Also, as I requested, could you also please post the results of the query:

select value from v$parameter
where name = 'utl_file_dir';

...and the ownership and permissions of your path, "/sqt"?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:54 (06May04) UTC (aka "GMT" and "Zulu"), 02:54 (06May04) Mountain Time)

 

[FunkMaster]

well the answer is

select value from v$parameter
*
ERROR at line 1:
ORA-00942: table or view does not exist

and the parameter not exist in this database
i have full control on the sqt drive. its the normal one we write to , all i have done really is taken an script that works addded the new cursor, and cursor fetch and now it does not

5% is not an rise, its an poke in the eye.

 

[SantaMufasa]

Funk,

Here are some followups:
1) I presumed you had DBA privileges (to be able to access v$parameter). It would be helpful if you could at least view certain values that reside in your database's data dictionary such as "V$..." values. You (or any other grantee) can see those values without receiving meaningful DBA privileges if your DBA grants you:

GRANT SELECT_CATALOG_ROLE to <your user name>;

It would eliminate one possible error cause if you confirmed the instance's "utl_file_dir" parameter value (since a NULL in utl_file_dir or a value that does not include "/sqt" would explain your error).

2) When you say, "i have full control on the sqt drive," who is "I"? Unless ORACLE, too, has write privileges to "/sqt", then you will encounter an error like the one you received, since it is actually the ORACLE server process that is writing to "/sqt".

3) If "utl_file_dir = /sqt" (or something similar) and the o/s ORACLE user has write privileges to "/sqt", then I would try to isolate the problem with the simplest possible script:

declare
	v_output_file utl_file.file_type;
begin
	v_output_file := utl_file.fopen('/sqt','yada.txt','W');
	utl_file.put_line(v_output_file,'This is a test');
	utl_file.fclose_all;
end;
/
PL/SQL procedure successfully completed.
SQL> get /sqt/yada.txt
  1* This is a test
SQL>

If you receive an error or do not see the same results that I receive, above, then there is a problem. If everything looks the same as above, then we must look deeper as to the cause of your run-time output error from the "SYS.UTL_FILE" packaged procedure.

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:57 (06May04) UTC (aka "GMT" and "Zulu"), 03:57 (06May04) Mountain Time)

 

[FunkMaster]

cheers for looking

after running that parameter script and asking around i think i know whats happening, will have speak to our DBAs about setting up that 'utl_file_dir'thing, the database that i was using is an live one ( we run many scripts againist copies) i was just thinking i bet they not set that up!. but cheers for looking and helping out, and that bit of parameter script thing.

cheers

5% is not an rise, its an poke in the eye.

 

[FunkMaster]

cheers again mufasa

5% is not an rise, its an poke in the eye.

 

[DeepDiverMom]

FunkMaster,

I have always learned from Mufasa's logical methods of troubleshooting and isolating problems. In this case, he has probably identified your problem even though you claimed that the things he identified as probable problems were okay, until you realized that he was right.

Then you thanked him with a "cheers". So besides a "5% raise being a poke in the eye", add to you list: "A 'cheers' or a 'thank-you' without a purple star is a poke in the eye." I'll leave it up to you to decide what to do.

Becca