DECLARE
l_refcursor SYS_REFCURSOR;
l_cols NUMBER;
l_desc DBMS_SQL.desc_tab;
l_curs INTEGER;
l_varchar VARCHAR2(4000);
FUNCTION xx_ref_cursor
RETURN SYS_REFCURSOR
IS
l_refcursor SYS_REFCURSOR;
BEGIN
OPEN l_refcursor FOR
SELECT inventory_item_id, segment1, description
FROM mtl_system_items_b
WHERE ROWNUM <= 10;
RETURN l_refcursor;
END;
BEGIN
l_refcursor := xx_ref_cursor;
l_curs := DBMS_SQL.to_cursor_number( l_refcursor);
DBMS_SQL.describe_columns(
c => l_curs
, col_cnt => l_cols
, desc_t => l_desc);
FOR i IN 1 .. l_cols
LOOP
DBMS_SQL.define_column(
l_curs
, i
, l_varchar
, 4000);
END LOOP;
WHILE DBMS_SQL.fetch_rows( l_curs) > 0
LOOP
FOR i IN 1 .. l_cols
LOOP
DBMS_SQL.COLUMN_VALUE(
l_curs
, i
, l_varchar);
DBMS_OUTPUT.put_line(
'Row Number :'
|| DBMS_SQL.last_row_count
|| ': '
|| l_desc( i).col_name
|| ' = '
|| l_varchar);
END LOOP;
END LOOP;
DBMS_SQL.close_cursor( l_curs);
END;
/
l_refcursor SYS_REFCURSOR;
l_cols NUMBER;
l_desc DBMS_SQL.desc_tab;
l_curs INTEGER;
l_varchar VARCHAR2(4000);
FUNCTION xx_ref_cursor
RETURN SYS_REFCURSOR
IS
l_refcursor SYS_REFCURSOR;
BEGIN
OPEN l_refcursor FOR
SELECT inventory_item_id, segment1, description
FROM mtl_system_items_b
WHERE ROWNUM <= 10;
RETURN l_refcursor;
END;
BEGIN
l_refcursor := xx_ref_cursor;
l_curs := DBMS_SQL.to_cursor_number( l_refcursor);
DBMS_SQL.describe_columns(
c => l_curs
, col_cnt => l_cols
, desc_t => l_desc);
FOR i IN 1 .. l_cols
LOOP
DBMS_SQL.define_column(
l_curs
, i
, l_varchar
, 4000);
END LOOP;
WHILE DBMS_SQL.fetch_rows( l_curs) > 0
LOOP
FOR i IN 1 .. l_cols
LOOP
DBMS_SQL.COLUMN_VALUE(
l_curs
, i
, l_varchar);
DBMS_OUTPUT.put_line(
'Row Number :'
|| DBMS_SQL.last_row_count
|| ': '
|| l_desc( i).col_name
|| ' = '
|| l_varchar);
END LOOP;
END LOOP;
DBMS_SQL.close_cursor( l_curs);
END;
/
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.