473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Return ref cursor from oracle procedure

I have spent numerous days trying to figure this out.

This is my set up.

I am trying to fill an html table with data from an Oracle table.
I am using php

The table I am referencing is fairly basic.
The table has an auto id incremented on each insert (intviewpostidpk)
It has a date field for when the insert occurred (dtmviewpostdate)
it has a number field that is associated to a posting id (intviewpostpostidfk)

What i need is a way to call a stored procedure that will fill my html table.

I am new to oracle, html, and php. I have had no issues with insert, delete, update using stored procedures.

Expand|Select|Wrap|Line Numbers
  1. SELECT INTVIEWPOSTPOSTIDFK, COUNT(INTVIEWPOSTPOSTIDFK) as COUNTPOSTVIEW 
  2.           FROM VIEWPOSTLOG 
  3.           GROUP BY INTVIEWPOSTPOSTIDFK 
  4.           ORDER BY COUNTPOSTVIEW DESC;
This is my sql statement I am using. It returns exactly the data I want when run in sql developer, but I need to be able to access this data and output it to my html table.

There are lots of different ways that I have seen to do this, but I have not been successful as of yet.

Any help or pointers into the right directions would be greatly appreciated
Nov 6 '07 #1
5 7006
amitpatel66
2,367 Expert 2GB
I have spent numerous days trying to figure this out.

This is my set up.

I am trying to fill an html table with data from an Oracle table.
I am using php

The table I am referencing is fairly basic.
The table has an auto id incremented on each insert (intviewpostidpk)
It has a date field for when the insert occurred (dtmviewpostdate)
it has a number field that is associated to a posting id (intviewpostpostidfk)

What i need is a way to call a stored procedure that will fill my html table.

I am new to oracle, html, and php. I have had no issues with insert, delete, update using stored procedures.

Expand|Select|Wrap|Line Numbers
  1. SELECT INTVIEWPOSTPOSTIDFK, COUNT(INTVIEWPOSTPOSTIDFK) as COUNTPOSTVIEW 
  2.           FROM VIEWPOSTLOG 
  3.           GROUP BY INTVIEWPOSTPOSTIDFK 
  4.           ORDER BY COUNTPOSTVIEW DESC;
This is my sql statement I am using. It returns exactly the data I want when run in sql developer, but I need to be able to access this data and output it to my html table.

There are lots of different ways that I have seen to do this, but I have not been successful as of yet.

Any help or pointers into the right directions would be greatly appreciated
Connecting to database from HTML is not possible.

You can make use of the below code in case if you want to store the data of the above query in html format:

Expand|Select|Wrap|Line Numbers
  1. SET MARKUP HTML ON
  2. SPOOL index.html
  3. <your select query here>
  4. spool off
  5. set markup HTML OFF
  6.  
Nov 7 '07 #2
Connecting to database from HTML is not possible.

You can make use of the below code in case if you want to store the data of the above query in html format:

Expand|Select|Wrap|Line Numbers
  1. SET MARKUP HTML ON
  2. SPOOL index.html
  3. <your select query here>
  4. spool off
  5. set markup HTML OFF
  6.  
I am using php in the html to do my calls to oracle ie this is one of my insert statements:
Expand|Select|Wrap|Line Numbers
  1. <?
  2. $strParam = "'".$SELEMPLOYEROPTIONIDFK."', '".$VCHEMPLOYERNAME."', '".$VCHEMPLOYERADDRESS."', '".$VCHEMPLOYERCITY."', '".$SELEMPLOYERPROVSTATEIDFK."', '".$VCHEMPLOYERXTRALOCATION."', '".$SELEMPLOYERCOUNTRYIDFK."', '".$VCHEMPLOYERPOSTALCODE."', '".$SELEMPLOYERSALUTATION."', '".$VCHEMPLOYERFIRSTNAME."', '".$VCHEMPLOYERLASTNAME."', '".$VCHEMPLOYERPHONENUM."', '".$VCHEMPLOYERFAXNUM."', '".$VCHEMPLOYEREMAIL."', ".$VCHEMPLOYERWEBSITE."', '".$VCHEMPLOYERPASSWORD."', '".$INTEMPLOYERIDPK."'";
  3.  
  4.                     //Updates existing employer profile
  5.                     $strQuery ="begin SP_UPDATE_EMPLOYER($strParam); end;";
  6.                     $dbConn->Execute($strQuery);
  7.  
  8. ?>
What I need is someway to return a ref cursor through php and oracle so that my select statement doesn't sit on my page.

right now my select statements look like this:
Expand|Select|Wrap|Line Numbers
  1. if ((($INTEMPLOYERIDPK) > 0) && (!isset($_POST["submit"]))){    
  2.  
  3.         //Fills fields on form with data to edit from the database
  4.         $strQuery = "SELECT * FROM EMPLOYER WHERE INTEMPLOYERIDPK = '$INTEMPLOYERIDPK'";
  5.         $rsEmployer = $dbConn->Execute($strQuery);
  6.         $intEmployerCount = $rsEmployer->RecordCount();        
  7.  
  8.         if($intEmployerCount=1){
  9.             $arr = $rsEmployer->FetchRow(); 
  10.             $SELEMPLOYEROPTIONIDFK          = $arr["INTEMPLOYEROPTIONIDFK"];    
  11.             $VCHEMPLOYERNAME                = $arr["VCHEMPLOYERNAME"]; 
  12.             $VCHEMPLOYERADDRESS             = $arr["VCHEMPLOYERADDRESS"]; 
  13.             $VCHEMPLOYERCITY                = $arr["VCHEMPLOYERCITY"]; 
  14.             $SELEMPLOYERPROVSTATEIDFK       = $arr["INTEMPLOYERPROVSTATEIDFK"];
  15.             $VCHEMPLOYERXTRALOCATION        = $arr["VCHEMPLOYERXTRALOCATION"];
  16.             $SELEMPLOYERCOUNTRYIDFK         = $arr["INTEMPLOYERCOUNTRYIDFK"]; 
  17.             $VCHEMPLOYERPOSTALCODE            = $arr["VCHEMPLOYERPOSTALCODE"]; 
  18.             $SELEMPLOYERSALUTATION          = $arr["VCHEMPLOYERSALUTATION"]; 
  19.             $VCHEMPLOYERFIRSTNAME           = $arr["VCHEMPLOYERFIRSTNAME"]; 
  20.             $VCHEMPLOYERLASTNAME            = $arr["VCHEMPLOYERLASTNAME"]; 
  21.             $VCHEMPLOYERPHONENUM            = $arr["VCHEMPLOYERPHONENUM"]; 
  22.             $VCHEMPLOYERFAXNUM              = $arr["VCHEMPLOYERFAXNUM"]; 
  23.             $VCHEMPLOYEREMAIL               = $arr["VCHEMPLOYEREMAIL"]; 
  24.             $VCHEMPLOYERWEBSITE             = $arr["VCHEMPLOYERWEBSITE"];
  25.             $VCHEMPLOYERIMGLOCATION            = $arr["VCHEMPLOYERIMGLOCATION"];
  26.         }    
  27.     }
Thanks for your help and any more you can give
Nov 13 '07 #3
amitpatel66
2,367 Expert 2GB
You can create a procedure in oracle with OUT parameter as REFERENCE CURSOR which will reuturn the REF CURSOR when called.
From PHP, call a procedure that will return ref cursor whcih you can use it in your PHP code.
Nov 13 '07 #4
That is what I was trying to do.

I kept crashing apache when trying to use bind variables for in parameters using the ExecuteCursor syntax.

Took me a bit, but just passed the php variable directly in my call:
Expand|Select|Wrap|Line Numbers
  1. $IDPK = $_SESSION['user_id'];
  2.  
  3. $rsEmployer = $dbConn->ExecuteCursor("BEGIN SELECT_EMP(:refc,$IDPK); END;",'refc');
  4.  
  5. $arr = $rsEmployer->FetchRow();
  6.    $EmpName = $arr['VCHEMPLOYERNAME'];
  7.           :                        :
  8.           :                        :
  9. $rsEmployer->Close();
  10.  
Thanks to all that helped. As always greatly appreciated.
Nov 27 '07 #5
amitpatel66
2,367 Expert 2GB
Good to hear that its working for you!!

MODERATOR
Nov 27 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Philip Mette | last post by:
Does anyone have any good references they could recommend on Cursor based SQL writing? I have to create SQL that can loop though records simular to VB loops and I have been told that this is the...
0
by: Chan | last post by:
Hi, I am trying to send set of rows from my c# web service to Oracle stored procedure. I think I can get this done using OpenXML in SQL Server. How to implement this in Oracle Stored...
2
by: Rhino | last post by:
I am trying to verify that I correctly understand something I saw in the DB2 Information Center. I am running DB2 Personal Edition V8.2.1 on Windows. I came across the following in the Info...
8
by: Yusuf INCEKARA | last post by:
I have a stored procedure : CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT AS set @RETCUR = CURSOR FORWARD_ONLY STATIC FOR SELECT ID,STORE_NAME FROM T_INF_STORE ORDER BY...
0
by: Chan | last post by:
Hi, I am trying to send set of rows from my c# web service to Oracle stored procedure. I think I can get this done using OpenXML in SQL Server. How to implement this in Oracle Stored...
12
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
2
by: BilalGhazi | last post by:
Hi All, I have this strange problem. I am user of two different database (both are same version 9i). I created a procedure and within this procedure i used a cursor to select the values, this is...
1
by: =?Utf-8?B?cmFuZHkxMjAw?= | last post by:
Can anyone offer pointers to articles/examples of passing a Ref Cursor ***IN*** to an Oracle stored procedure. I find tons of examples for getting a ref cursor OUT of a stored procedure. I'm using...
1
by: andeke | last post by:
Hi! I'm developing a classic ASP application in which I'm supposed to retrieve a recordset from an Oracle procedure. The Oracleprocedure only has one parameter, an out parameter of type REF...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.