PL/SQL 動態CURSOR運用

 

DECLARE
  -- Local variables here
  a        VARCHAR2(20);
  l_cursor NUMBER;
  l_state  NUMBER;
  l_sql    VARCHAR2(2000);
  l_name   VARCHAR2(100);
BEGIN
  -- Test statements here
  a := '''' || 'A' || '''' || ',' || '''' || 'B' || '''';
  dbms_output.put_line('a: ' || a);
  l_sql := 'select name from cux_test t where t.name in (' || a || ')';
  dbms_output.put_line('sql: ' || l_sql);
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
  dbms_sql.define_column(l_cursor, 1, l_name, 100);
  l_state := dbms_sql.EXECUTE(l_cursor);

  LOOP
    EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
    dbms_sql.column_value(l_cursor, 1, l_name);
    dbms_output.put_line('result: ' || l_name);
  END LOOP;
END;