Oracle parameterize finding column count of a table ORA-00905: missing keyword-CodePudding

I am adding a number of columns to multiple tables. Before I add them, I want to make sure they don't exist already.

SELECT count(*)
INTO myCount
FROM user_tab_cols
WHERE column_name = 'newCol'
  and table_name = 'tableName';

Since the only variables are the tableName and columnName, I thought of parameterizing these two variables hoping to reuse it. So I wrote a pl/sql:

DECLARE
 myCount      NUMBER;
 COUNT_COLUMN VARCHAR2(1000) :=
        ' SELECT count(*)
    INTO myCount
    FROM user_tab_cols
    WHERE column_name = :1
      and table_name = :2';


BEGIN

EXECUTE IMMEDIATE COUNT_COLUMN using 'columnName','tableName';
/*code to check count and add column*/

DBMS_OUTPUT.PUT_LINE(myCount);


EXCEPTION
    WHEN
        OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( 'sql failed: ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 200));
END;

I expected the myCount variable to contain number of columns in a table. But I get the following error.

sql failed: -905: ORA-00905: missing keyword

How do I get rid of this error? what other ways do I have to achieve this?

CodePudding user response：

INTO cluase must not be included in SQL query but to be used in EXECUTE IMMEDIATE statement. So, You need to fix your dynamic SQL as below -

DECLARE
 myCount      NUMBER;
 COUNT_COLUMN VARCHAR2(1000) :=
        ' SELECT count(*)
    FROM user_tab_cols
    WHERE column_name = :1
      and table_name = :2';

BEGIN            -- Add the BEGIN keyword to complete a PL/SQL block

EXECUTE IMMEDIATE COUNT_COLUMN INTO myCount using 'columnName','tableName';
/*code to check count and add column*/

DBMS_OUTPUT.PUT_LINE(myCount);


EXCEPTION
    WHEN
        OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( 'sql failed: ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 200));
END;