SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY table_name, column_id) = 1
            THEN '' ELSE 'UNION ALL ' END
       || 'SELECT DISTINCT '
       ||         ''''   || table_name  || ''' table_name'
       ||         ', '   || column_id   ||   ' column_id'
       ||         ', ''' || column_name || ''' column_name'
       ||      ', CAST(' || column_name || ' AS VARCHAR2(4000)) column_data'
       || ' FROM ' || owner ||'.'|| table_name exec_sql
  FROM all_tab_columns
 WHERE owner = 'SCOTT'
   AND table_name IN ('EMP', 'DEPT')
;

조회 결과를 다시 실행.

감사합니다 마농님

참고하여 테스트 완료하였습니다~