Creating file for inserted records in the database

Question

I have a PLSQL application, in so some part of the application I need to store the data in the database table (AUDIT_LOG) from external table (EXT_TAB_AUDIT_LOG) when entry in the external table matches some regexp. The issue is that I want to simultaneous create a file which contains only the entries from the external table inserted into the database.

the procedure that inserts into the database from external table is as shown below.

--------------------------------------------------------
--  DDL for Procedure PR_INSERT_AUDIT_LOG_FROM_FILE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "ABC"."PR_INSERT_AUDIT_LOG_FROM_FILE" (P_IMPORT_TIMESTAMP IN TIMESTAMP)
AS 
BEGIN

   INSERT INTO AUDIT_LOG 
      ( ID                   
      , PRIMARY_NAME   
      , PRIMARY_VALUE  
      , TERMINAL_DATE
      , LAND_ID           
      )
   SELECT ID                   
        , TRIM(PRIMARY_NAME)
        , TRIM(PRIMARY_VALUE)
        , TERMINAL_DATE        
        , LAND_ID                        
        , P_IMPORT_TIMESTAMP
   FROM EXT_TAB_AUDIT_LOG AL
   LEFT OUTER JOIN VERFIY_NAME VWZ ON VWZ.VERFIY_TABLE_ID = TRIM(AL.VERFIY_TABLE_ID)
   WHERE REGEXP_LIKE (ID,'\d{2}-\d{2}-\d{2}')
   AND   PRIMARY_NAME IS NOT NULL
   AND   PRIMARY_VALUE IS NOT NULL
   AND   TERMINAL_DATE IS NOT NULL
   AND   LAND_ID IS NOT NULL
 


   COMMIT;

EXCEPTION
   WHEN OTHERS
   THEN
      DECLARE
         V_PROCEDURE_NAME  VARCHAR2(30)  := $$PLSQL_UNIT;
         V_SUBJECT         VARCHAR2(255) := 'Error on DB procedure '||V_PROCEDURE_NAME||' on '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss');
         V_BODY            VARCHAR2(10000) := 'Hi all,'||chr(10)||chr(10)||'Procedure '||V_PROCEDURE_NAME||' returned the following error:'||chr(10)||SQLERRM;
      BEGIN
         SEND_MAIL ( 'GENERIC_DB_ERROR', V_SUBJECT,V_BODY);
         RAISE;
      END;  
END PR_INSERT_AUDIT_LOG_FROM_FILE;

/

How can I create a file that stores just the records saved in the database table matching the regexp with the records from the external table, Sample code will be helpful.

score 0 · Answer 1 · answered May 14 '21 at 12:38

0

You can write to files with utl_file.

answered May 14 '21 at 12:38

EdStevens

3,708
2
10
18

score 0 · Answer 2 · answered May 14 '21 at 12:48

Use UTL_FILE package to create file and write data.

Example:

DECLARE
-- declare file pointer variable
fp UTL_FILE.FILE_TYPE;
-- declare a cursor to fetch data from table
CURSOR c1 IS SELECT * FROM emp;
BEGIN
    -- open file
    fp := UTL_FILE.FOPEN('ABC', 'file4.txt', 'w');
    -- loop to fetch data
    FOR i IN c1
    LOOP
        -- write data into file
        UTL_FILE.PUT_LINE(fp, i.ename||' '||i.sal||' '||i.hiredate);
    END LOOP;
    -- close file
    UTL_FILE.FCLOSE(fp);
END;
/

When I create as per the above suggested answer I get PLS-00302: component 'ename' must be declared, as a compile time error. How can I solve this? — Syed Iftekharuddin, May 24 '21 at 17:53

Creating file for inserted records in the database

2 Answers2