Shaheer Badar

Set the Urdu Supported Character Set in Oracle Database

2012-02-05T21:56:00.000-08:00

Last week my Team Lead gave me the task to Store Urdu in Oracle Database (for sending SMS). For this purpose I have created a database through Database Configuration Assistant (dbca) and on Initialization Parameters step picked the Use Unicode (AL32UTF8). Setting character set to Unicode (AL32UTF8) enables you to store multiple language groups. Set the National Character Set AL16UFT16-Unicode UTF-16 Universal Character Set as shown below.

After database creation set the NLS_LANG parameter of Oracle Database on Window’s Registry as shown below.

Data value should be AMERICAN_AMERICAN.AL32UTF8. After this restart the Services of Oracle Database and TNS Listener.

To verify the NLS Database Parameters execute the following query.
select * from nls_database_parameters where parameter = any('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

To store the Urdu character sets I set the column datatype NVARCHAR and completed the first face of my project task successfully.
Unicode supported datatypes in Oracle Databse.
1) NCHAR
2) NVARCHAR
3) NCLOB

How to Identify Oracle Production Exam and Beta Exam

2012-02-01T18:14:00.000-08:00

Oracle Certification Beta Exam begins with ‘1Z1’ and Oracle Certification Production Exam begins with ‘1Z0’. Both versions of the exam have the same suffix. For example, a beta version of Oracle Fusion Middleware 11g Build Applications exam is 1Z1-151 and the production version of this exam will be 1Z0-151.

How to Fix Database Column Alias name problem in Microsoft Excel

2012-01-17T20:32:00.000-08:00

Database Column Alias name caused the query to fail problem when try to Microsoft Query to return data from some third-party database into Microsoft Excel. The error message that you receive depends on the data source that you use. I am using Oracle Database 10g and because the use of column alias name Microsoft Query generate ‘ORA-00923: FROM keyword not found where expected’ error.

A supported hotfix is available from Microsoft and this hotfix is prebuilt in Microsoft Excel 2007. In the following steps I will show you how to enable hotfix for Excel 2007.

1) Exit all office programs
2) Click start, and then click run
3) In the open box, type regedit and then click ok
4) Locate, and then click to select the following registry subkey: HEKY_CURRENT_USER\Software\Microsoft\Office\12.0\query\options
5) After you select the subkey that is specified in step 4, point to New on the Edit menu, and then click DWORD value.
6) Type AllowAlias, and then press ENTER.
7) Reghit-click AllowAlias, and then click Modify.
8) In the value data box, type 2, and then click OK

9) On the File Menu, click Exit to exit Registry Editor.

After applying above solution Column Alias problem will be resolved.

How to fetch Data from Oracle Database to Microsoft Excel

2012-01-13T05:29:00.000-08:00

Recently my Team Lead gave me the task to fetching the data from Oracle Database in Microsoft Excel through Microsoft Query. After spending some time in R&D I was successfully completed this task and now I am sharing my knowledge and steps to fetch data from Oracle Database to Microsoft Excel.

1) From the Data tab in MS Excel, click From Other Source icon

2) From Choose Data Source window Select New Data Source and click OK

3) From Create New Data Source window give the Data Source Name and select Microsoft ODBC for Oracle as Driver from the list.

4) Enter Databse User Name, Password and Server

5) Select Oracle Databsae tables and its Columns from Query Wizard-Choose Columns

6) Filter the data to specify which rows to include in your query.

7) Specify how you want your data sorted.

8) Select Retrun data to Microsoft Office Excel

9) From Import Data window - Select how you want to view this data in your workbook

10) Extracted Data

Overview of 1Z1-151 Oracle Fusion Middleware 11g Build Applications with Oracle Forms Exam

2012-01-06T23:21:00.000-08:00

Today I was appeared in the beta exam of 1Z1-151 Oracle Fusion Middleware 11g: Build Applications with Oracle Forms. The exam was too lengthy and in the duration of 3 hours total 282 questions were given. I was hardly attempt 150 questions. Some of the questions that were asked in the Oracle Fusion Middleware 1Z1-151 exam are given below.

Why would you want to create a control block in a form module?
Some forms that you wrote have been through part of the testing cycle. One of the testers has reported encountering a Navigation Trap in your form. Which scenario could have caused the Navigation Trap?
Which built-in should be used to invoke one form from another form so that you can navigate between the two?
Which built-in always starts a new Forms run-time session when you use it to invoke another form?
Which built-in can you use to programmatically set the validation status for all records in a block?
You are creating an application on a Window PC, and you want to test a form. What are two ways to start an OC4J instance on the PC?
You start an OC4J instance on your development PC and then run a form from the Forms Builder. Which statement describes the behavior of OC4J?
There is a stored procedure in the database called Raise_Salary which must be called without any parameters. There is no Raise_Salay procedure in the form or in the attached library. A When-Button-Pressed trigger in the Employee form contains this code. Raise_salary; where dose from search for the Raise_Salary procedure?

Oracle 1Z1-151 exam was neither too hard nor too easy. It is good practice to take beta exam before appear in actual exam. Beta Exam Close Date is February 25, 2012 and Beta exam score reports will be available at Pearson VUE approximately 11 weeks after the close of the Beta Exam. Production Exam will release approximate 10 weeks from exam close date.

Use of Format Trigger in Oracle Report Builder

2012-01-03T00:49:00.000-08:00

Format Trigger in Oracle Report Builder is a PL/SQL function. This trigger is going to fire before an object is printed in report output. It returns Boolean value, if value is true then goes to print and if value is false then donot print.

Database Many to Many Relationship with Examples

2011-12-30T00:31:00.000-08:00

We cannot characterize a many-to-many association directly in a relation scheme, because two tables cannot be children of each other. When you design your ERD, you will be challenged by lots of possibilities for many to many relationships.

You would have learned that we do not want to draw many-to-many relationships in our ERD. We want to break them up by putting a junction-entity in between. Instead of a many-to-many relationship, we then get two one-to-many relationships with the junction entity in the middle.

Examples:

1. Student-professor. A student will have one or more professors. The same professor will have lots of students.

2. At a hospital a patient will be assigned to a coupld of nurses. A specific nurse will be assigned to 1 or many patients.

3. A student will have lots of subjects and the same subject can be taken by lots of students.

Lets look at the Student - Professor

Now, because many-to-many are not allowed, we will change this to add a junction-entity (StudentProfessor) in the middle.

The three entities could translate into the following three tables:

In the student table, the StudentNumber will be the primary key.
In the Professor table, the ProfNumber will be the primary key.
In the junction table - StudentProfessor, the StudentNumber and the ProfNumber are both foreign keys. As a combination they will form the primary key.

The information in the tables could look as follows:

Student Table:

Professor Table:

StudentProfessor(Junction) Table:

Firing Sequence of Oracle Report Builder Triggers

2011-12-28T00:53:00.000-08:00

Oracle reports builder triggers are fired in the following sequence.

First Before Parameter Form triggre fire

Second After Parameter Form trigger fire

Third Before Report trigger fire

fourth Between Pages trigger fire

fifth After Report trigger fire

Database One to Many Relationship with example

2011-12-27T01:05:00.000-08:00

One to Many relationship is the most common relationship and your database entity relationship diagram (ERD) will be full of this kind of relationship.

Examples

1. Master-detail. You have a master record with many detail records. For example an order, there will be a master record with the order date, person placing the order, etc. And then detail records of everything ordered. The master record will have many details, and the detail will have only 1 master.

2. Supervisor-subordinates. A supervisor will have one or many subordinates. A subordinate will have only 1 manager.

3. Division- department. A division will have one or many departments. A department will belong to only 1 division.

Let’s look at the Master - Detail

The two entities could translate into the following two tables:

In the Master table, the OrderNumber is the primary key. In the detail table, OrderNumber is the foreign key. The primary key in the Detail table will be a combination of OderNumber and LineNumber.

The information in the tables could look as follows:

Master:Detail:

Available Triggers in Oracle Report Builder

2011-12-26T22:36:00.000-08:00

There are five types of triggers available in Oracle Report Builde

1) Before report trigger

2) After report trigger

3) Before Parameter trigger

4) After parameter trigger

5) Between pages trigger

Use of FNDSQF Library in Oracle Forms Developer

2011-12-24T20:38:00.000-08:00

The FNDSQF library used in Oracle Forms and it contains the routines for handling Message Dictionary, felxfields, prpfiles, concurrent processing, mulicurrency, WHO etc.

Database One to One Relationship with example

2011-12-24T01:54:00.000-08:00

In most database designs, if you have a One to One relationship, there is most likely something wrong with your database design. It should be very exceptional to have One to One relationship. In general this would point out that your two entities with the one to one relationship should be combined into one single entity.

Example:

In Pakistan a person can have only one ID Card. Also, a specific ID card can belong to only one person.

Let’s look at the Person and ID Document example:

In the Person table, the column Person ID will be the Primary Key. In the ID Document table, the ID Number will be the Primary key.

Use of APPDAYPK library in Oracle Forms

2011-12-21T08:41:00.000-08:00

The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle application provide to pick the dates for a date type field.

Image of Oracle Application Calender

Use of APPCORE library in Oracle Forms

2011-12-19T08:04:00.000-08:00

APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc.

APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.

List of Triggers in Oracle Report Developer

2011-12-17T03:42:00.000-08:00

There are five types of triggers in Oracle Report Developer

1) Before report trigger

2) After report trigger

3) Before Parameter trigger

4) After parameter trigger

5) Between pages trigger

List of Reports Layouts available in Oracle Reports Developer

2011-12-13T08:52:00.000-08:00

There are eight different layout formats:

1. Tabular

2. Form Like

3. Form Letter

4. Mailing Label

5. Group Left

6. Group Above

7. Matrix

8. Matrix with group

Which perform faster in Oracle Database IN or EXISTS

2011-11-10T23:04:00.000-08:00

EXISTS is always perform faster in Oracle Database because “EXISTS” returns a boolean value (True or False), whereas “IN” returns a value.

Oracle Solaris 11 releases with new features

2011-11-10T01:01:00.000-08:00

Oracle Solaris 11 will be officially released today, including the Sparc platform compatible with x86.

Oracle Solaris 11 is SPARC SuperCluster T4-4 Integrated Systems and Oracle SPARC T4 server product line operating system, its also supports Oracle Exadata Database cloud server X2-2, X2-8 system, middleware and Oracle Exalogic cloud server.

New in Oracle Solaris 11

Built for Clouds

Engineered for Oracle

Best for Enterprise Applications

How many LONG columns are allowed in a table in Oracle Database

2011-11-09T08:59:00.000-08:00

Restriction for the use of LONG columns in Oracle Databsae

1) Only one column of type LONG is allowed.

2) LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

3) LONG column can’t appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

What is the difference between CLOB and BFile in Oracle Database

2011-11-08T08:00:00.000-08:00

The Oracle BFile type, is used to store unstructured binary data outside of the database in the operating system. BFiles are read-only and only support random-reads.

CLOB type is stored in the database using two byte code character set. Unlike BFile, CLOB support sequential access and can be modified.

What is the difference between implicit and explicit cursor in Oracle Database

2011-11-07T04:33:00.000-08:00

Whenever we issue an INSERT, DELETE, UPDATE or single row SELECT INTO statement, Oracle creates a implicit cursor to deal with this operation.

An explicit cursor, is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR … IS statement.

As programmers, we can also create explicit cursors. These cursors work like a pointer to each row that is retrieved and allow us to work in each row at a time.

What is the difference between truncate and delete in Oracle Database

2011-11-05T18:44:00.000-07:00

TRUNCATE is much faster than delete because it does not allow for rollbacks to occur, and automatically commits data when finishes.Also truncate does not fire any trigger, so if there is a trigger on your table, TRUNCATE would not fire it but DELETE will and you cannot use the WHERE clause on TRUNCATE.

What is the DUAL table in Oracle Database

2011-11-05T01:17:00.000-07:00

The DUAL table is a table that Oracle creates with it is data dictionary consisting in exactly one row. DUAL belongs to the SYS user but all users can query it , and use it’s unique property of returning only one row, to select pseudo-columns .

What is Anonymous PL/SQL block in Oracle

2011-10-28T23:22:00.000-07:00

An anonymous PL/SQL block is a block of code that is not stored in the database. When submitted to the database, the code will be executed immediately. An anonymous PL/SQL block always starts with DECLARE or BEGIN.

Example:
BEGIN
dbms_output.put_line('Hi, this is anonymous PL/SQL');
END;
/