ORA-00936: missing expression - Line 62

Question

I've been trying to identify what's wrong with the Insert Statement in the Execute Immediate for few hours without luck. Made sure that I am not missing any commas or entering any incorrect character.

I have gone through all the answers on SO and other websites trying to figure out what could I be doing wrong but no luck.

Running this function results in the following error (error line starts with "-->" Please ignore it as its just for highlighting purpose):

ORA-00936: missing expression
ORA-06512: at "BDW_AMPS.COUNT_RECORDS", line 62

and here's the PL/SQL Code for the function:

CREATE OR REPLACE FUNCTION count_records (
    p_test_case_id   IN NUMBER,
    p_table_name     IN VARCHAR2
) RETURN VARCHAR2 IS

    v_amt_recs            INT;
    v_test_result         VARCHAR2(10);
    v_threshold_val       VARCHAR2(10);
    v_test_suite_table    VARCHAR2(100);
    v_test_result_id      NUMBER;
    v_batch_id            NUMBER;
    v_report_id           NUMBER;
    v_test_seq_no         NUMBER;
    v_session_name        VARCHAR2(100);
    v_error_description   VARCHAR2(100);
    v_process_by          VARCHAR2(100);
BEGIN
    v_test_suite_table := 'bdw_amps.spares_bdw_test_suite';
    v_process_by := 'INFORMATICA';
    EXECUTE IMMEDIATE 'SELECT THRESHHOLD_VALUE FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
    INTO v_threshold_val;
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name
    INTO v_amt_recs;

    EXECUTE IMMEDIATE 'SELECT BDW_AMPS.SPARES_TEST_SEQ_ID_SEQ.NEXTVAL FROM DUAL'
    INTO v_test_result_id;

    EXECUTE IMMEDIATE 'SELECT MAX(BATCH_ID) FROM BDW_AMPS.spares_bdw_session_audit 
     WHERE SESSION_NAME=(SELECT SESSION_NAME FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
                      || ')'
    INTO v_batch_id;

    EXECUTE IMMEDIATE 'SELECT REPORT_ID FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
    INTO v_report_id;

    EXECUTE IMMEDIATE 'SELECT TEST_SEQ FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
    INTO v_test_seq_no;

    EXECUTE IMMEDIATE 'SELECT SESSION_NAME FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
    INTO v_session_name;

    IF
        v_amt_recs > v_threshold_val
    THEN
        v_test_result := 'PASS';

    --> EXECUTE IMMEDIATE 'INSERT INTO BDW_AMPS.spares_bdw_test_results(
        TEST_RESULT_ID, 
        BATCH_ID, 
        REPORT_ID, 
        TEST_CASE_ID, 
        TEST_SEQ_NO, 
        TABLE_NAME, 
        SESSION_NAME, 
        TEST_RESULT, 
        PROCESS_DATE, 
        PROCESS_BY
        )
        VALUES 
        ('|| v_test_result_id || ',
        ' || v_batch_id || ',
        ' || v_report_id || ',
        ' || p_test_case_id || ',
        ' || v_test_seq_no || ',
        ' || p_table_name || ',
        ' || v_session_name || ',
        ' || v_test_result || ',
        SYSDATE,
        ' || v_process_by || '
        )';

    EXECUTE IMMEDIATE 'commit';

    ELSE
        v_test_result := 'FAIL';
        v_error_description := 'Count: ' || v_amt_recs || ' is greater than threshold value: ' || v_threshold_val;

    EXECUTE IMMEDIATE 'INSERT INTO BDW_AMPS.spares_bdw_test_results(
    TEST_RESULT_ID, 
    BATCH_ID, 
    REPORT_ID, 
    TEST_CASE_ID, 
    TEST_SEQ_NO, 
    TABLE_NAME, 
    SESSION_NAME, 
    TEST_RESULT,
    ERROR_DESCRIPTION,
    PROCESS_DATE, 
    PROCESS_BY
    )
    VALUES (
    '|| v_test_result_id || ',
    ' || v_batch_id || ',
    ' || v_report_id || ',
    ' || p_test_case_id || ',
    ' || v_test_seq_no || ',
    ' || p_table_name  || ',
    ' || v_session_name || ',
    ' || v_test_result || ',
    ' || v_error_description || ',
    SYSDATE,
    ' || v_process_by  || '
    )';
    EXECUTE IMMEDIATE 'commit';

    END IF;

    RETURN v_test_result;
END;

Answer 1

Assuming that this is a simplified example of something that really does need to be dynamic, one issue is that the string values are not quoted. (If you'd had date values they would need special handling too.)

For example:

create table demo (numcol number, stringcol varchar2(20));

declare
    l_num number := 123;
    l_string varchar2(20) := 'Kittens';
    l_sql long := 'insert into demo(numcol, stringcol) values ('||l_num||', '||l_string||')';
begin
    dbms_output.put_line(l_sql);
    execute immediate l_sql;
end;
/

Generated code:

insert into demo(numcol, stringcol) values (123, Kittens)

Fails with:

ORA-00984: column not allowed here

The error will vary depending on the contents of the string. For example, if it contains spaces:

declare
    l_num number := 123;
    l_string varchar2(20) := 'Kittens are cute';
    l_sql long := 'insert into demo(numcol, stringcol) values ('||l_num||', '||l_string||')';
begin
    dbms_output.put_line(l_sql);
    execute immediate l_sql;
end;
/

Generated code:

insert into demo(numcol, stringcol) values (123, Kittens are cute)

ORA-00917: missing comma

or commas:

declare
    l_num number := 123;
    l_string varchar2(20) := 'Kittens, Puppies';
    l_sql long := 'insert into demo(numcol, stringcol) values ('||l_num||', '||l_string||')';
begin
    dbms_output.put_line(l_sql);
    execute immediate l_sql;
end;
/

insert into demo(numcol, stringcol) values (123, Kittens, Puppies)

ORA-00913: too many values

You need to build the quoting:

declare
    l_num number := 123;
    l_string varchar2(20) := 'Kittens, Puppies';
    l_sql long := 'insert into demo(numcol, stringcol) values ('||l_num||', '''||l_string||''')';
begin
    dbms_output.put_line(l_sql);
    execute immediate l_sql;
end;
/

so that you generate

insert into demo(numcol, stringcol) values (123, 'Kittens, Puppies')

(If the string could contain quote characters, that would need more work.)

It's worth always building the dynamic SQL as a variable and printing or logging it on failure, as it's usually pretty clear what the issue is when you can see the code.

Another point is that concatenating values like this is resource-intensive, as Oracle tries to cache SQL statements for reuse, so they will be individually parsed and optimised and take space in the cache, but they will never be reused. If this is going to be frequently run with different values, you should consider using bind variables via the using clause of execute immediate.

Answer 2

Your DML(INSERT) statements do not need EXECUTE IMMEDIATE statements. So, remove them after line 61 :

CREATE OR REPLACE FUNCTION count_records (
    p_test_case_id   IN NUMBER,
    p_table_name     IN VARCHAR2
) RETURN VARCHAR2 IS

    v_amt_recs            INT;
    v_test_result         VARCHAR2(10);
    v_threshold_val       VARCHAR2(10);
    v_test_suite_table    VARCHAR2(100);
    v_test_result_id      NUMBER;
    v_batch_id            NUMBER;
    v_report_id           NUMBER;
    v_test_seq_no         NUMBER;
    v_session_name        VARCHAR2(100);
    v_error_description   VARCHAR2(100);
    v_process_by          VARCHAR2(100);
BEGIN
    v_test_suite_table := 'bdw_amps.spares_bdw_test_suite';
    v_process_by := 'INFORMATICA';

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name
    INTO v_amt_recs;

    v_test_result_id := BDW_AMPS.SPARES_TEST_SEQ_ID_SEQ.NEXTVAL;

    EXECUTE IMMEDIATE 'SELECT MAX(BATCH_ID) FROM BDW_AMPS.spares_bdw_session_audit 
     WHERE SESSION_NAME=(SELECT SESSION_NAME FROM '
                  || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = '
                      || p_test_case_id
                      || ')'
    INTO v_batch_id;

    EXECUTE IMMEDIATE 'SELECT THRESHHOLD_VALUE, REPORT_ID, TEST_SEQ,SESSION_NAME FROM '
                      || v_test_suite_table
                      || ' WHERE TEST_CASE_ID = :caseId'
    INTO v_threshold_val,v_report_id,v_test_seq_no,v_session_name
    USING p_test_case_id;

    IF
        v_amt_recs > v_threshold_val
    THEN
        v_test_result := 'PASS';

    INSERT INTO BDW_AMPS.spares_bdw_test_results(
        TEST_RESULT_ID, 
        BATCH_ID, 
        REPORT_ID, 
        TEST_CASE_ID, 
        TEST_SEQ_NO, 
        TABLE_NAME, 
        SESSION_NAME, 
        TEST_RESULT, 
        PROCESS_DATE, 
        PROCESS_BY
        )
        VALUES 
        ( v_test_result_id ,
          v_batch_id ,
          v_report_id ,
          p_test_case_id ,
          v_test_seq_no ,
          p_table_name ,
          v_session_name ,
          v_test_result ,
          SYSDATE,
         v_process_by 
        );

    commit;

    ELSE
        v_test_result := 'FAIL';
        v_error_description := 'Count: ' || v_amt_recs || ' is greater than threshold value: ' || v_threshold_val;

    INSERT INTO BDW_AMPS.spares_bdw_test_results(
    TEST_RESULT_ID, 
    BATCH_ID, 
    REPORT_ID, 
    TEST_CASE_ID, 
    TEST_SEQ_NO, 
    TABLE_NAME, 
    SESSION_NAME, 
    TEST_RESULT,
    ERROR_DESCRIPTION,
    PROCESS_DATE, 
    PROCESS_BY
    )
    VALUES (
    v_test_result_id ,
     v_batch_id ,
     v_report_id ,
     p_test_case_id ,
     v_test_seq_no ,
     p_table_name  ,
     v_session_name ,
     v_test_result ,
     v_error_description ,
    SYSDATE,
     v_process_by  
    );
    commit;

    END IF;

    RETURN v_test_result;
END;

while usage of them are right for SELECT statements because of dynamic table names.