Toad World® Forums

ERROR ON MY PROCEDURE WITH VARIABLES

Guys, i am new in Toad and SQL world and receive a project.

I have a daily process with multiple variables dates, and I need to make him run auto in Ora10g.

I’m trying to build a procedure, but there’s some problem when i try to run, can somebody help me?


Error code

ORA-00932: data tipe inconsistent: expected date, received number.


CREATE OR REPLACE PROCEDURE PRODUCAO_EMPRESTIMOS_DIARIA AS

DT_ANOMES_M0 NUMBER;
DT_ANOMES_M1 NUMBER;
DT_PARTITION_M0 VARCHAR(50);
DT_PARTITION_M1 VARCHAR(50);
DT_CARGA_ATUAL DATE;
DT_REFE_ATUAL DATE;
DT_CARGA_S1 DATE;
DT_REFE_S1 DATE;
DT_CARGA_M1 DATE;
DT_REFE_M1 DATE;


BEGIN

EXECUTE IMMEDIATE(‘SELECT MAX(DT_ANOMES) FROM DBM.TB_RD_EXPORT’) INTO DT_ANOMES_M0;


EXECUTE IMMEDIATE(‘SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(’||DT_ANOMES_M0||’,’‘RRRRMM’’),-1),’‘RRRRMM’’)) FROM DUAL’) INTO DT_ANOMES_M1;


DT_PARTITION_M0 := ‘P_’||DT_ANOMES_M0;


DT_PARTITION_M1 := ‘P_’||DT_ANOMES_M1;


EXECUTE IMMEDIATE(‘SELECT TRUNC(SYSDATE) FROM DUAL’) INTO DT_CARGA_ATUAL;


PR_EXCLUI_TABELAS(‘TMP_RG_DTS’);
EXECUTE IMMEDIATE (‘CREATE TABLE TMP_RG_DTS AS
SELECT DT_ANOMES,
DT_REFE,
MAX(TRUNC(DT_CARGA)) DT_CARGA
FROM DBM.TB_RD_EXPORT
WHERE DT_ANOMES IN (’||DT_ANOMES_M0||’,’||DT_ANOMES_M1||’)
AND DT_CARGA IN (’||DT_CARGA_ATUAL||’)
GROUP BY DT_ANOMES, DT_REFE
ORDER BY DT_ANOMES, DT_REFE
');

END;

please show line number, which is also shown with error