How to execute stored procedures

A

admin

Administrator
Staff member
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
<!-- e --><a href="mailto:[email protected]">[email protected]</a><!-- e -->

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;
 
You must log in or register to reply here.
Back
Top