Hello Experts,
I don't understand why the following execute immidiate does not work. Do you any idea?
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Updated:
EXECUTE IMMEDIATE 'ALTER TABLE DRMPOS.AVTR_T_FULL_GENERATION ADD PARTITION C_'||current_camp||' VALUES ('||current_camp||')';
CREATE OR REPLACE PROCEDURE DRMPOS.AVTR_P_FULL_GENERATION IS
current_camp AVTIMEPERIOD.AVCAMPAIGNID%TYPE;
drop_v varchar2(12);
partition_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(partition_not_exist, -2149);
BEGIN
SELECT DRMPOS.avtr_f_get_camp('201', AVCAMPAIGNID, 0, 4) INTO drop_v FROM AVTIMEPERIOD A WHERE AVTPDATE = TRUNC(SYSDATE);
execute immediate 'ALTER TABLE DRMPOS.AVTR_T_FULL_GENERATION DROP PARTITION C_'||drop_v;
EXCEPTION
WHEN partition_not_exist THEN
BEGIN
SELECT AVCAMPAIGNID INTO current_camp FROM AVTIMEPERIOD WHERE SUBSTR(NLS_LOWER(dataareaid),1,7) = '201' AND AVTPDATE = TRUNC(SYSDATE);
EXECUTE IMMEDIATE 'ALTER TABLE DRMPOS.AVTR_T_FULL_GENERATION ADD PARTITION C_'||current_camp||' VALUES ('||current_camp||')';
EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO DRMPOS.AVTR_T_FULL_GENERATION
(avcampaignid,to_memberid,leaderid,memberid,level_,recid,leader_recid,avgroupcode,STATE,COUNTY,AVCITYID,leader_avgroupcode,l_state,l_county,l_avcityid)
with
main_q AS (
SELECT CONNECT_BY_ROOT avl.leaderid to_memberid, avl.leaderid leaderid, avl.memberid memberid, LEVEL level_, CUST.RECID,
CUST.AVGROUPCODE AVGROUPCODE, PRIOR CUST.AVGROUPCODE LEADER_AVGROUPCODE,
CASE WHEN PRIOR CUST.RECID IS NULL THEN (SELECT RECID FROM CUSTTABLE C_TEMP WHERE substr(nls_lower(C_TEMP.DATAAREAID),1,7) = ''201'' and
substr(nls_lower(C_TEMP.ACCOUNTNUM),1,25) = AVL.LEADERID) ELSE PRIOR CUST.RECID END leader_recid
FROM avlmembers avl JOIN custtable cust
ON SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = SUBSTR(NLS_LOWER(CUST.DATAAREAID),1,7)
AND SUBSTR(NLS_LOWER(AVL.MEMBERID),1,25) = SUBSTR(NLS_LOWER(CUST.ACCOUNTNUM),1,25)
WHERE SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = ''201''
--AND CONNECT_BY_ROOT avl.leaderid != ''0'' AND leaderid != ''0''
AND (
(CONNECT_BY_ISLEAF = 0 AND SUBSTR (NLS_LOWER (AVL.MEMBERSTATUS), 1, 21) IN (''new'',''active'')
AND SUBSTR (NLS_LOWER (AVL.AVPAIDROLEID), 1, 21) != ''0_rep'' AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN (''a'', ''i'', ''p''))
OR (CONNECT_BY_ISLEAF = 1 AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN (''a'', ''i'', ''p'', ''r'')))
CONNECT BY NOCYCLE PRIOR AVL.MEMBERID = AVL.LEADERID),
addr AS (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID FROM (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID, row_number() OVER(PARTITION BY ADDR.ADDRRECID ORDER BY ADDR.TYPE) sıra FROM address addr
WHERE SUBSTR(NLS_LOWER(ADDR.DATAAREAID),1,7) = ''201'' AND ((ADDR.TYPE = 2 AND ADDR.ADDRESS != CHR(2)) OR (ADDR.TYPE = 17))
) WHERE sıra = 1)
SELECT :current_camp avcampaignid,
main_q.to_memberid, main_q.leaderid, main_q.memberid, level_, main_q.recid, main_q.leader_recid, main_q.AVGROUPCODE, addr.STATE,
addr.COUNTY, addr.AVCITYID, main_q.LEADER_AVGROUPCODE, addr2.STATE l_state, addr2.COUNTY l_county, addr2.AVCITYID l_avcityid
FROM main_q JOIN addr ON main_q.recid = addr.addrrecid
LEFT JOIN addr addr2 ON main_q.leader_recid = addr2.addrrecid' USING current_camp;
END;
END AVTR_P_FULL_GENERATION;
/
ORA-00900: geçersiz SQL deyimi
ORA-06512: konum "DRMPOS.AVTR_P_FULL_GENERATION", satır 16
ORA-02149: Belirtilen bölüm mevcut değil
Message was edited by: NightWing