컴퓨터이야기

디자인상의 문제, 코딩상의 문제, 하드웨어 문제, 비정상적인 값의 입력 등등....
프로그램을 수행하다 보면 다양한 에러들을 접하게 된다. 프로그램을 정상적으로
수행하기 위해서는 이들 에러에 대한 적절한 대책이 필요 한데, 특히 SQL문의
수행과 관련된 에러의 처리는 데이타의 무결성을 유지하고, 정확한 트랜젝션
수행의 기본이 된다. PRO*C에서는 SQL문과 관련된 에러를 처리하기 위한 다양한
방법을 제공하고 있는데, 크게 상태 변수(SQLSTATE, SQLCODE)를 사용하는 방법과 SQLCA를 활용하는 방법으로 나눠볼 수 있다.

1) MODE OPTION값에 따른 ERROR 처리

* MODE = ANSI 일때
SQLSTATE나 SQLCODE중에 하나는 반드시 사용해야 한다.
SQLCA는 선택적이다.
* MODE = ORACLE
SQLCA를 사용해야 한다.
SQLSTATE나 SQLCODE는 선언해도 사용되지 않는다.

2) SQLCODE

SQLCODE는 SQL문을 수행한 STATUS CODE 값을 가지고 있다.
이 변수는 LONG으로 선언해서 사용해야 한다.

< 예제 >
DECLARE SQL BEGIN DECLARE SECTION;
int emp_number, dept_number;
EXEC SQL END DECLARE SECTION;
long SQLCODE;

3) SQLSTATE 상태 변수 (status variable)

SQLCODE와 달리 SQLSTATE는 에러와 warning을 모두 저장하고 있고, character
5자리의 값을 가지고 있다. SQLSTATE를 사용하기 위해서는 반드시 CHAR SQLSTATE[6]; 으로 선언해야 한다.
SQLSTATE 상태 코드는 예외의 유형을 구분해주는 CLASS 두 자리와 구체적인
예외를 말해주는 3자리의 SUBCLASS로 구성된다. 이들 CODE값과 그 의미는 PROGRAMMER*S GIDE TO THE ORACLE PRO*C/C++ PRECOMPILER의 Handling
runtime error편에 자세히 나와 있다.

4) SQLCA(COMMUNICATION AREA)의 사용

SQLCA는 ORACLE COMMUNICATION을 다루는 C STRUCT를 말한다. 이는 상태 코드,
경고 FLAG, 처리된 ROW 수, PARSING ERROR, 기타 에러 메세지에 관한 정보를
담고 있다.


* SQLCA의 선언
EXEC SQL INCLUDE SQLCA; 또는 # include <sqlca.h>

* SQLCA STRUCTURE와 의미
- sqlcaid : identifier
- sqlcabc : SQLCA의 총size
- sqlcode : 최근에 실행된 SQL문의 상태코드
0 에러 없이 정상 수행
>0 SQL문이 수행되기는 했지만 exception이 발생
<0 SQL문 수행 안됨. sqlca.sqlerr[4]로 내용 확인 가능
- sqlerrm : 에러 메세지 길이(sqlerrml)와 메시지 내용(sqlerrmc)
메세지는 총 70자리 까지 밖에 저장되지 않음. 이 이상
의 메세지를 보려면 sqlglm()을 이용해야 한다.(sqlcpr.h
에 정의)
만약 sqlcode가 0일때 sqlerrmc의 내용을 보면 이전에
수행된 에러 메시지가 남아 있으므로 sqlcode < 0일때만
사용하는 것이 바람직하다.

- sqlerrd : SQL문 수행과 관련된 각종 INTEGER정보들
sqlerrd[0], sqlerrd[1], sqlerrd[3], sqlerrd[5]
: 현재 사용하고 있지 않음
sqlerrd[2] : 처리된 row수. cascade를 처리된 row수는 제외됨.
Array processing을 하는 중에 에러가 발생했다면, 이
변수에는 정상적으로 처리된 row의 수를 return하게
된다.
sqlerrd[4] : SQL문에서 parse error가 발생한 지점. ( 0부터 시작)

- sqlwarn : warning flag. 해당 warning 발생시 *W*를 set한다.
sqlwarn[0] : warning 발생 여부 set
sqlwarn[1] : character data의 경우truncated 여부 set
indicator변수를 사용하면 원래의 size 확인 가능.
sqlwarn[2] : NULL 값이 group함수에 사용되었는지를 set
sqlwarn[3] : select한 column과 bind한 column의 갯수가 다른
경우 set
sqlwarn[4] : where절이 없는update, delete문 수행 시 set.
where절이 없이 수행된다는 것은 비정상적인 상황
이기 때문에 warning을 한다.
sqlwarn[5]: EXEC SQL CREATE {PROCEDURE/FUNCTION |
PACKAGE|PACKAGE BODY]문 수행시 컴파일 에러
발생시 set

5) WHENEVER문 사용법

디폴트로 컴파일된 프로그램은 오라클 에러나, warning을 무시하고, 가능하다면
프로그램 수행을 계속한다. 비정상적인 동작에 적절한 조치를 취하기 위해 자동
으로 에러나 warning의 발생을 감지하려면 WHENEVER문을 사용하면 된다.
WHENEVER문의 scope rule은 위치에 의해 결정되는 것이지, logical하게
결정되는 것이 아니라는 점을 주의해야 한다. 그러므로 수행될 SQL 문 이전에
WHENEVER절을 위치하도록 해야 한다. 그리고, 다음 WHENEVER절이 나타나기
이전까지만 효과가 있다.

WHENEVER SQLWARING문을 사용하려면 반드시 SQLCA를 선언해야 한다.

* SYNTAX
EXEC SQL WHENEVER <condition> <action>;
* CONDITION유형
SQLWARNING / SQLERROR / NOT FOUND
* ACTION 유형
- CONTINUE :WHENEVER문을 사용하지 않은 것과 동일한 결과
- DO
- GOTO <label>
- STOP : COMMIT되지 않은 WORK은 모두 ROLLBACK 시킴.

condition과 action을 적절히 혼합하여 사용하면 되는데, 조심해야 할 사항은
WHENEVER SQLERROR GOTO <lable>을 사용할때 무한 loop에 걸리지 않도록 주의
해야 한다. (대신에 WHENEVER SQLERROR CONTINUE를 사용하는 것이 좋다.)

다음은 WHENEVER ...... DO 구문을 사용한 간단한 예제이다.

......
EXEC SQL WHENEVER SQLERROR DO handle_insert_error(*Insert Error*);
EXEC SQL INSERT INTO emp (empno, ename,deptno)
VALUES (:v_empno, :v_ename, :v_deptno);
EXEC SQL WHENEVER SQLERROR DO handle_delete_error(*delete Error*);

......
handle_insert_error(char *stmt)
{
switch(sqlca.sqlcode)
{ case -1 : /*duplicate l\key value */
......
break;
case -1401 : /*value too large*/
......
break;
default : /*do somthing here too*/
........
break;
}
}

handle_delete_error(char *stmt)
{
printf("%s\n\n", stmt);
if ( sqlca.sqlerrd[2] == 0 )
{ /* no rows deleted */
........
}
else
{
......
}
}

http://blog.naver.com/maxntop/120005086625

PROC 에서 9I SYNTAX (CASE WHEN , WAIT XX, JOINS) 사용시 PCC-S-02201 발생
=====================================================================

Problem Description
-------------------
9i 에 새롭게 소개된 다음과 같은 sql 문장들은 pro*c programs 에서는
사용하지 못하고 다음과 PCC-S-02201 에러를 발생하게 됩니다.

이것은 9i Pro*C parser 가 새로운 9i sql syntax features 를 인식하지
못하기 때문입니다.

다음과 같은 경우가 있습니다.

1)
-------
EXEC SQL DECLARE c1 CURSOR FOR
select job,SUM(CASE WHEN job='CLERK' then sal else sal*10 end) from
emp group by job;
-------
$ proc iname=programname.pc
PCC-S-02201, Encountered the symbol "WHEN" when expecting one of the
following:( ) * + - / . @ | at, day, hour, minute, month, second, year,

2)
-------
EXEC SQL DECLARE c1 CURSOR FOR
EXEC SQL select ename from emp for update of ename WAIT 60;
-------
$ proc iname=programname.pc
PCC-S-02201, Encountered the symbol "wait" when expecting one of
the following: ...

3)
-------
EXEC SQL DECLARE c1 CURSOR FOR
select empno, ename , dname from emp_new e LEFT OUTER JOIN dept_new d
on (e.deptno = d.deptno); /* or other forms of Join (RIGHT, FULL OUTER)*/
-------
$ proc iname=programname.pc
PCC-S-02201, Encountered the symbol "LEFT" when expecting one of the
following:
; , for, union, connect, group, having, intersect, minus,
order, start, where, with,

Solution Description
--------------------

2가지 방법으로 해결할 수 있습니다.

1- dynamic sql methods 를 사용하는 방법
다음 예제는 dynamic sql method 3 를 사용하고 있습니다.
($ORACLE_HOME/precomp/demo/proc 안에 있는
다른 pro*c dynamic sql samples 을 참고 하시기 바랍니다:
sample6.pc sample7.pc sample8.pc )
-------------------
/* in your pc program */

VARCHAR dynstmt[80];
...
...
...
void main() {
...
...
strcpy((char *)dynstmt.arr, "select job,SUM(CASE WHEN job='CLERK' then
sal else sal*10 end) from emp group by job");
dynstmt.len = (unsigned short)strlen((char *)dynstmt.arr);
EXEC SQL PREPARE S FROM :dynstmt;
EXEC SQL DECLARE C CURSOR FOR S;
...
...
...
}
-------------------
2- patchset 9.0.1.3 을 하시거나 9.2.0.1 이상으로 upgrade 하는 방법이 있습니다.

Reference Documents

출처 :

#include <stdio.h>
typedef char asciz[20];
EXEC SQL BEGIN DECLARE SECTION;
/* Define type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;
asciz username;
asciz password;
asciz emp_name;
short ind;
float salary;
float commission;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;

void sqlerror(); /* handles unrecoverable errors */

main()
{
/* Log on to ORACLE */
strcpy(username, "SCOTT");
strcpy(password, "TIGER");

/**
** ORA_NLS 는 ORACLE 7.2 에서만 필요 함.
**/
putenv("ORACLE_HOME=/oracle/oracle");
putenv("ORACLE_SID=RC722");
putenv("NLS_LANG=American_America.ko16ksc5601");
putenv("ORA_NLS=/oracle/oracle/ocommon/nls/admin/data");

EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n", username);
EXEC SQL DECLARE salespeople CURSOR FOR
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB LIKE 'SALES%';
EXEC SQL OPEN salespeople;
for ( ; ; )
{
EXEC SQL FETCH salespeople
INTO :emp_name, :salary, :commission:ind;
if (sqlca.sqlcode == 1403) break;
if (sqlca.sqlcode ==0) {
printf("%-11s%9.2f%13.2f\n", emp_name, salary, commission);
}
}
EXEC SQL CLOSE salespeople;
printf("\nHave a good day.\n");
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

void sqlerror()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:\n");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

보통의 어플리케이션 프로그램에서는 SQL문이 확정된 후 프로그램에 적용한다. 그러나, 다이나믹 SQL의 사용하는 경우는 사용자의 입력에 의거해서 SQL문이 작성되거나 다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우 다이나믹SQL을 사용하게 된다.
비교될 컬럼이 변경되는 경우(WHERE절),참조할 테이블이 변경되어야 하는 경우,INSERT, UPDATE시의 컬럼이 변경되는 경우
이 있다.
Method
SQL문
1
QUERY문이 아니면서 호스트 변수가 없는 경우
2
QUERY문이 아니면서 호스트 변수가 정해지지 않은 경우 
3
호스트 변수와 SELECT컬럼이 정해진 QUERY문인 경우
4
호스트 변수와 SELECT컬럼이 정해지지 않은 QUERY문인 경우
< Method 1 >
이 방법은 Dynamic SQL문을 작성하고 “EXECUTE IMMEDIATE”를 사용하여 즉시 실행한다. SQL문은 QUERY문이 아니어야 하고 - (SELECT문)- 입력용 호스트 변수가 존재하지 않아야 한다.Method 1은 SQL문이 매번 실행될 때마다 PARSING을 한다.
'DELETE FROM EMP WHERE DEPTNO = 20''GRANT SELECT ON EMP TO scott'
< Method 2 >
이 방법은 Dynamic SQL문을 작성한 후 “PREPARE”와 “EXECUTE” 명령에 의해 실행한다.SQL문은 QUERY문이 아니어야 하고, 여기에서 사용된 호스트변수는 프리컴파일 시점에서는 데이타 타입과 위치는 정해져 있어야 한다.
'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)''DELETE FROM EMP WHERE EMPNO = :emp_number
이 방법에서는 SQL문은 단 한번만 PARSING하게 되고 호스트 변수의 값을 달리하고 여러번 실행시킬 수 있다. CREATE나 GRANT같은 DDL 문은 PREPAREd된 후에 실행 할 수 있다.
< Method 3 >
이 방법은 Dynamic SQL문을 작성한 후 “PREPARE”, “DECALRE”, "OPEN", "FETCH", "CLOSE" 와 같은 커서 명령으로 실행한다. SELECT-LIST와 입력 호스트변수의 데이타타입과 위치는 프리컴파일 시점에서는 정해져야 한다. 이 방법은 query문이어야 한다.
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO''SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'
< Method 4 >
이 방법은 실행 시점까지 SELECT-LIST와 호스트변수의 데이타 타입과 갯수, 위치를 모를 경우 사용하는 방법이다.
'INSERT INTO EMP () VALUES ()''SELECT  FROM EMP WHERE DEPTNO = 20'
위의 네가지 방법 모두 Dynamic SQL문을 charactor string에 저장하고, "EXEC SQL"과 ";"는 생략한다.Method 2와 3은 입력 호스트변수의 위치와 데이타타입을 프리컴파일 시점까지 정해져 있어야 한다. Method 4는 가장 유연성이 좋다. 반면에 복잡한 코딩이 들어가도 Dynamic SQL의 개념을 알고 있어야 하기 때문에 잘 사용하지 않는다.보통 Method 4는 Method 1, 2, 3으로 해결 할 수 없는 경우에 사용한다.만약 프리컴파일 옵션을 DBMS=V6나 DBMS=V6_CHAR인 경우 SQL문을 배열에 저장하기 전에 BLANK를 채워줘야 한다. 그렇게 함으로서 변수를 CLEAR해 준다. 특히 배열을 다른 SQL문에서 다시 사용할 경우에는 특히 중요하다. 항상 SQL문을 저장하기 전에 호스트 스트링을 초기화 해야 한다. 오라클에서는 NULL-TERNIMATE는 스트링의 마지막이라고 인식하지 못하고 SQL문의 일부로 인식하기 때문에 사용하여서는 안된다. 만일 프리컴파일 옵션을 DBMS=V7로 했을 경우, 스트링의 값은 "PREPARE", 또는 "EXECUTE IMMEDIATE"하기 전에 NULL-TERNIMATE로 끝을 맺어줘야 한다. DBMS옵션의 값을 개의치 않을 경우에는 다이나믹 SQL문을 저장할 변수로 VARCHAR를 쓸 경우, VARCHAR의 length를 "PREPARE"나 "EXECUTE IMMEDIATE"를 실행하기 전에 정확하게 SET해주어야 한다.>
1. Method 1
결과값이 단순히 SUCCESS나 FAILURE인 간단하고 호스트변수를 사용하지 않는 다이나믹 SQL문일 경우 사용한다.이 방법은 "EXECUTE IMMEDIATE"를 사용하여 다이나믹 SQL문을 실행시킨다.
EXEC SQL EXECUTE IMMEDIATE {:host_string  |  string_literal };char	sql_stmt[132];....for (;;){    printf("Enter SQL statement: ");    gets(sql_stmt);    if(*sql_stmt == '\0')       break;    EXEC SQL EXECUTE IMMEDIATE :sql_stmt;}EXEC SQL EXECUTE IMMEDIATE  'REVOKE RESOURCE FROM MILLER';
2. Method 2
이 방법으로 실행할 경우 2번의 작업을 거쳐야 한다. 다이나믹SQL문은 QUERY문이어서는 안되고 첫번째로 PREPARE하고 EXECUTE되어 진다.SQL문에는 호스트변수와 INDICATOR변수를 가질 수 있다. PREPARE문은 한번만 수행하면 되고 EXECUTE문은 다른 호스트 변수값으로 여러 번 수행할 수 있다.. 더 나아가서 COMMIT이나 ROLLBACK문장을 수행하고 나서도 다시 PREPARE할 필요가 없다.(LOG OFF이나 RECONNECT가 아닌 경우)
EXEC SQL PREPARE statement_name        FROM {:host_string  |  :string_literal };
PRAPARE 명령은 SQL문을 PARSING하고 이름을 부여한다.위에서 statement_name은 호스트변수, 프로그램 변수가 아니고 프리컴파일러가 사용할 임시적인 변수로 DECALRE SECTION에 기술할 필요가 없다.
EXEC SQL EXECUTE statement_name [USING host_variable_list];:host_variable_list = :host_variable[:indicator1]                    [, :host_variable[:indicator2], ...]
EXECUTE는 PARSING된 SQL문을 "USING" 절의 호스트변수를 이용하여 수행한다.실제로 사용되는 모습은 아래와 같다.
...int	emp_number;char	delete_stmt[120], search_cond[40], temp[10];...strcpy(delete_stmt, "DELETE  FROM  EMP  WHERE EMPNO = :n AND ");printf("다음의 SQL문에서 검색조건을 입력하여 완성하시오.\n");printf("%s\n", delete_stmt);gets(search_cond);strcat(delete_stmt, search_cond);EXEC SQL PREPARE sql_stmt FROM :delete_stmt;for(;;){   printf("사원번호를 입력하세요: ");   get(temp);   emp_number = atoi(temp);   if (emp_number == 0)      break;   EXEC SQL EXECUTE sql_stmt USING :emp_number;}......
USING절의 이용SQL문이 EXECUTE될 때, USING절에 입력된 호스트변수의 값은 PREPARE된 다이나믹 SQL문에서 대응되는 위치에 대치된다. PREPARE된 다이나믹SQL문에서 각각의 변수 위치는 USING절의 각각의 다른 호스트변수와 대치되어야 한다. 
3. Method 3
이 방법은 Method 2와 비슷하나 PREPARE문장과 함께 커서를 선언하고 조작 하는게 필요하다.QUERY문을 사용할 수 있다. 사실상 다이나믹SQL문이 QUERY문이면 Method3이나 4를 사용해야 한다.SELECT-LIST의 컬럼의 갯수와 호스트 변수의 갯수가 프리컴파일 시점에서는 정해져 있어야 한다. 실행시점 전까지는 테이블명과 컬럼명등 데이타베이스 OBJECT의 이름은 정해져야 한다. 데이타베이스 OBJECT의 이름은 호스트변수를 사용하지 못한다.조건절(WHERE)이나 GROUP BY절, ORDER BY절 역시 실행 시점까지 정해져야 한다.
EXEC SQL PREPARE statement_name FROM { :host_string | string_literal };EXEC SQL DECALRE cursor_name CURSOR FOR statement_name;EXEC SQL OPEN cursor_name [USING host_variable_list ];EXEC SQL FETCH cursor_name INTO host_variable_list;EXEC SQL CLOSE cursor_name;
PREPARE는 다이나믹SQL문을 PARSING하고 이름을 부여한다. 아래의 예제는 select_stmt문자열을 sql_stmt로 이름을 부여한다.
char  select_stmt[132] =     "SELECT MGR, JOB FROM EMP WHERE SAL < :salary";EXEC SQL PREPARE sql_stmt FROM :select_stmt;
보통 WHERE절은 실행 시점에서 터미널로부터 입력되거나 어플리케이션 프로그램에서 자동으로 생성되게 한다.DECLARE명령은 PREPARE에 의해 붙여진 이름의 SQL문으로 커서를 정의한다.
EXEC SQL PREPARE sql_stmt FROM :select_stmtEXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
위의 예에서 sql_stmt와 emp_cursor는 호스트변수나 프로그램의 변수로 선언할 필요가 없다.OPEN명령은 커서에 메모리를 할당하고, 입력된 호스트변수를 BIND하고, QUERY를 실행시키고, 실행에 의해 나오는 데이타를 active set으로 설정한다. OPEN명령은 커서를 ACTIVE SET의 첫번째 ROW에 위치하게 되고, sqlerrd[2]를 0으로 set한다.
EXEC SQL OPEN emp_cursor USING :salary;
FETCH명령은 INTO절에 대응되는 호스트 변수에ACTIVE SET에서 ROW를 리턴한다. 만약 더 이상의 데이타가 없으면 오라클은 sqlca.sqlcode의 값을 1403을 설정하고 "no data found"를 리턴한다.
EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;
CLOSE명령은 커서를 무효화 시킨다. 커서를 CLOSE하고 난 후에는 더 이상 FETCH는 실행되지 않는다.
EXEC SQL CLOSE emp_cursor;