Export Data from a Table to CSV in Oracle PL/SQL

In Oracle, the package UTL_FILE contains many procedures and functions for writing a text file. Below are the syntax details and the necessary steps to write a file:

Syntax and Steps to Write a File Using UTL_FILE
-- Declare a variable to store file type
n_file UTL_FILE.FILE_TYPE;
-- Open the file in Begin section, it will open the file and return the file handle into the variable n_file
n_file := UTL_FILE.FOPEN('DIR_OBJ', 'YourCSVFileName.csv', 'w', 4000);
-- Write a single or multiple lines
UTL_FILE.PUT_LINE(n_file, 'abc, xyz, xxx');
-- Close the file
UTL_FILE.FCLOSE(n_file);

Basic Example

Declare
   n_file Utl_File.File_Type;
Begin
   -- The directory object MY_DIR must be exist or create a new one
   n_file := Utl_File.Fopen('MY_DIR', 'myfile.csv', 'w', '4000');

   Utl_File.Put_Line(n_file, 'First line.');
   Utl_File.Put_Line(n_file, 'Second line.');
   Utl_File.Put_Line(n_file, 'Third line.');

   Utl_File.Fclose(n_file);
End;

As I mentioned in the above example, the directory object MY_DIR must exist. The directory object in Oracle is a reference to the physical directory on the server. The following is an example of creating a directory object in Oracle:

-- Windows example
CREATE OR REPLACE DIRECTORY CSVDIR AS 'd:\oracle\csvfiles';
-- Linux example
CREATE OR REPLACE DIRECTORY CSVDIR AS '/usr1/oracle/csvfiles';
To learn more about the directory object in Oracle, check this link.



Export Data from a Table to CSV in Oracle Example
The following is an example of a stored procedure in Oracle, which will export the data from the EMP table to a CSV file:



Create Or Replace Procedure exp_emp_data Is

    n_file     utl_file.file_type;
    v_string   Varchar2(4000);
 
  -- get the data using cursor
  Cursor c_emp Is
    Select
        empno,
        ename,
        deptno,
        sal,
        comm
    From
        emp;

Begin
    n_file := utl_file.fopen('CSVDIR', 'empdata.csv', 'w', 4000);

-- if you do not want heading then remove below two lines
    v_string := 'Emp Code, Emp Name, Dept, Salary, Commission';
    utl_file.put_line(n_file, v_string);
   
    -- open the cursor and concatenate fields using comma
    For cur In c_emp Loop
        v_string := cur.empno
                    || ','
                    || cur.ename
                    || ','
                    || cur.deptno
                    || ','
                    || cur.sal
                    || ','
                    || cur.comm;
       
        -- write each row
        utl_file.put_line(n_file, v_string);
    End Loop;
   
    -- close the file
    utl_file.fclose(n_file);
Exception
    When Others Then
        -- on error, close the file if open
        If utl_file.is_open(n_file) Then
            utl_file.fclose(n_file);
        End If;
End;


Now your stored procedure has been created, execute it to export the data:

Begin
    exp_emp_data;
End;

weblogic install in oracle 12C

1.install fmw infrastructure --
2.install Forms & Reports dev --
3.install rcu --
4.install config --

#
D:\Middleware\JDK\bin\java -jar G:\Oracle_Software\wls\fmw_12.2.1.3.0_infrastructure.jar
G:\Oracle_Software\wls\setup_fmw_12.2.1.3.0_fr_win64.exe
D:\Middleware\Product\oracle_common\bin\rcu.bat
C:\Oracle\Middleware\Oracle_Home\oracle_common\common\bin\config.cmd


Oracle Forms-12.2.1[Forms]
Oralce HTTP Server(Collected)-12.2.1[ohs]
oracle reports Application-12.2.1[Reports]
oracle reports bridge-12.2.1[reportsBridgeComponebt]
oracle reports server-12.2.1[ReportsServerComponent]
oracle reports tools-12.2.1[ReportsToolsComponent]


# Create the components

1- Execute wlst.cmd/wlst.sh from ORACLE_HOME/oracle_common/common/bin

2- Connect to AdminServer.

    connect("weblogic","weblogic1","localhost:7001")

3- Run the following wlst command.

    createReportsToolsInstance(instanceName='reptools1',machine='AdminServerMachine')


#default.env
COMPONENT_CONFIG_PATH=D:\Middleware\Config\domains\base_domain\config\fmwconfig\components\ReportsToolsComponent\reptools1


#rwservlet.conf

   <webcommandaccess>L2</webcommandaccess>

Change Admin Workspace Pass in Oracle Apex

Uninstall Oracle Apex

Run oracle report 12c

1#

declare
v_show_document VARCHAR2 (2000) := 'http://localhost:9002/reports/rwservlet?';
v_connect VARCHAR2 (200)  := 'userid=jayson/jsn@orcl';
v_report_server VARCHAR2 (30)   := 'rep_wls_reports_abdulquium';
v_report_name VARCHAR2(100)   := 'E:\JAYSON\JNPBD\Reports\jnpbd_standard_batrch.rdf';
v_format VARCHAR2(12)    := 'PDF'; -- PDF or SPREADSHEET
begin
v_show_document := v_show_document
         || v_connect
         -- Report server
         || '&server='
         || v_report_server
         -- Report name
         || '&report='||v_report_name
         -- Reports parameters
         || '&destype=CACHE'
         || '&desformat='||v_format
         || '&paramform=no'
         || '&P_SBD_NO='||:JNPBD_STN_MAST.SBD_NO --Parameter Passed to report
         /*
         || '&date_from='||:ACCOUNTS_REPORTS_DATE.date_from --Parameter Passed to report
         || '&date_to='||:ACCOUNTS_REPORTS_DATE.date_to --Parameter Passed to report
         || '&acc_id_from='||:ACCOUNTS_REPORTS.cb_acc_id1 --Parameter Passed to report
         || '&acc_id_to='||:ACCOUNTS_REPORTS.cb_acc_id2 --Parameter Passed to report
         || '&Catagory_from='||:ACCOUNTS_REPORTS.cb_acc_id1 --Parameter Passed to report
         || '&Catagory_to='||:ACCOUNTS_REPORTS.cb_acc_id2 --Parameter Passed to report
         || '&Bl_noo='||:ACCOUNTS_REPORTS.BL_NO --Parameter Passed to report
         || '&bl_lot_noo='||:ACCOUNTS_REPORTS.LOT_NO --Parameter Passed to report
         || '&Lot_no_sm='||:ACCOUNTS_REPORTS.Lot_no_sm --Parameter Passed to report
         || '&SUPPLIERR='||:ACCOUNTS_REPORTS.SUPPLIERR --Parameter Passed to report
|| '&cleared_byy='||:ACCOUNTS_REPORTS.CLEARED_BY --Parameter Passed to report
|| '&chassis_noo='||:ACCOUNTS_REPORTS.chassis_no --Parameter Passed to report
|| '&codee='||:ACCOUNTS_REPORTS.chassis_code --Parameter Passed to report
|| '&modell='||:ACCOUNTS_REPORTS.modell --Parameter Passed to report
|| '&yardd='||:ACCOUNTS_REPORTS.yard_name --Parameter Passed to report
|| '&colorr='||:ACCOUNTS_REPORTS.colorr --Parameter Passed to report
|| '&Companyy='||:ACCOUNTS_MISC.company --Parameter Passed to report
|| '&Remarks='||:ACCOUNTS_REPORTS.LOT_NO_SM --Parameter Passed to report

*/


         ;
web.show_document(v_show_document);
end;

2#

PROCEDURE PRINT_PROC IS

    

      repid               REPORT_OBJECT;

      v_rep               VARCHAR2(1000);

      rep_status          varchar2(2000);

    vjob_id             VARCHAR2(1000) :='';

    v_url               varchar2(1000) := '';

    pl_id               ParamList; 

    pl_name             VARCHAR2(1000) := 'RP';

    v_REPORT_DESNAME    varchar2(2000) := '';

    v_rptserver    varchar2(1000):=:global.RWS;

      v_host         varchar2(1000):=:global.IP;

      v_path         varchar2(1000):=:GLOBAL.MENU_PATH; 

   

BEGIN

      pl_id := Get_Parameter_List(pl_name); 

    

    IF Id_Null(pl_id) THEN 

      null;

    ELSE

    destroy_Parameter_List(pl_name);

    END IF;

   

      pl_id := Create_Parameter_List(pl_name);

     

    repid := find_report_object('REPORT46');

        

    Add_Parameter(pl_id, 'P_SBD_NO', TEXT_PARAMETER,:JNPBD_STN_MAST.SBD_NO);      

    --Add_Parameter(pl_id, 'P_PRODUCT_CD', TEXT_PARAMETER, :JNPBD_STN_MAST.PRODUCT_CD);

     

   --if :REPOT_LIST=1 then

       SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, 'E:\JAYSON\JNPBD\Reports\jnpbd_standard_batrch.RDF');  

   /*

   elsif :REPOT_LIST=2 then

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, v_path||'\BD_B\REPORTS\BD_B_MFG_PROCESS_PRODUCT_WSIE.jsp');  

    elsif :REPOT_LIST=3 then

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, v_path||'\BD_B\REPORTS\BD_B_MFG_PRECUTION.jsp');  

    */

   --end if;

   

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_EXECUTION_MODE, BATCH);

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_COMM_MODE, SYNCHRONOUS);

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_SERVER, v_rptserver);

    SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESTYPE, CACHE);

    -- SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT,:REPOT_FMT);

      SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT, 'PDF');

      v_rep := RUN_REPORT_OBJECT(repid,pl_name);

    rep_status := REPORT_OBJECT_STATUS(v_rep);

    vjob_id := substr(v_rep, 6, length(v_rep));

    vjob_id := substr(v_rep,instr(v_rep,'_',-1)+1);

   

    

    if rep_status = 'FINISHED' then

      

    message('Report Completed'||v_rep, no_acknowledge);

       

      WEB.SHOW_DOCUMENT('http://'||v_host||'/reports/rwservlet/getjobid'||ltrim(vjob_id,'_')||'?'||'server='||v_rptserver,'_blank');

        

      else

       

      message('Error when running report.'||v_rep, no_acknowledge);

    

    end if;

EXCEPTION

    WHEN OTHERS THEN

   

    MESSAGE('Error '||v_rep||error_text,no_acknowledge);

   

END;

=================



/* Formatted on 1/18/2013 7:01:45 PM (QP5 v5.114.809.3010) */
PROCEDURE view_report
IS
v_canteenlocation varchar2 (500) := NULL;
v_item_name   varchar2 (500);
PMWHERE varchar2(1000);
V_URL  varchar2(1000);

v_report_server_name varchar2(1000);
v_ip_local  varchar2(1000);
v_ip_real  varchar2(1000);

v_item_name_t   varchar2 (500);
   v_path          varchar2 (500);
   v_sub_path      varchar2 (50);
   v_format        varchar2 (20):='PDF'; --enhancedspreadsheet
   pm_where        varchar2 (3000);
   v_char_val      varchar2 (100);
   v_date_val      date;
 
     v_char_val_t      varchar2 (100);
 
   v_date_val_char   varchar2 (100);
   v_report_id     number;
   v_rdf_name      varchar2 (100);
   v_report_name   varchar2 (100);

   CURSOR c1 (p_report_id IN number)
   IS
      SELECT   b.report_id ,
      b.parameter_id parameter_id_pk ,
               a.parameter_title parameter_name,
               b.parameter_title,
               DEFAULT_VALUE,
               mandatory,
               lov_button,
               lov_id,
               a.parameter_datatype_id,
           
               tool_tips,
               b.parameter_datatype_id||'0'||sl parameter_id
        FROM   report_parameter_list a, report_parameter b
       WHERE   a.parameter_id = b.parameter_id
       and  a.parameter_datatype_id = b.parameter_datatype_id
               AND b.report_id = p_report_id
               order by to_number(b.parameter_id||'0'||sl) asc;
             
             
  pl_id ParamList;
  vloc varchar2(100):=:global.r_path;

BEGIN
   --- collect report name


   v_report_id := :obj_id_R  ;
 
   select max(obj_name ) into v_report_name
   from sm_object_list
   where obj_id=v_report_id;
 
 

 
 
 
 
 
 
 
 
 


  pl_id := Get_Parameter_List('tmpdata');
  IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
  END IF;
  pl_id := Create_Parameter_List('tmpdata');                             


      Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');




   FOR i IN c1 (v_report_id)
   LOOP
   
    v_item_name := 'PARAMETER_LIST.P_' || i.parameter_id;
      v_item_name_t := 'PARAMETER_LIST.TP_' || i.parameter_id;
      IF i.parameter_id BETWEEN 300 AND 399
      THEN
     
           --- retrive item value
         v_date_val :=
           GET_ITEM_PROPERTY (v_item_name,
                               database_value);
                             
                             
                         
                           
                             v_date_val_char:=to_CHAR(v_date_val,'DD-MON-RR');
                           
                             -----
                         
                           

         IF i.mandatory = 1 AND TRUNC(v_date_val) IS NULL
         THEN
            msg(   'You must provide value in the field  '''
                || i.parameter_title
                || '''  for the selected report');
            GO_BLOCK ('PARAMETER_LIST');
            GO_ITEM (v_item_name);
            RAISE form_trigger_failure;
         END IF;
       
       
          scs.rp_parameter_update.ins_report_param_default_value (
   :global.userid                  ,
  i.report_id               ,
  i.parameter_id_pk,         
  v_date_val,
   null,
   v_char_val_t);
 Add_Parameter(pl_id,i.parameter_name ,TEXT_PARAMETER,v_date_val);

 if i.parameter_name is not null and v_date_val is not null then

          PMWHERE := RTRIM (PMWHERE||LTRIM (RTRIM ('''&''', ''''), '''')||i.parameter_name|| '='|| v_date_val|| '||','||');
         
 end if;




       
      ELSE
     
      --- retrive item value
         v_char_val :=
            GET_ITEM_PROPERTY (v_item_name,
                               database_value);
  v_char_val_t :=
            GET_ITEM_PROPERTY (v_item_name_t,
                               database_value);


 ---- mandatory paprameter check

         IF i.mandatory = 1 AND v_char_val IS NULL
         THEN
            msg(   'You must provide value in the field  '''
                || i.parameter_title
                || ''' for the selected report');
            GO_BLOCK ('PARAMETER_LIST');
            GO_ITEM (v_item_name);
            RAISE form_trigger_failure;
         END IF;
       
       
       
          scs.rp_parameter_update.ins_report_param_default_value (
   :global.userid                  ,
  i.report_id               ,
  i.parameter_id_pk,         
 null,
   v_char_val,v_char_val_t);
          Add_Parameter(pl_id,i.parameter_name ,TEXT_PARAMETER,v_char_val);

 if i.parameter_name is not null and v_char_val is not null then

          PMWHERE := RTRIM (PMWHERE||LTRIM (RTRIM ('''&''', ''''), '''')||i.parameter_name|| '='|| v_char_val|| '||','||');
         
 end if;

         
      END IF;
     
     
       
   END LOOP;

Add_Parameter(pl_id,'p_userid' ,TEXT_PARAMETER,:global.userid);

if :global.dpcode <> 'FAC' THEN

v_canteenlocation :='HO';
ELSE
v_canteenlocation :='FACTORY' ;
END IF;







       
       
       --  PMWHERE :=RTRIM (   PMWHERE|| LTRIM (RTRIM ('''&''', ''''), '''')|| I.RDF_PARAMETER|| '='|| V_FORM_FIELD || '||','||');   
       
       
        --   PMWHERE := PMWHERE||'&p_user_id='||:GLOBAL.g_user_id||'&p_obj_id='||V_REPORT_ID;
        select max(name  ) ,max(ip_local  ),max(ip_real  ) into v_report_server_name,v_ip_local,v_ip_real
   from report_server_name
   where is_active=1;
 
   if v_report_server_name is null then
    v_report_server_name :='rep_wls_reports_webserver12c';
   end if;
 
 

   
       if v_ip_real is null then
    v_ip_real :='180.148.211.170';
   end if;
 
 
 
  V_URL :=             'http://'||v_ip_real||':9002/reports/rwservlet?userid='||:dbuser||'/'||:dbpass||'@'||:dbname||'&server='||v_report_server_name
                  || '&desformat='
                  || V_FORMAT
                  || '&destype=cache&report='
                  || 'Z:\Stepserp\'||:path
                  || '\'
                  || v_report_name
                  || '.rdf'
                  || LTRIM (PMWHERE, '''');
                 
                  :o_msg:='';
                  --:o_msg:=v_url;
                  --message('url '||v_url);
 
  web.show_document(v_url);

 
   --- Report Calling     

               
               
               


--Add_Parameter(pl_id,'P_canteenlocation' ,TEXT_PARAMETER,v_canteenlocation);

   ---   report calling
  /*
   
Run_Product(REPORTS, :global.r_dir||v_report_name, SYNCHRONOUS, RUNTIME,
           FILESYSTEM, pl_id, NULL);
         
           */
         
         
  exception
when others then
:o_msg:=sqlerrm;       
         
         
         
 
END;

FRM-41214 When trying to run Reports from Forms 12c Using RUN_REPORT_OBJECT

The reason of these messages is that a new environment variable is needed in Forms 12c to call Reports using RUN_REPORT_OBJECT.

SOLUTION:

Add the following variable to your default.env or whatever env file you may be using:

COMPONENT_CONFIG_PATH=$DOMAIN_HOME/config/fmwconfig/components/ReportsToolsComponent/your_reports_tools_component_name

Note 1: Change your_reports_tools_component_name with your actual reports component name.

Note 2: Change $DOMAIN_HOME or %DOMAIN_HOME% with your real value.

search default.env

 set below section of default.env

# System settings

# ---------------

# You should not normally need to modify these settings

# 

FORMS=D:\Oracle\Middleware\Product\forms

COMPONENT_CONFIG_PATH=D:\Oracle\Middleware\Config\domains\base_domain\config\fmwconfig\components\ReportsToolsComponent\reptools1

apex audit code

-- Find most accessed pages
SELECT application_id,
application_name,
page_id,
page_name,
SUM (page_id) page_hit_count
FROM apex_workspace_activity_log
GROUP BY application_id,
application_name,
page_id,
page_name
ORDER BY SUM (page_id) DESC



-- Find slowest pages
-- Note: This depends on how you calculate slow
SELECT application_id,
application_name,
page_id,
page_name,
ROUND (AVG (elapsed_time), 5) avg_elapsed_time,
SUM (page_id) page_hit_count,
MEDIAN (elapsed_time) median_elapsed_time
FROM apex_workspace_activity_log
GROUP BY application_id,
application_name,
page_id,
page_name
ORDER BY 5 DESC


-- The following query identifies when an error occurs at the page or region level:
SELECT *
FROM apex_workspace_activity_log
WHERE error_message IS NOT NULL


/*After each bad login attempt, you can
return to the Login page and see an updated Login Attempts report.
To build this report on page 101, the Login page, create a report with the following query:
*/
SELECT user_name,
authentication_method,
access_date,
authentication_result,
custom_status_text
FROM apex_workspace_access_log
WHERE application_id = :app_id
ORDER BY access_date DESC;

/*

looking for item_name into all apps 

*/

SELECT workspace,application_id,application_name,page_id,page_name,item_name,item_help_textFROM apex_application_page_itemsWHERE item_name LIKE '%xxxxxxx%'

Dynamic menu in oracle apex

Create table tree_menu (
  parent_node integer,
  child_node integer,
  menu_desc varchar2(50),
  menu_type varchar2(20),
  page_no integer,
icon_img  varchar2(100)
);


Alter Table Tree_Menu add constraint pk_treemenu
primary key (menu_type, parent_node);


select     level,
        menu_desc as label,
        decode(page_no, null, null, 'f?p=&APP_ID.:'||"PAGE_NO"||':&APP_SESSION.')  as target,
'NO' is_current,
                   icon_img as image
            --decode(page_no, null, 'fa-folder-o', 'fa-file-text') as image
   from (select menu_desc,
parent_node,
child_node,
page_no,
icon_img ,
menu_type
from TREE_MENU T where menu_type = 'HOME')
  start with child_node is null
connect by prior parent_node = child_node
union all
select     level,
        menu_desc as label,
        decode(page_no, null, null, 'f?p=&APP_ID.:'||"PAGE_NO"||':&APP_SESSION.')  as target,
'NO' is_current,
          icon_img as image
        --decode(page_no, null, 'fa-folder-o', 'fa-file-text') as image
   from (select menu_desc,
parent_node,
child_node,
page_no,
icon_img ,
menu_type
from TREE_MENU T where menu_type = 'MAIN')
  start with child_node is null
connect by prior parent_node = child_node

name list

নবদিগন্তের অভিযাত্রা