PL/SQL procedure

[babsjoy]

I am a beginner PL/SQL programmer. I am trying obtain fields from a cursor, fetch them into a record and write the record out using UTL_FILE.PUT_LINE(filehandle,string)
I can't compile the below without getting an error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'.
Where do I convert the numeric fields to character ? In the record definition in the SELECT statement ? Can I use the record in the UTL_file.PUT_LINE command ? Should I use UTL_file.PUTF and convert the fields on the way to the file ? The code is below:

CREATE OR REPLACE PROCEDURE APRP100_Requisition_Unload(p_filedir in varchar2,p_filename varchar2)

--Enter parameters for the procedure in the brackets above.
IS
BEGIN

--------- PLANT 4235 LOGIC -------------

DECLARE
v_filehandle UTL_file.file_type;
v_unload_count_4235 integer := 0;

Type plant_4235_rec is record (
plant_4235_requistion_num varchar2(13),
plant_4235_expense_element varchar2(4),
plant_4235_nomeclature varchar2(28),
plant_4235_orginator varchar2(12),
plant_4235_pcc varchar2(6),
plant_4235_deliver_to_bldg varchar2(10),
plant_4235_jon varchar2(6),
plant_4235_fiscal_year varchar2(2),
plant_4235_total_amount number(9,2));

plant_4235_out_rec plant_4235_rec;

--CURSOR

Cursor UNLOAD_plant_4235 IS
Select substr(art_requisition_number,2,13),
art_expense_element,art_nomenclature,art_originator,
substr(art_pcc,2,5), art_deliver_to_bldg,
ajt_jon, ajt_jon,art_fy,art_total_document,
From v_apr_unload_req
where art_plant_flag = 'Y' and art_fy_type = 'C'
and art_requisition_type = '1';


BEGIN
open UNLOAD_plant_4235;

LOOP
FETCH UNLOAD_plant_4235
INTO plant_4235_out_rec;
EXIT WHEN UNLOAD_plant_4235%NOTFOUND;
IF SQL%FOUND THEN
v_unload_count_4235 :=
v_unload_count_4235 + 1;
v_filehandle :=UTL_file.FOPEN
(p_filedir,p_filename,'w');
END IF;

-- WRITE RECORD

FOR record_plant_4235 in unload_plant_4235 LOOP
UTL_file.PUT_line(v_filehandle,plant_4235_out_rec);
END LOOP;

END LOOP;

close UNLOAD_plant_4235;
UTL_file.FClose(v_filehandle);


EXCEPTION
-- EXPAND EXCEPTION LOGIC !!!
WHEN OTHERS THEN
If SQL%NOTFOUND then
DBMS_OUTPUT.PUT_LINE ('NO PLANT 4235 DATA FOUND');
End if;
END;

 

[SantaMufasa]

BabsJoy,

Your error results from trying to output a RECORD ("plant_4235_out_rec&quot instead of a VARCHAR expression to UTL_file.PUT_line. A quick method to resolve your error is to change your code from

"UTL_file.PUT_line(v_filehandle,plant_4235_out_rec);"

TO

"UTL_file.PUT_line(v_filehandle,
           plant_4235_requistion_num||
           plant_4235_expense_element||
           plant_4235_nomeclature||
           plant_4235_orginator||
           plant_4235_pcc||
           plant_4235_deliver_to_bldg||
           plant_4235_jon||
           plant_4235_fiscal_year||
           plant_4235_total_amount);"

Let me know if this solves your problem.

Dave
Sandy, Utah, USA @ 19:45 gmt, 12:45 Mountain TIme

 

[babsjoy]

Thanks !! For answering so promptly...I was trying that format when I checked my replies. Now for some reason when I compile it does not recongize my fields. GettingLS-00201: identifier 'PLANT_4235_REQUISITION_NUMBER' must be declared.
Below is my FOR LOOP I tried adding
plant_out_Rec.
In front of each field but that did not work. What am I doing wrong ?

FOR plant_4235_out_rec in unload_plant_4235 LOOP
UTL_file.PUT_LINE(v_filehandle,
plant_4235_requisition_number||
plant_4235_expense_element||
plant_4235_nomeclature||
plant_4235_orginator||
plant_4235_pcc||
plant_4235_deliver_to_bldg||
plant_4235_jon||
plant_4235_fiscal_year||
plant_4235_total_amount);
END LOOP;

 

[SantaMufasa]

Babsjoy,

My bad...I forgot to preface each expression with "plant_4235_out_rec.", which should solve the problem.

After reading your code more closely, I see that what you are apparently doing is simply reading, then writing data from a CURSOR. If that is what you are trying to do, then there is much tighter (and easier-to-read) PL/SQL CURSOR code available. I am happy to propose that code to you if you are interested in seeing it.

Let me know,

Dave
Sandy, Utah, USA @ 21:06 GMT, 14:06 Mountain Time

 

[babsjoy]

Yes, I would be very interested. Thanks again for replying so quickly.

 

[SantaMufasa]

Babsjoy,

Here is how I would have coded "tighter" for the same results:

DECLARE
	v_filehandle UTL_file.file_type;
	v_unload_count_4235     integer := 0;
    BEGIN
	v_filehandle :=UTL_file.FOPEN (p_filedir,p_filename,'w');
	for x in (Select
		 	substr(art_requisition_number,2,13) a
			,art_expense_element b
			,art_nomenclature c
			,art_originator d
			,substr(art_pcc,2,5) e
			,art_deliver_to_bldg f
			,ajt_jon g
			,ajt_jon h
			,art_fy	i
			,art_total_document j
				From v_apr_unload_req
			where art_plant_flag = 'Y'
			  and art_fy_type = 'C'
			  and art_requisition_type = '1') Loop
		v_unload_count_4235 := v_unload_count_4235 + 1;
		UTL_file.PUT_line(v_filehandle,x.a||x.b||x.c||x.d||x.e||
					x.f||x.g||x.h||x.i||x.j);
	end loop;
	if v_unload_count_4235 = 0 then
		DBMS_OUTPUT.PUT_LINE ('NO PLANT 4235 DATA FOUND');
	end if;
END;
/

Let me know if you have questions or thoughts about this code. (Without your tables, I couldn't test it, but I believe it's solid code. Please advise whether it worked or not.)

Dave
Sandy, Utah, USA @ 22:33 GMT, 15:33 Mountain Time

 

[babsjoy]

Again thanks for your quick response. It is late here on the east coast so I will have to try your code tomorrow and get back to you. Thanks again...

 

[babsjoy]

After looking at my specs a little closer. I realized that the code could be not a straight select from the view. The first position of the expense_element field has to be built before writing out the record. Your assistance in helping resolve this problem is greatly appreciated.

 

[SantaMufasa]

Babsjoy,

I'm certainly happy to help, and I'm certain we can solve your need, but first, I need more specs here. When you say, "...The first position of the expense_element field has to be built before writing out the record...", what is the exact algorithm for building the first position of the expense_element field (i.e., what are its source components and any data manipulation that must occur)? An actual example with input and result values would be great.

Dave
Sandy, Utah, USA @ 17:27 GMT, 10:27 Mountain Time

 

[babsjoy]

Just want to let you know that the code below is what I came up with. It's a small part of the BIG procedure that is being created. The view uses two tables where one table currently does not contain valid test data (data will have to be entered manually through TOAD). Right now I am coding and trying to obtain a clean compile before I can begin testing. The code has compiled clean. Testing will be another challenge...
Thanks....

CREATE OR REPLACE PROCEDURE APRP100_Requisition_Unload(p_filedir in varchar2,p_filename Varchar2)

--Enter parameters for the procedure in the brackets above.
IS
BEGIN

--------- PLANT 4235 LOGIC -------------

DECLARE
v_filehandle UTL_file.file_type;
v_count_4235 integer := 0;
expense_element_pos1 varchar2(1);
get_expense_element varchar2(4);

-- research to see if %rowtype can be used -- no time


Type plant_4235_rec is record (
plant_4235_requisition_number
varchar2(13),
plant_4235_expense_element varchar2(4),
plant_4235_nomeclature
varchar2(28),
plant_4235_orginator varchar2(12),
plant_4235_pcc varchar2(6),
plant_4235_deliver_to_bldg
varchar2(10),
plant_4235_jon varchar2(6),
plant_4235_fiscal_year varchar2(2),
plant_4235_total_amount number(9,2));

plant_4235_out_rec plant_4235_rec;

--CURSOR

Cursor UNLOAD_plant_4235 IS
Select substr
(art_requisition_number,2,13),
art_expense_element,
art_nomenclature,
art_originator,
substr(art_pcc,2,5),
art_deliver_to_bldg,
ajt_jon,art_fy,
art_total_document
From v_apr_unload_req
where art_plant_flag = 'Y' and
art_fy_type = 'C' and
art_requisition_type = '1';



BEGIN
v_filehandle :=UTL_file.FOPEN
(p_filedir,p_filename,'w');

open UNLOAD_plant_4235;

LOOP
FETCH UNLOAD_plant_4235
INTO plant_4235_out_rec;

EXIT WHEN UNLOAD_plant_4235%NOTFOUND;

IF SQL%FOUND THEN
expense_element_pos1 :=
substr(plant_4235_out_rec.plant_4235_jon,1,1);

IF substr(plant_4235_out_rec.plant_4235_jon,1,1) > 9 THEN
expense_element_pos1 := '5';
END IF;

IF substr(plant_4235_out_rec.plant_4235_jon,1,1) = 'I' THEN
expense_element_pos1 := '8';
END IF;

get_expense_element:=expense_element_pos1||plant_4235_expense_element;


-- WRITE RECORD

FOR plt in unload_plant_4235 LOOP
UTL_file.PUT_LINE(v_filehandle,
plt.plant_4235_requisition_number||
plt.get_expense_element||
plt.plant_4235_nomeclature||
plt.plant_4235_orginator||
plt.plant_4235_pcc||
plt.plant_4235_deliver_to_bldg||
plt.plant_4235_jon||
plt.plant_4235_fiscal_year||
plt.plant_4235_total_amount);
v_count_4235 :=v_count_4235 + 1;
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Plant 4235 records written to output file:');
DBMS_OUTPUT.PUT_LINE(to_char(v_count_4235));
close UNLOAD_plant_4235;
UTL_file.FClose(v_filehandle);


EXCEPTION
-- EXPAND EXCEPTION LOGIC !!!
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE ('No PLANT data found, file created');
END IF;
END;


-- END FOR FIRST BEGIN
END;