Please check the LiveSQL.
I'm getting error with the clob column, but it works with the varchar2. So my question is how to properly handle the clob column with dbms_sql?
You need to define the column as a CLOB. It's currently defined as a VARCHAR2 at line 18:
dbms_sql.define_column(l_cur, i, l_varchar2, 2000);
Change this to a CLOB variable and remove the length check:
DECLARE
l_clob CLOB;
l_refc SYS_REFCURSOR;
PROCEDURE refc2table(pi_refc IN SYS_REFCURSOR) IS
l_refcursor SYS_REFCURSOR;
l_clob CLOB;
l_varchar2 VARCHAR2(4000);
l_col_count NUMBER;
l_cur INTEGER;
l_desc dbms_sql.desc_tab2;
BEGIN
l_refcursor := pi_refc;
l_cur := dbms_sql.to_cursor_number(l_refcursor);
l_col_count := 0;
FOR i IN 1 .. 255 LOOP
BEGIN
dbms_sql.define_column(l_cur, i, l_clob);
l_col_count := i;
EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -1007) THEN
EXIT;
END IF;
END;
END LOOP;
dbms_sql.describe_columns2(l_cur, l_col_count, l_desc);
dbms_output.put_line('l_desc.first=' || l_desc.first);
dbms_output.put_line('l_desc.last=' || l_desc.last);
LOOP
EXIT WHEN(dbms_sql.fetch_rows(l_cur) <= 0);
FOR nn IN l_desc.first .. l_desc.last LOOP
dbms_output.put_line('l_desc(' || nn || ').col_name=' || l_desc(nn).col_name);
dbms_output.put_line('l_desc(' || nn || ').col_type=' || l_desc(nn).col_type);
IF l_desc(nn).col_type = dbms_sql.clob_type THEN
dbms_sql.column_value(l_cur, nn, l_clob);
dbms_output.put_line('l_clob=' || l_clob);
ELSIF l_desc(nn).col_type = dbms_sql.varchar2_type THEN
dbms_sql.column_value(l_cur, nn, l_varchar2);
dbms_output.put_line('l_varchar2=' || l_varchar2);
END IF;
END LOOP;
END LOOP;
dbms_sql.close_cursor(l_cur);
END refc2table;
BEGIN
l_clob := 'foo';
OPEN l_refc FOR SELECT l_clob cc FROM dual;
--OPEN l_refc FOR SELECT 'foo' cc FROM dual;
refc2table(l_refc);
END;
/
l_desc.first=1
l_desc.last=1
l_desc(1).col_name=CC
l_desc(1).col_type=112
l_clob=foo