Hi All,
This procedure was working in 10GR/2 but 11G thsi is failing with error:
Error(16,13): PLS-00341: declaration of cursor 'ANALYZED_CUR' is incomplete or malformed
Error(19,6): PL/SQL: SQL Statement ignored
Error(54,14): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error is popping up at all location which contains analyzed_cur and each_rec.
<pre>
PROCEDURE extract_test
AS
--local variables
outfile UTL_FILE.file_type;
lv_comma VARCHAR2 (1) := ',';
lv_detail VARCHAR2 (1000);
--open cursor for
CURSOR analyzed_cur
IS
SELECT
distinct c.pid,a.frequency,b.uplink,b.downlink
from
CELLS_SECTORS_LOOKUP a,
capacity_current_cell b,
best_serving_sector c,
TOWER_INSERVICE d
where a.cell_id =b.cell_id
and UPPER(a.site_id) =UPPER( c.inb_site_id)
and a.sector_id = c.inb_sector_id
and UPPER(d.site_id) =UPPER( a.site_id)
and UPPER(d.inservice_boolean) = 'Y'
AND (UPPER(available_for_sale) ='RED' OR available_for_sale is null);
each_rec analyzed_cur%ROWTYPE;
BEGIN
/* Opening output file name in 'Write' mode */
outfile := UTL_FILE.fopen (gv_exp_directory, gv_file_name, 'w', 32767);
UTL_FILE.put_line (outfile,'address_detail_pid,frequency,uplink,downlink');
OPEN analyzed_cur;
BEGIN
LOOP
FETCH analyzed_cur
INTO each_rec;
EXIT WHEN analyzed_cur%NOTFOUND;
lv_detail := '';
lv_detail := lv_detail || each_rec.pid;
lv_detail := lv_detail || lv_comma || each_rec.frequency;
lv_detail := lv_detail || lv_comma || each_rec.uplink;
lv_detail := lv_detail || lv_comma || each_rec.downlink;
UTL_FILE.put_line (outfile, lv_detail);
END LOOP;
END;
CLOSE analyzed_cur;
/* Close output file */
UTL_FILE.fclose (outfile);
COMMIT;
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Invalid Mode Parameter' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Invalid File Location' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Invalid Filehandle' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Invalid Operation' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Read Error' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Internal Error' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.charsetmismatch
THEN
UTL_FILE.fclose (outfile);
gv_error_message :=
'Opened With FOPEN_NCHAR But Later I/O Inconsistent'
|| ' - '
|| SQLERRM;
RAISE gv_exp_generate_error;
WHEN UTL_FILE.file_open
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'File Already Opened' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_maxlinesize
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Line Size Exceeds 32K' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_filename
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Invalid File Name' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.access_denied
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'File Access Denied By' || ' - ' ||(SQLERRM);
RAISE gv_exp_generate_error;
WHEN UTL_FILE.invalid_offset
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'FSEEK Param Less Than 0' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
WHEN OTHERS
THEN
UTL_FILE.fclose (outfile);
gv_error_message := 'Unknown UTL_FILE Error' || ' - ' || (SQLERRM);
RAISE gv_exp_generate_error;
END extract_test;
END SB_TEST_EXTRACTION_PKG;
</pre>