Unable to execute a package in oracle
I have a package given below:
create or replace
PACKAGE       PKG_CONV_PRODUCT
AS
PROCEDURE SP_LOAD_DATA;
PROCEDURE SP_CREATE_BACKUP_TABLES (
P_LOADING_ID IN CONV_LOAD_STATS.LOADING_ID%TYPE);
END PKG_CONV_PRODUCT;
And package body as below:
create or replace
PACKAGE BODY PKG_CONV_PRODUCT
AS
GV_LOADING_ID PLS_INTEGER;
TYPE TYP_TABLE_NAME
IS
TABLE OF USER_TABLES.TABLE_NAME%TYPE;
TAB_TABLE_NAME TYP_TABLE_NAME := TYP_TABLE_NAME   ( 'PRODUCTS',
                                                'PRODUCT_GROUP_CODES',
                                                'PRODUCT_SUB_GROUPS',
                                                'PRODUCT_GROUP_CODES_LICENSES',
                                                'PRODUCTS_OBJECTIVES',
                                                'PRODUCTS_HISTORY',
                                                'PRODUCTS_BREAK_POINTS',
                                                'PRODUCTS_LICENSES',
                                                'PRODUCTS_CONCESSION_PERCENT',
                                                'PRODUCTS_BD_STATUS')
PROCEDURE SP_LOAD_DATA
IS
BEGIN
GV_LOADING_ID := SEQ_CONV_PRODUCT.NEXTVAL;
SP_CREATE_BACKUP_TABLES (GV_LOADING_ID);
END SP_LOAD_DATA;
PROCEDURE SP_CREATE_BACKUP_TABLES(
P_LOADING_ID IN CONV_LOAD_STATS.LOADING_ID%TYPE)
IS
L_TABLE_NAME VARCHAR2(50);
BEGIN
FOR INDX IN TAB_TABLE_NAME.FIRST .. TAB_TABLE_NAME.LAST
LOOP
L_TABLE_NAME                       := TAB_TABLE_NAME (INDX);
IF(L_TABLE_NAME = 'PRODUCT_GROUP_CODES_LICENSES')  THEN
    L_TABLE_NAME :='PROD_GRP_CODES_LICENSES';
    ELSIF(L_TABLE_NAME = 'PRODUCTS_CONCESSION_PERCENT')  THEN
    L_TABLE_NAME :='PROD_CONCESSION_PERCENT';
    ELSIF(L_TABLE_NAME = 'SPONSORS_ADVISORY_APPROVAL')  THEN
    L_TABLE_NAME :='SPON_ADVISORY_APPROVAL';
 END IF;
      EXECUTE IMMEDIATE
        'CREATE TABLE BONUS.' ||
        SUBSTR ('BKP' ||
                P_LOADING_ID ||
                L_TABLE_NAME ,
                1,
                30)||
        ' TABLESPACE APP_TABLE AS SELECT * FROM BONUS.' ||
        L_TABLE_NAME ;
      EXECUTE IMMEDIATE
        'GRANT SELECT ON BONUS.' ||
        SUBSTR ('BKP' ||
                P_LOADING_ID ||
                L_TABLE_NAME ,
                1,
                30)||
      ' TO ROLE_BONUS_SELECT_SR01';
  END LOOP;
  END SP_CREATE_BACKUP_TABLES;
  END PKG_CONV_PRODUCT;
and i have getting following error:
ORA-00955: name is already used by an existing object ORA-06512: at
"BONUS.PKG_CONV_PRODUCT", line 58 ORA-06512: at "BONUS.PKG_CONV_PRODUCT",
line 40 ORA-06512: at line 2
can you please help me on this.
 
No comments:
Post a Comment