CODEWITHSUNDEEP
phporacle-database
user8153130
user8153130

Reputation: 11

oci_execute(): ORA-06550: PLS-00306: wrong number or types of arguments

I have used the below code to execute an oracle stored procedure using php.

$s = oci_parse($db, 'begin :bind2 := XXXXXXXXX(:bind1); end;');

if(!$s)
{
    echo "wrong"; 
}
else
{
    echo "Right";
    $in_var = 'XXXXXXX';
    $in = oci_bind_by_name($s, ':bind1', $in_var);

    if(!$in)
    {
        echo "Incorrect in";
    }
    else
    {
        echo "Correct in";
        $cursor = oci_new_cursor($db);
        // On your code add the latest parameter to bind the cursor resource to the Oracle argument
        $k = oci_bind_by_name($s,':bind2', $cursor, -1, OCI_B_CURSOR);
        if(!$k)
        {
            echo "Wrong";
        }
        else
        {
            echo "Correct";
            // Execute the statement as in your first try
            $execute = oci_execute($s) or die ("Unable to execute query\n");
            if(!$execute)
            {
                echo "false";
            }
            else
            {
                echo "correct";
                // and now, execute the cursor
                $result = oci_execute($cursor,OCI_DEFAULT);
                echo $result;
                //oci_bind_by_name($s, ":bind2", $out_var, 20); // 20 is the return length
                //oci_execute($s,OCI_DEFAULT);
                //echo "Procedure returned value: " . $out_var;

                // Logoff from Oracle...
                oci_free_statement($s);
                oci_close($db);
            }
        }
    }
}

I am getting the below error once executed the above code snippet.

"oci_execute(): ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'XXXXXXXXX' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /opt/lampp/htdocs/usermanagemet/callOraProc.php on line 79 Unable to execute query"

My procedure is:

CREATE OR REPLACE FUNCTION XXXXXXXXX(
    username IN VARCHAR2)
  RETURN VARCHAR2
AS
  v_query VARCHAR2( 100);
  v_out   VARCHAR2(20);
BEGIN
  v_query := 'ALTER USER '|| username ||
    ' ACCOUNT LOCK
    PROFILE GNVLOCK';

  --dbms_output.put_line(v_query);
  EXECUTE immediate v_query;
  RETURN 'true';
EXCEPTION
WHEN OTHERS THEN
  RETURN SQLCODE;
END SLT_GNVUSER_DISABLE;

Does anyone know what am I missing here?

Upvotes: 0

Views: 1700

Answers (1)

Martin
Martin

Reputation: 16423

The problem with your code appears to be that you are treating your Oracle object as a procedure, and it is actually a function.

You have used:

$s = oci_parse($db, 'begin XXXXXXXXX(:bind1, :bind2); end;');

You should instead use:

$s = oci_parse($db, 'begin :bind2 := XXXXXXXXX(:bind1); end;');

This will execute XXXXXXXXX and the result will end up in :bind2 as you already expect.

The first syntax you used would be suitable for a procedure that had an OUT parameter which you could access via bind2.

Incidentally, I don't know whether you have chosen your function naming scheme or inherited it, but XXXXXXXXX seems like a very undescriptive name for your function.

Upvotes: 1

Related Questions