Stack Exchange
ORA-00984: column not allowed here - Dynamic SQL
This is my package code.
CREATE OR REPLACE PACKAGE BODY FMSSMART.GENERIC_PURGER
AS

   PROCEDURE GET_PARTITIONID_JULIAN (i_date                   IN     DATE,
                              i_number_of_partitions   IN     NUMBER,
                              o_partitionID               OUT NUMBER)
   IS
   BEGIN
      SELECT MOD (TO_NUMBER (TO_CHAR (TO_DATE (i_date, 'YYYYMMDD'), 'j')),
                  i_number_of_partitions)
        INTO o_partitionID
        FROM DUAL;

      DBMS_OUTPUT.put_line (o_partitionID);
   END GET_PARTITIONID_JULIAN;

 PROCEDURE DELETE_TBL_SML
    (
      i_tablename IN VARCHAR2,
      o_retcode OUT NUMBER,
      o_errormsg OUT VARCHAR2
    )
     IS

    stmt VARCHAR2(1000);
    o_start_time DATE;
    o_end_time DATE;

    BEGIN
      select to_char(sysdate, 'YYYYMMDDHH24MISS') into o_start_time from dual;
      stmt := 'DELETE FROM ' ||i_tablename;
      EXECUTE IMMEDIATE stmt;
      COMMIT;
      select to_char(sysdate, 'YYYYMMDDHH24MISS') into o_end_time from dual;

    EXCEPTION WHEN OTHERS THEN
    o_retcode := SQLCODE;
    o_errormsg := substr(SQLERRM, 1, 200);


    INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name, procedure_name, start_time, end_time) VALUES (o_retcode, o_errormsg, 'GENERIC_PURGER','DELETE_TBL_SML', o_start_time, o_end_time);
    return;
    END DELETE_TBL_SML;

 PROCEDURE INSERT_AUDIT_PROC_TBL
 (
      i_retcode IN NUMBER,
      i_errormsg IN VARCHAR2,
      i_package_name IN  VARCHAR2,
      i_procedure_name IN VARCHAR2,
      i_start_time IN DATE,
      i_end_time IN DATE
 )
   IS
    BEGIN
          EXECUTE IMMEDIATE  'INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name, procedure_name, start_time, end_time) VALUES (i_retcode, i_errormsg, i_package_name,  i_procedure_name,i_start_time,  i_end_time)';
          COMMIT;
          RETURN;
   END INSERT_AUDIT_PROC_TBL;

END GENERIC_PURGER;
/

On Execution:
set autocommit off;
set serveroutput on size 1000000;
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
EXECUTE INSERT_AUDIT_PROC_TBL(-1, 'NA', 'GENERIC_PURGER','DELETE_TBL_SML', '20150212164527', '20150212164527');

I encountered an error which gives me:
Session altered.

BEGIN INSERT_AUDIT_PROC_TBL(-1, 'NA', 'GENERIC_PURGER','DELETE_TBL_SML', '20150212164527', '20150212164527'); END;

*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "FMSSMART.INSERT_AUDIT_PROC_TBL", line 12
ORA-06512: at line 1


Why are you using dynamic SQL for the audit insert?

Im using dynamic SQL for the audit insert, because it is not only be limited for encoutered errors.

The error you're getting is not from how you're calling the procedure, but what the procedure is doing. The ORA-00984 error is reported against line 12 of the FMSSMART.INSERT_AUDIT_PROC_TBL procedure, which is:
EXECUTE IMMEDIATE  'INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name, procedure_name, start_time, end_time) VALUES (i_retcode, i_errormsg, i_package_name,  i_procedure_name,i_start_time,  i_end_time)';

You're using dynamic SQL here when you don't need to; there's nothing dynamic and static SQL would be fine:
INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name,
  procedure_name, start_time, end_time)
VALUES (i_retcode, i_errormsg, i_package_name,  
  i_procedure_name,i_start_time,  i_end_time);

For future reference though, when you do use dynamic SQL you need to use bind variables for the values that are passed in; you have a bind placeholder in the dynamic SQL statement which is indicated by a colon, e.g. :var1, and then you supply the actual values with the using clause. At the moment in your original version i_retcode is being interpreted as a column name, not as your variable, which is out of scope to the dynamic context. So you would use something like:
EXECUTE IMMEDIATE  'INSERT INTO AUDIT_PROC_TBL (error_number, '
  || 'error_message, package_name, procedure_name, start_time, end_time) '
  || 'VALUES (:retcode, :errormsg, :package_name,  :procedure_name, '
  || ':start_time,  :end_time)'
USING i_retcode, i_errormsg, i_package_name,  i_procedure_name,
  i_start_time,  i_end_time;

I've split the statement onto multiple lines for readability; the concatenation via || means the final string is the same as if it was all on one line.

I have a couple of other observations beyond the scope of the question:

You're setting your NLS_DATE_FORMAT in the session and then relying on implicit conversion; using a format that doesn't match your string anyway oddly. It would be better to explicitly pass a date value in your call, e.g. to_date('20150212164527', 'YYYYMMDDHH24MISS') or with a timestamp literal.
It's even worse inside your DELETE_TBL_SML package as you're still relying on the session NLS setting, which you won't always control, and you're explicitly converting dates to strings only to implicitly convert them back. Instead of select to_char(sysdate, 'YYYYMMDDHH24MISS') into o_start_time from dual; just to o_start_time := sysdate).
committing or rolling back in a procedure is usually considered bad practice; it's better for the caller to decide if and when to commit, as it may be doing other things your procedure isn't aware of that should be treated as part of the same transaction.
catching exceptions you don't handle, especially when others, is usually a bug. Although you're returning the code and message to the caller here, they then have to look for it. It's almost always better to let the exception propagate back up to the caller - which can feed into a commit/rollback decision, too.

The delete procedure could be simplified to:
PROCEDURE DELETE_TBL_SML(i_tablename IN VARCHAR2) IS
  o_start_time DATE;
  o_end_time DATE;
BEGIN
  o_start_time := sysdate;
  EXECUTE IMMEDIATE 'DELETE FROM ' ||i_tablename;
  o_end_time := sysdate;

  INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name,
    procedure_name, start_time, end_time)
  VALUES (SQLERRCODE, substr(SQLERRM, 1, 200), 'GENERIC_PURGER',
    'DELETE_TBL_SML', o_start_time, o_end_time);
END DELETE_TBL_SML;

unless you only want to audit errors, in which case you would need to enclose the execute in its own sub-block; and then call it as exec FMSSMART.GENERIC_PURGER.DELETE_TBL_SML(<your table name>).

Hi Alex, thanks a lot, you solved my problem. Im also a beginner of this pl/sql, now, i can execute this sql and it was successful.
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
EXECUTE INSERT_AUDIT_PROC_TBL(-1, 'NA', 'GENERIC_PURGER','DELETE_TBL_SML', '20150212174829', '20150212174829');

Hi Alex, could you fix to me my code in DELETE_TBL_SML, Im having a trouble with this package. Thanks

@LelanCalusay - you should ask a new question for a new problem. I can see a couple of things that might not be behaving as you want, but you need to show how you're calling it and what output/error you get, and that deserves its own question.

ok, I applied your suggestions,
it gives me a good result without using NLS_DATE_FORMAT..
set autocommit off;
EXECUTE INSERT_AUDIT_PROC_TBL(-1, 'NA', 'GENERIC_PURGER','DELETE_TBL_SML', to_date('20150212183731', 'YYYYMMDDHH24MISS'), to_date('20150212183732', 'YYYYMMDDHH24MISS'));

It seems, I have to wait for 2 days to ask another question.
Maybe I should paste my code here

Could you fix the PROCEDURE DELETE_TBL_SML for me, Im having a hard time for pl/sql, im not used to do this, I started a couple of week for using procedures and packages.

I guess that's because of the downvote but not sure what the rules are. I've added a simplified version based on what I'd already said, but without knowing what is going wrong, I can't 'fix' it. I'd guess you're calling your current version without declaring variables for the out parameters, but it's better not to have those anyway in this case really. Anything else is going to have to wait until you can ask a new question, sorry.

I guess I got troubled in DELETE_TBL_SML, here is my code:
set autocommit off;
VAR CODE NUMBER;
VAR ERRMSG VARCHAR2;
EXEC DELETE_TBL_SML('FMSSUN.SUBS_TOPUP PARTITION (SUBS_TOPUP_P1)',:CODE,:ERRMSG);
PRINT CODE;
PRINT ERRMSG;
and I got the result
SQL> BEGIN DELETE_TBL_SML('FMSSUN.SUBS_TOPUP PARTITION (SUBS_TOPUP_P1)',:CODE,:ERRMSG); END;
   *

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FMSSMART.DELETE_TBL_SML", line 22
ORA-01861: literal does not match format string
ORA-06512: at line 1

@LelanCalusay - make it VAR ERRMSG VARCHAR2(200). And fix how you're setting o_start_time and o_end_time, as I already said.

how could I set o_start_time and o_end_time? this is my code:
stmt VARCHAR2(1000);
o_start_time DATE;
o_end_time DATE;
BEGIN
  select to_char(sysdate, 'YYYYMMDDHH24MISS') into o_start_time from dual;
  stmt := 'DELETE FROM ' ||i_tablename;
  EXECUTE IMMEDIATE stmt;
  COMMIT;
  select to_char(sysdate, 'YYYYMMDDHH24MISS') into o_end_time from dual;


@LelanCalusay - I already said in my answer, just do o_start_time := sysdate;. Don't convert to and from a string, especially with implicit conversion that relies on the NLS settings.

Heres what i have done:
stmt VARCHAR2(1000);
o_start_time DATE :=sysdate;
o_end_time DATE :=sysdate;
BEGIN

  stmt := 'DELETE FROM ' ||i_tablename;
  EXECUTE IMMEDIATE stmt;
  COMMIT;

but i found an error: ORA-01031: insufficient privileges

@LelanCalusay - that error speaks for itself - the package owner FMSSMART doesn't have the privileges needed to delete from the table you're passing, which in your example was owned by FMSSUN. (Or has privs but only granted through a role).

hi alex, how to grant this privlege?
grant delete on fmssun.subs_topup to fmssmart
