About Oracle and Oracle ā'pěks (#orclapex)

Couple of days before i had an interesting discussion with my client related to  Oracle external tables , Client: Is there any way to read the data from flat file(s) which are stored outside the database? Raja: Could you please explain in more detail? Client: Sure. We have data analytics team, what they will do is, they will export the file(s) (.csv/.txt) from their system and push it to our file system ( Oracle directory ).  They have a job to push their files to our file system, which will run in predefined interval. Once it's uploaded we should read the file(s) data and display it in our system. Raja: Yes! it's very much possible, if we go with O racle external table . Client: What is that? Raja:  It can be used for query, join and sort operations. External tables allow oracle to query data that is stored outside the database in flat files. Once it's running we can create views against it. Client: Oh! Sounds interesting. That's what i needed. Is there any limit

Oracle APEX Interactive Report has default search option, where we can search the values which are exists in the report. Here I am giving an elegant method to highlight the cell of interactive report based on search criteria. Step 1:  Create a new blank page. Step 2:  Create a new Interactive Report region to the page. Step 3:  Set  CSS Classes  "data-highlighter" to the region. Fig 1: Set CSS Classes Step 4: Create a class by adding below CSS to the page in inline CSS section. It will change the style of report cells. Go to   Page => CSS => Inline . highlight- data  {     font-weight: bold !important ;      background -color: #ffecb4 !important ; } Step 5: Get a class for IR search component by following below steps. Place a mouse cursor on IR search component. Right click a mouse (The right mouse button is often used to open contextual menus, which are pop-up menus that change depending where you click) and select the menu " Inspect " . DevTools pop-up come

Here I am giving an example to display user rating icons dynamically in oracle apex report. Step 1:  Create a new blank page. Step 2: Function Call:  Create a function  get_user_ratings.  If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query. Sample function in a package called  FXGN_GENERAL create   or   replace   FUNCTION  get_user_ratings ( p_value  IN   NUMBER )      RETURN   VARCHAR2    IS     l_return      VARCHAR2   ( 4000 );     l_checked     VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:orange;"></span>' ;     l_un_checked  VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:grey;"></span>' ;     l_max_value   NUMBER         :=  5 ;     l_remaining   NUMBER ;    BEGIN      IF  p_value  BETWEEN  1  AND   5   THEN   -- Check given values are in the range       l_remaining := l_max_value - p_value ;       

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,

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

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