Error in Getting newly inserted record through trigger-PLSQL

Question

I am trying to create a trigger on EMP table which will audit insertions in EMP_AUDIT table and send mail to respective empno. The thing I am not getting is to get newly inserted record in CSV attachment that is being generated in v_block_query. I want to get that data basis of :new.FIELD_NAME but it's throwing the error:

ORA-01008: not all variables bound ORA-06512: at "SCOTT.EMP_AFTER_INSERT", line 18 ORA-04088: error during execution of trigger 'SCOTT.EMP_AFTER_INSERT'

 create or replace TRIGGER emp_after_insert
                              AFTER INSERT
                                   ON emp
                                   FOR EACH ROW
                                DECLARE
        v_block_qry VARCHAR2(3000);
                          v_block_row VARCHAR2(6000);
                        TYPE bl_cur IS REF CURSOR;
                       v_result bl_cur;
                       v_rowcount NUMBER;
                   v_attachment CLOB;
           v_username varchar2(10);
            v_no number(4);
                v_name varchar2(10);
    v_email varchar2(30);
    v_subj varchar2(30);
             BEGIN
          insert into emp_audit(id,name,insertion_date) values(:new.empno,:new.ename,sysdate);
            dbms_output.put_line('inserted'||:new.empno);
               v_attachment := '"COL1", "COL2"' || CHR(13) || CHR(10);
                      v_block_qry := 'SELECT ''"'' || new.empno || ''", "'' || new.ename || ''", "'' || insertion_date ||''"'' AS ROWTXT FROM emp_audit ';
                      OPEN v_result FOR v_block_qry;
                        LOOP
                        v_rowcount := v_result%ROWCOUNT;
                        FETCH v_result INTO v_block_row;
                          EXIT WHEN v_result%NOTFOUND;
                          v_attachment := v_attachment || v_block_row || chr(13) || chr(10);
                        END LOOP;
                      CLOSE v_result;

                       dbms_output.put_line(v_attachment);
     select email into v_email from dept_email where deptno=:new.empno;
     dbms_output.put_line(v_email);
        v_subj:= 'MAIL_SUBJECT ' || TO_CHAR(TRUNC(SYSDATE-1), 'YYYY-MM-DD');
              UTL_MAIL.send_attach_varchar2(sender => '[email protected]',
              recipients        => v_email,
              subject           => 'mail testing',
              message           =>  'current record',
             mime_type         => 'text/html; charset=us-ascii', -- send html e-mail
             attachment        => v_attachment,
             att_inline        => FALSE,
             att_filename      => 'Change_Report' || TO_CHAR(TRUNC(SYSDATE-1), 'YYYY-MM-DD') || '.csv');

                 END;

Please Help. Thanks in advance

Answer 1

v_block_qry := 'SELECT ''"'' || BRANCH_NUMB || ''", "'' || LOAN_CODE || ''", "'' || LOAN_PRODUCT_NUMB || ''" , "'' || BRM_NUM || "'', "'' || CURR_CODE || "'', "'' || NAME_DESN || ''", "'' || CNIC_NUMB || ''" , "'' || PRINCIPAL_NEW || ''", "'' || AMOUNT_APPLIED_NEW || ''", "'' || ACCT_BASC || ''", "'' || ACCT_SFIX || ''" , "'' || DATE_ADDED_NEW || ''", "'' || DATE_DUE_NEW || ''", "'' || B_STATUS || ''", "'' || S_STATUS || ''", "'' || DISB_TOTAL_AMOUNT_NEW || ''"'' , "'' || OUTSTANDING_PRINCIPAL_NEW || ''", "'' || GROUPBR_NEW || ''", "'' || INSERTED_DATE || ''"''

' AS ROWTXT

           FROM SMFB.LMFLOANSNEW where branch_numb = :new.branch_code;

Answer 2

v_block_qry := 'SELECT ''"'' || BRANCH_NUMB || ''", "'' || LOAN_CODE || ''", "'' || LOAN_PRODUCT_NUMB || ''"
               , "'' || BRM_NUM || "'', "'' || CURR_CODE || "'', "'' || NAME_DESN || ''", "'' || CNIC_NUMB || ''"
               , "'' || PRINCIPAL_NEW || ''", "'' || AMOUNT_APPLIED_NEW || ''", "'' || ACCT_BASC || ''", "'' || ACCT_SFIX || ''"
               , "'' || DATE_ADDED_NEW || ''", "'' || DATE_DUE_NEW || ''", "'' || B_STATUS || ''", "'' || S_STATUS || ''", "'' || DISB_TOTAL_AMOUNT_NEW || ''"''
               , "'' || OUTSTANDING_PRINCIPAL_NEW || ''", "'' || GROUPBR_NEW || ''", "'' || INSERTED_DATE || ''"''


               AS ROWTXT FROM SMFB.LMFLOANSNEW where branch_numb='||:new.branch_code;

from keyword not found where expected. This is at live environment. Previous code was on my DB

Answer 3

v_block_qry := 'SELECT ''"'' || new.empno || ''", "'' || new.ename || ''", "'' || insertion_date ||''"'' AS ROWTXT FROM emp_audit ';
                  OPEN v_result FOR v_block_qry;

You cannot use new.empno and new.ename in your query

Replace

(SELECT ''"'' || new.empno || ''", "'' || new.ename || ''", "'' || insertion_date ||''"'' )

in your query with

(SELECT ''"'' || empno || ''", "'' || ename || ''", "'' || insertion_date ||''"'' )