반응형

개발환경에서 JAVA + ORACLE + JSP 일반적인 환경에서

ORACLE은 일반적으로 PL/SQL을 사용한다

그런데,,,

이 PL/SQL을 사용하다보면 JAVA + JSP를 통한 debugging 을 하기가 굉장히 어렵고

제대로 변수가 입력되었는지... 잘 동작하는지 찾기 어렵다.

그래서... 인터넷 검색을 하다보니

log4j 처럼

log4sql이라는 lib가 있다는 것을 알았다...

프로젝트 홈 : http://log4sql.sourceforge.net/index_kr.html

  Download log4sql-7.0.8



홈페이지에서 보면,

개발을 할경우 우리는 무수히 많은 프레임웍과 도구들을 사용 합니다.
View계층, Model계층 그리고 Controller또는 DataAcess계층에서 많은 도구들과 프레임웍은
현재 거의 모든 layer들에 걸쳐서 사용되고 개발되어지고 있습니다.
이런경우 개발자에게 중요한것중의 하나는 비즈니스 로직인 PL/SQL작업을 하는 것 입니다.
모든 PL/SQL작업은 SQL을 작성하고 실행한 후에 버그가 존재하가나 원하는 결과가 나오지 않을경우 수정하는 것이며
이런작업은 반복적으로 수행됩니다.
이런 경우에 우리는 jdbc또는 Connection Pooling(Apache DBCP) 또는 support 2PC (JTA,JOTM)등을 사용합니다.
또한 우리는 데이터 접근계층의 프레임웍으로 IBATIS나 SpringFramework을 사용할 경우 springJDBC또는 HIBERNATE등을 사용합니다.
이런 상황에서 우리는 의문을 갖게 됩니다. '내가 작성한 SQL이 정상적으로 작동한 것인가?', '내가 입력값으로 넣어준 값들이 제대로 등록된 SQL인가?'
SQL을 확인하고자 개발 소스의 구석구석에 System.out.println(...)으로 확인을 할 것입니다.
개발이 종료된 시점에 주석으로 가려진 가독성이 떨어지는 소스는 유지보수담당자에게 머리아픈 소스가 될 것이고
운영상에 문제가 발생할 경우 해당 SQL을 다시 확인하는 복잡한 작업이 반복될 것입니다.
이런경우를 경험한 개발자라면 log4sql은 간단한 설정으로 많은 편리함을, 개발의 즐거움을 당신에게 드릴것 입니다.

기본 apache common lang lib를 의존한다. : http://commons.apache.org/lang/

오픈 소스이며 apache 라이센스를 따른다 : http://www.apache.org/licenses/LICENSE-2.0





특징들

1.쉬운 설정.
log4sql은 굉장히 간단한 설정을 지원합니다.
개발자들이 할 일은 log4sql-xxx.jar파일을 클래스패스에 복사한 후에
사용하고 있는 driver-class 이름을 log4sql에서 지원하는 driver-class명으로만 바꿔주시면 됩니다.

 2.개발성 향상.
 log4sql은 SQL파라미터 매치된 로그뿐 아니라, 로그의 위치와 걸린시간 그리고 수행결과(INSERT,UPDATE,DELETE)를 보여드립니다.

 3. SQL실행시간을 가장 정확히 파악합니다.
log4sql은 실제 서비스와 동일한 시점의 순수한 SQL실행 시간만을 나타냅니다.
즉 이것은 Connection을 얻어오거나, Connection Pooling기법을 사용하더라도, 실제 작성된 SQL이 DBMS에 전달된 후 결과 값을 받아오기까지의 걸린 시간만을 측정하여
성능에 이상이 있는 SQL등을 쉽게 확인 할 수 있습니다.
 
 4. Application의 성능과 실행에 영향을 주지 않습니다.
 log4sql은 내부적인 문제가 존재하더라도 실제 Application에 영향을 주지는 않습니다.
내부적인 문제가 발생할 경우 해당로그는 설정변경으로 확인이 가능하며, 동시접속사용자가 많은경우 log4sql은 비동기 모드로의 전환이 실행환경에서 가능하도록 설계되었습니다.

5.어떤 유형의 프로젝트라도 적용이 가능합니다.
 JDK1.4이상의 아래의 JDBC드라이버 리스트를 사용하는 어떤 프로젝트라도 사용이 가능합니다.

[ORACLE DRIVER CLASS] 'oracle.jdbc.drirver.OracleDriver'
[MYSQL DRIVER CLASS] 'com.mysql.jdbc.Driver' or 'org.gjt.mm.mysql.Driver'
[SYBASE DRIVER CLASS] 'com.sybase.jdbc2.jdbc.SybDriver'
[DB2 DRIVER CLASS] 'com.ibm.db2.jcc.DB2Driver'
[INFOMIX DRIVER CLASS] 'com.informix.jdbc.IfxDriver'
[POSTGRESQL DRIVER CLASS] 'org.postgresql.Driver'
[MAXDB DRIVER CLASS] 'com.sap.dbtech.jdbc.DriverSapDB'
[FRONTBASE DRIVER CLASS] 'com.frontbase.jdbc.FBJDriver'
[HSQL DRIVER CLASS] 'org.hsqldb.jdbcDriver'
[POINTBASE DRIVER CLASS] 'com.pointbase.jdbc.jdbcUniversalDriver'
[MIMER DRIVER CLASS] 'com.mimer.jdbc.Driver'
[PERVASIVE DRIVER CLASS] 'com.pervasive.jdbc.v2.Driver'
[DAFFODILDB DRIVER CLASS] 'in.co.daffodil.db.jdbc.DaffodilDBDriver'
[JDATASTORE DRIVER CLASS] 'com.borland.datastore.jdbc.DataStoreDriver'
[CACHE DRIVER CLASS] 'com.intersys.jdbc.CacheDriver'
[DERBY DRIVER CLASS] 'org.apache.derby.jdbc.ClientDriver'
[ALTIBASE DRIVER CLASS] 'Altibase.jdbc.driver.AltibaseDriver'
[MCKOI DRIVER CLASS] 'com.mckoi.JDBCDriver'
[JSQL DRIVER CLASS] 'com.jnetdirect.jsql.JSQLDriver'
[JTURBO DRIVER CLASS] 'com.newatlanta.jturbo.driver.Driver'
[JTDS DRIVER CLASS] 'net.sourceforge.jtds.jdbc.Driver'
[INTERCLIENT DRIVER CLASS] 'interbase.interclient.Driver'
[PURE JAVA DRIVER CLASS] 'org.firebirdsql.jdbc.FBDriver'
[JDBC-ODBC DRIVER CLASS] 'sun.jdbc.odbc.JdbcOdbcDriver'
[MSSQL 2000 DRIVER CLASS] 'com.microsoft.jdbc.sqlserver.SQLServerDriver'
[MSSQL 2005 DRIVER CLASS] 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
[CUBRID DRIVER CLASS] 'cubrid.jdbc.driver.CUBRIDDriver'



사용법

 1.log4sql-xxx.jar파일을 클래스패스에 복사하십시요.
log4sql-xxx.zip파일은 sourceforge에서 다운로드한 후 압축을 해제합니다.
그리고나서 압축을 해제한 폴도에 가서 log4sql-xxx.jar을 복사한후 SQL로그를 보고자하는 어플리케이션을 lib디렉토리에 복사합니다.

 2.Driver Class이름을 변경합니다.
만약 Oracle Driver를 사용하신다면 driver-class의 설정은 아마도 아래와 같을 것입니다. <driver class='oracle.jdbc.driver.OracleDriver'>와 같다면 <driver class='core.log.jdbc.driver.OracleDriver'>로 변경하면 됩니다.
아래의 리스트는 log4sql에서 지원하고 있는 driver의 리스트 입니다.



JDBC TYPE Origin Your Driver Class -> log4sql Driver Class
[ORACLE DRIVER CLASS] oracle.jdbc.drirver.OracleDriver -> core.log.jdbc.driver.OracleDriver
[MYSQL DRIVER CLASS] com.mysql.jdbc.Driver' or'org.gjt.mm.mysql.Driver -> core.log.jdbc.driver.MysqlDriver
[SYBASE DRIVER CLASS] com.sybase.jdbc2.jdbc.SybDriver -> core.log.jdbc.driver.SybaseDriver
[DB2 DRIVER CLASS] com.ibm.db2.jcc.DB2Driver -> core.log.jdbc.driver.DB2Driver
[INFOMIX DRIVER CLASS] com.informix.jdbc.IfxDriver -> core.log.jdbc.driver.InfomixDriver
[POSTGRESQL DRIVER CLASS] org.postgresql.Driver -> core.log.jdbc.driver.PostgresqlDriver
[MAXDB DRIVER CLASS] com.sap.dbtech.jdbc.DriverSapDB -> core.log.jdbc.driver.MaxDBDriver
[FRONTBASE DRIVER CLASS] com.frontbase.jdbc.FBJDriver -> core.log.jdbc.driver.FrontBaseDriver
[HSQL DRIVER CLASS] org.hsqldb.jdbcDriver -> core.log.jdbc.driver.HSQLDriver
[POINTBASE DRIVER CLASS] com.pointbase.jdbc.jdbcUniversalDriver -> core.log.jdbc.driver.PointBaseDriver
[MIMER DRIVER CLASS] com.mimer.jdbc.Driver -> core.log.jdbc.driver.MimerDriver
[PERVASIVE DRIVER CLASS] com.pervasive.jdbc.v2.Driver -> core.log.jdbc.driver.PervasiveDriver
[DAFFODILDB DRIVER CLASS] in.co.daffodil.db.jdbc.DaffodilDBDriver -> core.log.jdbc.driver.DaffodiLDBDriver
[JDATASTORE DRIVER CLASS] com.borland.datastore.jdbc.DataStoreDriver -> core.log.jdbc.driver.JdataStoreDriver
[CACHE DRIVER CLASS] com.intersys.jdbc.CacheDriver -> core.log.jdbc.driver.CacheDriver
[DERBY DRIVER CLASS] org.apache.derby.jdbc.ClientDriver -> core.log.jdbc.driver.DerbyDriver
[ALTIBASE DRIVER CLASS] Altibase.jdbc.driver.AltibaseDriver -> core.log.jdbc.driver.AltibaseDriver
[MCKOI DRIVER CLASS] com.mckoi.JDBCDriver -> core.log.jdbc.driver.MckoiDriver
[JSQL DRIVER CLASS] com.jnetdirect.jsql.JSQLDriver -> core.log.jdbc.driver.JsqlDriver
[JTURBO DRIVER CLASS] com.newatlanta.jturbo.driver.Driver -> core.log.jdbc.driver.JturboDriver
[JTDS DRIVER CLASS] net.sourceforge.jtds.jdbc.Driver -> core.log.jdbc.driver.JTdsDriver
[INTERCLIENT DRIVER CLASS] interbase.interclient.Driver -> core.log.jdbc.driver.InterClientDriver
[PURE JAVA DRIVER CLASS] org.firebirdsql.jdbc.FBDriver -> core.log.jdbc.driver.PureJavaDriver
[JDBC-ODBC DRIVER CLASS] sun.jdbc.odbc.JdbcOdbcDriver -> core.log.jdbc.driver.JdbcOdbcDriver
[MSSQL 2000 DRIVER CLASS] com.microsoft.jdbc.sqlserver.SQLServerDriver -> core.log.jdbc.driver.MssqlDriver
[MSSQL 2005 DRIVER CLASS] com.microsoft.sqlserver.jdbc.SQLServerDriver -> core.log.jdbc.driver.Mssql2005Driver
[CUBRID DRIVER CLASS] cubrid.jdbc.driver.CUBRIDDriver -> core.log.jdbc.driver.CUBRIDDriver






설정하기

 우리는 log4sql_conf.jsp페이지를 통해서 보다 쉬운 운영상의 설정페이지를 제공합니다.
 
log4sql-xxx.zip파일을 sourceforge싸이트에서 다운로드한 후에 압축을 풀게되면 log4jsq_conf.jsp파일을 [log4sql-xxx.zip]/log4sql_conf.jsp에서 볼 수 있습니다.
log4sql_conf.jsp파일을 개발하고 있는 어플리케이션의 context-root에 복사하세요
(만약 개발중인 어플리케이션 서버의 context_root이름이 'site-root'이고 context_root까지의 디렉토리 구조가 'c://application/domain/site_root'와 같다면 log4sql_conf.jsp를 'c://application/domain/site_root/log4sql_conf.jsp'처럼 복사하세요)
그런 후에 log4sql_conf.jsp을 브라우져로 열기 위해서 'http://개발ip:개발port/site_root/log4sql_conf.jsp'처럼 접근합니다.

log4sql_sql.jsp의 형태는 아래와 같은 그림이 보여질것 입니다.


l환경설정에 사용되어지는 속성값들은 log4sql_configuration.properties에 위치하고 있으며,
항상 같은 설정이 적용되어지길 원할경우 [log4sql-xxx.jar]/core/log/log4sql_configuration.properties의 위치에서 해당항목의 값을 변경하시면 됩니다.


 1.Log Level(log4sql_configuration.properties 파일에서 'log.level'항목을 참조함).
Log Level은 로그를 남길경우의 우선 순위를 얘기하며, Log4j를 알고계신다면 쉽게 이해가 되실겁니다.
log4sql의 로그는 기본적으로 모두 DEBUG의 형태로 되어있으므로 DEBUG값 이상으로 로그레벨을 변경하게 되면 정상적인 로그를 남기지 않게 됩니다.
로그레벨의 종류는 DEBUG=0,INFO=1,WARNING=2,FATAL=3, ERROR=4, LOGGING_OFF=5와 같으며,
SQL수행도중에 구문문제 또는 잘못된 파라미터 INDEX에러가 발생하게 되면 자동으로 해당 SQL과 원인(Root cause Exception trace)를
ERROR 로그레벨로 남기게 되며, 모든 로그(Error 로그포함)를 남기지 않으려면 로그레벨을 LOGGING_OFF로 변경하시면 됩니다.

 2.Select Fix(log4sql_configuration.properties 파일에서 'query.logging.position.fixed.select'항목을 참조함).
 SELECT SQL에만 해당됩니다.
log4sql은 많은경우에 사용되어질 수 있도록 만들어 졌습니다.
따라서 우리는 log4sql을 사용하는 어플리케이션에서 어떠한 프레임웍을 사용하는지, 어떤 서버에서 실행되는지 알수가 없습니다.
문제가 있는 SQL일경우 사용자는 DEBUGING을 해야 할 것입니다. 그러기 위해선 어떤 클래서에서, 어떤 메소드에서 실행이 되었는지 알아야합니다.
log4sql은 SQL을 실행시킨 패키지와 클래스 그리고 어떤 메소드의 몇번째 라인에서 실행이 되었는지 실행된 시간과 함께 나타내 줍니다.
하지만, 위와같은 이유로 적합한 위치가 아닐수도 있으므로 log4sql은 적합한 위치를 사용자가 선택할 수 있도록 모든 실행에 참여한 클래스와 메소드들의 항목을 모두 보여줍니다.

 3.Select Position(log4sql_configuration.properties파일에서 'query.logging.position.select'항목을 참조함).
 2번 항목에서 적합하다고 생각되는 항목의 번호를 Select Position에 입력한 후 확인 버튼을 클릭하거나, 영구적으로 변경을 원하는 경우는 log4sql_configuration.properties에서 query.logging.position.select항목의 값으로 변경하시면 됩니다

 4.None Select Fix(log4sql_configuration.properties파일에서 'query.logging.position.fixed.none_select'항목을 참조함).
 INSERT, UPDATE, DELETE SQL에만 해당됩니다.
log4sql은 많은경우에 사용되어질 수 있도록 만들어 졌습니다.
따라서 우리는 log4sql을 사용하는 어플리케이션에서 어떠한 프레임웍을 사용하는지, 어떤 서버에서 실행되는지 알수가 없습니다.
문제가 있는 SQL일경우 사용자는 DEBUGING을 해야 할 것입니다. 그러기 위해선 어떤 클래서에서, 어떤 메소드에서 실행이 되었는지 알아야합니다.
log4sql은 SQL을 실행시킨 패키지와 클래스 그리고 어떤 메소드의 몇번째 라인에서 실행이 되었는지 실행된 시간과 함께 나타내 줍니다.
하지만, 위와같은 이유로 적합한 위치가 아닐수도 있으므로 log4sql은 적합한 위치를 사용자가 선택할 수 있도록 모든 실행에 참여한 클래스와 메소드들의 항목을 모두 보여줍니다

 5.None Select Position(log4sql_configuration.properties파일에서 'query.logging.position.none_select'항목을 참조함).
4번 항목에서 적합하다고 생각되는 항목의 번호를 Select Position에 입력한 후 확인 버튼을 클릭하거나, 영구적으로 변경을 원하는 경우는 log4sql_configuration.properties에서 query.logging.position.select항목의 값으로 변경하시면 됩니다.

 6.View Parameter Position(log4sql_configuration.properties파일에서 'query.logging.view.position'항목을 참조함).
 PreparedStatement를 사용할 경우 원하는 결과가 나오지 않을경우 해당 파리미터의 값이 적당하게 메치되었는지 궁금하게 됩니다.
이럴경우 개발자들은 난감한 상황에 접하게 됩니다. log4sql은 모든 파라미터가 결합된 SQL로그를 보여줍니다.
하지만, SQL문자열에 직접 입력된 값이 존재할 경우 개발자는 어떤값이 입력된 값인지, 어떤값이 문자열에 존재하는 값인지 혼동을 할 수 있습니다.
View Parameter Position값을 'true'로 변경할 경우 SQL log에서 입력된 값의 좌측에 '/**P*/'와 같은 표시를 보여줍니다. .

 7.View Internal Error(log4sql_configuration.properties파일에서 'query.logging.view.internal.exception'항목을 참조함).
 log4sql은 내부적인 문제가 있더라도 실제 SQL의 실행에는 영향을 미치지 않습니다.
log가 출력되지 않는 비정상적인 경우에 View Internal Error의 값을 'true'로 변경할 경우 내부적인 오류라면 오류의 내용을 출력할 것입니다.
이 내용은 사용자에게 고맙지 않은 경우일 것이나, 해당 내용을 log4sql개발팀에게 보내준다면 보다 견고한 버젼으로의 변경이 용이할 것입니다.

 8.Log To Asynchronous(log4sql_configuration.properties파일에서 'query.logging.asynchronous'항목을 참조함).
 log4sql은 SQL로그의 출력에 있어서 두가지 방법을 사용합니다. 동기적인 경우(Log To Asynchronous의 값을 'false'로 변경할 경우)모든 SQL로그는 순차적으로 실행이 될 것입니다.
이경우 log4sql에서의 모든 작업이 수행된 후에 다음 작업을 수행 할 것입니다.(많은 동시접속자가 존재하는 많은 로그가 출력되고 있는 상황에서 성능에 영향을 줄 수가 있습니다.
하지만 개발시 소수의 개발자에 의해서 디버그 용도로 실행되고 있을경우는 거의 영향을 주지 않습니다.) 비동기적인 경우(Log To Asynchronous의 값을 'true'로 변경할 경우)모든 SQL로그는 항상 순차적으로 실행되지 않습니다.
이경우 log4sql에서의 모든 작업과 SQL로그를 남기는 작업은 별도의 Thread로 동작할 것입니다.(많은 동시접속자가 존재(300tps)하는 많은 로그가 출력되고 있는 상황에서 성능을
보장하여 줄 것이나 이경우 로그는 순차적으로 남기지 않습니다.)

 9.View Appointed Package(log4sql_configuration.properties파일에서 'query.logging.view.appoint'항목을 참조함).
 아마도 개발자 혼자서 모든 개발을 진행하는 일은 흔치 않을것입니다.
따라서 log4sql은 개발자가 개발하고 있는 패키지 혹은 클래스 혹은 메소드에 한해서만 로그를 남길수 있습니다.
설정의 방법은 아래의 내용을 참조하세요.

[예약된 기호들]*..* : 모든 패키지를 의미합니다, * : 모든 클래스를 의미합니다, (..) : 모든 메소드들을 의미합니다
View Appointed Package의 값을 *..*.*.(..)로 입력한다면 실행 되고있는 모든 SQL로그가 보여질 것 입니다.
parttern 1 : *..*.*.(..) (모든 로그가 보여집니다.)
parttern 2 : net.*..*.*.(..) ('net'으로 시작되는 패키지 하위의 SQL모든 클래스의 로그가 출력됩니다.)
parttern 3 : *..*.Package_name.*..*.*.(..) ('Package_name'이 중간에 포함된 패키지의 로그가 보여집니다.)
parttern 4 : *..*.Package_name.*.(..)('Package_name'으로끝나는 모든 패키지의 로그가 보여집니다.)
parttern 5 : *..*.Class_name.(..) (클래스 이름이 'Class_name'인 곳의 로그가 보여집니다.)
parttern 6 : *..*.Package_name.Class_name.(..) (패지지가 'Package_name'로 종료되고 클래스 이름이 'Class_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 7 : *..*.Package_name.*..*.Class_name.(..) (패키지가 'Package_name'을 포함하고 있고 클래스 이름이 'Class_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 8 : *..*.Package_name.Class_name.Method_name (패키지의 이름이 'Package_name'로 종료되며, 클래스의 이름이 'Class_name'이고 메소드의 이름이 'Method_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 9 : *..*.Package_name.*..*.Class_name.Method_name (패키지의 이름이 'Package_name'을 포함하고 있고 클래스 이름이 'Class_name'이고 메소드 이름이 'Method_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 10 : *..*.Class_name.Method_name (모든 패키지에서 클래스 이름이 'Class_name'이고 메소드 이름이 'Method_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 11 : *..*.Package_name.*.Method_name (패키지 이름이 'Package_name'로 종료되고 메소드 이름이 'Method_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 12 : *..*.Package_name.*..*.*.Method_name (패키지 이름에서 'Package_name'를 포함하고 있고 메소드 이름이 'Method_name'인 곳의 SQL 로그가 출력됩니다.)
parttern 13 : *..*.*.Method_name (모든 패키지와 모든 클래스 중에서 메소드 이름이 'Method_name'인 곳의 SQL모든 로그가 출력됩니다.)
parttern 14 : net.sourceforge.log4sql.Class_name.Method_name (패키지가 'net.sourceforge.log4sql'이고 클래스 이름이 'Class_name'이고 메소드 이름이 'Method_name'인 곳의 SQL로그가 출력됩니다.)


p.$. : 아직 사용해 보진 않았다... 사용해야 하는데... 우선 간략하게 정리해본다... 쿠쿠... 시간나면 더 추가 ㄱ ~



반응형
반응형

EXCEPTION
    WHEN exception명1 [OR exception명2...] THEN
        문장1;
        문장2; ...
    [WHEN exception명3 [OR exception명4...] THEN
        문장1;
        문장2; ...]
    [WHEN OTHERS THEN
        문장1;
        문장2; ...]

 

WHEN OTHERS 구문은 EXCEPTION 처리의 마지막 구문으로서 미리 선언되지 못한 모든 경우의 EXCEPTION처리를 가능하게 합니다.

EXCEPTION Keyword에 의해 Error처리가 시작됩니다.

EXCEPTION 처리구문이 필요하지 않으면 생략할 수 있습니다.

 

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

Predefined EXCEPTION

DECLARE Section에서 선언할 필요가 없습니다.  

Oracle Server 에러 중에서 자주 발생되는 20가지 에러에 대해 미리 정의되어 있는 EXCEPTION입니다.

해당 EXCEPTION 처리 루틴에서 미리 정의된 EXCEPTION명을 참조하여 에러를 처리합니다.
Predefined EXCEPTION의 종류에는 다음과 같은 것들이 있습니다.

EXCEPTION명

에러번호

설   명

NO_DATA_FOUND

ORA-01403

데이터를 반환하지 않은 SELECT문

TOO_MANY_ROWS

ORA-01422

두 개 이상을 반환한 SELECT문

INVALID_CURSOR

ORA-01001

잘못된 CURSOR 연산 발생

ZERO_DIVIDE

ORA-01476

0으로 나누기

DUP_VAL_ON_INDEX

ORA-00001

UNIQUE COLUMN에 중복된 값을
입력할 때

CURSOR_ALREADY_OPEN

ORA-06511

이미 열러 있는 커서를 여는 경우

INVALID_NUMBER

ORA-01722

문자열을 숫자로 전환하지 못한 경우

LOGIN_DENIED

ORA-01017

유효하지 않은 사용자로 LOGON 시도

NOT_LOGGED_ON

ORA-01012

PL/SQL 프로그램이 오라클에 연결되지
않은 상태서 호출

PROGRAM_ERROR

ORA-06501

PL/SQL 내부에 오류

STORAGE_ERROR

ORA-06500

PL/SQL에 메모리 부족

TIMEOUT_ON_RESOURCE

ORA-00051

오라클이 자원을 기다리는 동안 시간
초과 발생

VALUE_ERROR

ORA-06502

산술, 절단 등에서 크기가 다른 오류 발생

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

 

CREATE OR REPLACE PROCEDURE show_emp
(v_salary   IN   s_emp.salary%type )
IS
v_name    s_emp.name%TYPE;
v_sal        s_emp.salary%TYPE;
v_title      s_emp.title%TYPE;
BEGIN
SELECT name, salary, title
INTO v_name, v_sal , v_title
FROM s_emp
WHERE salary = v_salary ;
DBMS_OUTPUT.PUT_LINE(' 이 름 '||' 급 여 '||' 직 책 '  );
DBMS_OUTPUT.PUT_LINE('--------------------------------' );
DBMS_OUTPUT.PUT_LINE(v_name ||v_sal ||v_title);
EXCEPTION
WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('ERROR!!!-해당급여를 받는 사원은
없습니다.');
WHEN TOO_MANY_ROWS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR!!!-해당급여를 받는 사원이
너무 많습니다.');

END;
/

 

SQL>EXECUTE show_emp (2400)
    ERROR!!!-해당급여를 받는 사원이 너무 많습니다.
    PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>EXECUTE show_emp (500)
    ERROR!!!-해당급여를 받는 사원은 없습니다.
    PL/SQL 처리가 정상적으로 완료되었습니다.

Non-Predefined EXCEPTION

Oracle Server Error중 미리 정의되지 않은 Error는 사용자가 DECLARE Section에서 EXCEPTION명을 정의하고 Oracle Server에서 제공하는 Error번호를 사용하여 Error와
연결한 후 EXCEPTION Section에서 Error처리 Routine을 기술합니다.

DECLARE
   exception명 EXCEPTION;
   PRAGMA EXCEPTION_INIT(exception명, 에러번호);
BEGIN
. . .
EXCEPTION
    WHEN exception명 THEN
. . .
END;

 


 

S_PRODUCT 테이블에서 제품번호를 입력받아 제품을 삭제하는 프로그램을
작성하면 다음과 같습니다.
Oracle Server 에러번호 -2292인 무결성 제약조건 위반 에러처리를 합니다.

 

CREATE OR REPLACE PROCEDURE del_product
(  v_id  IN     s_product.id%TYPE )
IS
  
 fk_error     EXCEPTION;
   PRAGMA    EXCEPTION_INIT(fk_error , -2292);

BEGIN
   DELETE FROM  s_product
   WHERE                id = v_id;
   COMMIT;
EXCEPTION
  
 WHEN fk_error THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE('참조되는 CHILD RECORD가 있으므로 삭제할
수 없습니다.');

END;
/

 

 

SQL>EXECUTE del_product(50530)
참조되는 CHILD RECORD가 있으므로 삭제할 수 없습니다.

PL/SQL 처리가 정상적으로 완료되었습니다.    

User Defined EXCEPTION

사용자 정의 EXCEPTION은 Oracle Server Error는 아니고, 사용자가 정한 조건이 만족되지 않을 때, Error를 발생시키는 방법입니다.

DECLARE Section에서 EXCEPTION명을 정의하고
BEGIN Section에서 RAISE문을 써서 에러를 발생시킵니다. 그리고 EXCEPTION Section에서 에러 처리문을 기술합니다.

DECLARE
    exception명 EXCEPTION;
BEGIN
   RAISE exception명;
. . .
EXCEPTION
    WHEN exception명 THEN
. . .
END;

 



 

S_EMP 테이블에 새로운 사원을 입력하는 프로그램을 작성하면
다음과 같습니다.단, 사번은 일련번호로 부여할 수 있도록 값을 지정하고
이름, 직책, 급여, 커미션은 사용자가 입력합니다.
단, 급여를 600이하로 입력시에는 입력이 불가능하도록 처리합니다.

 

CREATE OR REPLACE PROCEDURE in_emp
(v_name    IN    s_emp.name%TYPE ,
 v_sal      IN    s_emp.salary%TYPE ,
 v_title     IN    s_emp.title%TYPE ,
 v_comm   IN    s_emp.commission_pct%TYPE )
IS
v_id       s_emp.id%TYPE ;
lowsal_err   EXCEPTION ;
BEGIN
  SELECT MAX(id)+1
  INTO v_id
  FROM s_emp ;
 IF  v_sal  >= 600 THEN
  INSERT INTO  s_emp
   (id,name,salary,title,commission_pct,start_date)
  VALUES(v_id,v_name, v_sal,v_title,v_comm,SYSDATE) ;
 ELSE
  RAISE  lowsal_err ;
 END IF ;
EXCEPTION
WHEN lowsal_err THEN
  DBMS_OUTPUT.PUT_LINE ('ERROR!!!-지정한 급여가 너무 적습니다.
600이상으로 다시 입력하세요.') ;

END;
/

 

 

SQL>EXECUTE in_emp('김흥국',500,'과장',12.5)
ERROR!!!-지정한 급여가 너무 적습니다.600이상으로 다시 입력하세요.
PL/SQL 처리가 정상적으로 완료되었습니다.  

SQL>EXECUTE in_emp('김흥국',900,'과장',12.5)
PL/SQL 처리가 정상적으로 완료되었습니다

 

일반적인 Error처리를 위하여 Oracle에서 제공하는 함수인 SQLCODE, SQLERRM을
활용할 수 있습니다.

SQLCODE : Oracle에서 지정된 Error Code를 숫자로 Return

SQLERRM :

Oracle에서 지정된 Error Code에 해당하는 Error Message를
Return

 

SQLCODE, SQLERRM 함수를 활용하는 것 외에도 WHEN OTHERS절을 사용하여 Error를
처리할 수 있습니다. WHEN OTHERS절은 예상치 못한 Error처리를 위해서 미리 선언되지
않은 모든 종류의 Error를 처리합니다.

이번에는 SQLCODE와 SQLERRM에 대해서 예를 통해 살펴봅니다.



User Defined EXCEPTION의 예제9에서 작성한 Procedure를 이용하여 데이터를
입력하되 단, 커미션을 30%로 지정하여 실행하시오.

 

BEGIN in_emp('이한이',1200,'사원',30); END;

*
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.S_EMP_COMMISSION_PCT_CK)이
위배되었습니다
ORA-06512: "SCOTT.IN_EMP", 줄 14에서
ORA-06512: 줄 1에서  

 

 

SQL>SELECT constraint_name, constraint_type, search_condition
    2  FROM user_constraints
    3  WHERE table_name = 'S_EMP' ;


 

 


 

예제 9에서 실행시에 해당 COMMISSION_PCT를 입력하지 않는 경우에도
프로그램이 정상적으로 수행되도록 User Defined EXCEPTION의 예제9에서
작성한 프로그램을 보완하면 다음과 같습니다.

 

CREATE OR REPLACE PROCEDURE in_emp
(v_name    IN    s_emp.name%TYPE ,
 v_sal      IN    s_emp.salary%TYPE ,
 v_title     IN    s_emp.title%TYPE ,
 v_comm   IN    s_emp.commission_pct%TYPE )
IS
v_id            s_emp.id%TYPE ;
lowsal_err   EXCEPTION ;
v_code         NUMBER ;
v_message    VARCHAR2(100) ;
 
BEGIN
  SELECT MAX(id)+1
  INTO v_id
  FROM s_emp ;
 IF  v_sal  >= 600 THEN
  INSERT INTO  s_emp
   (id,name,salary,title,commission_pct,start_date)
  VALUES(v_id,v_name, v_sal,v_title,v_comm,SYSDATE) ;
 ELSE
  RAISE  lowsal_err ;
 END IF ;
EXCEPTION
WHEN lowsal_err THEN
  DBMS_OUTPUT.PUT_LINE ('ERROR!!!-지정한 급여가 너무 적습니다.
600이상으로 다시 입력하세요.') ;
WHEN OTHERS THEN
   v_code := SQLCODE ;
   v_message := SQLERRM ;
DBMS_OUTPUT.PUT_LINE('에러코드   =>'||v_code) ;
DBMS_OUTPUT.PUT_LINE('에러메세지=>'||v_message) ;

END;
/

 

 

SQL>EXECUTE in_emp('이한이',1200,'사원',30)
에러코드   =>-2290
에러메세지=>ORA-02290: 체크 제약조건
(SCOTT.S_EMP_COMMISSION_PCT_CK)이 위배되었습니다

PL/SQL 처리가 정상적으로 완료되었습니다.

반응형

'Database > ORACLE' 카테고리의 다른 글

System Account Lock (System 계정 락 걸림)  (0) 2010.07.15
오라클 클론 디비로 복구 하기  (0) 2010.07.13
RMAN 사용법  (0) 2010.07.06
오라클 암호화 기능  (0) 2010.07.02
오라클 pump 관련 자료  (0) 2010.06.30

+ Recent posts