
May 21st, 2004, 12:23 PM
|
|
Contributing User
|
|
Join Date: Oct 2003
Location: Omaha, Nebraska
Posts: 63
Time spent in forums: 1 h 27 m 5 sec
Reputation Power: 5
|
|
Oracle Temp Table and PHP
SUMMARY
The following is what I have done with no success. I will have to strip down the SQL statements or try to get the results without a temp table. Plus, I have tried this with and without calling the SQL statement in the variable $sql3.
Has anyone tried to create a temp table in Oracle using PHP, then displaying the results and deleting the temp table?
Code:
$sql0 = "CREATE GLOBAL TEMPORARY TABLE TMP".$subnum."
(GROUPID VARCHAR2(8))";
$sql1 = "INSERT INTO TMP".$subnum." (SELECT DISTINCT GROUPID FROM TAB_COLOR_PRODUCT
WHERE PRODUCTID IN (SELECT GROUPID
FROM TAB_COLOR_SUBGROUP
WHERE SUBNUM = '".$subnum."'
AND UNITNUM = '".$unitnum."' )
AND GROUPID LIKE '06%')";
$sql2 = "SELECT a.SEGMENT, b.DESCRIPTION, a.MENU_POS, a.SEG_ORDER FROM TAB_COLOR_SEGMENT A,
TAB_COLOR_DEFS B WHERE b.ID = a.SEGMENT AND a.SEGMENT IN ((SELECT GROUPID
FROM TMP".$subnum.")
MINUS
SELECT DISTINCT b.PAGEMBR FROM TAB_COLOR_LPN a, TAB_COLOR_PAGEMBR b WHERE
a.SEGMENT IN (SELECT GROUPID FROM TMP".$subnum.")
AND b.LPN = a.LPN
AND b.PGMBR_ORDER = '001'
AND b.PAGEMBR IN (SELECT GROUPID FROM TMP".$subnum.")
)
ORDER BY MENU_POS, SEG_ORDER";
$sql3 = "DROP TABLE TMP".$subnum;
$cursor = OCILogon($user, $pass, $tns);
if ( ! $cursor ) {
echo "Unable to connect: " . var_dump( OCIError() );
die();
}
// CREATE TEMP TABLE
$createTMP = OCIParse($cursor, $sql0);
OCIExecute($createTMP, OCI_DEFAULT);
// INSERT DATA INTO TEMP TABLE
$insrtTMP = OCIParse($cursor, $sql1);
OCIExecute($insrtTMP, OCI_DEFAULT);
// SELECT DATA
$slctDATA = OCIParse($cursor, $sql2);
OCIExecute($slctDATA, OCI_DEFAULT);
//-------------------------------------------------------
while (OCIFetchInto($slctDATA, $row, OCI_ASSOC | OCI_RETURN_NULLS)){
print $row['SEGMENT'].' '.$row['DESCRIPTION'].' '.$row['MENU_POS'].' '.$row['SEG_ORDER'].'<br>';
}
//-------------------------------------------------------
// DELETE TEMP TABLE
$deleteTMP = OCIParse($cursor, $sql3);
OCIExecute($deleteTMP, OCI_DEFAULT);
OCILogoff($cursor);
__________________
--Todd
|