can someone help me identify why I can not generate the file. The following errors are covered:
ORA-00979 - not a group by expression
ORA-06512 - at "Finempdbs_hmg_prc_emprestimo_gf" line 21
ORA-06512 - at line 3
ORA-00979 - not a group by expression
ORA-06512 - at "Finempdbs_hmg_prc_emprestimo_gf" line 21
ORA-06512 - at line 3
TO_CHAR (t.pgt_dat, 'yyyymmdd')
TO_DATE (t.pgt_dat, 'DD-MM-YYYY')
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PRC_EMPRESTIMO_GF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
DECLARE
v_con_num_est NUMBER (14, 4);
v_data NUMBER (14, 4);
v_principal_pago NUMBER (14, 4);
v_juros_pago NUMBER (14, 4);
v_mora_paga NUMBER (14, 4);
v_iof_pago NUMBER (14, 4);
v_multa_paga NUMBER (14, 4);
BEGIN
prc_emprestimo_gf (prc_con_num_est => v_con_num_est,
prc_data => v_data,
prc_principal_pago => v_principal_pago,
prc_juros_pago => v_juros_pago,
prc_mora_paga => v_mora_paga,
prc_iof_pago => v_iof_pago,
prc_multa_paga => v_multa_paga
);
END;
CREATE OR REPLACE PROCEDURE prc_emprestimo_gf (
prc_con_num_est OUT NUMBER,
prc_data out NUMBER,
prc_principal_pago OUT NUMBER,
prc_juros_pago OUT NUMBER,
prc_mora_paga OUT NUMBER,
prc_iof_pago OUT NUMBER,
prc_multa_paga OUT NUMBER
)
IS
v_con_num_est NUMBER (14, 4);
v_data NUMBER (8);
v_principal_pago NUMBER (14, 4);
v_juros_pago NUMBER (14, 4);
v_mora_paga NUMBER (14, 4);
v_iof_pago NUMBER (14, 4);
v_multa_paga NUMBER (14, 4);
v_linha VARCHAR2 (2000) := '';
v_arquivo UTL_FILE.file_type;
BEGIN
SELECT c.con_num_est,
TO_CHAR (t.pgt_dat, 'dd/mm/yyyy'),
SUM (t.ppcvalpriantpgt - t.ppcvalpripospgt),
SUM (t.ppcvaljurantpgt - t.ppcvaljurpospgt),
SUM (t.ppcvalmorantpgt - t.ppcvalmorpospgt),
SUM (t.ppcvaliofantpgt - t.ppcvaliofpospgt),
SUM (t.ppcvalmulantpgt - t.ppcvalmulpospgt)
INTO v_con_num_est,
v_data,
v_principal_pago,
v_juros_pago,
v_mora_paga,
v_iof_pago,
v_multa_paga
from emptblppc t, tb_con c, tb_pgt p
WHERE t.con_num = c.con_num
AND t.ppcdthalt = c.con_dat_alt
AND t.con_num = p.con_num
AND t.ppcdthalt = p.pgt_dat_alt
AND t.pgt_dat = p.pgt_dat
AND t.ppcdthalt = '01 jan 1960'
AND TO_CHAR (p.DATA, 'DD-MM-YYYY') = '23-12-2007'
GROUP BY c.con_num_est, TO_DATE (t.pgt_dat, 'dd/mm/yyyy');
v_arquivo := UTL_FILE.fopen ('/backup/CPMF', 'EMPRESTIMO_GF.CSV', 'W');
UTL_FILE.put_line (v_arquivo, 'v_con_num_est : '||NVL (v_con_num_est, 0));
UTL_FILE.put_line (v_arquivo, 'v_data : '||NVL (v_data, 0));
UTL_FILE.put_line (v_arquivo, 'v_principal_pago : '||NVL (v_principal_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_juros_pago : '||NVL (v_juros_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_mora_paga : '||NVL (v_mora_paga, 0));
UTL_FILE.put_line (v_arquivo, 'v_iof_pago : '||NVL (v_iof_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_multa_paga : '||NVL (v_multa_paga, 0));
UTL_FILE.fclose (v_arquivo);
END;
ERRO na linha 1:
ORA-06550: line 1, column 7:
PLS-00905: object FINEMPDBS.PRC_EMPRESTIMO_GF is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
CREATE OR REPLACE PROCEDURE prc_emprestimo_gf (
prc_con_num_est OUT NUMBER,
prc_data out NUMBER,
prc_principal_pago OUT NUMBER,
prc_juros_pago OUT NUMBER,
prc_mora_paga OUT NUMBER,
prc_iof_pago OUT NUMBER,
prc_multa_paga OUT NUMBER
)
IS
v_con_num_est NUMBER (14, 4);
v_data NUMBER (8);
v_principal_pago NUMBER (14, 4);
v_juros_pago NUMBER (14, 4);
v_mora_paga NUMBER (14, 4);
v_iof_pago NUMBER (14, 4);
v_multa_paga NUMBER (14, 4);
v_linha VARCHAR2 (2000) := '';
v_arquivo UTL_FILE.file_type;
BEGIN
SELECT c.con_num_est,
TO_CHAR (t.pgt_dat, 'dd/mm/yyyy'),
SUM (t.ppcvalpriantpgt - t.ppcvalpripospgt),
SUM (t.ppcvaljurantpgt - t.ppcvaljurpospgt),
SUM (t.ppcvalmorantpgt - t.ppcvalmorpospgt),
SUM (t.ppcvaliofantpgt - t.ppcvaliofpospgt),
SUM (t.ppcvalmulantpgt - t.ppcvalmulpospgt)
INTO v_con_num_est,
v_data,
v_principal_pago,
v_juros_pago,
v_mora_paga,
v_iof_pago,
v_multa_paga
from emptblppc t, tb_con c, tb_pgt p
WHERE t.con_num = c.con_num
AND t.ppcdthalt = c.con_dat_alt
AND t.con_num = p.con_num
AND t.ppcdthalt = p.pgt_dat_alt
AND t.pgt_dat = p.pgt_dat
AND t.ppcdthalt = '01 jan 1960'
AND TO_CHAR (p.DATA, 'DD-MM-YYYY') = '23-12-2007'
GROUP BY c.con_num_est, TO_DATE (t.pgt_dat, 'dd/mm/yyyy');
v_arquivo := UTL_FILE.fopen ('/backup/CPMF', 'EMPRESTIMO_GF.CSV', 'W');
UTL_FILE.put_line (v_arquivo, 'v_con_num_est : '||NVL (v_con_num_est, 0));
UTL_FILE.put_line (v_arquivo, 'v_data : '||NVL (v_data, 0));
UTL_FILE.put_line (v_arquivo, 'v_principal_pago : '||NVL (v_principal_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_juros_pago : '||NVL (v_juros_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_mora_paga : '||NVL (v_mora_paga, 0));
UTL_FILE.put_line (v_arquivo, 'v_iof_pago : '||NVL (v_iof_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_multa_paga : '||NVL (v_multa_paga, 0));
UTL_FILE.fclose (v_arquivo);
END;
/
What will be FINEMPDBS ?mariogus wrote: Error that appears in SQLPlus:ERRO na linha 1: ORA-06550: line 1, column 7: PLS-00905: object FINEMPDBS.PRC_EMPRESTIMO_GF is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored
*
ERRO na linha 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PRC_SALDO_CARTEIRA_GF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
create or replace procedure prc_saldo_carteira_gf (p_errmsg out varchar,
p_data_saldo in date) is
cursor c_saldo_carteira_gf (p_dt_saldo varchar2) is
SELECT distinct
cl.cli_cod_cgccpf as CNPJ,
cl.cli_rsl as RAZAO_SOCIAL,
c.pta_num NUMERO_CONTRATO,
c.con_val as VALOR_INICIAL,
t.oper_nom as GERENTE,
NVL((select sum(s.sdo_val)
from tb_con c,
tb_sdo s
where c.con_num = s.con_num
and c.con_dat_alt = s.sdo_dat_alt
and c.cli_cod = cl.cli_cod
and c.con_dat_alt = '01 jan 1960'
and c.sitcon_cod in (1,2,3,4)
and s.lan_num_lib = 1
and s.prc_num = 0
and s.lan_seq_par = 1
and s.lan_tip = 1
and s.sdo_dat >= p_dt_saldo), 0) as SALDO_DEVEDOR,
c.con_val_taxctb TAXA,
c.con_val_tar TAC,
(select max(c.con_dat_vct)
from tb_con c,
tb_sdo s
where c.con_num = s.con_num
and c.con_dat_alt = s.sdo_dat_alt
and c.cli_cod = cl.cli_cod
and c.con_dat_alt = '01 jan 1960'
and c.sitcon_cod in (1,2,3,4)
and s.lan_num_lib = 1
and s.prc_num = 0
and s.lan_seq_par = 1
and s.lan_tip = 1
and s.sdo_dat >= p_dt_saldo) as vencto,
(select max(c.CON_DAT)
from tb_con c,
tb_sdo s
where c.con_num = s.con_num
and c.con_dat_alt = s.sdo_dat_alt
and c.cli_cod = cl.cli_cod
and c.con_dat_alt = '01 jan 1960'
and c.sitcon_cod in (1,2,3,4)
and s.lan_num_lib = 1
and s.prc_num = 0
and s.lan_seq_par = 1
and s.lan_tip = 1
and s.sdo_dat >= p_dt_saldo) as Data_Contrato
FROM tb_cli cl,
tb_con c ,
tb_oper t
WHERE c.cli_cod = cl.cli_cod
AND c.con_dat_alt = '01 jan 1960'
AND c.sitcon_cod in (1,2,3,4)
AND c.con_num_ger = t.oper_cod
AND (NVL((select sum(s.sdo_val)
from tb_con c,
tb_sdo s
where c.con_num = s.con_num
and c.con_dat_alt = s.sdo_dat_alt
and c.cli_cod = cl.cli_cod
and c.con_dat_alt = '01 jan 1960'
and c.sitcon_cod in (1,2,3,4)
and s.lan_num_lib = 1
and s.prc_num = 0
and s.lan_seq_par = 1
and s.lan_tip = 1
and s.sdo_dat >= p_dt_saldo), 0)) > 0;
v_data_saldo varchar2(8);
v_nome_arquivo varchar2(100);
v_arquivo utl_file.file_type;
v_diretorio varchar2(200) := '/backup/CPMF';
v_registro_header varchar2(1000);
v_registro_detail varchar2(1000);
v_registro_trailler varchar2(1000);
v_quantidade number := 0;
begin
if p_data_saldo is null then
v_data_saldo := to_char(sysdate, 'ddmmyyyy');
else
v_data_saldo := to_char(p_data_saldo, 'ddmmyyyy');
end if;
v_nome_arquivo := 'CARTEIRAGF'||
v_data_saldo||
'.csv';
v_arquivo := utl_file.fopen(v_diretorio, v_nome_arquivo, 'W');
-- 4.2.1 Registro Header
v_registro_header := 'CNPJ'||
';'||
'RAZAO_SOCIAL'||
';'||
'NUMERO_CONTRATO'||
';'||
'VALOR_INICIAL'||
';'||
'SALDO_DEVEDOR'||
';'||
'TAXA'||
';'||
'TAC'||
';'||
'VENCTO'||
';'||
'DATA_CONTRATO'||
';'||
'GERENTE'||
';';
utl_file.put_line(v_arquivo, v_registro_header);
-- 4.2.2 Registro Detail
for i in c_saldo_carteira_gf (v_data_saldo) loop
v_registro_detail := substr(i.cnpj, 1, 2)||'.'||
substr(i.cnpj, 3, 3)||'.'||
substr(i.cnpj, 6, 3)||'/'||
substr(i.cnpj, 9, 4)||'-'||
substr(i.cnpj, 13, 2)||
';'||
i.razao_social||
';'||
i.numero_contrato||
';'||
i.valor_inicial||
';'||
to_char(i.saldo_devedor * 1000000, '9999999999999999,000000')||
';'||
to_char(i.taxa * 1000000, '999999999,000000')||
';'||
to_char(i.tac * 100, '999999999999,00')||
';'||
to_char(i.vencto, 'dd/mm/yyyy')||
';'||
to_char(i.data_contrato, 'dd/mm/yyyy')||
';'||
i.gerente||
';';
utl_file.put_line(v_arquivo, v_registro_detail);
v_quantidade := v_quantidade + 1;
end loop;
-- 4.2.7 Registro Trailler
v_registro_trailler := 'TOTAL DE REGISTROS'||
rpad(' ', 10, ' ')||
v_quantidade;
utl_file.put_line(v_arquivo, v_registro_trailler);
utl_file.fclose(v_arquivo);
exception
when utl_file.invalid_mode then
p_errmsg := SQLERRM;
when utl_file.invalid_path then
p_errmsg := SQLERRM;
when utl_file.invalid_filehandle then
p_errmsg := SQLERRM;
when utl_file.invalid_operation then
p_errmsg := SQLERRM;
when utl_file.read_error then
p_errmsg := SQLERRM;
when utl_file.write_error then
p_errmsg := SQLERRM;
when utl_file.internal_error then
p_errmsg := SQLERRM;
when others then
p_errmsg := SQLERRM;
end prc_saldo_carteira_gf;
/
DECLARE
v_erromsg VARCHAR2 (1000);
BEGIN
prc_saldo_carteira_gf (p_errmsg => v_erromsg,
p_data_saldo => TRUNC (SYSDATE)
);
END;
DECLARE
v_erromsg VARCHAR2 (1000);
BEGIN
prc_saldo_carteira_gf (p_errmsg => v_erromsg,
p_data_saldo => TRUNC (SYSDATE)
);
if v_erromsg is not null then
dbms_output.put_line('Erro : '||v_erromsg);
end if;
END;
SQL> execute prc_saldo_carteira_gf
BEGIN prc_saldo_carteira_gf; END;
*
ERRO na linha 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PRC_SALDO_CARTEIRA_GF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> declare
x integer;
begin
dbms_job.submit (x,'prc_saldo_carteira_gf;',
trunc(sysdate)+1+21/24+15/1440,
'trunc(sysdate)+1+21/24+15/1440');
dbms_output.put_line(TO_CHAR(x));
end;
/ 2 3 4 5 6 7 8 9
declare
*
ERROR at line 1:
ORA-06550: line 1, column 93:
PLS-00306: wrong number or types of arguments in call to 'PRC_SALDO_CARTEIRA_GF'
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 4
prc_saldo_carteira_gf (p_errmsg => v_erromsg,
p_data_saldo => TRUNC (SYSDATE)
);
Users browsing this forum: No registered users and 7 guests