oracle: rowid in cursor
Posted: Sun Feb 14, 2010 9:39 pm
i need help on how to retrieve the value of rowid in a cursor on a stored proc.
$query = "BEGIN temppkg.VIEW_PRC(:code, :refcur_reg, :status, :message); END;";
// bind in parameters
oci_bind_by_name($stmt, ':code', $code, 200);
// bind out parameters
oci_bind_by_name($stmt, ':status', $status, 200);
oci_bind_by_name($stmt, ':message', $message, 200);
// bind the ref cursor
$refcur_reg = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':refcur_reg', $refcur_reg, -1, OCI_B_CURSOR);
// execute the statement
oci_execute($stmt);
// treat the ref cursor as a statement resource
oci_execute($refcur_reg, OCI_DEFAULT);
oci_fetch_all($refcur_reg, $reg_details, null, null, OCI_FETCHSTATEMENT_BY_ROW);
when i execute it this error will be displayed,
Catchable fatal error: Object of class OCI-Lob could not be converted to string.
tis is the stored procedure.
PROCEDURE VIEW_PRC (p_CODE IN VARCHAR2,
o_reg_data OUT SYS_REFCURSOR,
o_status OUT NOCOPY VARCHAR2,
o_message OUT NOCOPY VARCHAR2) IS
v_PROCEDURENAME CONSTANT VARCHAR2(30) := 'VIEW_PRC';
SELECT_REG CONSTANT VARCHAR2(40) := 'SELECT REG_INFO';
V_RETCODE NUMBER;
v_TotalRecords NUMBER;
NoValuePassed EXCEPTION;
NODATA EXCEPTION;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(v_PACKAGENAME||' '||v_PROCEDURENAME,'');
v_START_INDEX:=NVL(i_start_index,v_START_INDEX);
v_REC_PER_PAGE:=NVL(i_row_count,v_REC_PER_PAGE);
V_CHECKPOINT := SELECT_REG;
-- AR_TRANSACTIONS
OPEN o_reg_data FOR
SELECT c.*
FROM (SELECT B.*
FROM (SELECT A.TAX_CODE
,(select b.description
from REG_TAX_TYPES b
where b.code = a.tax_code) tax_desc
,A.RETRN_PERIOD
,A.RETRN_PERIOD_SEQ_NUM
,decode(A.STATUS_CODE,'O','OPEN'
,'C','CLOSED'
,'X','CANCELLED','AGED') STATUS_CODE
,A.TRANS_CODE
,(select c.description
from TAS_TRANSACTION_TYPES c
where c.trans_code = a.trans_code) trans_desc
,A.ASSMT_NOTICE_NO
,A.TRANS_DATE
,A.TRANS_AMOUNT
,A.REASON_CODE
,(select d.description
from TAS_REASON_TYPES d
where d.reason_code = a.reason_code) reason_desc
,A.SURCHARGE
,A.INTEREST
,A.COMPROMISE
,A.TOTAL_AMOUNT
,ROWNUM rn
,A.ROWID rid
FROM ARM_AR_TRANSACTIONS A
WHERE A.BRANCH_CODE = p_CODE
ORDER BY 1) B
WHERE rn <= v_rec_per_page + v_start_index) c
WHERE rn >= v_start_index + 1;
SELECT COUNT(*)
INTO o_ar_rec_count
FROM ARM_AR_TRANSACTIONS A
WHERE A.BRANCH_CODE = p_CODE;
o_Status:='SUCCESSFUL';
o_message:='Status sucessful';
DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
BEGIN
o_status:=NVL(o_status,'ERROR');
o_message:=SQLERRM;
LOG_ERROR(SQLERRM, --V_ERROR.ORACLE_ERROR,
NULL,
NULL, --V_BATCHID,
NULL, --V_ERROR.MODULE,
V_CHECKPOINT,
NULL, --V_ERROR.BLOCK,
V_REC,
NULL, --BATCH,
V_RETCODE);
END;
END;
$query = "BEGIN temppkg.VIEW_PRC(:code, :refcur_reg, :status, :message); END;";
// bind in parameters
oci_bind_by_name($stmt, ':code', $code, 200);
// bind out parameters
oci_bind_by_name($stmt, ':status', $status, 200);
oci_bind_by_name($stmt, ':message', $message, 200);
// bind the ref cursor
$refcur_reg = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':refcur_reg', $refcur_reg, -1, OCI_B_CURSOR);
// execute the statement
oci_execute($stmt);
// treat the ref cursor as a statement resource
oci_execute($refcur_reg, OCI_DEFAULT);
oci_fetch_all($refcur_reg, $reg_details, null, null, OCI_FETCHSTATEMENT_BY_ROW);
when i execute it this error will be displayed,
Catchable fatal error: Object of class OCI-Lob could not be converted to string.
tis is the stored procedure.
PROCEDURE VIEW_PRC (p_CODE IN VARCHAR2,
o_reg_data OUT SYS_REFCURSOR,
o_status OUT NOCOPY VARCHAR2,
o_message OUT NOCOPY VARCHAR2) IS
v_PROCEDURENAME CONSTANT VARCHAR2(30) := 'VIEW_PRC';
SELECT_REG CONSTANT VARCHAR2(40) := 'SELECT REG_INFO';
V_RETCODE NUMBER;
v_TotalRecords NUMBER;
NoValuePassed EXCEPTION;
NODATA EXCEPTION;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(v_PACKAGENAME||' '||v_PROCEDURENAME,'');
v_START_INDEX:=NVL(i_start_index,v_START_INDEX);
v_REC_PER_PAGE:=NVL(i_row_count,v_REC_PER_PAGE);
V_CHECKPOINT := SELECT_REG;
-- AR_TRANSACTIONS
OPEN o_reg_data FOR
SELECT c.*
FROM (SELECT B.*
FROM (SELECT A.TAX_CODE
,(select b.description
from REG_TAX_TYPES b
where b.code = a.tax_code) tax_desc
,A.RETRN_PERIOD
,A.RETRN_PERIOD_SEQ_NUM
,decode(A.STATUS_CODE,'O','OPEN'
,'C','CLOSED'
,'X','CANCELLED','AGED') STATUS_CODE
,A.TRANS_CODE
,(select c.description
from TAS_TRANSACTION_TYPES c
where c.trans_code = a.trans_code) trans_desc
,A.ASSMT_NOTICE_NO
,A.TRANS_DATE
,A.TRANS_AMOUNT
,A.REASON_CODE
,(select d.description
from TAS_REASON_TYPES d
where d.reason_code = a.reason_code) reason_desc
,A.SURCHARGE
,A.INTEREST
,A.COMPROMISE
,A.TOTAL_AMOUNT
,ROWNUM rn
,A.ROWID rid
FROM ARM_AR_TRANSACTIONS A
WHERE A.BRANCH_CODE = p_CODE
ORDER BY 1) B
WHERE rn <= v_rec_per_page + v_start_index) c
WHERE rn >= v_start_index + 1;
SELECT COUNT(*)
INTO o_ar_rec_count
FROM ARM_AR_TRANSACTIONS A
WHERE A.BRANCH_CODE = p_CODE;
o_Status:='SUCCESSFUL';
o_message:='Status sucessful';
DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
BEGIN
o_status:=NVL(o_status,'ERROR');
o_message:=SQLERRM;
LOG_ERROR(SQLERRM, --V_ERROR.ORACLE_ERROR,
NULL,
NULL, --V_BATCHID,
NULL, --V_ERROR.MODULE,
V_CHECKPOINT,
NULL, --V_ERROR.BLOCK,
V_REC,
NULL, --BATCH,
V_RETCODE);
END;
END;