1 2 Previous Next 20 Replies Latest reply: Mar 29, 2013 8:46 AM by Jason_(A_Non) Go to original post RSS
      • 15. Re: Sys.Xmltype.Createxml performance
        Marco Gralike
        So what does your new code look like, just to see if anything obvious stands out for your version?
        ?
        • 16. Re: Sys.Xmltype.Createxml performance
          Jason_(A_Non)
          The purpose for the BFILENAME in Odie's example was to read the data in from disk as the file resided on the server in his example.

          In your example, if we go back to the very first post, you have
           Procedure Some_Information(in_Some_Details In Clob, in_Batch_Id In Varchar2, in_Modified_User_Uid In Varchar2, in_Modified_Date In Varchar2, out_Status Out Varchar2, out_Error Out Varchar2)
          As
          Begin
          
          Xml_Type:=Sys.Xmltype.Createxml(in_Some_Details);
          As this procedure is called from .NET, you already have the data in your "in_Some_Details" CLOB and you convert it to XMLType and store it in "Xml_Type". That's the same data type the "doc" variable from Odie's example is so you have already done line
          28    doc := xmltype(bfilename('TEST_DIR', 'smldata.xml'), nls_charset_id('AL32UTF8'));
          so just start from Line 29.

          Of course this is still all a guess as you haven't shown us the latest code.
          • 17. Re: Sys.Xmltype.Createxml performance
            998054
            Procedure Some_Information(in_Some_Details In Clob, in_Batch_Id In Varchar2, in_Modified_User_Uid In Varchar2, in_Modified_Date In Varchar2, out_Status Out Varchar2, out_Error Out Varchar2)
            As
            Begin

            Xml_Type:=Sys.Xmltype.Createxml(in_Client_Details);

            Insert Into XXXXX(I_Batch_Id,I_Client_Account_Id,V_Client_Legal_Name,
            V_Old_Office_Code, V_Old_Office_Business_Name,V_New_Office_Code,V_New_Office_Business_Name,
            V_Old_Cost_Center_Code,V_Old_Cost_Center_Name,V_New_Cost_Center_Code, V_New_Cost_Center_Name,
            V_Client_Status,D_Client_Inactivation_Date,V_Select_Ynflag,
            V_Replace_Ynflag,V_Replace_All_Policy_Ynflag,V_Replace_All_Service_Ynflag,
            I_Created_By_Uid,I_Modified_By_Uid,
            D_Created_Timestamp,D_Modified_Timestamp,
            V_Batch_Status,D_Pol_Eff_Date_From,D_Pol_Eff_Date_To
            )
            (Select in_Batch_Id, A.CLIENT_ACCOUNT_ID, A.CLIENT_LEGAL_NAME, A.OLD_OFFICE_CODE, A.OLD_OFFICE_BUSINESS_NAME,
            A.NEW_OFFICE_CODE, A.NEW_OFFICE_BUSINESS_NAME, A.OLD_COST_CENTER_CODE, A.OLD_COST_CENTER_NAME,
            A.NEW_COST_CENTER_CODE, A.NEW_COST_CENTER_NAME, A.CLIENT_STATUS,
            CASE
            WHEN A.CLIENT_INACTIVATION_DATE =' ' THEN NULL
            ELSE To_Date(A.CLIENT_INACTIVATION_DATE,'DD/MM/YYYY')
            END ,
            A.SELECT_FLAG, A.REPLACE_FLAG, A.REPLACE_ALL_POLICY_FLAG, A.REPLACE_ALL_SERVICE_FLAG,
            in_Modified_User_Uid,in_Modified_User_Uid,
            To_Date(in_Modified_Date,'yyyyMMddhh24miss'),To_Date(in_Modified_Date,'yyyyMMddhh24miss'),'A',
            CASE
            WHEN A.POL_EFF_DATE_FROM =' ' THEN NULL
            ELSE To_Date(A.POL_EFF_DATE_FROM,'DD/MM/YYYY')
            END ,
            CASE
            WHEN A.POL_EFF_DATE_TO =' ' THEN NULL
            ELSE To_Date(A.POL_EFF_DATE_TO,'DD/MM/YYYY')
            END
            FROM ( XMLTABLE('/NewDataSet/InformationSet' passing Xml_Type columns BATCH_ID VARCHAR2(12 Char) PATH 'I_BATCH_ID'
            ,CLIENT_ACCOUNT_ID NUMBER(12) PATH 'I_CLIENT_ACCOUNT_ID'
            , CLIENT_LEGAL_NAME VARCHAR2 (50 Char) PATH 'V_CLIENT_LEGAL_NAME'
            ,OLD_OFFICE_CODE VARCHAR2 (5 Char) PATH 'V_OLD_OFFICE_CODE'
            ,OLD_OFFICE_BUSINESS_NAME VARCHAR2 (30 Char) PATH 'V_OLD_OFFICE_NAME'
            ,NEW_OFFICE_CODE VARCHAR2 (5 Char) PATH 'V_NEW_OFFICE_CODE'
            ,NEW_OFFICE_BUSINESS_NAME VARCHAR2 (30 Char) PATH 'V_NEW_OFFICE_NAME'
            ,OLD_COST_CENTER_CODE VARCHAR2 (5 Char) PATH 'V_OLD_COSTCENTER_CODE'
            ,OLD_COST_CENTER_NAME VARCHAR2 (40 Char) PATH 'V_OLD_COSTCENTER_NAME'
            ,NEW_COST_CENTER_CODE VARCHAR2 (5 Char) PATH 'V_NEW_COSTCENTER_CODE'
            ,NEW_COST_CENTER_NAME VARCHAR2 (40 Char) PATH 'V_NEW_COSTCENTER_NAME'
            ,CLIENT_STATUS VARCHAR2 (4 Char) PATH 'V_CLIENT_STATUS'
            ,CLIENT_INACTIVATION_DATE VARCHAR2 (40 Char) PATH 'V_CLIENT_INACTIVATION_DATE'
            ,SELECT_FLAG VARCHAR2 (4 Char) PATH 'V_SELECT_FLAG'
            ,REPLACE_FLAG VARCHAR2 (4 Char) PATH 'V_REPLACE_FLAG'
            ,REPLACE_ALL_POLICY_FLAG VARCHAR2 (4 Char) PATH 'V_REPLACE_ALL_POLICY_FLAG'
            ,REPLACE_ALL_SERVICE_FLAG VARCHAR2 (4 Char) PATH 'V_REPLACE_ALL_SERVICE_FLAG'
            ,POL_EFF_DATE_FROM VARCHAR2 (40 Char) PATH 'V_POL_EFF_DATE_FROM'
            ,POL_EFF_DATE_TO VARCHAR2 (40 Char) PATH 'V_POL_EFF_DATE_TO'
            )
            ) A
            Where Not Exists (Select 1 From Mbclnt M Where M.I_Batch_Id=in_Batch_Id And M.I_Client_Account_Id=A.Client_Account_Id)
            );


            out_Status:='0';
            out_Error:='No Error 2';
            dbms_output.put_line(out_Status);
            EXCEPTION WHEN OTHERS THEN
            out_Status:='100';
            out_Error:= SQLERRM;

            End;
            • 18. Re: Sys.Xmltype.Createxml performance
              998054
              The below is some trail runs I was doing on the 2nd link you had mentioned.I am running into errors if one of the tags does not have the data,I mean it gives me
              ORA-30625: method dispatch on NULL SELF argument is disallowed
              ORA-06512: at line 28

              I mean if I remove the data between tags I am getting above errors.




              create table users(EMPNO NUMBER(4),SAL NUMBER(4),HIREDATE Varchar2(30 Char));

              Declare
              TYPE import_q_rec IS RECORD
              (EMPN dbms_sql.NUMBER_TABLE,
              SALN dbms_sql.NUMBER_TABLE,
              HIREDATE dbms_sql.VARCHAR2_TABLE
              );
              l_import_q_tab import_q_rec;
              l_row_index Number:=1;
              l_temp_nd XMLTYPE;
              l_result_xml XMLTYPE:=XmlType('<ROWSET>
              <ROW>
              <EMPNO>2290</EMPNO>
              <SAL>2000</SAL>
              <HIREDATE>31-DEC-1992</HIREDATE>
              </ROW>
              <ROW>
              <EMPNO>111</EMPNO>
              <SAL>123</SAL>
              <HIREDATE>31-DEC-1992</HIREDATE>
              </ROW>
              </ROWSET>');
              begin
              WHILE l_result_xml.Existsnode('/ROWSET/ROW[' || To_Char(l_row_index) || ']') > 0
              LOOP
              l_temp_nd :=l_result_xml.Extract('/ROWSET/ROW[' || To_Char(l_row_index) || ']');
              l_import_q_tab.EMPN(l_row_index) := l_temp_nd.extract('/ROW/EMPNO/text()').getstringval();
              l_import_q_tab.SALN(l_row_index) := l_temp_nd.extract('/ROW/SAL/text()').getstringval();
              l_import_q_tab.HIREDATE(l_row_index) := l_temp_nd.extract('/ROW/HIREDATE/text()').getstringval();
              l_row_index := l_row_index + 1;
              END LOOP;

              FORALL i IN 1..l_row_index-1
              INSERT INTO users
              VALUES
              (l_import_q_tab.EMPN(i), l_import_q_tab.SALN(i),l_import_q_tab.HIREDATE(i));
              End;
              /
              • 19. Re: Sys.Xmltype.Createxml performance
                998054
                The below is the code I was using to test run the concept you had mentioned in the 1st link.I will make the changes that you have mentioned and then try rerunning again.

                DECLARE
                xml_handle DBMS_XMLSTORE.ctxtype;
                f BFILE;
                number_of_rows NUMBER;
                xml_data CLOB;
                BEGIN
                f := BFILENAME ('C:/Oracle/xmltest/', 'mmxxcc.xml');

                DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION);

                DBMS_LOB.fileopen (f, DBMS_LOB.file_readonly);
                DBMS_LOB.loadfromfile (xml_data, f, DBMS_LOB.getlength (f));
                DBMS_LOB.fileclose (f);

                xml_handle := DBMS_XMLSTORE.newcontext ('users');
                --DBMS_XMLSTORE.setrowtag (xml_handle, 'EMPLOYEE');
                number_of_rows := DBMS_XMLSTORE.insertxml (xml_handle, xml_data);

                DBMS_OUTPUT.PUT_LINE( number_of_rows || ' rows inserted.' );
                DBMS_XMLSTORE.closecontext (xml_handle);
                DBMS_LOB.freetemporary (xml_data);
                COMMIT;
                END;
                • 20. Re: Sys.Xmltype.Createxml performance
                  Jason_(A_Non)
                  That ORA error is Oracle's error of saying it is trying to perform an operation on something that is NULL. Instead of returning NULL, as many other Oracle functions/methods do, it returns an ORA message instead.

                  To avoid that, you need to do an existsnode before you try to extract any data that may not exist. So you would need something like
                  IF (l_temp_nd.existsnode('/ROW/SAL/text()') = 1) THEN
                     l_import_q_tab.SALN(l_row_index) := l_temp_nd.extract('/ROW/SAL/text()').getstringval();
                  END IF
                  1 2 Previous Next