Solutions for Some Issues in Using Oracle Database with PHPMaker 10

I have been working with Oracle database with PHPMaker 10 to generate some web applications. There are actually some basic issues that need the quick solution. Started from the notice message Use of undefined constant OCI_COMMIT_ON_SUCCESS, how to use the certain data type in Oracle (such as ENUM in MySQL), how to overcome the error message from Oracle Error: ORA-00984: column not allowed here, and many others (in future). I found those three issues while using Oracle11g database with PHPMaker 10.

This post/article will give you the solutions to handle all the possibilities issue in using Oracle11g database with PHPMaker 10 (as this is the latest version I have been using). I found this solutions after doing some investigation and implementing the proper step while I was using Oracle database with PHPMaker 10 for the very fist time. The good news is all the solutions are working properly.

Of course, I will add another solutions for another issues in this post, if I find another in future. I am really sure that this will be helpful for those of you who have been working with Oracle database in PHPMaker 10. You may come back to this article at anytime you want. You can also add another issue(s) including the solution(s) by leaving your comment below.

[hidepost]

Issue #1, Notice: Use of undefined constant OCI_COMMIT_ON_SUCCESS

Usually, this will be happened for the PHP service that has never used Oracle extension.

Solution #1: If you are using Oracle11g (the most popular Oracle version), then simply enable php_oci8_11g extension in your php.ini file.

Find the following code in php.ini file:

;extension=php_oci8_11g.dll

then change it become:

extension=php_oci8_11g.dll

This change will enable the extension, save the file, and then make sure you restart your web server.

Issue #2, Implementing ENUM Data Type in MySQL Into Oracle

Most of the time, we usually use ENUM data type in MySQL database for the field which need the simple value, such "Y" and "N", "1" and "0", etc. Unfortunately, Oracle does not have this data type. However, we can use the similar data type with some additional steps.

Solution #2: In Oracle, you can use VARCHAR2 data type, afterwards, you have to add CHECK to insert the allowed value.

For simplicity, try to use the following SQL to generate a simple table named TESTTABLE which contain two fields named TEST_ID and TEST_OPTION which has that ENUM data type:

CREATE TABLE "TESTTABLE" 
("TEST_ID" VARCHAR2(5), 
"TEST_OPTION" VARCHAR2(1), 
 CHECK ("TEST_ID" IS NOT NULL ) ENABLE, 
 CHECK ("TEST_OPTION" IN ('Y','N') ) ENABLE, 
 PRIMARY KEY ("TEST_ID") );

As you can see, the allowed values in that TEST_OPTION field is Y and N.

Issue #3, Error: ORA-00984: column not allowed here

From the second issue above, if you set OPTION_TEST become Checkbox from the Fields setup of PHPMaker, then this error will be happened while user adding a new record into a table which has such ENUM field type in a web application that generated by PHPMaker 10. The same error also will be shown up while user editing a record.

As we can see, we have been using "Y" and "N" values in that such ENUM data type. This error happened, since PHPMaker 10 will generate the incomplete value that related to the value of such ENUM data type.

Let's say we use TESTDB database which contains the TESTTABLE table above, we want to insert a new record which contain such ENUM data type value.

So here is the SQL that generated by PHPMaker 10:

INSERT INTO "TESTDB"."TESTTABLE" ("TEST_ID","TEST_OPTION") VALUES ('00001',Y)

As you can see the value of "Y" is not enclosed by the single quote. This will raise an error column not allowed here since there is a value which does not match to TEST_OPTION field which should be a string value.

Solution #3: You have to enclose the value of that ENUM data type both for Row_Inserting and Row_Updating server events by using the following code:

$rsnew["TEST_OPTION"] = "'".$rsnew["TEST_OPTION"]."'"; // enclose it with single quote

by adding that code both in the server event, then the value for such ENUM data type will be converted correctly, and the Error ORA-00984 will be gone.
[/hidepost]