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_varchar2, 2000);
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;
ORA-06562: type of out argument must match type of column or bind variable ORA-06512: at "SYS.DBMS_SQL", line 1771 ORA-06512: at line 39 ORA-06512: at line 53 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-06562
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_varchar2, 2000);
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;
Statement processed.
l_desc.first=1
l_desc.last=1
l_desc(1).col_name=CC
l_desc(1).col_type=96
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_varchar2, 2000);
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;
ORA-06562: type of out argument must match type of column or bind variable ORA-06512: at "SYS.DBMS_SQL", line 1771 ORA-06512: at line 39 ORA-06512: at line 53 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-06562