Showing posts from November, 2020

Send an Oracle Database Table Data in CSV File as Mail Attachment using a Simple PL/SQL APIs

Here I am giving an example to send an oracle database table data in csv/text file as mail attachment using a simple PL/SQL APIs. Step 1:  Create a new procedure as below, which should take care of following actions,  Convert table data into csv file.  Save generated csv file to Oracle Directory.  Send generated csv file as an attachment. CREATE OR REPLACE  PROCEDURE table_data_to_csv_file (p_from IN VARCHAR2,                                                                 p_to   IN VARCHAR2,                                                                 p_cc   IN VARCHAR2,                                                                 p_bcc  IN VARCHAR2,                                                                 p_file_location IN VARCHAR2,                                                                 x_return_status out VARCHAR2,                                                                 x_return_message out VARCHAR2) AS   CURSOR cur_emp_data   IS     SELECT emp.empno,

Custom Action Menu in Oracle APEX Reports

Couple of days before, my client asked me to bring an action menu in interactive report instead of bringing multiple icons (buttons) in each row. I searched in google and found excellent blog post from John Snyders , where he explained the steps to bring custom action menu in oracle apex interactive report is amazing. He has done his job with lots of love. I tried his solution and it worked. Here I am giving an example to create custom menus in Oracle apex interactive report using JavaScript. This demo consists of 3 pages. The main page ( page 1 ) is an Interactive Report showing the departments from the  eba_demo_chart_dept  table. The report includes an Actions column with a menu button in it. This means there is a menu button in each row and the actions in that menu are specific to the department in the row.  Opening the menu gives you the option to Edit the department in a modal dialog page ( page 3 ) or go to an employee report page ( page 2 ) that shows all the employees from t

Number of Tables by their Size in Oracle Database

Here I am giving an example to find out number of tables by their size (Bytes, KB, MB, GB and TB) in oracle database. Query 1: [Bytes]  Number of all tables accessible to the  current user  in Oracle database by their size grouped into  predefined intervals . SELECT  size_interval ,         count ( * )   AS  no_of_tables    FROM   ( SELECT   DATA . segment_name table_name ,                CASE                 WHEN   DATA . bytes >  1000000000                 THEN   '1b Bytes and more'                 WHEN   DATA . bytes >  1000000                 THEN   '1m - 1b Bytes'                 WHEN   DATA . bytes >  1000                 THEN   '1k - 1m Bytes'                 WHEN   DATA . bytes >  100                 THEN   '100 - 1k Bytes'                 WHEN   DATA . bytes >  10                 THEN   '10 - 100 Bytes'                 ELSE   '0 - 10 Bytes'                END   AS  size_interval          FROM   ( SELECT  us . segme