Return in Oracle Stored Procedures

[Anon]

Sorry bring again a yet discussed topic, but I couldn´t find help in the messages about stored procedures in packages with Oracle. I´ve tried everything... But I can´t get the return.

See my code:

include ("db.php");
$dbase = new DB;
$cnn = $dbase->conecta();

$usuario = "AMA123";
$senha = "CPEDAN";

$st = "BEGIN PACK_USUARIO.PROC_LOGIN '$usuario',:codigo_saida,'$senha'); END;";
$stmt = OCIParse($cnn,$st);
OCIBindByName($stmt,":codigo_saida",&$resp,-1);
OCIExecute($stmt);

echo "DONE!!";

?>

I already have tried to create cursor, Bind with :, without :, et cetera, just fail.
The last error message told me "wrong type or number of parameters" in Execute.

Could some help me, please? Thank you.

Here is the Procedure:

PROCEDURE PROC_LOGIN(
codigo IN varchar2,
codigo_saida OUT t_usu_codigo,
usu_senha IN varchar2)
IS
BEGIN
SELECT USU_CODIGO
INTO codigo_saida (1)
FROM USU
WHERE USU_CODIGO = CODIGO
AND USU_SENHA = usu_senha;
END PROC_LOGIN;

[vmachan]

Hi,
Have you tried executing the stored procedure from the SQLPLUS prompt. If so does it execute properly and return the expected value.
The following SQL
"SELECT USU_CODIGO
INTO codigo_saida (1) " especially the (1) looks unfamiliar to me. What is the purpose of the "(1)".
It looks like you are using a user-defined datatype. Ensure that the value being selected into the OUT parameter matches this datatype.
If I find something else, I will let you know.

[Anon]

Hi,

The PHP/Oracle interface doesn't support Oracle arrays datatype.

Use simple type or cursors instead.

Hth

[Anon]

Thank you. I think I could improve my roll of problemas, but now I have a new problem:

Warning: OCIStmtExecute: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYSTEM.PACK_USUARIO", line 53 ORA-06512: at line 1 in C:\WINDOWS\Desktop\MOL\test.php on line 15

this message show at ociexecute when the select returns more than 1 row. Could you help me? I have to use cursor?

Thank you.

[Anon]

Hi,

Yes this message means that the "SELECT INTO" returns more than 1 row.

If you just need the 1st row just add " AND ROWNUM = 1 " to the WHERE clause.

If you need many rows you'll have to use CURSOR as IN/OUT parameter.

Read the PHP/Oracle manual : http://www.php.net/manual/ref.oci8.php and
http://www.php.net/manual/function.ocinewcursor.php on how to use Cursor parameters.

Hope this helps

[Anon]

Hi,
thank you again, but 10x0 to PHP...
I am trying to use cursor just like I have seen in many messages in this list, but I get always the same error:

Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROC_LOGIN_TEMP1' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in C:\WINDOWS\Desktop\MOL\teste.php on line 15

I'm going crazy...
Can you give me a little help more?
See my code:

$curs = OCINewCursor($conn);
$stmt = OCIParse($conn,"begin PACK_USUARIO.PROC_LOGIN_TEMP1(:usu_codigo); end;");

ocibindbyname($stmt,"usu_codigo",&$curs,-1,OCI_B_CURSOR);
ociexecute($stmt);
ociexecute($curs);

...

Thank you again.

[jblallement]

This error is raised by Oracle and PHP has nothing to deal with it.

Your PHP code looks right, could you send the PL/SQL Code of the Stored Proc.

Thanx.

[Anon]

The procedure:

PROCEDURE PROC_LOGIN_TEMP1(
codigo out varchar2)
IS
BEGIN
SELECT USU_CODIGO
INTO codigo
from usu;
END PROC_LOGIN_TEMP1;

Not seems to me it is wrong...

Thanks

[vmachan]

Hi,
I looked at your stored procedure and it does not use a reference cursor (REF CURSOR in Oracle).
I have a small sample stored procedure which outputs a reference cursor. The oracle package defines a data type of type REF CURSOR, and the stored procedure then uses it.
I could not test it using PHP because I do not have the OCI8 client libraries. I would appreciate it if you could tell me how to set this up.

------ BEGIN ------
CREATE OR REPLACE PACKAGE VM_PKG AS
TYPE VM_REFCUR IS REF CURSOR;
END VM_PKG;
/

CREATE OR REPLACE PROCEDURE vm_tst_php_oci_curs
(
RC OUT VM_PKG.VM_REFCUR
)
IS
BEGIN
OPEN RC
FOR SELECT username
FROM all_users
;
END vm_tst_php_oci_curs;
/

------ END -----
The following is the sample PHP program which I was not able to test.

Please let me know if this works or if you found any other solution already.

----- BEGIN -----
<?php

//Calling Login Values.. we keep the DB Login Info in a single file
$db_id = ocilogon("scott", "tiger", "mcbuddb");

//Create Query String
$queryStr = "begin vm_tst_php_oci_curs(:myCurs); end;";

$cursor = OCINewCursor($db_id);
$stmt = OCIParse ($db_id, $queryStr);

//Bind the returned cursor
OCIBindByName($stmt, myCurs, &$cursor, -1, OCI_B_CURSOR);

OCIExecute($stmt);
OCIExecute($cursor);

//Find number of columns returned and initialize array to accept data
$numOfColls = OCINumCols($cursor);
$result = array();
$arr_index = 0;

//This will step thru the data returned in the cursor
while(OCIFetch($cursor))
{
    //  Iterate thru the number of columns in the returned data
    //  creating an ASSOC 2D-array
    for ($i=1; $i<=$numOfColls; $i++)
    {
        $colName = OCIColumnName($cursor, $i);
        $result[$arr_index][$colName] = OCIResult($cursor,$colName);
        echo "$colName = $result[$arr_index][$colName]\n";
    }

    //  Increment the numeric value of the ASSOC array
    $arr_index = $arr_index+1;

}   //  End While

OCILogoff($db_id);

?>

------ END ------

[Anon]

Hi,
If you want the php_oci8.dll, sure, I can send you.
I was looking my last message and now I see I have done a mistake. The real procedure is this:

PROCEDURE PROC_LOGIN_TEMP1(
USU_CODIGO out t_usu_codigo)
IS
CURSOR SELECIONA_USUARIO IS
SELECT USU_CODIGO
FROM USU;
PERCOUNT NUMBER DEFAULT 1;
BEGIN
FOR X_CURSOR IN SELECIONA_USUARIO
LOOP
USU_CODIGO(PERCOUNT) := X_CURSOR.USU_CODIGO;
PERCOUNT := PERCOUNT + 1;
END LOOP;
END PROC_LOGIN_TEMP1;

and I think the use of cursor is correct in it. But the message is the same...
I'll try use like you.

Thank you the help!

[vmachan]

Hi,
I did find php_oci8.dll on my PC in the Apache modules/mod_php4/extensions directory.
But when I uncomment the extension=php_oci8.dll line in the php.ini file and trhen start my Apache web server, it gives a fatal error, "program has performed an illegal operation and will be shutdown". When I click on "Details>>" it says that "APACHE caused an invalid page fault in module PHP_OCI8.DLL at 015f:012b102e." I am not sure why my php_oci8.dll wont load.
I would appreciate it if you could help me.

Thank You

[Anon]

Have you ever tried to get another php_oci8.dll? I think this can be the first step.

[vmachan]

Where do I download it from?

[Anon]

www.php4win.de if I am not wrong.
But I can send you by mail.

Do you have compiled PHP with --with_oci8 parameter?

[jblallement]

The parameter USU_CODIGO is an array and arrays are not supported by PHP.

You must return a CURSOR datatype and make it fetched by PHP.

Look the sample above from Vinayak D. Machan.

Your code should read

------ BEGIN ------
CREATE OR REPLACE PACKAGE PACK_USUARIO AS
-- Define a ref cursor datatype
TYPE USUARIO_REFCUR IS REF CURSOR;
END PACK_USUARIO;
/

CREATE OR REPLACE PROCEDURE PROC_LOGIN_TEMP1
(
RC OUT PACK_USUARIO.USUARIO_REFCUR
)
IS
BEGIN

-- Open the cursor passed as argument
OPEN RC FOR
SELECT USU_CODIGO
FROM USU;

END PROC_LOGIN_TEMP1;
/
------ END ------

Not sure if RC must be OUT or IN OUT.

• You should consult the PL/SQL manual about the use of CURSOR datatype.
• You'd better test your proc with an SQL query tool ( TOAD, SQL+ ... ) to debug it and be certain it runs as expected before using it with PHP. It will save you a ot of time.

[Anon]

Hehe worked! 🙂)
Thank you! I've done this way and worked at first time. Now I have a long work ahead. Thank you again.

[Anon]

😛 I have one more problem...
How can I use more than one cursor when I need return many columns?

I am trying to find in some books, and I think this is not possible, because they don't talk about this...

Thank you.

[jblallement]

Hi

If you need to retrieve many cols from the same query you just need 1 cursor

---- BEGIN PL/SQL ----
CREATE OR REPLACE PROCEDURE PROC_TEST
(
RC OUT PACK_TEST.REFCUR
)
IS
BEGIN

-- Open the cursor passed as argument
OPEN RC FOR
SELECT COL1, COL2
FROM TABLE1;

END PROC_TEST;
---- END PL/SQL ----

If you need many distincts queries you can pass many cursor:

---- BEGIN PL/SQL ----
CREATE OR REPLACE PROCEDURE PROC_TEST
(
RC1 OUT PACK_TEST.REFCUR ,
RC2 OUT PACK_TEST.REFCUR
)
IS
BEGIN

-- Open the cursor passed as argument
OPEN RC1 FOR
SELECT COL11, COL12
FROM TABLE1;

OPEN RC2 FOR
SELECT COL21, COL22
FROM TABLE2;

END PROC_TEST;
---- END PL/SQL ----

You can also mix with simple types:

---- BEGIN PL/SQL ----
CREATE OR REPLACE PROCEDURE PROC_TEST
(
RC OUT PACK_TEST.REFCUR,
VAL OUT INTEGER
)
IS
BEGIN

-- Open the cursor passed as argument
OPEN RC FOR
SELECT COL1, COL2
FROM TABLE1;

VAL := 12;

END PROC_TEST;
---- END PL/SQL ----

[Anon]

Hi,
done! Yes, my problem was solved.
Thank you and Vinayak to help me.

[Anon]

Hi I am trying to execute this oracle procedure in PHp and print out the datas. Could u please tell me how i would do it?
I tried various ways but it didnt work...

thanks a lot
Kaiser

PHP code:
//$sql2=" exec dynamic_sql('$Co','$billsys','$prod','$type','$serv')";

echo "sql2 :$sql2 ";

//OCIBindByName($stmt, "result", &$result);

$cursor2 = OCIParse ($conn, $sql2);
if ($cursor2 == false){
echo OCIError($cursor2)."<BR>";
exit;
}
//OCICommit ($conn);

ocibindbyname($cursor2,"Co",&$Co,30);
ocibindbyname($cursor2,"billsys",&$billsys,30);
ocibindbyname($cursor2,"prod",&$prod,30);
ocibindbyname($cursor2,"type",&$type,30);
ocibindbyname($cursor2,"serv",&$serv,30);

ociexecute($cursor2);

The Procedure:

PROCEDURE DYNAMIC_SQL(COMPANY IN products_marketing.PR_COMPANY_ID%TYPE,
BILLSYS IN products_marketing.PR_BILL_SYSTEM_ID%TYPE,
PRODCODE IN products_marketing.PR_CD%TYPE,
ACCTYPE IN products_marketing.PR_ACCESS_TYPE%TYPE,
ACCSERV IN products_marketing.PR_ACCESS_SERVICE%TYPE)
IS
v_cursorid INTEGER;
v_selectStmt VARCHAR2(1000);
v_pr_local_ind products_marketing.PR_LOCAL_IND%TYPE;
v_pr_company_id products_marketing.PR_COMPANY_ID%TYPE;
v_pr_bill_system_id products_marketing.PR_BILL_SYSTEM_ID%TYPE;
v_pr_cd products_marketing.PR_CD%TYPE;
v_pr_access_type products_marketing.PR_ACCESS_TYPE%TYPE;
v_pr_access_service products_marketing.PR_ACCESS_SERVICE%TYPE;
v_pr_desc products_marketing.PR_DESC%TYPE;
v_pr_line products_marketing.PR_LINE%TYPE;
v_pr_type products_marketing.PR_TYPE%TYPE;
v_pr_level products_marketing.PR_LEVEL%TYPE;
v_pr_term products_marketing.PR_TERM%TYPE;
v_pr_pricing_ind products_marketing.PR_PRICING_IND%TYPE;
v_pr_active_ind products_marketing.PR_ACTIVE_IND%TYPE;
v_criteria_count INTEGER;
v_dummy INTEGER;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('START PROC');
/ clear temp table /
delete from temp_prod_mkt;
COMMIT;
/ open cursor for processing /
v_cursorid := DBMS_SQL.OPEN_CURSOR;
/ create the query string /
IF COMPANY IS NULL and BILLSYS IS NULL and PRODCODE IS NULL and ACCTYPE IS NULL and ACCSERV IS NULL THEN
v_selectStmt := 'SELECT PR_LOCAL_IND, PR_COMPANY_ID, PR_BILL_SYSTEM_ID, PR_CD, PR_ACCESS_TYPE, PR_ACCESS_SERVICE, PR_DESC, PR_LINE, PR_TYPE, PR_LEVEL, PR_TERM, PR_PRICING_IND, PR_ACTIVE_IND FROM products_marketing ';
-- DBMS_OUTPUT.PUT_LINE(v_selectStmt);
ELSE
v_selectStmt := 'SELECT PR_LOCAL_IND, PR_COMPANY_ID, PR_BILL_SYSTEM_ID, PR_CD, PR_ACCESS_TYPE, PR_ACCESS_SERVICE, PR_DESC, PR_LINE, PR_TYPE, PR_LEVEL, PR_TERM, PR_PRICING_IND, PR_ACTIVE_IND FROM products_marketing WHERE ';
-- DBMS_OUTPUT.PUT_LINE(v_selectStmt);
v_criteria_count := 0;
IF COMPANY IS NOT NULL THEN
v_selectStmt := v_selectStmt || ' PR_COMPANY_ID = :comp';
v_criteria_count := v_criteria_count + 1;
END IF;
IF BILLSYS IS NOT NULL THEN
IF v_criteria_count > 0 then
v_selectStmt := v_selectStmt || ' AND ';
END IF;
v_selectStmt := v_selectStmt || ' PR_BILL_SYSTEM_ID = :bill ';
v_criteria_count := v_criteria_count + 1;
END IF;
IF PRODCODE IS NOT NULL THEN
IF v_criteria_count > 0 then
v_selectStmt := v_selectStmt || ' AND ';
END IF;
v_selectStmt := v_selectStmt || ' PR_CD = :prcd ';
v_criteria_count := v_criteria_count + 1;
END IF;
IF ACCTYPE IS NOT NULL THEN
IF v_criteria_count > 0 then
v_selectStmt := v_selectStmt || ' AND ';
END IF;
v_selectStmt := v_selectStmt || ' PR_ACCESS_TYPE = :atyp ';
v_criteria_count := v_criteria_count + 1;
END IF;
IF ACCSERV IS NOT NULL THEN
IF v_criteria_count > 0 then
v_selectStmt := v_selectStmt || ' AND ';
END IF;
v_selectStmt := v_selectStmt || ' PR_ACCESS_SERVICE = :aserv ';
END IF;
END IF ;
-- DBMS_OUTPUT.PUT_LINE('End Of IF..Else block');
/ v_selectStmt := v_selectStmt || ' ORDER BY PR_LOCAL_IND, PR_COMPANY_ID, PR_BILL_SYSTEM_ID, PR_CD, PR_ACCESS_TYPE, PR_ACCESS_SERVICE, PR_DESC, PR_LINE, PR_TYPE, PR_LEVEL, PR_TERM, PR_PRICING_IND, PR_ACTIVE_IND'; /
/ parse the query /
DBMS_SQL.PARSE(v_Cursorid, v_selectStmt, DBMS_SQL.V7);
/ bind the input variables /
IF COMPANY IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':comp', COMPANY);
END IF;
IF BILLSYS IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':bill', BILLSYS);
END IF;
IF PRODCODE IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':prcd', PRODCODE);
END IF;
IF ACCTYPE IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':atyp', ACCTYPE);
END IF;
IF ACCSERV IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':aserv', ACCSERV);
END IF;
/DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':comp', COMPANY);
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':bill', BILLSYS);
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':prcd', PRODCODE);
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':atyp', ACCTYPE);
DBMS_SQL.BIND_VARIABLE(v_Cursorid, ':aserv', ACCSERV);/
/ define the output variables /
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 1, v_pr_local_ind, 1);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 2, v_pr_company_id, 2);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 3, v_pr_bill_system_id, 3);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 4, v_pr_cd, 6);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 5, v_pr_access_type, 10);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 6, v_pr_access_service, 10);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 7, v_pr_desc, 40);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 8, v_pr_line, 30);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 9, v_pr_type, 30);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 10, v_pr_level, 10);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 11, v_pr_term, 10);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 12, v_pr_pricing_ind, 1);
DBMS_SQL.DEFINE_COLUMN(v_Cursorid, 13, v_pr_active_ind, 1);
-- DBMS_OUTPUT.PUT_LINE('define column done');
/ execute the statement /
v_dummy := DBMS_SQL.EXECUTE(v_Cursorid);
-- DBMS_OUTPUT.PUT_LINE('SQL Execute');
/ fetch loop /
LOOP
-- DBMS_OUTPUT.PUT_LINE('Enter Loop');
IF DBMS_SQL.FETCH_ROWS(v_Cursorid) = 0 THEN
-- DBMS_OUTPUT.PUT_LINE('No Recs');
EXIT;
END IF;
/ retrieve rows from buffer into variables /
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 1, v_pr_local_ind);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 2, v_pr_company_id);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 3, v_pr_bill_system_id);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 4, v_pr_cd);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 5, v_pr_access_type);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 6, v_pr_access_service);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 7, v_pr_desc);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 8, v_pr_line);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 9, v_pr_type);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 10, v_pr_level);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 11, v_pr_term);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 12, v_pr_pricing_ind);
DBMS_SQL.COLUMN_VALUE(v_Cursorid, 13, v_pr_active_ind);
/ insert data into a temp table /
INSERT INTO temp_prod_mkt(PR_LOCAL_IND, PR_COMPANY_ID, PR_BILL_SYSTEM_ID, PR_CD,
PR_ACCESS_TYPE, PR_ACCESS_SERVICE, PR_DESC, PR_LINE, PR_TYPE, PR_LEVEL,
PR_TERM, PR_PRICING_IND, PR_ACTIVE_IND)
VALUES(v_pr_local_ind, v_pr_company_id, v_pr_bill_system_id, v_pr_cd, v_pr_access_type,
v_pr_access_service, v_pr_desc, v_pr_line, v_pr_type, v_pr_level, v_pr_term,
v_pr_pricing_ind, v_pr_active_ind);
-- DBMS_OUTPUT.PUT_LINE('record inserted');
COMMIT;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE('Exit Loop');
/ close cursor /
DBMS_SQL.CLOSE_CURSOR(v_Cursorid);
-- DBMS_OUTPUT.PUT_LINE('Close Cursor');
/ commit transactions /
/COMMIT; /
-- DBMS_OUTPUT.PUT_LINE('Commit performed');
EXCEPTION
WHEN OTHERS THEN
/close the cursor and raise the error again /
DBMS_SQL.CLOSE_CURSOR(v_Cursorid);
RAISE;
END DYNAMIC_SQL;