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

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?

CodePudding user response：

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