14 Replies Latest reply: Apr 12, 2013 11:31 PM by ahmad RSS

    Problem with exporting data

    ahmad
      Dears,

      I have this code to export data from oracle form 10g using webutil into csv file, but the problem is my table has 16000 record and when the file is printed out i got 5000 records and then the system is crashing...

      Any Help ??
      Procedure Pu_Establishment_Prc Is
       
       Cursor vc_get_imported_Data Is
       Select A.Employee_Name, A.Employee_Login, A.Employee_Position,
             A.Division_Organization_Name, A.Employee_Row_Id, A.Product_Line,
             A.Product_Alias_Name, A.Product, A.Parent_Product, A.Device_Product,
             A.Parent_Product_Line, A.Product_Type, A.Service_Type, A.Product_Atm_Type,
             A.Order_Number, A.Order_Type_I, A.Order_Sub_Type_I, A.Order_Date,
             A.Serial_Number, A.Old_Phone_Number, A.Sim_Number, A.Old_Sim_Number,
             A.Primary_Multiline_Number, A.Install_Date, A.Status_I, A.Attribute_Name_Val,
             A.Order_Status_I, A.Order_Status, A.Order_Type, A.Order_Sub_Type,
             A.Last_Updated_Date, A.Created_By_Login, A.Row_Id, A.Action_Type_I,
             A.Action_Type, A.Start_Price, A.Net_Price, A.Current_Billing_Account_Number,
             A.Field_Service_Order_Status_I, A.Nationality, A.Full_Name, A.First_Name,
             A.Middle_Name, Last_Name, Account_Name, Account_Number, A.Account_Type,
             A.Account_Type_I, Account_Sub_Type, Account_Sub_Type_I, A.Tamayoz_Flag,
             A.Tamayoz_Type, Tamayoz_Type_I, Company_Id_Type_I, A.Company_Id_Type,
             A.Commercial_Registration_Number, A.Customer_Value, A.Customer_Age_Value,
             A.Payment_Value, A.Blacklist_Flag, A.Avg_Usage, Order_Day_Name,
             A.Order_Day_Of_Week, Order_Week, Order_Day_Of_Month, A.Order_Month,
             A.Order_Month_Name, A.Order_Year, A.Order_Quarter, A.Order_Date_I, Region,
             A.City, A.Region_I, A.Source_I, A.Source, A.Alternate_Phone,
             A.Service_Request_Number, A.Import_Id, A.Import_Date, Serial_No,
             A.Archive_File_Status, A.Archive_Resone, A.Archive_Serial, A.Archive_Menu,
             A.Archive_Carton, A.Archive_Date, A.Archive_Employee, A.Archive_Backeg_Number,
             A.Archive_Resept_Date, A.Transaction_Type
        From Import_Stc_Daily_Reports_Dtl A, Shows_Users B
        Where 1=1
          And A.Employee_Login = B.User_No(+)
          And B.User_Type(+) <> 6
          And Nvl(B.Region_Code, -1)         = Nvl(:Blk_1.Region_Code, Nvl(B.Region_Code, -1))
          And Nvl(B.Gen_Department_Code, -1) = Nvl(:Blk_1.Gen_Dept, Nvl(B.Gen_Department_Code, -1))
          And Nvl(B.Department_Code, -1)     = Nvl(:Blk_1.Dept_Code, Nvl(B.Department_Code, -1))
          And Nvl(B.Section_Code, -1)        = Nvl(:Blk_1.Section_Code, Nvl(B.Section_Code, -1))
          And Nvl(B.Branch_Code, -1)         = Nvl(:Blk_1.Branch_Code, Nvl(B.Branch_Code, -1))
          And A.Employee_Login               = Nvl(:Blk_1.Scr_User_No, A.Employee_Login)
          And To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr') Between Nvl(:Blk_1.From_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr')) 
                                                                     And Nvl(:Blk_1.To_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr'))
          --and rownum < 11000                                                                  
      Order By Employee_Login;
       
       In_File Client_Text_Io.File_Type;
       Lc$Fic                  Varchar2(10000) ;
       Vn_Date                 Number;
       Vv_File_Path            Varchar2(1000);
       Line                    Varchar2(32767) ;--NCLOB;
      Begin
       Lc$Fic := 'Test_Report'||:Blk_1.Scr_Import_Id||'.csv';
       Vv_File_Path := Webutil_File.File_Save_Dialog ('C:\', Lc$Fic ,'','Location to save file...') ;
       In_File := Client_Text_Io.Fopen(Vv_File_Path,'w');
       
       
      --========================== File Header ==========================================================
        /*  Line :='Employee Name'              ||';'||
              'Employee Login'                ||';'||
              'Employee Position'             ||';'||
              'Division Organization Name'    ||';'||
              'Employee Row Id'               ||';'||
              'Product Line'                  ||';'||
              'Product Alias Name'            ||';'||
              'Product'                       ||';'||
              'Parent Product'                ||';'||
              'Device Product'                ||';'||
              'Parent Product Line'           ||';'||
              'Product Type'                  ||';'||
              'Service Type'                  ||';'||
              'Product Atm Type'              ||';'||
              'Order Number'                  ||';'||
              'Order Type I'                  ||';'||
              'Order Sub Type I'              ||';'||
              'Order Date'                    ||';'||
              'Serial Number'                 ||';'||
              'Old Phone Number'              ||';'||
              'Sim Number'                    ||';'||
              'Old Sim Number'                ||';'||
              'Primary Multiline Number'      ||';'||
              'Install Date'                  ||';'||
              'Status I'                      ||';'||
              'Attribute Name Val'            ||';'||
              'Order Status I'                ||';'||
              'Order Status'                  ||';'||
              'Order Type'                    ||';'||
              'Order Sub Type'                ||';'||
              'Last Updated Date'             ||';'||
              'Created By Login'              ||';'||
              'Row Id'                        ||';'||
              'Action Type I'                 ||';'||
              'Action Type'                   ||';'||
              'Start Price'                   ||';'||
              'Net Price'                     ||';'||
              'Current Billing Account Number'||';'||
              'Field Service Order Status I'  ||';'||
              'Nationality'                   ||';'||
              'Full Name'                     ||';'||
              'First Name'                    ||';'||
              'Middle Name'                   ||';'||
              'Last Name'                     ||';'||
              'Account Name'                  ||';'||
              'Account Number'                ||';'||
              'Account Type'                  ||';'||
              'Account Type I'                   ||';'||
              'Account Sub Type'                 ||';'||
              'Account Sub Type I'               ||';'||
              'Tamayoz Flag'                     ||';'||
              'Tamayoz Type'                     ||';'||
              'Tamayoz Type I'                   ||';'||
              'Company Id Type I'                ||';'||
              'Company Id Type'                  ||';'||
              'Commercial Registration Number'||';'||
              'Customer Value'                   ||';'||
              'Customer Age Value'               ||';'||
              'Payment Value'                    ||';'||
              'Blacklist Flag'                   ||';'||
              'Avg Usage'                        ||';'||
              'Order Day Name'                   ||';'||
              'Order Day Of Week'                ||';'||
              'Order Week'                       ||';'||
              'Order Day Of Month'               ||';'||
              'Order Month'                      ||';'||
              'Order Month Name'                 ||';'||
              'Order Year'                       ||';'||
              'Order Quarter'                    ||';'||
              'Order Date I'                     ||';'||
              'Region'                           ||';'||
              'City'                             ||';'||
              'Region I'                         ||';'||
              'Source I'                         ||';'||
              'Source'                           ||';'||
              'Alternate Phone'                  ||';'||
              'Service Request Number'           ||';'||
              'Import Id'                        ||';'||
              'Import Date'                      ||';'||
              'Serial No'                        ||';'||
              'Archive File Status'              ||';'||
              'Archive Resone'                   ||';'||
              'Archive Serial'                   ||';'||
              'Archive Menu'                     ||';'||
              'Archive Carton'                   ||';'||
              'Archive Date'                     ||';'||
              'Archive Employee'                 ||';'||
              'Archive Backeg Number'            ||';'||
              'Archive Resept Date'              ||';'||
              'Transaction Type'                 ;
           Client_Text_Io.Put_Line(In_File,Line); */
           
        For Rec In vc_get_imported_Data Loop     
          Line := 
              --Rec.Employee_Name                ||';'||  
              Rec.Employee_Login             /*    ||';'||  
              Rec.Employee_Position             ||';'||  
              Rec.Division_Organization_Name    ||';'||  
              Rec.Employee_Row_Id               ||';'||  
              Rec.Product_Line                  ||';'||  
              Rec.Product_Alias_Name            ||';'||  
              Rec.Product                       ||';'||  
              Rec.Parent_Product                ||';'||  
              Rec.Device_Product                ||';'||  
              Rec.Parent_Product_Line           ||';'||  
              Rec.Product_Type                  ||';'||  
              Rec.Service_Type                  ||';'||  
              Rec.Product_Atm_Type              ||';'||  
              Rec.Order_Number                  ||';'||  
              Rec.Order_Type_I                  ||';'||  
              Rec.Order_Sub_Type_I              ||';'||  
              Rec.Order_Date                    ||';'||  
              Rec.Serial_Number                 ||';'||  
              Rec.Old_Phone_Number              ||';'||  
              Rec.Sim_Number                    ||';'||  
              Rec.Old_Sim_Number                ||';'||  
              Rec.Primary_Multiline_Number      ||';'||  
              Rec.Install_Date                  ||';'||  
              Rec.Status_I                      ||';'||  
              Rec.Attribute_Name_Val            ||';'||  
              Rec.Order_Status_I                ||';'||  
              Rec.Order_Status                  ||';'||  
              Rec.Order_Type                    ||';'||  
              Rec.Order_Sub_Type                ||';'||  
              Rec.Last_Updated_Date             ||';'||  
              Rec.Created_By_Login              ||';'||  
              Rec.Row_Id                        ||';'||  
              Rec.Action_Type_I                 ||';'||  
              Rec.Action_Type                   ||';'||  
              Rec.Start_Price                   ||';'||  
              Rec.Net_Price                     ||';'||  
              Rec.Current_Billing_Account_Number||';'||  
              Rec.Field_Service_Order_Status_I  ||';'||  
              Rec.Nationality                   ||';'||  
              Rec.Full_Name                     ||';'||  
              Rec.First_Name                    ||';'||  
              Rec.Middle_Name                   ||';'||  
              Rec.Last_Name                     ||';'||  
              Rec.Account_Name                  ||';'|| 
              Rec.Account_Number                ||';'||  
              Rec.Account_Type                  ||';'||  
              Rec.Account_Type_I                ||';'||  
              Rec.Account_Sub_Type              ||';'||  
              Rec.Account_Sub_Type_I            ||';'||  
              Rec.Tamayoz_Flag                  ||';'||  
              Rec.Tamayoz_Type                  ||';'||  
              Rec.Tamayoz_Type_I                ||';'||  
              Rec.Company_Id_Type_I             ||';'||  
              Rec.Company_Id_Type               ||';'||  
              Rec.Commercial_Registration_Number||';'||  
              Rec.Customer_Value                ||';'||  
              Rec.Customer_Age_Value            ||';'||  
              Rec.Payment_Value                 ||';'||  
              Rec.Blacklist_Flag                ||';'||  
              Rec.Avg_Usage                     ||';'||  
              Rec.Order_Day_Name                ||';'||  
              Rec.Order_Day_Of_Week             ||';'||  
              Rec.Order_Week                    ||';'||  
              Rec.Order_Day_Of_Month            ||';'||  
              Rec.Order_Month                   ||';'||  
              Rec.Order_Month_Name              ||';'||  
              Rec.Order_Year                    ||';'||  
              Rec.Order_Quarter                 ||';'||  
              Rec.Order_Date_I                  ||';'||  
              Rec.Region                        ||';'||  
              Rec.City                          ||';'||  
              Rec.Region_I                      ||';'||  
              Rec.Source_I                      ||';'||  
              Rec.Source                        ||';'||  
              Rec.Alternate_Phone               ||';'||  
              Rec.Service_Request_Number        ||';'||  
              Rec.Import_Id                     ||';'||  
              Rec.Import_Date                   ||';'||  
              Rec.Serial_No                     ||';'||  
              Rec.Archive_File_Status           ||';'||  
              Rec.Archive_Resone                ||';'||  
              Rec.Archive_Serial                ||';'||  
              Rec.Archive_Menu                  ||';'||  
              Rec.Archive_Carton                ||';'||  
              Rec.Archive_Date                  ||';'||  
              Rec.Archive_Employee              ||';'||  
              Rec.Archive_Backeg_Number         ||';'||  
              Rec.Archive_Resept_Date           ||';'||  
              Rec.Transaction_Type             */    ;
         Client_Text_Io.Put_Line(In_File,Line);                   
        End Loop;           
       Client_Text_Io.Fclose(In_File);
            Message('SUCCESSFUL');
            Message(' ');
      --=======================================================
        Exception When Others Then
           Message('Error Occured while exporting'||sqlerrm);
           Message('Error Occured while exporting'||sqlerrm);   
      End;
        • 1. Re: Problem with exporting data
          François Degrelle
          Hello,
          <p>Read this article</p>
          Francois
          • 2. Re: Problem with exporting data
            ahmad
            Thanks, but still i didn't get 16000 records.
            and then, the system is crashed with error message:

            frm-92101 there was a failure in the forms server during startup
            • 3. Re: Problem with exporting data
              François Degrelle
              Also try to execute a
              synchronize;
              instruction every 1000 exported lines...

              Francois
              • 4. Re: Problem with exporting data
                CraigB
                There's an old saying, "Use the right tool for the job." Right now, your code looks like you are trying to use a hammer on a screw instead of a screw driver. If you are exporting a small dataset, WebUtil is OK. Anything larger than 1000 records, in my opinion, and you should look at using something else. The point of Francois's article was to demonstrate to you that if you are going to export data to the client using WebUtil, you are better off exporting the file to the Application Server (AS) and then transfering the file from the AS to the client using WebUtil. This is much more efficient and a whole lot faster.

                I recommend you use TEXT_IO to export your data set to a file on the AS. Once the creation of the file is complete, then I would use WebUtil's WEBUTIL_FILE_TRANSFER.AS_TO_CLIENT or WEBUTIL_FILE_TRANSFER.AS_TO_CLIENT_WITH_PROGRESS procedures to transfer the file from the AS to the client.

                The most efficient method would be to create a procedure in your database and call the procedure from your form. The key to this method is that you would want to write the file from the database to a location that is a shared directory with the AS. Then use one of the two WebUtil procedures I mention above to transfer the file to your client.

                As to your SQL, I recommend you implement bulk processing to make your PL/SQL process more efficient. Take a look at this Bulk Processing with BULK COLLECT and FORALL article by Steven Feuerstein for information on how to implement.

                Why it bombs out at 5000 records is hard to determine. Because of the number of columns you are exporting, it may be that you are simply running out of memory in your client JVM session - which could be the cause of the FRM-92101 error.

                The one question I have about your CURSOR SQL pertains to this line:
                And B.User_Type(+)  6
                Is this a typo? Should it be *"And B.User_Type(+) = 6"* instead?

                The bottom line is I strongly recommend you create the file outside of Forms and then use Forms (WebUtil) to transfer the file to the client. If you MUST use Forms to create the file, then I recommend you use TEXT_IO to create it on the AS and again use WebUtil to transfer the file to the client.

                Hope this helps.
                Craig...
                • 5. Re: Problem with exporting data
                  ahmad
                  Thank you, i tried that but the form was Hanging (infinit loop) !



                  Procedure Pu_Establishment_Prc Is
                   
                   Cursor vc_get_imported_Data Is
                   Select A.Employee_Name, A.Employee_Login, A.Employee_Position,
                         A.Division_Organization_Name, A.Employee_Row_Id, A.Product_Line,
                         A.Product_Alias_Name, A.Product, A.Parent_Product, A.Device_Product,
                         A.Parent_Product_Line, A.Product_Type, A.Service_Type, A.Product_Atm_Type,
                         A.Order_Number, A.Order_Type_I, A.Order_Sub_Type_I, A.Order_Date,
                         A.Serial_Number, A.Old_Phone_Number, A.Sim_Number, A.Old_Sim_Number,
                         A.Primary_Multiline_Number, A.Install_Date, A.Status_I, A.Attribute_Name_Val,
                         A.Order_Status_I, A.Order_Status, A.Order_Type, A.Order_Sub_Type,
                         A.Last_Updated_Date, A.Created_By_Login, A.Row_Id, A.Action_Type_I,
                         A.Action_Type, A.Start_Price, A.Net_Price, A.Current_Billing_Account_Number,
                         A.Field_Service_Order_Status_I, A.Nationality, A.Full_Name, A.First_Name,
                         A.Middle_Name, Last_Name, Account_Name, Account_Number, A.Account_Type,
                         A.Account_Type_I, Account_Sub_Type, Account_Sub_Type_I, A.Tamayoz_Flag,
                         A.Tamayoz_Type, Tamayoz_Type_I, Company_Id_Type_I, A.Company_Id_Type,
                         A.Commercial_Registration_Number, A.Customer_Value, A.Customer_Age_Value,
                         A.Payment_Value, A.Blacklist_Flag, A.Avg_Usage, Order_Day_Name,
                         A.Order_Day_Of_Week, Order_Week, Order_Day_Of_Month, A.Order_Month,
                         A.Order_Month_Name, A.Order_Year, A.Order_Quarter, A.Order_Date_I, Region,
                         A.City, A.Region_I, A.Source_I, A.Source, A.Alternate_Phone,
                         A.Service_Request_Number, A.Import_Id, A.Import_Date, Serial_No,
                         A.Archive_File_Status, A.Archive_Resone, A.Archive_Serial, A.Archive_Menu,
                         A.Archive_Carton, A.Archive_Date, A.Archive_Employee, A.Archive_Backeg_Number,
                         A.Archive_Resept_Date, A.Transaction_Type
                    From Import_Stc_Daily_Reports_Dtl A, Shows_Users B
                    Where 1=1
                      And A.Employee_Login = B.User_No(+)
                      And B.User_Type(+) <> 6
                      And Nvl(B.Region_Code, -1)         = Nvl(:Blk_1.Region_Code, Nvl(B.Region_Code, -1))
                      And Nvl(B.Gen_Department_Code, -1) = Nvl(:Blk_1.Gen_Dept, Nvl(B.Gen_Department_Code, -1))
                      And Nvl(B.Department_Code, -1)     = Nvl(:Blk_1.Dept_Code, Nvl(B.Department_Code, -1))
                      And Nvl(B.Section_Code, -1)        = Nvl(:Blk_1.Section_Code, Nvl(B.Section_Code, -1))
                      And Nvl(B.Branch_Code, -1)         = Nvl(:Blk_1.Branch_Code, Nvl(B.Branch_Code, -1))
                      And A.Employee_Login               = Nvl(:Blk_1.Scr_User_No, A.Employee_Login)
                      And To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr') Between Nvl(:Blk_1.From_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr')) 
                                                                                 And Nvl(:Blk_1.To_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr'))
                      --and rownum < 10010                                                                 
                  Order By Employee_Login;
                   
                   In_File Client_Text_Io.File_Type;
                   Lc$Fic                  Varchar2(10000) ;
                   Vn_Date                 Number;
                   Vv_File_Path            Varchar2(1000);
                   Line                    Varchar2(32767) ;--NCLOB;
                   i                       number;
                  Begin
                   Lc$Fic := 'Test_Report'||:Blk_1.Scr_Import_Id||'.csv';
                   Vv_File_Path := Webutil_File.File_Save_Dialog ('C:\', Lc$Fic ,'','Location to save file...') ;
                   In_File := Client_Text_Io.Fopen(Vv_File_Path,'W');
                   
                   
                  --========================== File Header ==========================================================
                    /*  Line :='Employee Name'              ||';'||
                          'Employee Login'                ||';'||
                          'Employee Position'             ||';'||
                          'Division Organization Name'    ||';'||
                          'Employee Row Id'               ||';'||
                          'Product Line'                  ||';'||
                          'Product Alias Name'            ||';'||
                          'Product'                       ||';'||
                          'Parent Product'                ||';'||
                          'Device Product'                ||';'||
                          'Parent Product Line'           ||';'||
                          'Product Type'                  ||';'||
                          'Service Type'                  ||';'||
                          'Product Atm Type'              ||';'||
                          'Order Number'                  ||';'||
                          'Order Type I'                  ||';'||
                          'Order Sub Type I'              ||';'||
                          'Order Date'                    ||';'||
                          'Serial Number'                 ||';'||
                          'Old Phone Number'              ||';'||
                          'Sim Number'                    ||';'||
                          'Old Sim Number'                ||';'||
                          'Primary Multiline Number'      ||';'||
                          'Install Date'                  ||';'||
                          'Status I'                      ||';'||
                          'Attribute Name Val'            ||';'||
                          'Order Status I'                ||';'||
                          'Order Status'                  ||';'||
                          'Order Type'                    ||';'||
                          'Order Sub Type'                ||';'||
                          'Last Updated Date'             ||';'||
                          'Created By Login'              ||';'||
                          'Row Id'                        ||';'||
                          'Action Type I'                 ||';'||
                          'Action Type'                   ||';'||
                          'Start Price'                   ||';'||
                          'Net Price'                     ||';'||
                          'Current Billing Account Number'||';'||
                          'Field Service Order Status I'  ||';'||
                          'Nationality'                   ||';'||
                          'Full Name'                     ||';'||
                          'First Name'                    ||';'||
                          'Middle Name'                   ||';'||
                          'Last Name'                     ||';'||
                          'Account Name'                  ||';'||
                          'Account Number'                ||';'||
                          'Account Type'                  ||';'||
                          'Account Type I'                   ||';'||
                          'Account Sub Type'                 ||';'||
                          'Account Sub Type I'               ||';'||
                          'Tamayoz Flag'                     ||';'||
                          'Tamayoz Type'                     ||';'||
                          'Tamayoz Type I'                   ||';'||
                          'Company Id Type I'                ||';'||
                          'Company Id Type'                  ||';'||
                          'Commercial Registration Number'||';'||
                          'Customer Value'                   ||';'||
                          'Customer Age Value'               ||';'||
                          'Payment Value'                    ||';'||
                          'Blacklist Flag'                   ||';'||
                          'Avg Usage'                        ||';'||
                          'Order Day Name'                   ||';'||
                          'Order Day Of Week'                ||';'||
                          'Order Week'                       ||';'||
                          'Order Day Of Month'               ||';'||
                          'Order Month'                      ||';'||
                          'Order Month Name'                 ||';'||
                          'Order Year'                       ||';'||
                          'Order Quarter'                    ||';'||
                          'Order Date I'                     ||';'||
                          'Region'                           ||';'||
                          'City'                             ||';'||
                          'Region I'                         ||';'||
                          'Source I'                         ||';'||
                          'Source'                           ||';'||
                          'Alternate Phone'                  ||';'||
                          'Service Request Number'           ||';'||
                          'Import Id'                        ||';'||
                          'Import Date'                      ||';'||
                          'Serial No'                        ||';'||
                          'Archive File Status'              ||';'||
                          'Archive Resone'                   ||';'||
                          'Archive Serial'                   ||';'||
                          'Archive Menu'                     ||';'||
                          'Archive Carton'                   ||';'||
                          'Archive Date'                     ||';'||
                          'Archive Employee'                 ||';'||
                          'Archive Backeg Number'            ||';'||
                          'Archive Resept Date'              ||';'||
                          'Transaction Type'                 ;
                       Client_Text_Io.Put_Line(In_File,Line); */
                       
                    For Rec In vc_get_imported_Data Loop
                          I:=1;
                          while i <= 1000 Loop          
                      Line := 
                          --Rec.Employee_Name                ||';'||  
                          Rec.Employee_Login                 /*||';'||  
                          Rec.Employee_Position             ||';'||  
                          Rec.Division_Organization_Name    ||';'||  
                          Rec.Employee_Row_Id               ||';'||  
                          Rec.Product_Line                  ||';'||  
                          Rec.Product_Alias_Name            ||';'||  
                          Rec.Product                       ||';'||  
                          Rec.Parent_Product                ||';'||  
                          Rec.Device_Product                ||';'||  
                          Rec.Parent_Product_Line           ||';'||  
                          Rec.Product_Type                  ||';'||  
                          Rec.Service_Type                  ||';'||  
                          Rec.Product_Atm_Type              ||';'||  
                          Rec.Order_Number                  ||';'||  
                          Rec.Order_Type_I                  ||';'||  
                          Rec.Order_Sub_Type_I              ||';'||  
                          Rec.Order_Date                    ||';'||  
                          Rec.Serial_Number                 ||';'||  
                          Rec.Old_Phone_Number              ||';'||  
                          Rec.Sim_Number                    ||';'||  
                          Rec.Old_Sim_Number                ||';'||  
                          Rec.Primary_Multiline_Number      ||';'||  
                          Rec.Install_Date                  ||';'||  
                          Rec.Status_I                      ||';'||  
                          Rec.Attribute_Name_Val            ||';'||  
                          Rec.Order_Status_I                ||';'||  
                          Rec.Order_Status                  ||';'||  
                          Rec.Order_Type                    ||';'||  
                          Rec.Order_Sub_Type                ||';'||  
                          Rec.Last_Updated_Date             ||';'||  
                          Rec.Created_By_Login              ||';'||  
                          Rec.Row_Id                        ||';'||  
                          Rec.Action_Type_I                 ||';'||  
                          Rec.Action_Type                   ||';'||  
                          Rec.Start_Price                   ||';'||  
                          Rec.Net_Price                     ||';'||  
                          Rec.Current_Billing_Account_Number||';'||  
                          Rec.Field_Service_Order_Status_I  ||';'||  
                          Rec.Nationality                   ||';'||  
                          Rec.Full_Name                     ||';'||  
                          Rec.First_Name                    ||';'||  
                          Rec.Middle_Name                   ||';'||  
                          Rec.Last_Name                     ||';'||  
                          Rec.Account_Name                  ||';'|| 
                          Rec.Account_Number                ||';'||  
                          Rec.Account_Type                  ||';'||  
                          Rec.Account_Type_I                ||';'||  
                          Rec.Account_Sub_Type              ||';'||  
                          Rec.Account_Sub_Type_I            ||';'||  
                          Rec.Tamayoz_Flag                  ||';'||  
                          Rec.Tamayoz_Type                  ||';'||  
                          Rec.Tamayoz_Type_I                ||';'||  
                          Rec.Company_Id_Type_I             ||';'||  
                          Rec.Company_Id_Type               ||';'||  
                          Rec.Commercial_Registration_Number||';'||  
                          Rec.Customer_Value                ||';'||  
                          Rec.Customer_Age_Value            ||';'||  
                          Rec.Payment_Value                 ||';'||  
                          Rec.Blacklist_Flag                ||';'||  
                          Rec.Avg_Usage                     ||';'||  
                          Rec.Order_Day_Name                ||';'||  
                          Rec.Order_Day_Of_Week             ||';'||  
                          Rec.Order_Week                    ||';'||  
                          Rec.Order_Day_Of_Month            ||';'||  
                          Rec.Order_Month                   ||';'||  
                          Rec.Order_Month_Name              ||';'||  
                          Rec.Order_Year                    ||';'||  
                          Rec.Order_Quarter                 ||';'||  
                          Rec.Order_Date_I                  ||';'||  
                          Rec.Region                        ||';'||  
                          Rec.City                          ||';'||  
                          Rec.Region_I                      ||';'||  
                          Rec.Source_I                      ||';'||  
                          Rec.Source                        ||';'||  
                          Rec.Alternate_Phone               ||';'||  
                          Rec.Service_Request_Number        ||';'||  
                          Rec.Import_Id                     ||';'||  
                          Rec.Import_Date                   ||';'||  
                          Rec.Serial_No                     ||';'||  
                          Rec.Archive_File_Status           ||';'||  
                          Rec.Archive_Resone                ||';'||  
                          Rec.Archive_Serial                ||';'||  
                          Rec.Archive_Menu                  ||';'||  
                          Rec.Archive_Carton                ||';'||  
                          Rec.Archive_Date                  ||';'||  
                          Rec.Archive_Employee              ||';'||  
                          Rec.Archive_Backeg_Number         ||';'||  
                          Rec.Archive_Resept_Date           ||';'||  
                          Rec.Transaction_Type             */    ;
                     Client_Text_Io.Put_Line(In_File,Line);
                     I := I + 1;
                                       
                          End Loop;
                          Synchronize; 
                         end loop;
                   Client_Text_Io.Fclose(In_File);
                   
                   
                        Message('SUCCESSFUL');
                        Message(' ');
                  --=======================================================
                    Exception When Others Then
                       Message('Error Occured while exporting'||sqlerrm);
                       Message('Error Occured while exporting'||sqlerrm);   
                  End;
                  • 6. Re: Problem with exporting data
                    François Degrelle
                    Synchronize every 1000 records:
                    If mod(I,1000) = 0 Then synchronize; end if ;
                    Francois
                    • 7. Re: Problem with exporting data
                      ahmad
                      Thank you, may you please modify my code by putting your code ?

                      i need to be sure that it is in the right place.
                      • 8. Re: Problem with exporting data
                        François Degrelle
                        Put it inside a general loop through all the records.

                        Francois
                        • 9. Re: Problem with exporting data
                          ahmad
                          its hanged :(
                          Procedure Pu_Establishment_Prc Is
                           
                           Cursor vc_get_imported_Data Is
                           Select A.Employee_Name, A.Employee_Login, A.Employee_Position,
                                 A.Division_Organization_Name, A.Employee_Row_Id, A.Product_Line,
                                 A.Archive_Resept_Date, A.Transaction_Type
                            From Import_Stc_Daily_Reports_Dtl A, Shows_Users B
                            Where 1=1
                              And A.Employee_Login = B.User_No(+)
                              And B.User_Type(+) <> 6
                              And Nvl(B.Region_Code, -1)         = Nvl(:Blk_1.Region_Code, Nvl(B.Region_Code, -1))
                              And Nvl(B.Gen_Department_Code, -1) = Nvl(:Blk_1.Gen_Dept, Nvl(B.Gen_Department_Code, -1))
                              And Nvl(B.Department_Code, -1)     = Nvl(:Blk_1.Dept_Code, Nvl(B.Department_Code, -1))
                              And Nvl(B.Section_Code, -1)        = Nvl(:Blk_1.Section_Code, Nvl(B.Section_Code, -1))
                              And Nvl(B.Branch_Code, -1)         = Nvl(:Blk_1.Branch_Code, Nvl(B.Branch_Code, -1))
                              And A.Employee_Login               = Nvl(:Blk_1.Scr_User_No, A.Employee_Login)
                              And To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr') Between Nvl(:Blk_1.From_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr')) 
                                                                                         And Nvl(:Blk_1.To_Date, To_Date(Substr(A.Order_Date, 1, 10), 'Dd/Mm/Rrrr'))
                              --and rownum < 10010                                                                 
                          Order By Employee_Login;
                           
                           In_File Client_Text_Io.File_Type;
                           Lc$Fic                  Varchar2(10000) ;
                           Vn_Date                 Number;
                           Vv_File_Path            Varchar2(1000);
                           Line                    Varchar2(32767) ;--NCLOB;
                           i                       number;
                          Begin
                           Lc$Fic := 'Test_Report'||:Blk_1.Scr_Import_Id||'.csv';
                           Vv_File_Path := Webutil_File.File_Save_Dialog ('C:\', Lc$Fic ,'','Location to save file...') ;
                           In_File := Client_Text_Io.Fopen(Vv_File_Path,'W');
                           
                              I:=1; 
                              
                            For Rec In vc_get_imported_Data Loop
                                  
                                  If mod(I,1000) = 0 Then  
                                          
                              Line := 
                                  --Rec.Employee_Name                ||';'||  
                                  Rec.Employee_Login                 /*||';'||  
                                  Rec.Employee_Position             ||';'||  
                                  Rec.Division_Organization_Name    ||';'||  
                                  Rec.Archive_Backeg_Number         ||';'||  
                                  Rec.Archive_Resept_Date           ||';'||  
                                  Rec.Transaction_Type             */    ;
                               
                                  end if ;
                                  
                                  synchronize;
                                  
                             Client_Text_Io.Put_Line(In_File,Line);
                             
                             
                             I := I + 1;
                             
                                 end loop;
                           Client_Text_Io.Fclose(In_File);
                           
                           
                                Message('SUCCESSFUL');
                                Message(' ');
                          --=======================================================
                            Exception When Others Then
                               Message('Error Occured while exporting'||sqlerrm);
                               Message('Error Occured while exporting'||sqlerrm);   
                          End;
                          • 10. Re: Problem with exporting data
                            InoL
                            Client_Text_Io.Fopen(Vv_File_Path,'W');
                            Client_Text_Io.Put_Line(In_File,Line);
                            The whole idea of the previous posts by François and Craig is to NOT use webutil. You are still using it.... B-)
                            • 11. Re: Problem with exporting data
                              ahmad
                              yes, i want to use it...
                              • 12. Re: Problem with exporting data
                                François Degrelle
                                Use Text_IO() to generate the file on the A.S then after Webutil to download it on the client machine.

                                Francois
                                • 13. Re: Problem with exporting data
                                  CraigB
                                  The whole idea of the previous posts by François and Craig is to NOT use webutil. You are still using it....
                                  I'm not saying "Don't use WebUtil", I'm saying +"Use it wisely."+ When writing a large number of records from Forms to the Client, WebUtil.Client_Text_IO is the wrong tool. Better to create the file on the App Server and then use WebUtil to transfer the file to the client.

                                  Craig...
                                  • 14. Re: Problem with exporting data
                                    ahmad
                                    Thanks for all...my problem has been resolved.