Posts

Showing posts from 2019

Load data from XML or Excel file to oracle database with proper validation script

Image
create table HR_temp (col1 varchar2(20), col2 varchar2(20), col3 varchar2(20), col4 varchar2(20), col5 varchar2(20), col6 varchar2(20), col7 varchar2(20),col8 varchar2(20),col9 varchar2(20),col10 varchar2(20),col11 varchar2(20),col12 varchar2(20),col13 varchar2(20),col14 varchar2(20)); / create table HR_temp_ERROR (ERROR varchar2(20), Status varchar2(20), col1 varchar2(20), col2 varchar2(20), col3 varchar2(20), col4 varchar2(20), col5 varchar2(20), col6 varchar2(20), col7 varchar2(20),col8 varchar2(20),col9 varchar2(20),col10 varchar2(20),col11 varchar2(20),col12 varchar2(20),col13 varchar2(20),col14 varchar2(20)); / create or replace procedure proc_parseXml_new(pSessionId varchar2, pTransType varchar2, pOperation varchar2, pRawXMLdata clob)  is    v_parser        Dbms_Xmlparser.Parser;    v_Xml_Clob      Clob;    v_Doc           Dbms_Xmldom.Domdocument;    v_Ch...
Post a Comment
Read more

Create table of two consecutive months if date range belong to two months

select to_date('01-JUL-2019')+level-1 "JULY", CASE WHEN MONTHS_BETWEEN('09-AUG-2019','01-JUL-19')>1 THEN CASE WHEN LEVEL<=TO_NUMBER(TO_CHAR(TO_DATE('09-AUG-2019'),'DD')) THEN ADD_MONTHS('01-JUL-2019',1)+LEVEL-1 END END "August" FROM DUAL CONNECT BY LEVEL<=CASE WHEN MONTHS_BETWEEN('09-AUG-2019', '01-JUL-19')>1 THEN TO_NUMBER(TO_CHAR(LAST_DAY('01-JUL-2019'),'DD')) ELSE TO_DATE('09-AUG-2019')-TO_DATE('01-JUL-2019') +1 END ----------------------------------- ANOTHER METHOD ----------------------------------- SELECT "JULY", "AUGUST" FROM ( SELECT ROWNUM RN, CASE WHEN TO_CHAR(TO_DATE(MNTH),'MON')='JUL' THEN MONTH END "JULY" FROM ( SELECT TO_DATE('01-JUL-2019')+LEVEL-1 MNTH FROM DUAL CONNECT BY LEVEL<=TO_DATE('09-AUG-19')-T0_DATE('01-JUL-2019')+1 ...
Post a Comment
Read more

Procedure to load XML / Excel data into table

Image
create or replace procedure proc_parseXml(pRawXMLdata clob)  is    v_parser        Dbms_Xmlparser.Parser;    v_Xml_Clob      Clob;    v_Doc           Dbms_Xmldom.Domdocument;    v_Child_Node    dbms_xmldom.domnodelist;    v_xml_Node      Dbms_Xmldom.Domnode;    v_empno         number;    v_ename         varchar2(20);    v_job           varchar2(20);    v_hiredate      varchar2(20); Begin       -- CLOB data   v_Xml_Clob :=pRawXMLdata ;    -- Create XML Parser.    v_parser  := Dbms_Xmlparser.Newparser;    -- Parse XML into DOM object                         Dbms_Xmlparser.P...
Post a Comment
Read more

ORA-01704: string literal too long error while using regexp_substr

Image
create table dynamic_query(test clob); / create or replace procedure proc_too_large is v_empcode clob; str clob; v_count number; begin v_empcode:='CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||     'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||     'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||     'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A...
Post a Comment
Read more