Muhammad Nadeem Chaudhry's Blog


Installing APEX 4.2.5 in Oracle Database 11gR2

Posted in Uncategorized by Muhammad Nadeem Chaudhry on August 25, 2014

Time Dimension – SQL Query

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 30, 2014

This query generates a relational table storing the information needed to create an OLAP time dimension.

CREATE TABLE TIMES AS
SELECT
— DAY LEVEL
TIME_ID AS DAY_ID,
INITCAP(TO_CHAR(TIME_ID,’FMMONTH DD, YYYY’)) AS DAY_DESC,
INITCAP(TO_CHAR(TIME_ID, ‘FMDAY’)) AS DAY_NAME,
TO_NUMBER(TO_CHAR(TIME_ID – 1, ‘D’)) AS DAY_OF_WEEK,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DD’)) AS DAY_OF_MONTH,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DDD’)) AS DAY_OF_YEAR,
1 AS DAYS_IN_DAY,

– MONTH LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-M”MM’) AS MONTH_ID,
TO_CHAR(TIME_ID, ‘FMMONTH YYYY’) AS MONTH_DESC,
DECODE(MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4), 0, 4, MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4)) AS MONTH_OF_QUARTER,
TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)) AS MONTH_OF_YEAR,
TO_CHAR(TIME_ID, ‘FMMONTH’) AS MONTH_NAME,
LAST_DAY(TIME_ID) AS END_OF_MONTH,
TO_CHAR(LAST_DAY(TIME_ID),’DD’) AS DAYS_IN_MONTH,

– QUARTER LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-Q”Q’) AS QUARTER_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMQTH “QUARTER,” YYYY’)) AS QUARTER_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘Q’)) AS QUARTER_OF_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1 AS END_OF_QUARTER,
(TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1) – (TRUNC(TIME_ID, ‘Q’) – 1) AS DAYS_IN_QUARTER,

– YEAR LEVEL
TO_NUMBER(TO_CHAR(TIME_ID, ‘YYYY’)) AS YEAR_ID,
(TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1) – (TRUNC(TIME_ID, ‘YYYY’) – 1) AS DAYS_IN_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1 AS END_OF_YEAR,

– THIS IS THE SAME FOR ALL WEEKS
7 AS DAYS_IN_WEEK,

– CALENDAR WEEK LEVEL
TO_CHAR(TIME_ID, ‘IYYY’) || ‘-CW’ || TO_CHAR(TIME_ID, ‘IW’) AS CAL_WEEK_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMIWTH “WEEK OF” IYYY’)) || ‘, ENDING ‘ || TO_CHAR(TRUNC(TIME_ID + 7, ‘IW’) – 1, ‘FMMONTH DD, YYYY’) AS CAL_WEEK_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘IW’)) AS CAL_WEEK_OF_YEAR,
TRUNC(TIME_ID + 7, ‘IW’) – 1 AS END_OF_CAL_WEEK

FROM (SELECT TO_DATE(’01/01/2008′,’MM/DD/YYYY’) + ROWNUM – 1 AS TIME_ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1492)     — ADD ONE MORE DAY BY SUBTRACTING MIN-MAX DATES
ORDER BY TIME_ID;

Secure Database Passwords in an Oracle Wallet

Posted in Uncategorized by Muhammad Nadeem Chaudhry on September 10, 2013

Read article:

http://www.idevelopment.info/data/Oracle/DBA_tips/Security/SEC_15.shtml

Moreover,

SQL> connect /@orcl

impdp /@orcl

Difference between Oracle Database 10g & 11g

Posted in Uncategorized by Muhammad Nadeem Chaudhry on August 28, 2013

Please read:

Difference between Oracle 10g and 11g

Oracle Datapump Parameter REMAP_SCHEMA

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 3, 2013

Loads all objects from the source schema into a target schema.

Syntax
REMAP_SCHEMA=source_schema:target_schema

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:

expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott schema after the import by using the existing password (without resetting it).If user scott does not exist before you execute the import operation, import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.

Configuring Oracle Workflow for OWB

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 1, 2013

Send Report via EMail Attachment in Oracle Forms 10g

Posted in Uncategorized by Muhammad Nadeem Chaudhry on May 31, 2013

DECLARE
report_id REPORT_OBJECT := find_report_object(‘MYREPORT’);
report_message VARCHAR2(100) :=”;
rep_status VARCHAR2(100) :=”;
v_error Exception;
thelist paramlist;
BEGIN
— Create parameters list
thelist:= Get_Parameter_List(‘rep_paramlist’);
IF NOT Id_Null (thelist) THEN
DESTROY_PARAMETER_LIST(thelist);
END IF;
thelist := Create_Parameter_List (‘rep_paramlist’);

— Add report paremeters
ADD_PARAMETER(thelist, ‘FROM’,TEXT_PARAMETER, ‘abc@abc.com’);
ADD_PARAMETER(thelist, ‘SUBJECT’,TEXT_PARAMETER, ‘MRF NO:’||:GLOBAL.SIFNO);
ADD_PARAMETER(thelist, ‘PROJECT_NBR’,TEXT_PARAMETER, 968404);

— Set report object properties
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME, ‘C:\workingDirectory\MRF.RDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER, ‘rep_XP_oracleasFRM’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE, MAIL );
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT, ‘PDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,’paramform=no mrfno=’||:GLOBAL.SIFNO);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESNAME,’abc@abc.com’ );

— Check report status
report_message := run_report_object (report_id, thelist);
rep_status := report_object_status (report_message);
BEGIN
WHILE
rep_status in (‘RUNNING’,’OPENING_REPORT’,’ENQUEUED’)
LOOP
rep_status := report_object_status(report_message);
END LOOP;
EXCEPTION
WHEN v_error then
message(‘Error in sending Action Checklist email.’|| rep_status);
END;
END;

Moving ASM Database Files from one Diskgroup to Another

Posted in Uncategorized by Muhammad Nadeem Chaudhry on April 9, 2013

Configure Oracle Application Express (APEX) in Oracle 11gR2

Posted in Uncategorized by Muhammad Nadeem Chaudhry on September 26, 2012

In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.

1. To configure the embedded PL/SQL gateway:

Go to the $ORACLE_HOME/apex directory.

2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.

SYS AS SYSDBA@DB11G> @apxconf

PORT———-8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.

Default values are in brackets [ ].

Press Enter to accept the default value

Enter a password for the ADMIN user [] admin_password

Enter a port for the XDB HTTP listener [ 8080]

…changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

…changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

3. Unlock the ANONYMOUS account.

SYS AS SYSDBA@ DB11G > ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

4. Enable Oracle XML DB HTTP server

SYS AS SYSDBA@ DB11G > EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SYS AS SYSDBA@ DB11G > COMMIT;

Commit complete.

5. We’re now ready to access APEX.

http://host:port/apex

http://host:port/apex/apex_admin — for admin page

Port in this case is 8080 which is the default.

Upgrade Oracle Database from 10g to 11g with Data Pump

Posted in Uncategorized by Muhammad Nadeem Chaudhry on August 27, 2012

As described in “Oracle Database Upgrade Guide 11g Release 1 (11.1)“, there are three upgrade methods offered while you want to upgrade database from 10g to 11g.

  1. Database Upgrade Assistant (DBUA)
  2. Manual Upgrade (Oracle provided scripts)
  3. Export/Import (exp/imp or expdp/impdp)

Besides benefits mentioned in above upgrade document, I’m thinking we are beneficial from using export/import approach. The reasons we decided using this method are:

  • We want to set up identical testing databases on development server first
  • We want to keep original production databases unchanged anyway for safety reason

With this approach, we first installed Oracle 11g software on development server and created empty database by using provided standard template New_Database.dbt, which could be found at $ORACLE_HOME/assistants/dbca/templates. After that, we followed the following steps to move data from 10g database to newly created 11g database.

Step 1: Pre-create tablespaces on target 11g database

Step 2: Export full database of source 10g database

expdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=export.log

Step 3: Copy dumpfile over network to development server

Step 4: Grant IMPORT_FULL_DATABASE system privilege to user SYSTEM of target 11g database

Step 5: Import full database to target 11g database

Note: If you have co-existing oracle home/version on development server, please make sure switching to Oracle 11g environment, which will assure to use data pump at 11g version to import data.

Impdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=import.log

Next Page »

Follow

Get every new post delivered to your Inbox.