Cara... Bom, vou me ater ao erro e não à qualidade do código, blza?
Se precisar de um help pra dar uma melhorada no código, estamos por aqui.
Aparentemente o erro está nessa linha:
a_rec(v_idx).SRC_CD_OPRT_ORGN := r.SRC_CD_OPRT_ORGN;
Você faz uma referência a seu cursor "r", porém, não existe essa coluna no
seu cursor, simplesmente pq você não definiu o nome da coluna no select
(que será o nome do campo de seu cursor).
Nessa parte aqui:
CASE
WHEN DIR_TRAFEGO = 'Outgoing' or large_account is not null
then '63102' -- Angola
WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO = 'Forwarding'
then
nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
Else '-1'
END, --SRC_CD_OPRT_ORGN
Substitua por
CASE
WHEN DIR_TRAFEGO = 'Outgoing' or large_account is not null
then '63102' -- Angola
WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO = 'Forwarding'
then
nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
Else '-1'
END as SRC_CD_OPRT_ORGN, --SRC_CD_OPRT_ORGN
Isso vale para os outros erros mostrados.
Evandro Giachetto
Oracle DBA
evandrogiache...@gmail.com
2015-03-05 14:35 GMT-03:00 lmarinh...@yahoo.com.br [oracle_br] <
oracle_br@yahoogrupos.com.br>:
>
>
> Olá Galera,
>
> Estou obtendo um erro bobo aqui que não estou conseguindo ver. Esta dando
> erro que eu devo declarar algumas variáveis e as mesmas esta declaradas. O
> Código abaixo
>
>
> DECLARE
>
> t0 number := dbms_utility.get_time;
>
> v_idx number :=1;
>
> type t_rec is record(
>
> SRC_DT_CALL NUMBER,
>
> SRC_TM_CALL VARCHAR2(30),
>
> SRC_CD_BNSS_UNIT VARCHAR2(30),
>
> SRC_CD_CALL_AREA VARCHAR2(30),
>
> SRC_CD_NTWK_ELMT VARCHAR2(30),
>
> SRC_CD_OPRT_ORGN VARCHAR2(30),
>
> SRC_CD_OPRT_DSTN VARCHAR2(30),
>
> SRC_CD_PDSV VARCHAR2(30),
>
> SRC_CD_SBSC VARCHAR2(30),
>
> SRC_CD_PRCN_PLAN VARCHAR2(30),
>
> SRC_CD_TRF_WAY VARCHAR2(30),
>
> SRC_CD_TRF_TYPE VARCHAR2(30),
>
> SRC_CD_TRF_SUB_TYPE VARCHAR2 (30),
>
> SRC_CD_TRF_NTWK VARCHAR2 (30),
>
> SRC_CD_TRF_RMNG_TYPE VARCHAR2 (30),
>
> SRC_CD_TRF_PERD_TYPE VARCHAR2 (30),
>
> SRC_CD_TRF_CHRG_CALL VARCHAR2 (30),
>
> SRC_CD_TRF_LINE_TYPE VARCHAR2 (30),
>
> SRC_CD_TRF_TRMN_PONT VARCHAR2 (30),
>
> SRC_CD_TRF_DROP_CALL VARCHAR2 (30),
>
> SRC_CD_TRF_HOME_ZONE VARCHAR2 (30),
>
> SRC_CD_TRF_TCNL VARCHAR2 (30),
>
> SRC_CD_FIN_BLNC_TYPE VARCHAR2 (30),
>
> SRC_CD_PAY_TYPE VARCHAR2 (30),
>
> SRC_CD_EQPM_MODL VARCHAR2 (30),
>
> SRC_NR_ORGN VARCHAR2 (30),
>
> SRC_NR_DSTN VARCHAR2 (30),
>
> SRC_QT_DRTN_TRFC NUMBER (22),
>
> SRC_QT_CHRG_DRTN_TRFC NUMBER (22),
>
> SRC_QT_UPLD_TRFC NUMBER (22),
>
> SRC_QT_DWLD_TRFC NUMBER (22),
>
> SRC_VL_TRFC NUMBER (22),
>
> SRC_VL_BLNC_USED NUMBER (22),
>
> X_VL_TRFC_UTT NUMBER (22),
>
> X_VL_TRFC_USD NUMBER (22),
>
> V_TIMESTAMP date,
>
> X_CD_GLOB_CALL_REF VARCHAR2 (30),
>
> X_FL_RMNG NUMBER (22),
>
> SRC_CD_TRF_CHRG_TYPE number(22),
>
> X_CD_MCC_ROAM number(22),
>
> X_CD_MNC_ROAM number(22),
>
> X_QT_DRTN_TRFC_FREE NUMBER (22),
>
> X_CD_FAF NUMBER (22),
>
> X_DS_SRC_SYST VARCHAR2 (30),
>
> X_VL_PRVS_BLNC NUMBER (22),
>
> X_VL_BLNC NUMBER (22),
>
> X_QT_TOTL_DATA_TRFC NUMBER (22),
>
> X_CD_SRCE_FILE NUMBER (22),
>
> NSEQ_TRF NUMBER(22),
>
> X_CD_SERV_CLSS NUMBER (22));
>
> type t_rec_array is table of t_rec index by pls_integer;
>
> a_rec t_rec_array;
>
> BEGIN
>
> for r in(SELECT SUBSTR(A.DATA_EVENTO,1,8)AS
> SRC_DT_CALL,SUBSTR(A.DATA_EVENTO,9,6)AS SRC_TM_CALL,-1 AS
> SRC_CD_BNSS_UNIT,-1 AS SRC_CD_CALL_AREA,nvl(ID_CELULA,-1) AS
> SRC_CD_NTWK_ELMT,
>
> CASE
>
> WHEN DIR_TRAFEGO = 'Outgoing' or large_account is not null
> then '63102' -- Angola
>
> WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO = 'Forwarding'
> then
>
>
> nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
>
> Else '-1'
>
> END, --SRC_CD_OPRT_ORGN
>
> CASE
>
> WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO =
> 'Forwarding' or large_account is not null then
>
> '63102' -- Angola
>
> WHEN DIR_TRAFEGO = 'Outgoing' then
>
>
> nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
>
> Else '-1'
>
> END , --SRC_CD_OPRT_DSTN
>
> nvl(A.Service_identifier,-1) AS SRC_CD_PDSV,
> --NVL(SERVICE_IDENTIFIER,-1)
>
> CASE
>
> WHEN A.DIR_TRAFEGO = 'Outgoing' then
> substr(A.MSISDN_ORIGEM,4,9)
>
> WHEN A.DIR_TRAFEGO = 'Incoming' then
> decode(A.SUB_TIPO_TRAFEGO,'4','-2',substr(A.MSISDN_DESTINO,4,9))
>
> WHEN A.DIR_TRAFEGO = 'Forwarding' then
> substr(A.MSISDN_ORIGEM,4,9)
>
> Else '-1'
>
> END AS SRC_CD_SBSC,
>
> nvl(A.servicos,'-1') AS SRC_CD_PRCN_PLAN,
>
> nvl(A.DIR_TRAFEGO,-1) AS SRC_CD_TRF_WAY,
>
> case
>
> when service_identifier = 'Gy_10' then '1'
>
> when SUB_TIPO_TRAFEGO in ('5') then '5'
>
> when service_identifier = 'SCAP_6' then '5'
>
> when SUB_TIPO_TRAFEGO in ('0','1') then '2'
>
> when SUB_TIPO_TRAFEGO in ('2','3') then '3'
>
> when SUB_TIPO_TRAFEGO in ('4') then '1'
>
> when SUB_TIPO_TRAFEGO in ('6','7','8') then '4'
>
> else '-1'
>
> end as SRC_CD_TRF_TYPE ,---SRC_CD_TRF_TYPE
>
> case
>
> when service_identifier = 'Gy_10' then '4'
>
> when service_identifier = 'SCAP_6' then '5'
>
> else nvl(SUB_TIPO_TRAFEGO,-1)
>
> end as SRC_CD_TRF_SUB_TYPE, -----SRC_CD_TRF_SUB_TYPE
>
>
> nvl( A.ID_TRAFEGO,-1) AS SRC_CD_TRF_NTWK,
>
> -1 AS SRC_CD_TRF_RMNG_TYPE,
>
> nvl(A.ID_PERIODO,3) AS SRC_CD_TRF_PERD_TYPE,
>
> -1 AS SRC_CD_TRF_CHRG_CALL,
>
> -1 AS SRC_CD_TRF_LINE_TYPE,
>
> nvl(E.ID_TRF_TRMN_PONT,-1) AS SRC_CD_TRF_TRMN_PONT,
>
>
> -1 AS SRC_CD_TRF_DROP_CALL,
>
> -1 AS SRC_CD_TRF_HOME_ZONE,
>
> -1 AS SRC_CD_TRF_TCNL,
>
> case --caso um dos dois seja null -> -1
>
> when B.TIPO_SALDO_USADO is null or B.SERVICE_CLASS is null
> then '-1'
>
> when substr(B.TIPO_SALDO_USADO,1,2)='AC' then
> B.TIPO_SALDO_USADO
>
> else B.TIPO_SALDO_USADO||'_'||B.SERVICE_CLASS
>
> end AS SRC_CD_FIN_BLNC_TYPE,
>
> NVL(D.SRC_CD_PAY_TYPE,-1) AS SRC_CD_PAY_TYPE,
> --NVL(B.SERVICE_CLASS,-1),
>
> -1 AS SRC_CD_EQPM_MODL,
>
> nvl(A.MSISDN_ORIGEM,-1) AS SRC_NR_ORGN,
>
> NVL(A.MSISDN_DESTINO,-1) AS SRC_NR_DSTN,
>
> NVL(A.DURACAO_REAL,0) AS SRC_QT_DRTN_TRFC,
>
> nvl(A.DURACAO_TAXADA,0) AS SRC_QT_CHRG_DRTN_TRFC,
>
> 0 AS SRC_QT_UPLD_TRFC,
>
> nvl(A.QNT_TRAFEGO_TOTAL,0) AS SRC_QT_DWLD_TRFC,
>
>
> nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0)*7.2,5),0) AS
> SRC_VL_TRFC,
>
> nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO),6),0) AS SRC_VL_BLNC_USED,
>
>
> nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0),5),0) AS
> X_VL_TRFC_UTT, -- mail Rui Alves
>
>
> nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0)*.06666667,5),0)
> AS X_VL_TRFC_USD, -- mail Rui Alves
>
> to_date('20150218','YYYYMMDD') , --TIMESTAMP
>
> NVL(A.GCR,-1) AS X_CD_GLOB_CALL_REF,
>
> case -- identificação de sms em roaming
>
> when A.SUB_TIPO_TRAFEGO ='2' and
>
> A.servicos in ('12', '13', '14', '15', '22', '51',
> '61', '62', '63', '64', '65', '117', '118', '119', '120', '121', '122',
> '123', '124', '125', '126', '131', '132' , '159')
>
> then '1'
>
> when SUB_TIPO_TRAFEGO ='2' then '0'
>
> else NVL(A.ROAMING,0)
>
> end, --X_FL_RMNG,
>
> NVL(A.TIPO_TAXACAO,-1) AS SRC_CD_TRF_CHRG_TYPE,
>
> nvl(A.pais_orig,-1), -- X_CD_MCC_ROAM --JIRA207,
>
> nvl(A.operador,-1), -- X_CD_MNC_ROAM --JIRA207,
>
> nvl(A.DURACAO_TAXADA_ZERO,0) AS X_QT_DRTN_TRFC_FREE,
>
> nvl(A.FAF,-1) AS X_CD_FAF,
>
> nvl(A.FONTE_ID,-1) AS X_DS_SRC_SYST,
>
> nvl(TO_NUMBER(B.VALOR_SALDO_ANTERIOR),0) X_VL_PRVS_BLNC,
>
> nvl(TO_NUMBER(B.VALOR_SALDO_ACTUAL),0) X_VL_BLNC,
>
> nvl(QNT_TRAFEGO_TOTAL,0) X_QT_TOTL_DATA_TRFC,
>
> A.NFILE_ID AS X_CD_SRCE_FILE
>
> ,A.NSEQ_TRF --JIRA164
>
> ,NVL (TO_NUMBER(B.SERVICE_CLASS),-1) --JIRA192 X_CD_SERV_CLSS
>
> FROM (select
> decode(DIR_TRAFEGO,'Incoming',MSISDN_ORIGEM,MSISDN_DESTINO)
> NR_TRMN_PONT,const.*
>
> from DW_ETL.TMP_STG_MME_TRAFEGO_TAX_CONST const
>
> where
>
> ( sub_tipo_trafego in ('0','1','2','3','4')
>
> or
>
> service_identifier in ('Gy_10')
>
> )
>
> and
>
> (service_identifier not in ('SCAP_6') or
> service_identifier is null)
>
> ) A,
>
> DW_ETL.TMP_STG_MME_TRAFEGO_TAX_VAR B,
>
> DW_ETL.STG_MME_BLNC_TYPE C,
>
> (select SRC_CD_PAY_TYPE from DW_STAGE.src_sbsc
> partition(day_20150218) group by SRC_CD_PAY_TYPE ) D,
>
> DW_ETL.tmp_trf_trmn_pont E,
>
> (select cd_srce_sytm as large_account from
> dw.ref_trf_trmn_pont where ds_trmn_detl = 'Móvel (Intra-rede)') F
>
> WHERE A.ROW_ID_STG=B.ROW_ID_STG
>
> AND B.SERVICE_CLASS=D.SRC_CD_PAY_TYPE (+)
>
> AND B.TIPO_SALDO_USADO=C.X_DS_ACR_FIN_BLNC_TYPE (+)
>
> AND B.SERVICE_CLASS=C.X_ID_SRVC_CLASS (+)
>
> AND A.NR_TRMN_PONT=E.MSISDN_DESTINO(+)
>
> --AND A.MSISDN_DESTINO=E.MSISDN_DESTINO(+)
>
> AND E.ID_TRF_TRMN_PONT=F.large_account(+))
>
> loop
>
> a_rec(v_idx).SRC_DT_CALL := r.SRC_DT_CALL;
>
> a_rec(v_idx).SRC_TM_CALL := r.SRC_TM_CALL;
>
> a_rec(v_idx).SRC_CD_BNSS_UNIT := r.SRC_CD_BNSS_UNIT;
>
> a_rec(v_idx).SRC_CD_CALL_AREA := r.SRC_CD_CALL_AREA;
>
> a_rec(v_idx).SRC_CD_NTWK_ELMT := r.SRC_CD_NTWK_ELMT;
>
> a_rec(v_idx).SRC_CD_OPRT_ORGN := r.SRC_CD_OPRT_ORGN;
>
> a_rec(v_idx).SRC_CD_OPRT_DSTN := r.SRC_CD_OPRT_DSTN;
>
> a_rec(v_idx).SRC_CD_PDSV := r.SRC_CD_PDSV;
>
> a_rec(v_idx).SRC_CD_SBSC := r.SRC_CD_SBSC;
>
> a_rec(v_idx).SRC_CD_PRCN_PLAN := r.SRC_CD_PRCN_PLAN;
>
> a_rec(v_idx).SRC_CD_TRF_WAY := r.SRC_CD_TRF_WAY;
>
> a_rec(v_idx).SRC_CD_TRF_TYPE := r.SRC_CD_TRF_TYPE;
>
> a_rec(v_idx).SRC_CD_TRF_SUB_TYPE := r.SRC_CD_TRF_SUB_TYPE;
>
> a_rec(v_idx).SRC_CD_TRF_NTWK := r.SRC_CD_TRF_NTWK;
>
> a_rec(v_idx).SRC_CD_TRF_RMNG_TYPE := r.SRC_CD_TRF_RMNG_TYPE;
>
> a_rec(v_idx).SRC_CD_TRF_CHRG_CALL := r.SRC_CD_TRF_CHRG_CALL;
>
> a_rec(v_idx).SRC_CD_TRF_LINE_TYPE := r.SRC_CD_TRF_LINE_TYPE;
>
> a_rec(v_idx).SRC_CD_TRF_TRMN_PONT := r.SRC_CD_TRF_TRMN_PONT;
>
> a_rec(v_idx).SRC_CD_TRF_DROP_CALL := r.SRC_CD_TRF_DROP_CALL;
>
>
> a_rec(v_idx).SRC_CD_TRF_HOME_ZONE := r.SRC_CD_TRF_HOME_ZONE;
>
> a_rec(v_idx).SRC_CD_TRF_TCNL := r.SRC_CD_TRF_TCNL;
>
> a_rec(v_idx).SRC_CD_FIN_BLNC_TYPE := r.SRC_CD_FIN_BLNC_TYPE;
>
>
> a_rec(v_idx).SRC_CD_PAY_TYPE := r.SRC_CD_PAY_TYPE;
>
> a_rec(v_idx).SRC_CD_EQPM_MODL := r.SRC_CD_EQPM_MODL;
>
> a_rec(v_idx).SRC_NR_ORGN := r.SRC_NR_ORGN;
>
> a_rec(v_idx).SRC_NR_DSTN := r.SRC_NR_DSTN;
>
> a_rec(v_idx).SRC_QT_DRTN_TRFC := r.SRC_QT_DRTN_TRFC;
>
> a_rec(v_idx).SRC_QT_CHRG_DRTN_TRFC := r.SRC_QT_CHRG_DRTN_TRFC;
>
>
> a_rec(v_idx).SRC_QT_UPLD_TRFC := r.SRC_QT_UPLD_TRFC;
>
> a_rec(v_idx).SRC_QT_DWLD_TRFC := r.SRC_QT_DWLD_TRFC;
>
> a_rec(v_idx).SRC_VL_TRFC := r.SRC_VL_TRFC;
>
> a_rec(v_idx).SRC_VL_BLNC_USED := r.SRC_VL_BLNC_USED;
>
> a_rec(v_idx).X_VL_TRFC_UTT := r.X_VL_TRFC_UTT;
>
> a_rec(v_idx).X_VL_TRFC_USD := r.X_VL_TRFC_USD;
>
> a_rec(v_idx).V_TIMESTAMP := r.V_TIMESTAMP;
>
> a_rec(v_idx).X_CD_GLOB_CALL_REF := r.X_CD_GLOB_CALL_REF;
>
>
> a_rec(v_idx).X_FL_RMNG := r.X_FL_RMNG;
>
> a_rec(v_idx).SRC_CD_TRF_CHRG_TYPE := r.SRC_CD_TRF_CHRG_TYPE;
>
>
> a_rec(v_idx).X_CD_MCC_ROAM := r.X_CD_MCC_ROAM;
>
> a_rec(v_idx).X_CD_MNC_ROAM := r.X_CD_MNC_ROAM;
>
> a_rec(v_idx).X_QT_DRTN_TRFC_FREE := r.X_QT_DRTN_TRFC_FREE;
>
>
> a_rec(v_idx).X_CD_FAF := r.X_CD_FAF;
>
> a_rec(v_idx).X_DS_SRC_SYST := r.X_DS_SRC_SYST;
>
> a_rec(v_idx).X_VL_PRVS_BLNC := r.X_VL_PRVS_BLNC;
>
> a_rec(v_idx).X_VL_BLNC := r.X_VL_BLNC;
>
> a_rec(v_idx).X_QT_TOTL_DATA_TRFC := r.X_QT_TOTL_DATA_TRFC;
>
>
> a_rec(v_idx).X_CD_SRCE_FILE := r.X_CD_SRCE_FILE;
>
> a_rec(v_idx).NSEQ_TRF := r.NSEQ_TRF;
>
> a_rec(v_idx).X_CD_SERV_CLSS := r.X_CD_SERV_CLSS;
>
>
>
> v_idx := v_idx + 1;
>
> if (mod(v_idx, 100) = 0) then
>
> forall i in a_rec.first .. a_rec.last
>
> insert into T_LUIZ_SRC_TRF_BLNC_TYPE_DETL values a_rec(i);
>
> a_rec.delete;
>
> v_idx := 1;
>
> end if;
>
> end loop;
>
> if a_rec.first is not null then
>
> forall i in a_rec.first .. a_rec.last
>
> insert into T_LUIZ_SRC_TRF_BLNC_TYPE_DETL values a_rec(i);
>
> end if;
>
> commit;
>
> dbms_output.put_line('Tempo: ' || ((dbms_utility.get_time - t0) /
> 100) || ' segundos');
>
> end;
>
>
> O ERRO APRESENTADO:
>
>
> ORA-06550: line 172, column 44:
>
> PLS-00302: component 'SRC_CD_OPRT_ORGN' must be declared
>
> ORA-06550: line 172, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 173, column 44:
>
> PLS-00302: component 'SRC_CD_OPRT_DSTN' must be declared
>
> ORA-06550: line 173, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 201, column 39:
>
> PLS-00302: component 'V_TIMESTAMP' must be declared
>
> ORA-06550: line 201, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 203, column 37:
>
> PLS-00302: component 'X_FL_RMNG' must be declared
>
> ORA-06550: line 203, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 205, column 41:
>
> PLS-00302: component 'X_CD_MCC_ROAM' must be declared
>
> ORA-06550: line 205, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 206, column 41:
>
> PLS-00302: component 'X_CD_MNC_ROAM' must be declared
>
> ORA-06550: line 206, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 215, column 42:
>
> PLS-00302: component 'X_CD_SERV_CLSS' must be declared
>
> ORA-06550: line 215, column 9:
>
> PL/SQL: Statement ignored
>
> ORA-06550: line 220,
>
>
> Agradeço se alguém puder me ajudar.
>
> Obrigado
>
> Luiz Marinho
>
>
>
>
