I am currently building a php-oracle application for the college I work for, and as part of that application I have cause to to create some insert/update procedures. My best solution for this is to have an input/output parameter representing the id of the field that needs to updated. This works fine.
If the passed id is 0 it performs an insert instead. This is also fine. The procedure creates a new id one higher than the current max, and uses that instead. Except for I would now like the procedure to return this new id back to the php. After searching for an appropriate way to do this, I came across oci_bind_by_name, but all the examples in the documentation show this being used in an inwards direction only. I found some outwards ones eventually, but they either didn't work, or were for special types like BLOBS and ROWIDS. I can't find a working example using a number or other ordinary type. Here is the simplest one of my procedures: create or replace procedure welfare.update_equipment ( ue_user in varchar2, ue_equip_id in out number, ue_stu_code in varchar2, ue_lsf_no in number, ue_lsf_year in varchar2, ue_amount in float, ue_method in number, ue_notes in varchar2 ) as update_time date; begin if ue_equip_id = 0 then select max(lsf_equipment_id)+1, sysdate into ue_equip_id, update_time from welfare.lsf_equipment; else select sysdate into update_time from dual; update welfare.lsf_equipment set lsf_end_date = update_time where lsf_equipment_id = ue_equip_id and lsf_end_date is null; end if; insert into welfare.lsf_equipment ( lsf_stu_code, lsf_no, lsf_year, lsf_amount, lsf_payment_method_code, lsf_equipment_id, lsf_equipment_notes, lsf_user_created, lsf_date_created ) values ( ue_stu_code, ue_lsf_no, ue_lsf_year, ue_amount, ue_method, ue_equip_id, ue_notes, ue_user, update_time ); end; Here is the php that calls it: $sql="begin welfare.update_equipment('$user',:equip_id,'$stu_code',$lsf_no,'$lsf_year',$amount,$method,'$notes'); end;"; //echo $sql; //debug line //Open Oracle Connection $db_conn=OCI_new_connect("db","username","password"); $psql = OCI_Parse($db_conn, $sql); OCI_bind_by_name($psql,"equip_id",&$equip_id,32); OCI_Execute($psql) or die("Couldn't execute statement."); // free resources and close connection OCI_close($db_conn); And finally, this is the error I get (including a stack trace): An error has occurred and the page has not loaded correctly. Please try and refresh/reload the page. If the error persists, then please contact MIS and we will look into the problem. errno: 2 errstr: oci_bind_by_name() [<a href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]: Unknown or unsupported datatype given: 1 errfile: D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php errline: 77 Stack Trace: #0 UpdateEquipmentForm->renderError(An error has occurred and the page has not loaded correctly. Please try and refresh/reload the page. If the error persists, then please contact MIS and we will look into the problem., 2, oci_bind_by_name() [<a href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]: Unknown or unsupported datatype given: 1, D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php, 77) called at [D:\Apache Group\Apache2\htdocs\includes\pages\base_page.php:148] #1 UpdateEquipmentForm->error_handler(2, oci_bind_by_name() [<a href='function.oci-bind-by-name'>function.oci-bind-by-name</a>]: Unknown or unsupported datatype given: 1, D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php, 77, Array ([user] => dhills,[equip_id] => 0,[stu_code] => 1358 ,[lsf_no] => 9231,[lsf_year] => 04/05,[amount] => 666,[method] => 1,[notes] => testing...,[sql] => begin welfare.update_equipment('dhills',:equip_id,'1358 ',9231,'04/05',666,1,'testing...'); end;,[db_conn] => Resource id #24,[psql] => Resource id #25)) called at [D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:77] #2 oci_bind_by_name(Resource id #25, equip_id, 0, 32) called at [D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:77] #3 UpdateEquipmentForm->updateEquipDetails() called at [D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:39] #4 UpdateEquipmentForm->__construct() called at [D:\Apache Group\Apache2\htdocs\Learner_Support_Fund\update_equipment.php:206] I guess I must be doing something horribly wrong. But what? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php