Tuesday, April 5, 2011

How to Connect to a Oracle/Peoplesoft Database using PHP

Entry from within tnsnames.ora file


ORA_SERVICE = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=ora_service)))

 This is sample script to connect and execute a query:
$db_conn["host"] = "ORA_SERVICE"; # service name in the tnsnames.ora file 
$db_conn["user"] = "myuser"; # username 
$db_conn["pass"] = "mypass"; # password 
$db_conn["library"] = "OCI"; 
 
$connect_id = ocilogon($db_conn["user"], $db_conn["pass"], $db_conn["host"]); 
 
$query = "SELECT * FROM table"; 
$statement = ociparse($connect_id, $query); 
ociexecute($statement); 
 
$result = array(); while(ocifetchinto($statement, $tmp, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS)) { 
  array_push($result, $tmp); } 
 
ocifreestatement($statement); 
 
var_dump($result); # result is here 


Please also review this connection link for PHP - http://www.php.net/manual/en/function.oci-connect.php

SQL Query to find all Records within a Specific Peoplesoft Component

I found this query on CompShack and wanted to also post on here.  I have used the following sample: 'ADM_APPL_MAINTNCE'

This query would be useful when you do a CNTL+J and are wanting to know the table names without going into App Designer.

SELECT DISTINCT (recname)
FROM psrecdefn
WHERE recname IN
(SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.pnlname)
 FROM pspnlgroup a, pspnlfield b
WHERE (   a.pnlname = b.pnlname OR a.pnlname =b.subpnlname)
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE' -- specify your component name)
AND recname <> ' ')
UNION
SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.subpnlname)
 FROM pspnlgroup a,pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname )
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE') -- specify your component name)
AND recname <> ' ')
AND rectype in ('0')  -- specify record type, default is 0 for table
--AND rectype in ('0', '1', '2')  -- specify all types
ORDER BY recname ASC