Get a custom GUID in Oracle

       

drop function get_custom_guid;

create function get_custom_guid
   return varchar2 is

     l_guid varchar2(36);

begin

   select substr(guid,0,8) || '-' || substr(guid, 9,4) || '-' || substr(guid, 13,4)|| '-' || substr(guid, 17,4) || '-' || substr(guid,21)<

    into l_guid from (select sys_guid() as guid from dual) ;

   return l_guid;

end;



select get_custom_guid() guid from dual;

       
 

Get external IP of a server machine in PHP

1. $externalContent = file_get_contents('http://checkip.dyndns.com/');
   preg_match('/Current IP Address: \[?([:.0-9a-fA-F]+)\]?/', $externalContent, $m);
   $externalIp = $m[1];
   //AppleConnect Configuration
   $sugar_config['AppleConnectIpKey'] = $externalIp;  
2. 
   
3. 
   
4.   
5. 
   

Installing SugarCRM with Oracle backend on a Macbook Pro

Pre-requisites

1. MAMP installation pointing to 5.4.42 or Higher version of PHP (PHP version may change in future)
2. SugarCRM Codebase (we are using 7.8.RC1 as of now)
3. Oracle Instant Client , SDK and SQLPlus
4. If (OS Codename >= El Capitan)
   1. Refer to answer by Chris Ostmo in http://stackoverflow.com/questions/32590053/copying-file-under-root-got-failed-in-os-x-el-capitan-10-11
5. else
   1. Take the plunge !!! 

Steps

1. Verify if OCI8 driver is already enabled on your PHP installation (mostly not)
   1. Start Apache from MAMP
   2. Click OpenWebstart page (or) launch http://localhost:8888/MAMP/?language=English
   3. Launch PHPInfo :  http://localhost:8888/MAMP/index.php?language=English&page=phpinfo
   4. Verify OCI8 installation
   5. If not install OCI8 following the steps
2. Enable PHP to connect to Oracle
   1. Install Oracle Client for Mac
      1. Download : Oracle Instant Client, SQLPlus and SDK (http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html)
         1. instantclient-basic-macos.x64-11.2.0.4.0.zip
         2. instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
         3. instantclient-sdk-macos.x64-11.2.0.4.0.zip
      2. Extract contents of zip files to a common folder /Library/Oracle/instantclient_11_2
      3. The next step is to copy the necessary files into your OS X dynamic library and bin directories. Open a terminal window and go to the directory above the one you created containing all the files you unzipped.

         sudo cp /Library/Oracle/instantclient_11_2/sdk/include/*.h /usr/include
         sudo cp /Library/Oracle/instantclient_11_2/sqlplus /usr/bin
         sudo cp /Library/Oracle/instantclient_11_2/*.dylib /usr/lib
         sudo cp /Library/Oracle/instantclient_11_2/*.dylib.* /usr/lib

         sudo ln -s libclntsh.dylib.11.1 libclntsh.dylib

         Now "cd" to the /usr/lib directory and create the following link:

         sudo ln -s libclntsh.dylib.11.1 libclntsh.dylib

   2. Export Environment variables

      1. export ORACLE_HOME=/Library/Oracle/instantclient_11_2

         export TNS_ADMIN=$ORACLE_HOME/network/admin

         export DYLD_LIBRARY_PATH=/Library/Oracle/instantclient_11_2/

         export MAMP_PATH=/Applications/MAMP/bin/php/php5.3.29/bin
export PATH=$MAMP_PATH:$PATH:$ORACLE_HOME:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin

   3. Install OCI8 driver as extension for PHP
      1. Download the source for the version of php that you are using in MAMP (e.g.: 5.4.42 in this case).
      2. Extract the PHP source file and move the created folder to the following location (based on PHP version): /Applications/MAMP/bin/php/php5.3.29/include/php. (The contents of the extracted folder should be in the root of ../include/php)
         1. navigate to include/php folder and execute ./configure
      3. Run sudo pecl install oci8
         1. When Prompted, use instantclient,/Library/Oracle/instantclient_11_2/ as the Oracle instant Client path.
            1. If you faced an error for AUTOCONF not found, (you don't have it installed in your MAC)
               1. curl -OL http://ftpmirror.gnu.org/autoconf/autoconf-latest.tar.gz
               2. tar xzf autoconf-latest.tar.gz 
               3. cd autoconf-*
               4. ./configure --prefix=/usr/local
               5. make 
               6. sudo make install
            2. export PHP_AUTOCONF=/usr/bin/autoconf
            3. rerun the same command
         2. If all went well, the last line of the build process should say: You should add "extension=oci8.so" to php.ini 
         3. Open php.ini from /Applications/MAMP/bin/php/php5.3.29/conf and make the entry for extension=oci8.so at the end of the current extensions
         4. Open /Applications/MAMP/Library/bin/envvars and add the path to the instant client library: DYLD_LIBRARY_PATH="/Library/Oracle/instantclient_11_2/:/Applications/MAMP/Library/lib:$DYLD_LIBRARY_PATH”
   4. Restart MAMP
   5. Verify OCI8 library from phpinfo page
3. Connecting to Oracle Instance
   1. PHP Connects to Oracle through OCI8 driver
      1. OCI8 Driver relies on using tnsnames.ora
      2. tnsnames.ora is expected under $TNS_ADMIN and/or /etc folder
   2. Populate entry for your Oracle DB in tnsnames.ora
      1. Note : If you maintain tnsnames.ora under both $ORACLE_HOME/network/admin and/or /etc folders ensure to keep them in Sync
      2. Sample : 

         • sugarcrm1 = 
(DESCRIPTION =   
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))   
(CONNECT_DATA =      (SERVER = DEDICATED)      (service_name = orcl)    )  ) 

4. Verify Connectivity to Oracle through OCI8
   1. Sqlplus schemaname/password@tnsentry_name from Terminal
   2. You should be prompted with SQL > on successful Connection 
5. Verifying Connection from PHP
   • create a file connect.php under docroot folder configured for MAMP Apache as below
   • 2.   
     3.   
     4.   
     5.   
     6. $conn = oci_connect('system', 'oracle', 'sugarcrm1');  
     7. if(!$conn){  
     8.         echo " Sorry 1 no donut for you !!\n";  
     9.   
     10.   
     11.   
     12.   
     13.         $e = oci_error();  
     14.   echo "".htmlentities($e['message'], ENT_QUOTES)."

";  

        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);  

}  

  

  

  

  

echo " Yes !! Got connection for you !!\n";  

  

  

  

  

$stid = oci_parse($conn, "SELECT to_char(sysdate,'DD-MON_RRRR HH24:MI:SS') FROM dual");  

  

  

$x=1;  

while ($x <= 20) {  

  

  

  

  

  oci_execute($stid);  

  

  

  

  

  echo "\n" ;  

  echo " \n" ;  

  while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {  

  

  

  echo "

\n";  

  $x++;  

  

  

  

  

}  

  

  

  

  

oci_close($conn);  

  

  

  

  

  

?>  

Execute http://localhost:8888/connect.php

You should see the below output. (Actual values may differ based on Oracle DB version)

1. Yes Got connection for you !!

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   08-JUL_2015 10:50:09

   .....

Sugar Installation with Oracle

    Follow “Installation Procedure” section in Sugar Installation Guide (https://support.sugarcrm.com/04_Knowledge_Base/02Administration/100Install/Installing_Sugar_with_an_Oracle_Database/)

References

• https://support.sugarcrm.com/04_Knowledge_Base/02Administration/100Install/Installing_Sugar_with_an_Oracle_Database/
• http://www.enavigo.com/2012/01/04/enabling-oracle-oci8-php-extension-on-os-x-snow-leopard/