glerma
Posts: 56
|
| Posted: 04/24/2005, 11:02 AM |
|
I need to execute a stored procedure from a Custom Code event.. I am having problems formatting the execution of the stored procedure so that it executes correctly.
Here is what I am trying to do.
Execute a SP to Insert rows into a single table. The reason why I need to insert this manually using custom coding is because one of the values that needs to be entered is coming from a Multiple selection ListBox object, thus I cannot use the IDE to build my call to the SP. If I do so, only the first selection will be inserted. I need it to insert all values selected. What I have done instead is selected a Custom Code Event from the OnClick Server event for the form Submit Button. I then perform a loop on the POST variable for the Select ListBox:
PHP Coding Example:
/************************************************************
EVENT: UPDATE_OR_INSERT_BUSINESS_CATEGORIES_INTO_DATABASE
Save the contents of the selected business categories into the database.
************************************************************/
global $ChooseVCat;
/**
Initialize Database Connection Class.
**/
$db = new clsDBmfcdb();
/**
Loop through each selection and insert the selection into the db.
**/
$orgid = "12345";
foreach ($_POST['listedVcategories'] as $value) {
$db->bind(":org_id", $orgid);
$db->bind(":vend_cat_id", $value);
$sSQLx = "BEGIN MFC_USER_UTILS.ADD_VEND_BUS_CAT(:org_id,:vend_cat_id);END;";
//Execute the statement.
$db->query($sSQLx);
}
/**
Close the db class descriptor.
/**
$db->close();
When this gets called, I am getting the following errors:
Warning: ocibindbyname() [function.ocibindbyname]: OCIBindByName: ORA-01036: illegal variable name/number in /data/www/mfc/docroot/0001/db_oci8.php on line 114
Warning: ocibindbyname() [function.ocibindbyname]: OCIBindByName: ORA-01036: illegal variable name/number in /data/www/mfc/docroot/0001/db_oci8.php on line 114
Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-01008: not all variables bound in /data/www/mfc/docroot/0001/db_oci8.php on line 120
Here is the SP ( I am using Oracle)
/**
* ORACLE PROCEDURE add_vend_bus_cat
*
* For saving a selected Vendor Business Category.
* this procedure inserts a row into the ORG_VENDOR_CAT_ASSIGN
* table containing the org_id and vendor_cat_id.
*
* @author GLerma <glerma at foobar dot com >
* @project Undisclosed
*/
PROCEDURE ADD_VEND_BUS_CAT(
ORG_ID_IN IN ORG_ACCOUNTS.ORG_ID%TYPE,
VENDOR_CAT_ID_IN IN ORG_VENDOR_CATEGORIES.VENDOR_CAT_ID%TYPE
)
IS
BEGIN
INSERT INTO ORG_VENDOR_CAT_ASSIGN (ORG_ID,VENDOR_CAT_ID) VALUES (ORG_ID_IN,VENDOR_CAT_ID_IN);
END;
The problem is figuring out how to call the SP within my loop, while using the existing CCS Database Abstract API. How do I bind my variables to the SP? Do I call the PHP OCIBindByName or do I use the function built into the db_oci8.php file included with the CCS API?
Once again, here is my summary:
Intent: Using Custom Code, Insert one or many rows of data into a single table from a Multiple Selection Listbox object.
Language: PHP 5.0
Database Engine: Oracle 10g.
Table Properties:
Table Name:
Vendor_cat_assign
Columns:
org_id
vendor_cat_id
Event to call SP:
Form->Button->Server->OnClick
Any assistance or guidance would be tremendously helpful.
George
|
