2 Replies Latest reply on Aug 6, 2013 5:35 PM by JagannathRao

    Creating a table via stored proc, getting error

    JagannathRao

      MY querry is as following :

      create or replace
      PROCEDURE New_Account_Activations (
      MYDATE_DD  OUT VARCHAR2,
      MYDATE_MM  OUT VARCHAR2,
      MYDATE_YYYY OUT VARCHAR2,
      MYDATE OUT VARCHAR2,
      VAR1 OUT VARCHAR2,
      VAR2 OUT VARCHAR2,
      VAR3 OUT VARCHAR2,
      TABLE_NAME OUT VARCHAR2 ) IS

      BEGIN
      SELECT TO_CHAR (SYSDATE -2, 'YYYY' ) INTO MYDATE_YYYY FROM DUAL ;
      SELECT TO_CHAR (SYSDATE -2, 'MM' ) INTO MYDATE_MM FROM DUAL ;
      SELECT TO_CHAR (SYSDATE -2, 'DD' ) INTO MYDATE_DD FROM DUAL ;
      MYDATE := MYDATE_YYYY||MYDATE_MM || MYDATE_DD || '000000.000Z'  ;
      --TABLE_NAME:= 'NEWACCOUNTACTI_TEST' ;
      VAR1 := 'B';
      VAR2 := 'A';
      VAR3 := 'Y';
      TABLE_NAME := 'NewAccountActi'|| '_' || MYDATE_YYYY || '_'|| MYDATE_MM || '_'|| MYDATE_DD  ;
      execute immediate 'CREATE TABLE TABLE_NAME AS SELECT * FROM NEWACCOUNTACTI_TEST WHERE 1=0';

      END;

       

       

      ERRORS

      ORA-01031: insufficient privileges

      ORA-06512: at "JACK.NEW_ACCOUNT_ACTIVATIONS", line 21

      ORA-06512: at line 12

      Process exited.