Sunday, 12 October 2014

How to export data from Oracle 12c database to Excel file.

All you need is WAMP Server, I am usig version 2.5.

You can download it from http://sourceforge.net/projects/wampserver/files/latest/download

After installing wamp server  goto php.ini file located in wamppath\apache\apacheversion\bin\php.ini

The wamp path  is the path where the wamp is installed.
The directory apacheversion is named as the version you have installed. i.e. apache2.4.9
After opening php.ini (say in notepad) search for string oci, you will find
;extension=php_oci8.dll
;extension=php_oci_11g.dll
If you are using Oracle version <=10g, un-comment extension=php_oci8.dll (remove ; from begining)
Else if you are using 11g then un-comment extension=php_oci8_11g.dll
But if you are using 12c you need to add a line in the file, extension=php_oci8_12c.dll.
You will also need to download php_oci8_12c.dll . It can be downloaded in zipped format containing all dll's from version 8 to 12.
Link for the client download : http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/.

I am giving you the links, you need to download your needed version like if you  have php 5.4 in 64 bit OS download  php_oci8-2.0.8-5.5-nts-vc11-x64.zip 

Wednesday, March 12, 2014  7:27 PM       566414 php_oci8-2.0.8-5.3-nts-vc9-x86.zip
Wednesday, March 12, 2014  7:31 PM       579365 php_oci8-2.0.8-5.3-ts-vc9-x86.zip
Wednesday, March 12, 2014  7:20 PM       562128 php_oci8-2.0.8-5.4-nts-vc9-x86.zip
Wednesday, March 12, 2014  7:23 PM       577956 php_oci8-2.0.8-5.4-ts-vc9-x86.zip
Wednesday, March 12, 2014  7:06 PM       575229 php_oci8-2.0.8-5.5-nts-vc11-x64.zip
Wednesday, March 12, 2014  7:13 PM       564670 php_oci8-2.0.8-5.5-nts-vc11-x86.zip
Wednesday, March 12, 2014  7:09 PM       589774 php_oci8-2.0.8-5.5-ts-vc11-x64.zip
Wednesday, March 12, 2014  7:16 PM       579897 php_oci8-2.0.8-5.5-ts-vc11-x86.zip
Thursday, April 10, 2014 11:03 PM       584008 php_oci8-2.0.8-5.6-nts-vc11-x64.zip
Thursday, April 10, 2014 10:55 PM       573662 php_oci8-2.0.8-5.6-nts-vc11-x86.zip
Thursday, April 10, 2014 11:07 PM       593400 php_oci8-2.0.8-5.6-ts-vc11-x64.zip
Thursday, April 10, 2014 10:59 PM       581718 php_oci8-2.0.8-5.6-ts-vc11-x86.zip
 
Extract the files in wamppath\bin\php\phpversion\ext\ directory 
i.e. C:\wamp\bin\php\php5.5.12\ext\

Restart your service.
Make sure your Oracle service ad Listener service is Up and running.

Here is the sample code to connect to Oracle and export data to Excel file
//===============================================================

<?php
//$conn=oci_connect('userame','password','hostname/oracle_servicename');
$conn=oci_connect('hr','hr','192.168.0.109/orcl.localdomain');
if(!$conn)
{
    $err = oci_error();
    trigger_error(htmlentities($err['message'], ENT_QUOTES), E_USER_ERROR);   
}

    function cleanData($str)
    {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    }
   
    $stid = oci_parse($conn, 'SELECT emp_code, emp_name FROM GREENPLY.employee_master');
    oci_execute($stid);

    $filename = "ora_data.xls";
    header("Content-Type: text/plain");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Type: application/vnd.ms-excel;charset=UTF-16LE");
/* using implode directly to $row is creating some problem (the columns value coming twice) thats
   why I have first created array from string and then split it into string with tab delimiter. */

    echo implode("\t",explode("\\t", "Emp Code\\tEmp Name")) . "\r\n";

    while (($row = oci_fetch_array($stid, OCI_BOTH)) != false)
    {
        array_walk($row, 'cleanData');
        echo implode("\t",explode("\\t", $row[0] . '\\t' . $row[1])) . "\r\n";
    }
oci_free_statement($stid);
oci_close($conn);
//http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/php_oci8-2.0.8-5.5-ts-vc11-x64.zip   
//Source Code Link : https://drive.google.com/file/d/0BznrW3lgX0ozaTFTeDJEWWd6VE0/view?usp=sharing
?>
 

No comments: