Problem with control character in output from PL/SQL code

From: Andrew Cooper <alcooper_at_mail-g.deakin.edu.au>
Date: Thu, 14 Jan 1999 17:32:05 +1000
Message-ID: <369D8F65.1DFEBA7C@mail-g.deakin.edu.au>

Hi All,

I have been trying to extract trigger code from the 'user_triggers' table in an Oracle 7 database, so as we can run them as scripts if need be to re create them. Below is the script which I have written...

• Author: Andrew Cooper
• Date: 08-01-1999
• Purpose: Script to extract the code for Callista Triggers
• into files of their own for entry into Clear Case.
  --
  --
  

set serveroutput on;
set long 50000000
DECLARE
 v_path VARCHAR2(255);

        v_ext           VARCHAR2(3) := 'trg';
        v_type          VARCHAR2(12);
        v_name          VARCHAR2(30);
        v_text          VARCHAR2(2000);
 v_file_name VARCHAR2(255);
        fp_read  UTL_FILE.FILE_TYPE;
 fp_write UTL_FILE.FILE_TYPE;
 fp_append UTL_FILE.FILE_TYPE;

 fp_old_write UTL_FILE.FILE_TYPE;
 fp_buffer VARCHAR2(255);

        CURSOR c_obj IS
                        SELECT  ut.trigger_name,
                                ut.description,
                                ut.trigger_body
                        FROM    user_triggers ut
   where   ut.trigger_name='TRG_UAI_BR_IU'
                        ORDER BY  trigger_name;

BEGIN
 v_path :='/opt/mis/oradb04/CALF/clear_case/outdir';

        FOR v_obj_rec IN c_obj
        LOOP
            DBMS_OUTPUT.ENABLE(1000000);

                v_file_name :=

LOWER(v_obj_rec.trigger_name||'.'||v_ext);

• Open File for writing fp_write := utl_file.fopen('/opt/mis/oradb04/CALF/clear_case/outdir', v_file_name, 'w'); UTL_FILE.PUT(fp_write, 'CREATE OR REPLACE TRIGGER'); UTL_FILE.PUT(fp_write, v_obj_rec.description);
• Close File UTL_FILE.FCLOSE(fp_write);
• Open File for appending the trigger body fp_append := utl_file.fopen('/opt/mis/oradb04/CALF/clear_case/outdir', v_file_name, 'a'); DBMS_OUTPUT.PUT_LINE('TRIGGER_BODY: '|| v_obj_rec.trigger_body); UTL_FILE.PUT(fp_append, v_obj_rec.trigger_body||chr(10)||'/');

  --Close File.
  UTL_FILE.FCLOSE(fp_append);
    END LOOP;
  EXCEPTION
   WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('INVLAID MODE');     DBMS_OUTPUT.PUT_LINE('SQL Code='||TO_CHAR(SQLCODE)); -- Typical usage

           DBMS_OUTPUT.PUT_LINE('SQL Error Msg='||SQLERRM); -- Typical usage

   WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('INVALID_PATH');     DBMS_OUTPUT.PUT_LINE('SQL Code='||TO_CHAR(SQLCODE)); -- Typical usage

           DBMS_OUTPUT.PUT_LINE('SQL Error Msg='||SQLERRM); -- Typical usage

   WHEN UTL_FILE.INVALID_OPERATION THEN     DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');     DBMS_OUTPUT.PUT_LINE('SQL Code='||TO_CHAR(SQLCODE)); -- Typical usage

           DBMS_OUTPUT.PUT_LINE('SQL Error Msg='||SQLERRM); -- Typical usage

   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('OTHERS');
    DBMS_OUTPUT.PUT_LINE('SQL Code='||TO_CHAR(SQLCODE)); -- Typical usage

           DBMS_OUTPUT.PUT_LINE('SQL Error Msg='||SQLERRM); -- Typical usage
END;
/

Unfortunately I am getting a control character at the very bottom of the output as displayed below.

CREATE OR REPLACE TRIGGER
trg_apc_br_iud
BEFORE INSERT OR DELETE OR UPDATE
ON ADMISSION_PROCESS_CAT
FOR EACH ROW
DECLARE
 v_admission_cat admission_process_cat.admission_cat%TYPE;  v_message_num s_message.message_number%TYPE; BEGIN

• Set the Admission Category value. IF DELETING THEN v_admission_cat := :old.admission_cat; ELSE v_admission_cat := :new.admission_cat; END IF;
• Validate the admission category closed indicator. IF admk_val_apc.admp_val_ac_closed ( v_admission_cat, v_message_num) = FALSE THEN raise_application_error(-20000, genp_get_message(v_message_num)); END IF; END; ^@ /

The '^@' only appears in Unix..

Has anyone ever seen this before, and if so, how do I get rid of the control character.

I can be contacted on Email: alcooper_at_deakin.edu.au Received on Thu Jan 14 1999 - 01:32:05 CST