problem in inserting

From: jaggy <kumar.jagadish_at_sella.it>
Date: 15 Feb 2006 03:21:57 -0800
Message-ID: <1140002517.593082.186150@g14g2000cwa.googlegroups.com>

Hi All,

I have a query mentioned below.

I am taking values from XML for some fields.The problem is while inserting some of the columns(XML) are null and only the last column(XML) has values. But when I execute the select statement alone, all the columns(XML) has values.

I created another table with just three columns and tried to insert values from XML all the fields are having values.

Kindly suggest me on this issue.

Structure of the table

---

IB_BR_ID NUMBER(20)
IB_BR_MITTENTE VARCHAR2(50)
IB_BR_RICEVENTE VARCHAR2(50)
IB_BR_IMPORTO VARCHAR2(25)
IB_BR_DATA_VALUTA DATE
IB_BR_DATA_VALUTA_D DATE
IB_BR_DATA_REGIME DATE
IB_BR_DATA_ORDINE DATE
IB_BR_DATA_CONTABILE DATE
IB_BR_CRO VARCHAR2(20)
IB_BR_O_ANAG VARCHAR2(50)
IB_BR_O_COD VARCHAR2(30)
IB_BR_D_ANAG VARCHAR2(50)
IB_BR_D_COD VARCHAR2(30)
IB_BR_D_IND VARCHAR2(4000)
IB_BR_D_LOC VARCHAR2(35)
IB_BR_CAUSALE VARCHAR2(50)
IB_BR_DIVISA VARCHAR2(5)
IB_BR_CAUSALE_SIA VARCHAR2(20)

INSERT INTO /*+ append */ IBG_MA_BONIFICI_RETE (

IB_BR_MITTENTE, --1
IB_BR_RICEVENTE, --2
IB_BR_IMPORTO, --3
IB_BR_DATA_VALUTA, --4
IB_BR_DATA_VALUTA_D, --5

--IB_BR_DATA_REGIME, --6

IB_BR_DATA_ORDINE, --7
IB_BR_DATA_CONTABILE, --8
IB_BR_CRO, --9
IB_BR_O_ANAG, --10
IB_BR_O_COD, --11
IB_BR_D_ANAG, --12
IB_BR_D_COD, --13
IB_BR_D_IND, --14
IB_BR_D_LOC, --15
IB_BR_CAUSALE, --16
IB_BR_DIVISA, --17
IB_BR_CAUSALE_SIA --18

)
SELECT DS_ABI_BANCA_PROP,
DS_ABI_BANCA_DESTINATARIA,

LTRIM( TO_CHAR(DS_IMPORTO, '9999999999999999990D90',

'NLS_NUMERIC_CHARACTERS='',.''')) || '/C' as x, TRUNC(DS_DATA_VALUTA_ADDEBITO) as y,
TRUNC(DS_DATA_VALUTA_BENEF_CLIENTE) as z,
--NULL,

TRUNC(DS_DATA_ESECUZIONE) as xx,
TRUNC(DS_DATA_INIZIO_STATO) as yy,
SUBSTR(DS_RIF_UNIVOCO,1,20) as one,

SUBSTR(XMLTYPE(DS_DETTAGLI_XML).EXTRACT('/DISPOSIZIONE_PC_DETAILS/@DENOMINAZIONE_ORDINANTE').getStringVal(),1,50) as two,
SUBSTR(XMLTYPE(DS_DETTAGLI_XML).EXTRACT('/DISPOSIZIONE_PC_DETAILS/@CODICE_FISCALE_ORDINANTE').getStringVal(),1,30) as three,
SUBSTR(XMLTYPE(DS_DETTAGLI_XML).EXTRACT('/DISPOSIZIONE_PC_DETAILS/@DESCRIZIONE_DESTINATARIO').getStringVal(),1,50) as four,
SUBSTR(DS_CONTO_DESTINATARIO, 5, 8) as five, --13 SUBSTR(to_char(XMLTYPE(DS_DETTAGLI_XML).EXTRACT('/DISPOSIZIONE_PC_DETAILS/@INDIRIZZO_DESTINATARIO').getStringVal()),1,20) as six,
SUBSTR(DS_LOCALITA_BENEF,1,35) as seven, --15 SUBSTR(XMLTYPE(DS_DETTAGLI_XML).EXTRACT('/DISPOSIZIONE_PC_DETAILS/@DESCRIZIONE_RIFERIMENTO_OPERAZIONE').getStringVal(),1,50), DS_ISO_DIVISA_ADDEBITO, --17
SUBSTR(DS_CAUSALE,1,20)
FROM PRG_TR_DISPOSIZIONE
WHERE DS_ABI_BANCA_PROP = '03268'
AND DS_STATO = 'ESAUR'
AND DS_TIPO_INTERNO IN ('BSI','BAII')
AND TRUNC(DS_data_inizio_stato) between
trunc(to_date('01-01-2006','dd/mm/yyyy')) and trunc(to_date('02-01-2006','dd/mm/yyyy')) and rownum < 2;

Thanks in advance,
Jaggy Received on Wed Feb 15 2006 - 05:21:57 CST