Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was just testing an anonymous code which I wrote for audit trail It's just a sample for test. However I am getting this error:

[Error] Execution (1: 1): ORA-00984: column not allowed here
ORA-06512: at line 29

Code:

DECLARE
    colName VARCHAR2(2000);
    colNewVal VARCHAR2(2000);
    sqlInsert VARCHAR2(2000);
BEGIN
   colName := 'BOOK_NAME,AUTHOR,PUBLISHER,PUBLISH_YEAR';
   colNewVal := 'BOOK1,AUTH1,PUB1,2001';
   sqlInsert := 'INSERT INTO TBL_TRN_AUDIT_TRAIL_MASTER(';


   FOR cur_var IN (SELECT REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL) col_name FROM DUAL 
        CONNECT BY REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var.col_name || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')VALUES(';
    dbms_output.put_line (sqlInsert);
    FOR cur_var2 IN (SELECT REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL) col_new_val FROM DUAL 
        CONNECT BY REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var2.col_new_val || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')';
    dbms_output.put_line (sqlInsert);
    EXECUTE IMMEDIATE sqlInsert;

END;

Line 29 is the execute immediate command. Am I doing something wrong here??

share|improve this question
1  
Show us the output of dbms_output.put_line (sqlInsert); –  mustaccio Feb 14 at 13:51

1 Answer 1

Actually i sort of got the problem here. In the Insert into statement I was not giving proper quotes for the Values which are VARCHAR2. Got sorted now.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.