1. root 로그인

2 adduser #####

3. passwd #####

4. su – enterprisedb

5. cd bin

6. ./psql template1

7. CREATE USER ###### WITH PASSWORD '********';

8. CREATE DATABASE db1;

9. GRANT ALL PRIVILEGES ON DATABASE db1 to ######;

10. \q

CREATE OR REPLACE PROCEDURE GCAUSER.PROC_CERT_VALIDATION(V_DN IN VARCHAR,V_CERTSTAT OUT VARCHAR) AS
/******************************************************************************
   NAME:       PROC_CERT_VALIDATION
   PURPOSE:    인증서를 경로 풀검증

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   0.5        2010-04-23  강혁             1.인증서검증

******************************************************************************/

v_issuerdn VARCHAR2(500);
v_dnloop VARCHAR2(500);
v_stat VARCHAR2(32);qui
v_loop NUMBER;
   
BEGIN
   
    
    
    SELECT ISSUERDN,STAT  into v_issuerdn ,v_stat
    FROM PKIENTITYPKC
    WHERE DN=V_DN;
    
    --PRINT :v_stat;
    IF v_stat!='GOOD' THEN
 BEGIN
       V_CERTSTAT := v_stat;
       RETURN;
    END;
    END IF;
   
    v_loop := 1;
   
    IF v_stat='GOOD' THEN
    BEGIN
        WHILE v_loop=1 LOOP
        BEGIN
            SELECT ISSUERDN,STAT,DN  into v_issuerdn ,v_stat,v_dnloop
            FROM A A,B B
            WHERE B.DN=v_issuerdn AND B.SID=A.ASID;
            IF v_stat!='GOOD' THEN
            BEGIN
                V_CERTSTAT := 'FAIL';
                RETURN; 
            END;      
            END IF;
            IF v_issuerdn=v_dnloop THEN
            BEGIN
                 IF v_stat!='GOOD' THEN
                 BEGIN
                     V_CERTSTAT := 'FAIL';
                     RETURN; 
                 END;
                 ELSE
                 BEGIN
                    V_CERTSTAT := v_stat;  
                    v_loop := 0;
                    RETURN;
                 END;  
                 END IF;
            END;
            END IF;
               
        END;
        END LOOP;  
               
    END;
    END IF;
   
   
/*EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;*/
END PROC_CERT_VALIDATION;
/

===========================================================================

sqlplus 에 접속해서
variable  KKK VARCHAR2(32);

EXECUTE  PROC_CERT_VALIDATION('인증서 DN값',:KKK);

print kkk

차례대로 입력한뒤  하면 결과값이 GOOD 또는 FAIL이 나오는 프로시져