following is the sample proc

CREATE OR REPLACE MXADMIN.PROCEDURE SP_UPDATE_QTY (

  TABLENAME   NVARCHAR2,

  ID    IN NUMBER)

IS

  SQL_STMT  VARCHAR2 (1000);

BEGIN

  SQL_STMT :=

      'UPDATE MXADMIN.SAMPLE_TEST SET PROCESSED_RECORDS = ( SELECT COUNT(*) FROM MXADMIN.'|| TABLE_NAME ||' WHERE ID = ' || ID || ')' || ', MODIFIED_DATE = SYSDATE'|| ' WHERE ID = ' || ID || ';';



  DBMS_OUTPUT.PUT_LINE(SQL_STMT);

  EXECUTE IMMEDIATE SQL_STMT;

  COMMIT;

END;

Trying to execute as

EXEC MXADMIN.PROCEDURE SP_UPDATE_QTY ('TEMP_RECORDS',100);

following are the printed output:

UPDATE MXADMIN.SAMPLE_TEST SET PROCESSED_RECORDS = (SELECT COUNT (*)

       FROM MXADMIN.TEMP_RECORDS WHERE ID = 100) WHERE ID =100; 

I am getting error as

Error at line 1

ORA-00933: SQL command not properly ended

ORA-06512: at "MXADMIN.PROCEDURE SP_UPDATE_QTY", line 11

ORA-06512: at line 1

if i copy sql in printed output and ran it, it works fine. when i run the proc by supplying values it throws an error. please help me on this dynamic sql

CodePudding user response：

Remove the final semi-colon in dynamic SQL statement, here:

... ' WHERE ID = ' || ID || ';';
                         ------
                         here

because it raises the

ORA-00933: SQL command not properly ended

error.

CodePudding user response：

You have multiple errors including:

• CREATE OR REPLACE MXADMIN.PROCEDURE SP_UPDATE_QTY should be CREATE OR REPLACE PROCEDURE MXADMIN.SP_UPDATE_QTY
• The argument is TABLENAME but in the dynamic query TABLE_NAME is used.
• You do not want ; at the end of the dynamic SQL string.
• You should be using bind variables for the id values and never use string concatenation if you do not have to.
• You should also assert that the dynamic table name is valid.

Like this:

CREATE OR REPLACE PROCEDURE MXADMIN.SP_UPDATE_QTY (
  TABLENAME IN NVARCHAR2,
  ID        IN NUMBER
)
IS
  SQL_STMT VARCHAR2(1000);
BEGIN
  SQL_STMT := 'UPDATE MXADMIN.SAMPLE_TEST
               SET PROCESSED_RECORDS = (SELECT COUNT(*)
                                        FROM MXADMIN.'|| DBMS_ASSERT.QUALIFIED_SQL_NAME(TABLENAME) ||'
                                        WHERE  ID = :1),
                   MODIFIED_DATE = SYSDATE
                   WHERE ID = :2';
  DBMS_OUTPUT.PUT_LINE(SQL_STMT);
  EXECUTE IMMEDIATE SQL_STMT USING id, id;
  COMMIT;
END;
/

db<>fiddle here