exportoracle

exportoracle.sql

SELECT LISTAGG(PCMN_CODE, ',') WITHIN GROUP (ORDER BY PCMN_CODE) OVER 
(PARTITION BY CTYP_CODE ) FROM PKG_CMN_MST WHERE CTYP_CODE = 12;
---------
SELECT LISTAGG(''||CHR('39')||''||CHR('39')||''||CHR('39')||'||V_REC.'||COLUMN_NAME , '||'||CHR('39')||''||CHR('39')||''||CHR('39')||',') WITHIN GROUP (ORDER BY COLUMN_ID) OVER 
(PARTITION BY TABLE_NAME ) FROM USER_TAB_COLS WHERE 
TABLE_NAME = 'CHKLST_INF' ORDER BY COLUMN_ID ;



SELECT LISTAGG(''||COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_ID) OVER 
(PARTITION BY TABLE_NAME ) FROM USER_TAB_COLS WHERE 
TABLE_NAME = 'CHKLST_INF' ORDER BY COLUMN_ID ;
-----------------------------
SET SERVEROUTPUT ON;
Begin
 FOR V_REC IN (
   SELECT * FROM CHKLST_INF
  ) LOOP
    DBMS_OUTPUT.PUT('INSERT INTO CHKLST_INF(CLI_CODE,CHKLST_CODE,CLI_SUBNO,CLI_NO_NAME,CLI_CHKLST_DESC,CLI_CHILD_STATUS,CLI_CODE_REF,STATUS)');
    dbms_output.put('VALUES('''||V_REC.CLI_CODE||''','''||V_REC.CHKLST_CODE||''','''||V_REC.CLI_SUBNO||''','''||V_REC.CLI_NO_NAME||''','''||V_REC.CLI_CHKLST_DESC||''','''||V_REC.CLI_CHILD_STATUS||''','''||V_REC.CLI_CODE_REF||''','''||V_REC.STATUS ||''||CHR('39')||');');
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;
  END;
/
  ------------'||CHR('39')||'
 SELECT * FROM CHKLST_INF;