How to query/fetch data of MS Access DB from an Oracle DB?

To day I'll show you how we can read/query data which is in MS Access database from oracle database. it is a step by step description and I assume that you have a oracle database and MS access database. So I'll not discuss of this two database installation or configuration. For this tutorial I've used

• Oracle database 11g version 11.2.0.1.0
• Windows XP SP-3
• MS Access 2007

Steps to complete the tutorial:

• Step1: Creating System DSN (Data Source Name) with Microsoft Access Driver
• Step2: Configuring Oracle Net Listener-listener.ora file
• Step3: Configuring oracle heterogeneous services 11g HS (initaccessdb.ora)
• Step4: Configuring Oracle Local Naming file - tnsnames.ora
• Step5: Creating DB Link in Oracle and accessing data from MS Access database

Step 1: Creating System DSN (Data Source Name) with Microsoft Access Driver

First go to ODBC Data Source Administrator. For this follow the step below:
Go to Start>Programs>Oracle-Oradb11g_home1 (in my case it is home2)>Configuration and Migration Tools> Microsoft ODBC Administrator

In WINDOWS 10 you may not find the Configuration and Migration Tools in start menu. In this case you can open it from control panel> administrative tools>ODBC Data Sources (32-bit)/ODBC Data Sources (64-bit). Remember if your access database is 32 bit then open ODBC Data Sources (32-bit), if it is 64 bit then open ODBC Data Sources (64-bit)

It will open Microsoft ODBC Administrator console

Click on System DSN tab then Click Add button. Select Microsoft Access Driver (*.mdb,*.accdb) then click finish.

Some times you may not find Microsoft Access Driver like above image, specially in widows 7, as bellow image showing blank driver list.

 In this case open odbc driver from following location: C:\Windows\SysWOW64\odbcad32

  

 

It will open the following window. In this window give a data source name. In my case I’ve given the name accessdb. Please remember or note down the data source name. Provide a description, description is not mandatory. Click on select button to select your MS Access database (.mdb or .accdb file).

Then choose your access database file

then click OK> OK> OK it will Save the configuration.

Step 2: Configuring Oracle Net Listener-listener.ora file

Let's locate the file; it is in database home/network/admin directory. In my case: it is
D:\oracle\app\product\11.2.0\dbhome_1\NETWORK\ADMIN  

Note: if you do not find any listener.ora file simply create a file named listener.ora and add the following entry Listener.ora file

   LISTENER =
     (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.201.76)(PORT = 1521)) 
      )
    )
     )

   SID_LIST_LISTENER=
    (SID_LIST =
     (SID_DESC =
      (SID_NAME = accessdb)
      (ORACLE_HOME = D:\oracle\app\product\11.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
     )
   )
  

In first block:
• HOST: the host name is your computer, where database is resides, name or IP address.
In second block:
• SID_NAME: is the name of access DSN Which we create at step 1 in this case it is 'accessdb'
• ORACLE_HOME: oracle database home directory path
• PROGRAM = dg4odbc (leave it as it is)

Step 3: Configuring oracle heterogeneous services 11g HS (initaccessdb.ora)

Go to heterogeneous service directory in oracle database_home/hs/admin in my case it is  
D:\oracle\app\product\11.2.0\dbhome_1\hs\admin

There you will find a file named 'initdg4odbc.ora'. make a copy of this file and rename it as initaccessdb.ora and Add the following text to the file:
HS_FDS_CONNECT_INFO = accessdb
HS_FDS_TRACE_LEVEL = 0

Note: the file name should be init then your access DSN name and extension is .ora.
HS_FDS_CONNECT_INFO: Here we write the name of the DSN created in the ODBC source tool in the first step, so it's 'accessdb'

Step 4: Configuring Oracle Local Naming file - tnsnames.ora

TNS file location is D:\oracle\app\product\11.2.0\dbhome_1\NETWORK\ADMIN in the tnsnames.ora file make an entry like bellow

  accessdb =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.201.76) (PORT = 1521))
  (CONNECT_DATA =
    (SID = accessdb)
  )
  (HS=OK)
  )
  

Here
HOST: Name or IP address of the database server
SID: This is the name of the access DSN name in this case it is accessdb 
 HS=OK: it is a mandatory entry so leave it as it is.

At this point we need to restart oracle database listener service. To do so, Open a command line terminal change your working directory to: Database home/BIN (D:\oracle\app\product\11.2.0\dbhome_1\BIN )

To stop the listener
D: \oracle\app\product\11.2.0\dbhome_1\BIN >LSNRCTL.EXE stop 

To start  listener
D: \oracle\app\product\11.2.0\dbhome_1\BIN >LSNRCTL.EXE start

Alternatively in windows run write services.msc and press enter then find the oracle listener service and right click on the service name (OracleOraDb11g_home1TNSLinstener) then choose restart.

Step 5: Creating DB Link in Oracle and accessing data from MS Access database

You need to create a database link(DBLink) in your oracle database. Create DB Link command
CREATE public DATABASE LINK accessdblink USING 'accessdb';

Note: Here accessdblink is the name of link we creating and accessdb is the name of access DSN

Query the database:
Select * from empmas@accessdblink;

Last Update: Tuesday 27 March, 2018

Previous Update:Wednesday 02 August, 2017

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.

 DECLARE
 v_ref_cur sys_refcursor;
 BEGIN
    OPEN v_ref_cur FOR 
    'select empno, ename,sal 
      from emp
         where deptno =:deptno ' 
       USING p_deptno;
 RETURN v_ref_cur;
 END;

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
  IS
  v_ref_cur sys_refcursor;
  BEGIN
   OPEN v_ref_cur FOR 
   'select empno, ename, sal from emp
    where deptno =:deptno' 
   USING p_deptno;
   RETURN v_ref_cur;
  END;
 

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.

CREATE OR REPLACE TYPE emptype AS OBJECT ( empno NUMBER, ename VARCHAR2 (10), mgr NUMBER, sal NUMBER );

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.

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

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

1/11/4712

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')
      FROM DUAL

It will return

Eleven

. 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')
      FROM DUAL

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')
      FROM DUALFROM DUAL

It will through and 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)
  RETURN VARCHAR2
  AS
  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);
  

  BEGIN
  FOR i IN 1 .. arrary_element.COUNT
  

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

  LOOP
  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

  THEN
  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);
  END LOOP;
  RETURN v_word;
  END;
  

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)
     FROM DUAL;

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.

---