Showing posts from 2015

Generate DDL source code using Oracle SQL

Here I am giving an example to  generate DDL (Data Definition Language) source code using Oracle SQL. One of my client wants to generate all the DDL source code for some database objects . So i just found the little piece of code to create DDL source. Database Objects are,   Ø    Sequence Ø    Table Ø    Index Ø    View Ø    Database Link Ø    Materialized view Ø    Function Ø    Procedure Ø    Package Ø    Package Body Oracle has provided default package  dbms_metadata   to generate DDL source code, we can make use of it. SQL Query: SELECT object_type,                dbms_metadata.get_ddl (REPLACE(object_type,' ','_'), object_name, owner) AS ddl_source     FROM all_objects  WHERE object_type IN ('SEQUENCE', 'TABLE', 'INDEX', 'VIEW', 'PACKAGE',                                          'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'MATERIALIZED VIEW',                  

Print setting/configuration in Oracle APEX to use BI Publisher

Navigation with Admin Credentials: .  Access the Administration Services home page.  Select Manage Service.  Select Instance Settings, under Manage Environment Settings.  Click Report Printing to focus on just the Report Printing attributes. Attributes Setting:  Oracle BI Publisher: Advanced Support.  Print Server Protocol: HTTP or HTTPS (depends on your configuration).  Print Server Host Address: The host-name for the machine where you installed BI Publisher - (  e.g.  Print Server Port: The port given at the end of the BI Publisher installation -  (e.g. 9704).  Print Server Script: /xmlpserver/convert. That's it.  Now you can use BI Publisher as your report server.

Setup a common page refresh option in Oracle APEX 4.2 using JS

Objective: To setup a common page refresh option in application, i nstead of making page refresh option in each page. Solution:     Step 1: Upload a button picture. Shared Components => Images => Create => Upload Step 2: Create a new navigation bar. Shared Components => Navigation Bar Entries => Create Image – let it empty Icon Subtext - <img src="#APP_IMAGES#page_refresh.png" title="Refresh"> Icon Image Alt : “Refresh” Image Height : 40 Image Width: 30 Target Type : URL URL Target : javascript:window.location.reload(false); Note: It will just refresh the page (No reload).

Viewing APEX Mail Log & Queue

Run below query in SQL Workshop  SELECT * FROM apex_mail_log ORDER BY last_updated_on DESC; Above said query, used to find the apex mail log. This query will find the mails in apex mail queue. SELECT * FROM apex_mail_queue ORDER BY last_updated_on DESC; We can push apex mail by apex_mail.push_queue exec apex_mail.push_queue; OR BEGIN   apex_mail.push_queue (p_smtp_hostname => 'IP_ADDRESS',                                           p_smtp_portno      => 'PORT_NUMBER'                                         ); END;

APEX Reports with dynamic help text in Oracle APEX 4.2 using CSS

Objective : To show dynamic help text in APEX reports (Interactive/classical) in Oracle APEX 4.2 using CSS. Solution: Step 1:  Create Interactive report Step 2:  Use below query in IR Region source. SELECT   * FROM   (     SELECT       ROWNUM level_value ,       upper(target) target,       started_by     FROM       (         SELECT           ('<span style="cursor:help" title="'           || upper(reportname)           || '">'           || (             CASE               WHEN aa.sub_pageid !=:app_page_id               THEN '<a href="'                 ||'f?p=&APP_ID.:'                 ||aa.sub_pageid                 ||':'                 || :app_session                 ||':::'                 ||aa.sub_pageid                 ||':">'                 ||initcap(aa.subtabname)                 ||' </a>'               ELSE '<

Capture Log (Login, Logout, Session, System) Details in Oracle Apex 4.2

Objective: This post will explain you about, how to capture use login, logout and session out time in Oracle APEX applications using security attributes. Section 1:  To Capture User Login Details: User Log Reports provides the detailed information about the users' login details along with their history. These reports are more accurate and also provides the details of the user, their logon time, logoff time, the computer from which they logged on, the domain controller they reported, etc., along with their logon history. It also provides  the details of Currently Logged On Users and Frequently Logged-on Users. The following information can be captured in the Database. 1. Login Date 2. Login Time 3. Session Id 4. System IP Address 5. Host Name 6. User Name Step 1:   Table Creation.  <<table_name>> (column_names)   {Based on the column Requirement} Step 2:   Creation of Page Hidden Items. Create Page Hidden Items P0_SESSION