ORACLE PL/SQL Package Procedure or Function Calls

Scriptcase 9 General
#1

I am trying to call an Oracle 11G Procedure.

Right out of the Oracle PHP Cookbook. After reading though the forum, i discovered that the best way to test this is to create a blank container and add the test code.

I think what I am missing is how to convert std PHP OCI sytax to Scriptcase Syntax.

I think i need the equivalent of oci_parse and oci_bind_by_name.

The second does invoke the DB, after I created a synonym to my procedure. I get an Oracle DB Error that looks promising …
ociexecute(): ORA-01008: not all variables bound
Error while accessing the database:
ORA-01008: not all variables bound
{SC_DB_ERROR_INI}View SQL{SC_DB_ERROR_MID}BEGIN s_sayHello(:name, :message); END;{SC_DB_ERROR_CLS}Close{SC_DB_ERROR_END}

Any help would be greatly appreciated. The syntax should be simular to mysql procedure calls one would think.

Thanks, Tom

Here are my code attempts.

I have tried simply calling the procedure.
// Tried this…
//Call s_sayHello(:name, :message);

// Tried this. This is causing the Error above and looks most promising.
//$sql = ‘BEGIN s_sayHello(:name, :message); END;’;
// sc_exec_sql($sql);

<?php
$conn = oci_connect(‘SCOTT’,‘TIGER’) or die;

$stmt = oci_parse($conn,$sql);

// Bind the input parameter
oci_bind_by_name($stmt,’:name’,$name,32);

// Bind the output parameter
oci_bind_by_name($stmt,’:message’,$message,32);

// Assign a value to the input
$name = ‘Harry’;

oci_execute($stmt);

// $message is now populated with the output value
print "$message
";

?>

#2

Not sure, don’t worked with Oracle, but your error is an error coming from ORACLE not from SC.

ORA-01008: not all variables bound

Note: <?php ?> is not needed on a blank app.

#3

Actually I don’t understand what you are trying to do. The strong point of SC is that you don’t need to call the OCI yourself. SC has all the whisles and bells to work with stored procedures and tables out of the box. Why are you trying this approach in stead of using one of the other application types? What do you want to achieve?

#4

I don’t have to call OCI myself and would love to use the Scriptcase Procedure call. I cannot get that to work either. I have created and Oracle Delete procedure.

Scriptcase does attempt to call the procedure and then fails with an ORA-06550 Line 1, column 7: PLS-00306 wrong number or types of argument in call to ‘P_ASM_DELETE’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

Finding the procedure call in my form PHP, I determined that there is a bug. The Procedure call had no variables associated with it, even though I selected them when created the name delete procedure both, varchar.

So next I guessed and the variable syntax and made further progress. I added :account_parent_, :account_child_ between the parenthesis

Here is the generated PHP code in context:

$rs1->Close();
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_oracle))
{
$comando = “BEGIN P_ASM_DELETE_BY_PARENT_CHILD(:account_parent_, :account_child_); END;”;
}
else
{
$comando = “P_ASM_DELETE_BY_PARENT_CHILD”;
}
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_oracle))
{
$comando = $this->Db->PrepareSP($comando);
}

Now while testing this I am getting an ORA-01008: not all variables bound.

So now I guess we have come full circle. What is the Scriptcase equivalent for oci_bind_by_name? Or what are the bound versions (names / correct syntax) of the variables?

#5

Just one more thing for Clarity.

The Generated code prior to my change was $comando = “BEGIN P_ASM_DELETE_BY_PARENT_CHILD(); END;”;

This is obviously what was causing the PLS-00306

Thanks Again for your input.

#6

Making progress. So i decided to pass the an update procedure 4 Literals. 1 numeric and 3 alphanumeric surrounded by single quotes and separated by Comma’s and it WORKED. My update was processed by an Oracle PL/SQL procedure.

Anyone have an idea as to which variables contain the physical data after the update sync icon is pressed in a form containing an editable grid?

#7

Has you tried with field names with {}? Example, {name}

#8

SOLVED: Oracle Pl/sql Procedure Call Format

Albert you were right when you eluded to the $. Actually ‘$this->parm_’

Gui, thanks for hanging in there with me, debug in Zend is a great tool, btw. Won’t reach all of the PHP code with out runtime support (the purchased version), but works great after the scriptcase page is loaded hitting the index.php and a few others.

Here is my code for the Oracle procedure call, used for and Update. from the form_…_apl.php

I simply needed to use the $this with single quotes. The interpreter pulls the data value and surrounds it with single quotes for character values. Numerics leave them out.

      $bUpdateOk = true;
      $tmp_result = (int) $rs1-&gt;fields[0]; 
      if ($tmp_result != 1) 
      { 
          $this-&gt;Campos_Mens_erro = $this-&gt;Ini-&gt;Nm_lang['lang_errm_nfnd']; 
          $this-&gt;nmgp_opcao = "nada"; 
          $bUpdateOk = false;
          $this-&gt;sc_evento = 'update';
      } 
      $aUpdateOk = array();
      $bUpdateOk = $bUpdateOk && empty($aUpdateOk);
      if ($bUpdateOk)
      { 
          $rs1-&gt;Close(); 
          if (in_array(strtolower($this-&gt;Ini-&gt;nm_tpbanco), $this-&gt;Ini-&gt;nm_bases_oracle))
          {
              $comando = "BEGIN P_ASM_UPDATE_BY_SEQ($this-&gt;account_structure_seq_ , '$this-&gt;account_parent_', '$this-&gt;account_child_' ,
			  '$this-&gt;account_child_alias_'); END;";
#9

What i dont understand is why you are digging into generated code, instead on do IT directly on SC as Albert suggested

#10

I am going to use the delete procedure actually. Before the row is actually deleted it will insert into a seperate table for use in consideration for other inserts, finally an insert is going to be made in another productivity tracking table that will use the use id from scriptcase, defaulted timestamp and transaction weighting factor.

This table is going to be used in a employee performance tracking data warehouse app.