Why do I get "ORA-00933: SQL command not properly ended" error?

November 30, 2021

I created a function and it uses a dynamic sql:

create function check_ref_value
(
    table_name varchar2,
    code_value number,
    code_name  varchar2
) return number is
    l_query varchar2(32000 char);
    l_res   number;
begin
    l_query := '
        select sign(count(1))
        into :l_res
        from '|| table_name ||'
        where '|| code_name ||' = :code_value
    ';

    execute immediate l_query
    using in code_value, out l_res;

    return l_res;
end;

But when I try to use it I get an exception "ORA-00933: SQL command not properly ended"
What is wrong with this code?

>Solution :

You can use EXECUTE IMMEDIATE ... INTO ... USING ... to get the return value and DBMS_ASSERT to raise errors in the case of SQL injection attempts:

create function check_ref_value
(
    table_name varchar2,
    code_value number,
    code_name  varchar2
) return number is
    l_query varchar2(32000 char);
    l_res   number;
begin
    l_query := 'select sign(count(1))'
            || ' from  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)
            || ' where ' || DBMS_ASSERT.SIMPLE_SQL_NAME(code_name)
            || ' = :code_value';
    execute immediate l_query INTO l_res USING code_value;

    return l_res;
end;
/

Which, for the sample data:

CREATE TABLE abc (a, b, c) AS
SELECT 1, 42, 3.14159 FROM DUAL;

Then:

SELECT CHECK_REF_VALUE('abc', 42, 'b') AS chk FROM DUAL;

Outputs:

CHK

1

CHK
1

And:

SELECT CHECK_REF_VALUE('abc', 42, '1 = 1 OR b') AS chk FROM DUAL;

Raises the exception:

ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 160
ORA-06512: at "FIDDLE_UVOFONEFDEHGDQJELQJL.CHECK_REF_VALUE", line 10

As for your question:

What is wrong with this code?

Using SELECT ... INTO is only valid in an SQL statement in a PL/SQL block and when you run the statement via EXECUTE IMMEDIATE it is executed in the SQL scope and not a PL/SQL scope.

You can fix it by wrapping your dynamic code in a BEGIN .. END PL/SQL anonymous block (and reversing the order of the bind parameters in the USING clause):

create function check_ref_value
(
    table_name varchar2,
    code_value number,
    code_name  varchar2
) return number is
    l_query varchar2(32000 char);
    l_res   number;
begin
    l_query := '
      BEGIN
        select sign(count(1))
        into :l_res
        from '|| DBMS_ASSERT.SIMPLE_SQL_NAME(table_name) ||'
        where '|| DBMS_ASSERT.SIMPLE_SQL_NAME(code_name) ||' = :code_value;
      END;
    ';

    execute immediate l_query
    using out l_res, in code_value;

    return l_res;
end;
/

(However, that is a bit more of a complicated solution that just using EXECUTE IMMEDIATE ... INTO ... USING ....)

db<>fiddle here