Search This Blog

Saturday, September 27, 2014

An Overview and Clear Understanding of Oracle REF CURSOR

An Overview and Clear Understanding of Oracle REF CURSOR

I'll discus shortly how we can use ref cursor easily, with some easy example which will make a very clear concept about ref cursor. After reading this topic you will be very familiar with the ref cursor. So let's start.

First I'll go for an overview of ref cursor like what is ref cursor? How data is fetched using ref cursor? What is the use of ref cursor? and some key information about ref cursor

The name "Ref Cursor" can be confusing because a ref cursor variable is not a cursor. The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database which acts as a pointer or a handle to a result set on the database. We can use ref cursor as OUT parameter of procedure and Function. Remember ref cursor do not hold actual data like variable or array or oracle explicit cursor. It's just a pointer or a handle to the result set of a query. What does it mean? This point will be clear later in this topic.

Oracle introduces REF CURSOR type in oracle 7.3. Oracle 9i introduced the predefined SYS_REFCURSOR type, that's mean we no longer have to define our own REF CURSOR types and I'll use this SYS_REFCURSOR type in my example. Oracle 11g allows the two-way conversion between ref cursors to DBMS_SQL cursors. Oracle 12c allows implicit statements results, similar to that seen in Transact-SQL. I'll discus this two point later here in another post.

N.B. I used oracle database 11g R2, toad 9.5 and SQL*PLUS for the entire example in this post.
The following example shows the syntax of ref cursor.
 v_ref_cur sys_refcursor;
    OPEN v_ref_cur FOR 
    'select empno, ename,sal 
      from emp
         where deptno =:deptno ' 
       USING p_deptno;
 RETURN v_ref_cur;

Code Anatomy: First I declare a variable v_ref_cur which data type is ref cursor. Then opening the variable with 'OPEN v_ref_cur' and in FOR clause I writing the query. Remember query must be enclosed in single quotation mark.
At this point I'll write a simple function that opens a ref cursor
  CREATE OR REPLACE FUNCTION get_emps_info (p_deptno IN NUMBER)
  RETURN sys_refcursor
  v_ref_cur sys_refcursor;
   OPEN v_ref_cur FOR 
   'select empno, ename, sal from emp
    where deptno =:deptno' 
   USING p_deptno;
   RETURN v_ref_cur;

Here v_ref_cur is a variable which data type is ref cursor. Now we can look into v_ref_cur with following code in SQL*PLUS.
SQL> var v_rc_handle refcursor;
[Declare variable as refcursor type to hold information,which will be returned from our ref cursor.]

N.B. SQL*PLUS refcursor is similar to pl/sql sys_refcursor.

SQL> exec : v_rc_handle := get_emps_info (20);
[Calling our get_emps_info function and getting the ref cursor handle in v_rc_handle variable]

SQL> print v_rc_handle;
[Printing v_rc_handle variable]
That means our ref cursor retunes employees information. It's great!! But at first I told ref cursor do not hold data, so how it showing data. Now it is time to make it clear. Look at the following command.We just trying to print our v_rc_handle variable again

SQL> print v_rc_handle;
SP2-0625: Error printing variable " v_rc_handle"

Why SQL*PLUS can't print the v_rc_handle variable now? Because of at this point the ref cursor doesn't hold any data; v_rc_handle just a pointer to the query result set. Our first print command fetched all the rows and then it closes the cursor and when we tried to print the ref cursor again, we got an error because SQL*Plus find the cursor as it is open and couldn't print it again.

Now I'll try it a little bit more details, so that it can be clearer. I'll use some PL/SQL code.

[+] [-]  Show/Hide Code
  • DECLARE v_rc_handle sys_refcursor; v_empno NUMBER; v_ename VARCHAR2 (20); v_mgr NUMBER; v_sal NUMBER; BEGIN v_rc_handle := get_dept_emps (10); -- This returns an open cursor LOOP FETCH v_rc_handle INTO v_empno, v_ename, v_mgr, v_sal; EXIT WHEN v_rc%NOTFOUND; -- Exit the loop when we've run out of data DBMS_OUTPUT.put_line (v_empno||', '||v_ename|| ); END LOOP; CLOSE v_rc; END;

Code Clarification :

v_rc_handle is a variable which will act as handle to our ref cursor and variable v_empno, v_ename , v_mgr v_sal will hold our original value. Whit 'v_rc_handle:= get_dept_emps (20);' this line of code we getting the ref cursor handle. Then in loop we are fetching our data. 'EXIT WHEN v_rc%NOTFOUND;' this line will end the loop and Exit the loop when all record of ref cursor will be fetch

Is it possible to use a oracle ref cursor in a SQL query and How?

Now question is, is it possible to use a ref cursor in a SQL query and How? Answer is yes possible. To achieve it first we need to create a type structure. Since SQL cannot read/access PL/SQL table structures so the type must be a database object.

This code will create a object type named emptype. Now with this object type we will create a table type which can hold data like a database table.
Create or replace type emp_tabtyp as table of emptype;

This emp_tabtyp type is a structure to hold a record. To populate this type with data which coming from the ref cursor we will create a function using our table type emp_tabtyp as follows:

[+] [-]  Show/Hide Code
  • CREATE OR REPLACE FUNCTION populate_emps_info (deptno IN NUMBER := NULL) RETURN emp_tabtyp IS v_emptype emp_tabtyp := emp_tabtyp (); -- Its a local table structure v_ref_cur sys_refcursor; v_cnt NUMBER := 0; v_empno NUMBER; v_ename VARCHAR2 (10); v_mgr NUMBER; v_sal NUMBER; BEGIN v_ref_cur := get_emps_info (deptno); LOOP FETCH v_ref_cur INTO v_empno, v_ename, v_mgr, v_sal; EXIT WHEN v_rc%NOTFOUND; v_emptype.EXTEND; v_cnt := v_cnt + 1; v_emptype (v_cnt) := emptype (v_empno, v_ename, v_mgr, v_sal); END LOOP; CLOSE v_rc; RETURN v_emptype; END;

Function populate_emps_info calls the get_emps_info function that opens the ref cursor, then looping through and fetching each row and populating our SQL type structure. When all rows are fetched, the ref cursor is closed and the SQL table structure is passed back from the function. So now we have something in an structure that SQL understands, we should be able to query directly from it..

SQL> select * from table(populate_emps(30));

We've successfully get the ref cursor handle and used it to fetch the data back. Remember again A ref cursors is not a cursor or data holder. Once opened, they are simply a pointer to a query that has yet to fetch data.

So, this is all about ref cursor for now. I think it will help you to understand ref cursor. Enjoy it. If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.

Friday, September 26, 2014

How to convert number into word/text with PL/SQL?

How to convert number to word/text with PL/SQL:Easy Method

Today I’ll discus on how we can convert number into word easly. The easiest way is to use ‘JSP’ format mask of Julian date. Here ‘J’ for Julian day. Julian day is the number of day from 1 January, 4712 B.C and ‘SP’ for spell. Now we will see some example to be clear about Julian date. If we write the SQL query like below

SELECT TO_DATE (11, 'j')
      FROM DUAL;

It will return


Since, Julian day is the number of day from 1 January, 4712 B.C so it added 11 days with 1 January, 4712 and return result 11 January,4712. Now we will use ‘Jsp’ format mask to spell the number 11.

SELECT TO_CHAR (TO_DATE (11, 'j'), 'Jsp')

It will return


. In this way we can convert number to word/text. Now we will see some more examples.

SELECT TO_CHAR (TO_DATE (1234567, 'j'), 'Jsp')

It will return:

One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven

So easy and nice to convert number into word/text! But If we increase one more digit what will happen?

SELECT TO_CHAR (TO_DATE (12345678, 'j'), 'Jsp')

It will through and error ;

Julian date error

What happen? Why it can’t spell this number? Because of there is a limitation of Julian date, it’s ranges from 1 to 5373484. After this number it can’t do anything. So what can we do? We can resolve the issue very easily with little tricks.

Here is a function which can cater this problem and can spell out any number you wish.This function will spell number in metric system.

[+] [-]  Show/Hide Code

  • CREATE OR REPLACE FUNCTION fnc_spell_number (p_number IN NUMBER)
    TYPE myarray IS TABLE OF VARCHAR2 (255);

    --Declaring a Oracle Associative ARRAY type to hold string

    arrary_element myarray := myarray
          (       '',
          ' Thousand ',
          ' Million ',
          ' Billion ',
          ' Trillion ',
          ' Quadrillion ',
          ' Quintillion ',
          ' Sextillion ',
          ' Septillion ',
          ' Octillion ',
          ' Nonillion ',
          ' Decillion ',
          ' Undecillion ',
          ' Duodecillion '

    --Initializing the ARRAY

    v_number VARCHAR2 (50) DEFAULT TRUNC (p_number);
    v_word VARCHAR2 (4000);

    FOR i IN 1 .. arrary_element.COUNT

    --This loop will iterate up to, how many element in array

    EXIT WHEN v_number IS NULL;
    IF (SUBSTR (v_number, LENGTH (v_number) - 2, 3) <> 0)

    --it will cut the last three digit of the number, every time

    v_word := TO_CHAR (TO_DATE (SUBSTR (v_number, LENGTH (v_number) - 2, 3), 'J'), 'Jsp') || arrary_element (i) || v_word;

    --arrary_element (i) will added the text from array. First time it
    --will added null 2nd time it will added thousand then million then
    --billion and so on.

    END IF;
    v_number := SUBSTR (v_number, 1, LENGTH (v_number) - 3);
    RETURN v_word;

Now if we test this function with the number 12345678 or any other number it will return value up to ‘DUODECILLION’;

SELECT fnc_spell_number (12345678)

Now it can return:

Twelve Million Three Hundred Forty-Five Thousand Six Hundred Seventy-Eight

I hope it will help you to understand how we can convert number into word/text.
If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.

How to install Oracle Apex4 on Windows: A step by step description

Installing Oracle Apex on Windows

How you install Oracle Application Express depends upon which HTTP server you Decide to use. There are three options

  • 1: Oracle Application Express Listener
  • 2: Embedded PL/SQL Gateway
  • 3: Oracle HTTP Server

In all of these options Apex installation procedure is same and configuring Embedded PL/SQL Gateway is simpler. Embedded PL/SQL gateway provides the equivalent core features of Oracle HTTP Server and MOD_PLSQL.

In this article I’ll show how to install apex and configure the Embedded PL/SQL Gateway. In this case I've used

  • Oracle Database 11gR2 (11.2.0)
  • Oracle apex 4.2.5
  • Toad 10.5
  • Windows 7
If you want to configure oracle rest data service with tomcat read this article How to install Apex5 in windows and configure Oracle REST Data Services as web listener
Remember I’ll not discus about installing database.I assume that you have a database already installed and you have the administrative privileges on the database. Also assume that you have downloaded apex. If not so, you may get a copy of Oracle Apex 4.2.5 on OTN

Steps to install Oracle Apex

Step 1

Login to database and create a table space (e.g.TS_APEX). Table space creating command is as below


Step 2

Unzip the apex folder. (Right click on folder then choose extract here) you will find a folder named apex. Copy this folder anywhere on your hard drive you wish. In my case I’ve put it in C:\apex.

Step 3

Open Command Prompt (cmd). Change your working directory to apex where you put the unzipped apex folder (in my case it is C:\apex). Below images showing the command

Step 4

Now open SQL*Plus with nolog option by executing the command

C:\apex> sqlplus /nolog ENTER


SQL> conn sys as sysdba ENTER

(To connect to database with sysdba privileges)

When password require enter sys user’s password. ENTER

Step 5

Now you need to run apex installation file named apexins.sql. in command prompt enter the command like below

SQL> @apexins.sql ts_apex ts_apex temp /i/ ENTER

[ Here APEXINS.SQL is the apex main installation file. TS_APEX is the table space name which we creates at first step for apex user, apex files and temp is also a table space for apex temporary file. /i/ is a virtual drive for images. ]

It may take 30/40 minutes and may be disconnect from database. Remember whenever you disconnect from database every time you must follow the step (3, 4) to connect again.

When Oracle Application Express installs, it creates three new database accounts:

  • • APEX_040100 - The account that owns the Oracle Application Express schema and metadata.
  • • FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
  • • APEX_PUBLIC_USER - The minimally privileged account used for Oracle

When installation is finished it shows message like flowing image

In a new installation of Oracle Application Express, you must change the password of the internal ADMIN account. To changes password connect to database again and run the flowing command

SQL> @apxchpwd

When prompted enter a password for the ADMIN account. It should be complex password like abcd#1234

Also you need to unlock APEX_PUBLIC_USER. To unlock account execute the following command



Configuring the embedded PL/SQL gateway

To Configuring the embedded PL/SQL gateway run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was copied after unzipped as shown in the following example:

SQL> @apex_epg_config system_drive:\temp

In my case it is SQL> @apex_epg_config C:\

Then, you need to unlock the ANONYMOUS account.


Enable Oracle XML DB HTTP server:

SQL> exec dbms_xdb.sethttpport(8080);

SQL> commit;

to make sure the port is set to 8080 run the following statement

select all DBMS_XDB.GETHTTPPORT() from dual

You could also check the following statement

select all t.status from dba_registry t where ( ( t.comp_id = 'APEX' )) expect VALID;

Unlock some users which are related to apex (anonymous, apex_030200, apex_040100, apex_public_user

How to Uninstall Oracle Application Express

Open Command Prompt. change your working directory to the apex folder where it was installed then connect to the database

sqlplus /nolog press enter

conn sys@xe as sysdba

here XE is database name.


After completed the process Remove the apex folder from the drive

Browse Apex from Your PC

Open a browser (firefox/Explorer etc). Write the URL: http://host:port/apex (eg: http://blackhorse:8080/apex)
Your default workspace is- internal
user name is- admin
password is- admin user password which you changed at step 5

I hope it will help you to understand how to install and uninstall oracle application express

If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.

Thursday, September 18, 2014

5 Best Healthy Foods: We Should Not Ignore

5 More best healthy foods: We should not ignore

One can find diversity in our food like our culture and climate. There is no doubt that our food is rich and delicious. And Spice are at the heart of our food to enhance the appetite and test. Let's talk about some very special food- we should not ignore-


Cinnamon is a spice obtained from the inner bark of several trees from the genus Cinnamomum that is used in both sweet and savoury foods. Ref. Wikipedia.

Small amounts of cinnamon (darchini in bengali) have been used for a long time. Cinnamon oil is used mostly as a flavouring agent in medicine. This is a great source of manganese, fiber, iron and calcium. Some studies suggest that cinnamon may have a regulatory effect on blood sugar, making it especially beneficial for people with type 2 diabetes. It is a great way to help reduce the risk of heart disease. Studies have shown that patients given small amount of cinnamon powder combined with one tablespoon of honey every morning before breakfast had significant relief in arthritis pain after one week and could walk without pain within one month. It has also an anti-clotting effect on the blood. Please remember - Health risk can not be ruled out during high takes of cinnamon daily.


Black seed which is known as Kalo Jira in Bengali, Habbatul Barakah in Arabic and botanical name is Nigella Sativa L. Black seed is still known as one of 'all-time greatest source' of natural medicines. It has been used by million of people to promote health and fight disease. There are many nutrients found in black seed like vitamins, enzymes and traces of minerals including - calcium, potassium, iron and sodium. It contains 58% of essential fatty acids including omega 6 and 3 that are needed for balancing and strengthening the immune system. Besides its capacity to reduce inflammation and improve milk production in nursing mothers, it also strengthens the liver, kidney and increases the body's resistance against disease. Its capacity to lower excess blood pressure and improve the heart conditions is also known. Its role for the treatment of the bronchial asthma and other respiratory illnesses and its antimicrobial activities (especially disease causing organisms like E. Coli and Staphylococcus etc) are recently known.


Allium sativum, commonly known as garlic, is a species in the onion genus, Allium. Its close relatives include the onion, shallot, leek, chive,[1] and rakkyo.[2] With a history of human use of over 7,000 years, garlic is native to central Asia,[3] and has long been a staple in the Mediterranean region, as well as a frequent seasoning in Asia, Africa, and Europe. It was known to Ancient Egyptians, and has been used for both culinary and medicinal. Ref. Wikipedia.

Garlic has been cultivated for at least 5000 years. A herb lovingly called "the stinking rose" in light of its many therapeutic benefits. The French character for eating large quantities of garlic may account for them being so healthy. It is an effective detoxifier of blood and lymph in the body. It dilates the peripheral blood vessels, resulting in lowering the blood pressure. This is high in iodine. It sems that garlic increases the production of hydrogen sulfide in blood, which in the short term relaxes your blood vessels and increases blood flow. The power to boost hydrogen sulfide production may help explain why a garlic-rich diet appears to protect against various cancers. Then again, eating too much raw garlic could produce problems, for example irritation of or even damage to the digestive tract. Garlic is a proven broad-spectrum antibiotic that combats bacterial, intestinal parasites, and viruses.


Fish is a good source of protein and doesn't have the high saturated fat that fatty meat products do. Fatty fish like mackerel, lake trout, herring, sardines, albacore tuna and salmon are high in two kinds of omega-3 fatty acids, eicosapentaenoic acid (EPA) and docosahexaenoic acid(DHA).

Omega-3 oils have been called "the miracle food of the 21st century." Research shows, it can help to prevent heart disease, maintain optimum blood pressure and cholesterol levels and gibe almost immediate relief from joint pain, migraines, depression, autoimmune diseases and many other conditions. And , by improving brain development and memory functioning, certain Omega-3 oils also provide the perfect brain food.

Omega-3 acids are found in fatty fish (Hilsa, Pangash, Katla, Carp, Salmon, Sardine). The predominant fatty acids found in fatty fish and fish oils are eicosapentaenoic acid (EPA) and docosahexaenoic acid(DHA). They are most beneficial and active. More recent research has established that fish oils (EPA and DHA) play a crucial role in the management of depression. Clinical trials have shown that fish oil supplementation is helpful in the treatment of many disorders including rheumatoid arthritis, diabetes and ulcerative colitis. Eat a variety of fish at least twice a week. Include oils and foods rich in alpha-linolenic acid (flaxseed, canola oils).


The "superstar of drinks" is good for different diseases from cold to cardiac disease. There are three forms of honey, liquid, partially crystallized and granulated. Honey can be used as a food, preservative, or medicine. Honey diluted with water has been used to stomachs. Honey's anti- cough properties are related to its capacity to dilute bronchial secretions and improve function of the bronchial epithelium. Uncontaminated or pure honey is a natural, healthy, energy-rich and easily-digestible food. It is also effective against antibiotic-resistant bacteria. It is found that a mixtute of honey and cinnamon or honey and apple cider vinegar of sometimes honey and ginger cures lots of diseases. I addition, honey isn't for everybody. It is not advisable for babies less than one year of age.