This discussion is archived
14 Replies Latest reply: Apr 12, 2013 11:31 PM by ahmad RSS

Problem with exporting data

ahmad Newbie
Currently Being Moderated
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çoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,
    <p>Read this article</p>
    Francois
  • 2. Re: Problem with exporting data
    ahmad Newbie
    Currently Being Moderated
    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çoisDegrelle Oracle ACE
    Currently Being Moderated
    Also try to execute a
    synchronize;
    instruction every 1000 exported lines...

    Francois
  • 4. Re: Problem with exporting data
    CraigB Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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çoisDegrelle Oracle ACE
    Currently Being Moderated
    Synchronize every 1000 records:
    If mod(I,1000) = 0 Then synchronize; end if ;
    Francois
  • 7. Re: Problem with exporting data
    ahmad Newbie
    Currently Being Moderated
    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çoisDegrelle Oracle ACE
    Currently Being Moderated
    Put it inside a general loop through all the records.

    Francois
  • 9. Re: Problem with exporting data
    ahmad Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    yes, i want to use it...
  • 12. Re: Problem with exporting data
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for all...my problem has been resolved.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points