Page 1 of 1

oracle: rowid in cursor

Posted: Sun Feb 14, 2010 9:39 pm
by pedro_problemado
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;