PHP call oracle store procedure

For PHP, it is not a difficult task to connect to Oracle database.
But recently, I am working on a project that require me to execute the oracle store procedure with pass in some input & retrieve the output.

It should be pretty easy, just I never try it before.
So I write a simple Oracle Store procedure & a PHP page.

Below is my testing.

Oracle Store Procedure

View Code ORACLE
CREATE OR REPLACE
procedure test
(
	whoami IN VARCHAR2,
	msg OUT VARCHAR2
)
AS
 
BEGIN
	msg := 'HELLO ' || whoami;
END;

PHP Script

View Code PHP
<?php
        $database = ociplogon('XXXX', 'XXXXX','//192.168.XX.XX/SID');
        $sql = "alter session set
                nls_date_format = 'YYYY-MM-DD hh24:mi:ss'
                QUERY_REWRITE_INTEGRITY = TRUSTED
                QUERY_REWRITE_ENABLED = TRUE
                NLS_LENGTH_SEMANTICS=CHAR" ;
        $stmt = ociparse($database, $sql);
        $err = ocierror($database);
 
        $name = "World";
        $welcome = '';
 
        $query = "BEGIN test (:name, :output_welcome); END;";
 
        $s = ociparse($database, $query);
 
        ocibindbyname($s, ':name', $name, 32);
        ocibindbyname($s, ':output_welcome', $welcome, 32);
 
        ociexecute($s);
        echo $welcome;
?>

For the above example, you should receive the result “Hello World”.

3 Comments to “PHP call oracle store procedure”

  1. Md Anwar 25 June 2009 at 2:00 pm #

    i have tryed the above code but following error occure.
    HTTP 500
    Most likely causes:
    The website is under maintenance.
    The website has a programming error.

    What you can try:
    Refresh the page.

    Go back to the previous page.

    More information

    This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying.

    For more information about HTTP errors, see Help.

    Reply
  2. h2Guru 25 June 2009 at 10:18 pm #

    Hi Md Anwar,

    May I know your PHP configuration & the Oracle version?
    I also facing some problem previously while the oracle version is different. For my development, I am using the Oracle 10, but for actual scenario, I am using the oracle 9.
    So facing some problem on it.
    Maybe you can post up some of the setting & I will take a look for it.

    Recently working with this PHP call oracle procedure, will post up more info about this after complete the project.

    Cheers~~!

    Reply
  3. clear 23 March 2013 at 10:36 pm #

    I am really grateful to the owner of this
    website who has shared this enormous article at at this place.

    Reply

Leave a Reply to clear

Click here to cancel reply.