I wrote an utl_file package to generate an html file. It is working fine, however, I am having difficulty formating my output.
I like to have values returned one line at a time. For instance,
Name:Sam Ok
area code: 407
phone: 333-4444
picture: display here.
It is displaying values on one line.
Please help if you can. Here is the code.
Thanks,
sam
*****************************************************************
CREATE OR REPLACE PROCEDURE Cr_Movie_PRV_HTML(
P_Filename IN varchar2,
p_Id IN mist_physician.mp_phys_Id%TYPE
)
AS
CURSOR C_mist IS
SELECT mp_name,
mp_update_datetime,
mp_area_code,
mp_phone,
mp_address,
mp_fax
FROM Mist_Physician
WHERE Mp_phys_id = P_Id;
v_mp_name mist_physician.mp_name%type;
v_mp_update_datetime mist_physician.mp_update_datetime%type;
v_mp_areacode mist_physician.mp_area_code%type;
v_phone mist_physician.mp_phone%type;
v_mp_address mist_physician.mp_address%type;
v_mp_fax mist_physician.mp_fax%type;
V_FileHandle UTL_FILE.FILE_TYPE; -- This will be used to store the filename
BEGIN
--Open The specified file to write into and get V_FileHandle.
V_FileHandle := UTL_FILE.FOPEN('c:\mist_info', P_FileName, 'w');
IF Utl_File.IS_OPEN(V_FileHandle) = FALSE THEN
RAISE_APPLICATION_ERROR(-20000,'Error opening '||P_FileName||' for WRITE');
END IF;
Let's build the header file that will be inside the file that will identify why thus file is created.
utl_file.put_line(V_FileHandle,'#---------------------------------------');
utl_file.put_line(V_FileHandle,'Name'||' : '||mist_Rec.mp_name);
utl_file.put_line(V_FileHandle,'Date');
utl_file.put_line(V_FileHandle,'Phone');
utl_file.put_line(V_FileHandle,'Address');
utl_file.put_line(V_FileHandle,'# Generated on: '||to_char(sysdate, 'Mon DD, YYYY (Dy) HH:MI:SS AM'));
utl_file.put_line(V_FileHandle,'#-----------------------------------------');
utl_file.put_line(V_FileHandle,' ');
-- Next we will query data from the Movie table. Only data that are relevant.
-- Using PUT_LINE, write text with the field arguments out to the file
BEGIN
FOR Mist_Rec IN C_Mist LOOP
UTL_FILE.PUT_LINE(V_FileHandle,'Name'||':'||Mist_Rec.Mp_Name||','||'<img src=c:/our_website/co16.jpeg>');
END LOOP;
SELECT mp_name,
mp_update_datetime,
mp_area_code,
mp_phone,
mp_address,
mp_fax
INTO v_mp_name,
v_mp_update_datetime,
v_mp_areacode,
v_phone,
v_mp_address,
v_mp_fax
FROM Mist_Physician
WHERE Mp_phys_id = P_Id;
END;
-- Now Close the file
UTL_FILE.FCLOSE( V_FileHandle);
EXCEPTION
WHEN Utl_File.Invalid_Path THEN
RAISE_APPLICATION_ERROR(-20001,'Error: Invalid Path');
WHEN Utl_File.Invalid_Operation THEN
RAISE_APPLICATION_ERROR(-20002,'Error: Invalid File Operation');
IF Utl_File.IS_OPEN(V_FileHandle) = TRUE THEN
Utl_File.FCLOSE(V_FileHandle);
END IF;
WHEN Utl_File.Invalid_Mode THEN
RAISE_APPLICATION_ERROR(-20003,'Error: Invalid Mode');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || '-'||SQLERRM);
END Cr_Movie_PRV_HTML;
/**********************End Of Mist*********************/