[SAS] 오라클 UPLOAD 매크로

************************* 공통매크로 설정(SERVER) ****************************/

 

/***** 오라클(SVOC) 컬럼에 COMMENT 추가하기 *****/
/***** 실행 예 : %ORA_COMMENT(테이블명, 컬럼명, COMMENT명); *****/
RSUBMIT;
%MACRO ORA_COMMENT(P_TBL, P_COL, P_CMT);
DATA    _NULL_;
        P_CMT2 = "&P_CMT";

        CALL SYMPUT('P_CMT2', "'"||COMPRESS("&P_CMT")||"'");
RUN;

PROC SQL;
        CONNECT TO ORACLE(USER=USERID PASSWORD='PASSWORD' PATH=SID);
        EXECUTE
        (
                COMMENT ON COLUMN "&P_TBL"."&P_COL" IS &P_CMT2
        ) BY ORACLE;
        DISCONNECT FROM ORACLE;
QUIT;
%MEND;
ENDRSUBMIT;

 

 

/***** 오라클(SVOC) 테이블에 COMMENT 추가하기 *****/
/***** 실행 예 : %ORA_COMMENT_TBL(테이블명, COMMENT명); *****/
RSUBMIT;
%MACRO ORA_COMMENT_TBL(P_TBL, P_CMT);
DATA    _NULL_;
        P_CMT2 = "&P_CMT";
        C_DAY  = PUT(DATETIME(), DATETIME22.);

        CALL SYMPUT('P_CMT2', "'"||COMPRESS("&P_CMT")||"(생성일 : "||COMPRESS(C_DAY)||")'");
RUN;

PROC SQL;
        CONNECT TO ORACLE(USER=USERID PASSWORD='PASSWORD' PATH=SID);
        EXECUTE
        (
                COMMENT ON TABLE "&P_TBL" IS &P_CMT2
        ) BY ORACLE;
        DISCONNECT FROM ORACLE;
QUIT;
%MEND;
ENDRSUBMIT;

 

 

/***** 해당 데이타셋 라벨명을 오라클(SVOC) COMMENT에 추가하기 *****/
/***** 실행 예 : %ORA_COMMENT_RUN(라이브러리명.테이블명); *****/
RSUBMIT;
%MACRO ORA_COMMENT_RUN(P_SET);
DATA    _NULL_;
        X = INDEX("&P_SET", '.');
        LIB = UPCASE(SUBSTR("&P_SET", 1, X-1));
        TBL = UPCASE(SUBSTR("&P_SET", X+1));
       
        CALL SYMPUT('Z_LIB', COMPRESS(LIB));
        CALL SYMPUT('Z_TBL', COMPRESS(TBL));
RUN;

PROC SQL;
        CREATE TABLE TMP_TBL_INFO AS
        SELECT  LIBNAME
                , MEMNAME
                , VARNUM
                , NAME
                , TYPE
                , LENGTH
                , LABEL
        FROM    DICTIONARY.COLUMNS
        WHERE   LIBNAME = UPCASE("&Z_LIB")
        AND     MEMNAME = UPCASE("&Z_TBL")
;
QUIT;

DATA    _NULL_;
SET     TMP_TBL_INFO END=EOF NOBS=TCNT;
        CALL SYMPUT('Z_MEMNAME'||LEFT(_N_), COMPRESS(MEMNAME));
        CALL SYMPUT('Z_NAME'||LEFT(_N_), COMPRESS(NAME));
        CALL SYMPUT('Z_LABEL'||LEFT(_N_), COMPRESS(LABEL));

        IF EOF THEN CALL SYMPUT('Z_TCNT', COMPRESS(TCNT));
RUN;

%DO Z = 1 %TO &Z_TCNT;
        %ORA_COMMENT(Z_&&Z_MEMNAME&Z, &&Z_NAME&Z, &&Z_LABEL&Z);
%END;

%ORA_COMMENT_TBL(Z_&Z_TBL, );
%MEND;
ENDRSUBMIT;

 

 

/***** 오라클(SVOC)에 테이블 UPLOAD하기 *****/
RSUBMIT;
%MACRO ORA_TBL_UPLOAD(P_SET);

DATA    _NULL_;
        X = INDEX("&P_SET", '.');
        LIB = UPCASE(SUBSTR("&P_SET", 1, X-1));
        TBL = UPCASE(SUBSTR("&P_SET", X+1));
       
        CALL SYMPUT('Z_LIB', COMPRESS(LIB));
        CALL SYMPUT('Z_TBL', COMPRESS(TBL));
        CALL SYMPUT('Z_ORA', "ORA_SVOC.Z_"||COMPRESS(TBL));
        CALL SYMPUT('Z_ORAT',"Z_"||COMPRESS(TBL));
RUN;

PROC SQL;
        CONNECT TO ORACLE(USER=USERID PASSWORD='PASSWORD' PATH=SID);
        EXECUTE
        (
                DROP TABLE &Z_ORAT CASCADE CONSTRAINTS PURGE
        ) BY ORACLE;
        DISCONNECT FROM ORACLE;
QUIT;

DATA    &Z_ORA;
SET     &P_SET;
RUN;

%ORA_COMMENT_RUN(&P_SET); /* 오라클 테이블에 LABEL 입력 */
%MEND;
ENDRSUBMIT;

 

 

 

 

 

 

/***** 오라클(SVOC)에 테이블 DROP하기 *****/
RSUBMIT;
%MACRO ORA_TBL_DROP(P_TBL);

libname ORA_SVOC oracle path="SID" user="USERID"    password="PASSWORD"  dbcommit=1000 insertbuff=1000 ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS=NO ;

PROC SQL;
        CREATE TABLE
        SELECT  *
        FROM    ORA_SVOC.TAB
        WHERE   TNAME LIKE 'Z_SC3_101029A%'
        ;
QUIT;

DATA    _NULL_;
        X = INDEX("&P_SET", '.');
        LIB = UPCASE(SUBSTR("&P_SET", 1, X-1));
        TBL = UPCASE(SUBSTR("&P_SET", X+1));
       
        CALL SYMPUT('Z_LIB', COMPRESS(LIB));
        CALL SYMPUT('Z_TBL', COMPRESS(TBL));
        CALL SYMPUT('Z_ORA', "ORA_SVOC.Z_"||COMPRESS(TBL));
        CALL SYMPUT('Z_ORAT',"Z_"||COMPRESS(TBL));
RUN;

PROC SQL;
        CONNECT TO ORACLE(USER=USERID PASSWORD='PASSWORD' PATH=SID);
        EXECUTE
        (
                DROP TABLE &Z_ORAT CASCADE CONSTRAINTS PURGE
        ) BY ORACLE;
        DISCONNECT FROM ORACLE;
QUIT;

DATA    &Z_ORA;
SET     &P_SET;
RUN;

%ORA_COMMENT_RUN(&P_SET); /* 오라클 테이블에 LABEL 입력 */
%MEND;
ENDRSUBMIT;

 

 

 

 

/***** 오라클(SVOC)에 테이블 DROP하기 *****/
RSUBMIT;
OPTION MPRINT;
%MACRO ORA_TBL_DROP(P_TBL);
DATA    _NULL_;
        TBL = "&P_TBL";
        CALL SYMPUT('Z_ORAT',"Z_"||COMPRESS(TBL));
RUN;
%PUT Z_ORAT IS &Z_ORAT;

PROC SQL;
        CREATE TABLE DROP_TBL_LIST AS
        SELECT  TNAME
        FROM    ORA_SVOC.TAB
        WHERE   TNAME LIKE "&Z_ORAT.%"
        AND     TABTYPE = 'TABLE'
        ;
QUIT;

DATA    _NULL_;
SET     DROP_TBL_LIST END=EOF NOBS=TCNT;
        CALL SYMPUT('ZTNAME'||LEFT(_N_), COMPRESS(TNAME));
        IF EOF THEN CALL SYMPUT('TCNTX', COMPRESS(TCNT));
RUN;
%PUT TCNTX IS &TCNTX;

%DO Z = 1 %TO &TCNTX;
%PUT DROP TABLE NAME IS &&ZTNAME&Z;
PROC SQL;
        CONNECT TO ORACLE(USER=USERID PASSWORD='PASSWORD' PATH=SID);
        EXECUTE
        (
                DROP TABLE &&ZTNAME&Z CASCADE CONSTRAINTS PURGE
        ) BY ORACLE;
        DISCONNECT FROM ORACLE;
QUIT;
%END;
%MEND;
ENDRSUBMIT;