'Pro C'에 해당되는 글 3건

  1. Pro*c를 이용한 오라클 PL/SQL function 호출
  2. ORA-01002 fetch out of sequence
  3. oracle 유용한 쿼리

Pro*c를 이용한 오라클 PL/SQL function 호출

Proc*c를 이용해 Sub Program(Procedure or Function)을 호출 하는 방법에 대해 설명한다.

자료를 찾아보면 대부분의 샘플 코드가 Procedure 로만 되어 있다.
여기서는 Function을 호출 해 보기로 한다.
사실 호출 방법은 거의 비슷 하다.

- 컴파일 방법
PL/SQL 구문을 사용하기 때문에 proc 컴파일 옵션을 주어야 한다.
그런데, 컴파일 옵션을 주면 다른 Pro*c 구문에서 컴파일이 안되는 일이 발생을 한다.
따라서, PL/SQL 구문을 사용하는 소스는 별도 .pc 파일로 때어 내어서 별도 컴파일을 해야한다.

$ proc include=../inc iname=sub.pc SQLCHECK=SEMANTICS userid=oracleid/oraclepwd

여기서 기존 컴파일 방법과 다른 부분은 "SQLCHECK=SEMANTICS" 옵션이 추가된 것과,
userid 옵션이 추가된 것이다.
userid 옵션에는 오라클 로긴을 위한 ID와 Password를 넣어 준다.


- 샘플 소스
/** @file       sub.pc
 * @brief       Remote Function Call  
 * @author    newtype
 * @date    2009-04-10
 */

#ifndef _SUB_PC_
#define _SUB_PC_

#include "common.h"

EXEC SQL INCLUDE SQLCA;

/** @brief 오라클 Function을 호출 한다.
 * @param in [IN] 오라클 Fucntion Parameter
 * @param out [OUT] 오라클 Fucntion Return 값을 반환
 * @return 0     정상처리
 * @return 그외  오라클 에러 코드
 * @remarks 참고 사항
 * @warning 주의 사항
*/
int CallOracleFunction(char* in, char* out)
{    
    EXEC SQL BEGIN DECLARE SECTION;
        char szOut[MAX_BUF] = "";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL EXECUTE
    BEGIN
        :szOut := ORA_SAMPLE_FUNC( in );
        IF szOut = 'newtype' THEN
            strcpy( out, szOut) ;
            COMMIT;
        ELSE
            strcpy( out, 'newtype.pe.kr');
            ROLLBACK;
        END IF;
    END;
    END-EXEC;

    return nErrCode;
}

#endif


ORA-01002 fetch out of sequence


ORA-01002 fetch out of sequence 는 여러 경우에 발생을 한다.

그중 한가지에 대한 오류 발생 사례 이다.

DB Link로 Remote 테이블을 참조 하고 있는 프로세스가 작업을 수행하고 있는 상태에서,
Remote DB가 Shutdown 되는 경우 위 에러가 발생 한다.
Remote DB가 Start되고 새로운 세션을 맺어야 프로세스가 정상 동작을 한다.
세션을 유지한 상태에서 Remote DB가 Start 되어도 위 에러가 계속 발생한다.

말이 어렵다 테스트 해보자.

1. ORA TNS에 RemoteDB를 추가한다.

2. DB 링크를 생성한다.
CREATE PUBLIC DATABASE LINK test_link CONNECT TO remoteDBid IDENTIFIED BY remoteDBpwd USING 'remoteDB';
CREATE SYNONYM test_proc FOR test_proc_table@test_link
SELECT * FROM test_proc

3. 데이터를 삽입한다.
insert into test_proc values ( 1, 1 );
insert into test_proc values ( 2, 1 );
~~~~~~
insert into test_proc values ( 33, 1 );
insert into test_proc values ( 44, 1 );

4. 테스트 프로그램을 작성한다.
$ vi t.pc
#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

#define MAX_ARRAY_SIZE 10

int main()
{
    int n;

    EXEC SQL BEGIN DECLARE SECTION;
        int  hSEQ; 
        char hOracleUser[20] = "localDBid";
        char hOraclePwd[20]  = "localDBpwd";
    EXEC SQL END DECLARE SECTION;  
    
    EXEC SQL CONNECT :hOracleUser IDENTIFIED BY :hOraclePwd;
    if(sqlca.sqlcode !=0)
    {
        printf( "login fail\n");  
        return 0;
    }
    
    for (n=0; n<10; n++)
    {
        printf( "start of job\n");  
 
        EXEC SQL DECLARE CUR_TEST_PROC CURSOR FOR  
            SELECT SEQ FROM TEST_PROC;
     
        EXEC SQL OPEN CUR_TEST_PROC;         

        while(1)
        {
            EXEC SQL FETCH CUR_TEST_PROC INTO :hSEQ;
                
            sleep(3);            
                
            if(sqlca.sqlcode != 0) 
            {
                if(sqlca.sqlcode == 1403) 
                {
                    printf( "data not found\n");
                    break;
                }
                else 
                {
                    printf( "fetch Error [%20s]\n" , sqlca.sqlerrm.sqlerrmc);
                    break;
                }
            }

            printf("fetch [%d]\n", hSEQ );
        } 
        EXEC SQL CLOSE CUR_TEST_PROC;      
        printf( "end of job\n");  
        
    } /* for */

    return 0;
}




$ proc iname=t.pc
$ cc -o t t.c -L/home/oracle/app/oracle/product/8.1.7/lib -lclntsh -R/home/oracle/app/oracle/product/8.1.7/lib

5. 프로세스 구동
start of job

6. Remote DB Shutdown

7. 에러 확인
fetch Error [ORA-01002: fetch out of sequence]


이거 원인을 파악하려고, 오늘 하루를 다 보냈다. ㅡ.ㅡ


참고 
DB 링크 조회 쿼리
SELECT * FROM USER_DB_LINKS 

시노님 조회 쿼리
SELECT * FROM TAB WHERE TABTYPE='SYNONYM'


oracle 유용한 쿼리

  • Join UPDATE

  • UPDATE AA A
    SET A.TARGET = (
        SELECT B.TARGET   
          FROM BB B
         WHERE A.PK = B.PK
    ) WHERE EXISTS ( SELECT 1 FROM BB B WHERE A.PK = B.PK );
    


  • 쿼리 시간 보기

  • set timing on;
    set timing off;
    


  • PLAN 정보 보기

  • set autot on exp
    사용자 쿼리
    set autot off
    rollback;
    


  • 현재 연결된 세션

  • select count(*) from v$session;
    


  • 세션 정보

  • select status, sid from v$session;
    


  • 사용자명 별 연결 세션

  • select USERNAME, count(1) from v$session group by USERNAME;
    


  • 케릭터셋 보기

  • select * from v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET'
    


  • 메모리에 올라가 있는 쿼리 보기

  • select * from V$SQL_SHARED_MEMORY
    


  • 메모리에 올라가 있는 쿼리 지우기
    (CPU, DISK 사용량이 증가하므로, 야간에 작업할 것)

    ALTER SYSTEM FLUSH SHARED_POOL;
    


  • 연결된 커서 갯수 및 쿼리 출력

  •    SELECT a.cnt,
              a.user_name,
              b.sql_text,
              a.hash_value
         FROM ( SELECT COUNT (*) cnt,
                       user_name,
                       hash_value
                  FROM v$open_cursor
              GROUP BY user_name,
                       hash_value) a,
              v$sqlarea b
        WHERE a.hash_value = b.hash_value AND ROWNUM <= 100
     ORDER BY cnt DESC;
    


  • 특정 테이블의 외래키 출력

  •    SELECT A.TABLE_NAME table_name
            , A.CONSTRAINT_NAME key_name
            , B.TABLE_NAME referencing_table
            , B.CONSTRAINT_NAME foreign_key_name 
            , B.STATUS fk_status  
         FROM USER_CONSTRAINTS A
            , USER_CONSTRAINTS B  
        WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME 
          AND B.CONSTRAINT_TYPE = 'R'
          AND A.OWNER = '대문자 계정명'
          AND ( A.TABLE_NAME = '대문자 테이블명' OR B.TABLE_NAME = '대문자 테이블명' )
     ORDER BY 1, 2, 3, 4;
    


  • Oracle Admin계정으로 로긴

  • $ sqlplus "/as sysdba"
    


  • Oracle 종료
  • (Oracle 계정에서)

    $ lsnrctl stop 
    $ sqlplus "/as sysdba"
    
    SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]
    

    NORMAL : 현재 사용자가 연결을 끊을때까지 대기 default 옵션
    TRANSACTIONAL : 접속된 사용자들이 새롭게 트랜잭션을 시작할 수 없습니다. 사용자의 트랜잭션이 끝나면 연결이 종료되면 모든 트랙잭션이 종료되면 데이터베이스가 즉시 종료 됩니다.
    IMMEDIATE : 현재 진행중인 SQL문이 완료되지 않으며 현재 사용자의 연결을 즉시 끝으며, 모든 트랜잭션을 롤백합니다.
    ABORT : 강제 종료라고 할수 있으며 다음번 데이터베이스 시작시 인스턴스 복구가 필요합니다.


  • Oracle 시작
  • (Oracle 계정에서)

    $ sqlplus "/as sysdba"
    
    STARTUP [NORMAL | NOMOUNT | MOUNT]
    exit
    
    $ lsnrctl start
    


  • Oracle 기본포트 1521 변경 방법
  • (Oracle 계정에서)

    $ lsnrctl stop
    $ sqlplus "/as sysdba"
    
    ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))";
    ALTER SYSTEM REGISTER;
    exit
    
    $ lsnrctl start
    



  • Pro-C 관련 유용한 사이트

  • http://hobak.emailkorea.net/hobak/
    http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/
    http://www.lsbu.ac.uk/oracle/oracle9i/appdev.920/a97269/toc.htm


  • Pro-C 관련 글

  • shared_pool 메모리 사용량
    Sql 바인딩
    하드파싱 쿼리 잡는 방법
    $sqltext 에 대해서