oracle使用pro*c编程获取所有表格最后修改时间

包含目录：

• include （目录：oracle_handler.h）
• src  （目录：main.cpp oracle_handler.pc）
• build    （目录：执行cmake ..）
• CMakeLists.txt

注：cmake中利用外部命令，执行oracle自带的proc程序将pc文件转化为cpp文件，供编译使用。

CMakeLists.txt

PROJECT(query_last_time)                                  # 设置工程名 
cmake_minimum_required(VERSION 2.8)                       # 指定最低版本需求 cmake最低版本
SET(CMAKE_CXX_COMPILER "g++")                             # 设置g++编译器
SET(CMAKE_BUILD_TYPE Debug)                               # 设置编译类型debug
if(CMAKE_BUILD_TYPE MATCHES "Debug")                      # 根据类型设置编译选项
    MESSAGE("-- Build Debug")
    SET(CMAKE_CXX_FLAGS "$ENV{CXXFLAGS} -O0  -Wall -g")
    SET(CMAKE_C_FLAGS "$ENV{CFLAGS} -O0 -Wall -g")
else(CMAKE_BUILD_TYPE MATCHES "Release")
    MESSAGE("-- Build Release")
    SET(CMAKE_CXX_FLAGS "$ENV{CXXFLAGS} -O0  -Wall")
    SET(CMAKE_C_FLAGS "$ENV{CFLAGS} -O0 -Wall")
endif()

add_definitions(-Wno-unused-local-typedefs 
        -Wno-unused-value 
        -Wno-unused-but-set-variable 
        -Wno-unused-variable 
        -Wno-unused-function 
        )                                                 # 忽略未使用警告

execute_process(COMMAND rm -fr 
${CMAKE_SOURCE_DIR}/build/*)                              # 执行shell命令
set(ENV{LD_LIBRARY_PATH} "$ENV{ORACLE_HOME}/lib")         # 设置oracle动态库环境
execute_process(COMMAND proc 
include=${CMAKE_SOURCE_DIR}/include 
code=cpp cpp_suffix=cpp 
${CMAKE_SOURCE_DIR}/src/oracle_handle.pc)                 # 执行shell命令将pc文件生成cpp

###CMAKE_SOURCE_DIR为CMakeLists.txt同级目录
INCLUDE_DIRECTORIES(${CMAKE_SOURCE_DIR}/include 
    /data/oracle/product/11.2.0/precomp/public)           # 指定头文件目录
AUX_SOURCE_DIRECTORY(${CMAKE_SOURCE_DIR}/src SOURCE_LIST) # 遍历src下所有源文件并保存在SOURCE_LIST变量中
###也可以使用以下方式逐个设置源文件
###set(SRC_LIST ${CMAKE_SOURCE_DIR}/src/main.cpp
###    ${CMAKE_SOURCE_DIR}/src/1.cpp
###    ${CMAKE_SOURCE_DIR}/src/2.cpp)
LINK_DIRECTORIES(${CMAKE_SOURCE_DIR}/lib
    $ENV{ORACLE_HOME}/lib)                                # 添加库目录，类似于'-L'

set(EXECUTABLE_OUTPUT_PATH ${PROJECT_BINARY_DIR}/bin)     # 重定义生成路径
ADD_EXECUTABLE(${PROJECT_NAME} ${SOURCE_LIST})            # 编译
TARGET_LINK_LIBRARIES(${PROJECT_NAME} clntsh)             # 链接 类似于'-l'

oracle_handler.h

#ifndef __ORACLE_HANDLER_H__
#define __ORACLE_HANDLER_H__

int Login();
int Logout();
int QueryLastTime(char *pLastTime);


#endif

oracle_handler.pc

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE ORACA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLCPR;
EXEC ORACLE OPTION (RELEASE_CURSOR = YES);

//去右空格
void Trim_right(char *p)
{
    char *pTmp = p+strlen(p)-1;
    while(pTmp != p)
    {
        if(*pTmp == 0)
            break;
        if(*pTmp != ' ')
            break;

        *pTmp-- = 0;
    }
}

int Login()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char alAccount[128];
    EXEC SQL END DECLARE SECTION;

    //sprintf(alAccount , "%s/%s as sysdba" , "sys","123456");
    sprintf(alAccount , "%s/%s" , "zhangchen","123");
    EXEC SQL connect :alAccount;
    if(sqlca.sqlcode != 0)
    {
        printf("Connect oracle failed!\n");
        printf("error code [%d], reason[%s] \n",  sqlca.sqlcode, ( char * )sqlca.sqlerrm.sqlerrmc);
        return -1;
    }
    return 0;
}

int Logout()
{
    EXEC SQL COMMIT WORK RELEASE;
    if(sqlca.sqlcode != 0)
    {
        printf("Disconnect oracle failed!\n");
        printf("error code [%d], reason[%s] \n",  sqlca.sqlcode, ( char * )sqlca.sqlerrm.sqlerrmc);
        return -1;
    }
    return 0;
}

int QueryLastTime(char *pLastTime)
{
    int ilMaxScn = 0;

    EXEC SQL BEGIN DECLARE SECTION;
    char alOwner[128];
    char alTable[128];
    int scn;
    char alLastTime[128];
    char alSql[1024];
    EXEC SQL END DECLARE SECTION;

    //声明游标遍历all_tables，并过滤系统表空间中的信息，查询所有用户自建表
    EXEC SQL DECLARE curs CURSOR FOR SELECT OWNER,TABLE_NAME,TABLESPACE_NAME FROM all_tables where TABLESPACE_NAME!='SYSAUX' and TABLESPACE_NAME!='TEMP' and TABLESPACE_NAME!='SYSTEM';
    EXEC SQL OPEN curs;
    if(sqlca.sqlcode != 0)
    {
        printf("error code [%d], reason[%s] \n",  sqlca.sqlcode, ( char * )sqlca.sqlerrm.sqlerrmc);
        return -1;
    }

    while(1)
    {
        scn = 0;
        memset(alOwner , 0 , sizeof(alOwner));
        memset(alTable , 0 , sizeof(alTable));
        memset(alLastTime , 0 , sizeof(alLastTime));
        memset(alSql , 0 , sizeof(alSql));

        EXEC SQL FETCH curs INTO :alOwner , :alTable;
        if(sqlca.sqlcode == 1403)
        {
            printf("遍历结束\n");
            EXEC SQL CLOSE curs;
            break;
        }
        else if(sqlca.sqlcode != 0)
        {
            printf("Fetch error sqlcode[%d]", sqlca.sqlcode);
            EXEC SQL CLOSE curs;
            return -2;
        }
        else
        {
            Trim_right(alOwner);
            Trim_right(alTable);
            //printf("Get owner[%s] table[%s]\n" , alOwner , alTable);

            //查询表格的最大scn号，并将scn转换成时间
            sprintf(alSql , "select max(ora_rowscn),to_char(scn_to_timestamp(max(ora_rowscn)),\'yyyy-mm-dd hh24:mi:ss\') from %s.%s" , alOwner , alTable);
            //printf("Exec sql [%s]\n" , alSql);
            //动态sql，预备
            EXEC SQL PREPARE tmp FROM :alSql;

            //声明游标，遍历表格scn（只有一条）
            EXEC SQL DECLARE  curs2 CURSOR FOR tmp;
            EXEC SQL OPEN curs2;
            if(sqlca.sqlcode == -1405)
            {
                //空表中没有scn信息，返回null值，会报1405错误
                EXEC SQL CLOSE curs2;
                continue;
            }
            else if(sqlca.sqlcode == -8181)
            {
                //表长期没有数据修改，scn号过老无法转换成时间
                EXEC SQL CLOSE curs2;
                continue;
            }
            else if(sqlca.sqlcode != 0)
            {
                printf("Open cursor failed , error code [%d], reason[%s] \n",  sqlca.sqlcode, ( char * )sqlca.sqlerrm.sqlerrmc);
                EXEC SQL CLOSE curs2;
                continue;
            }

            EXEC SQL FETCH curs2 INTO :scn, :alLastTime;
            if(sqlca.sqlcode != 0)
            {
                printf("error code [%d], reason[%s] \n",  sqlca.sqlcode, ( char * )sqlca.sqlerrm.sqlerrmc);
            }
            else
            {
                //查询成功
                Trim_right(alLastTime);
                printf("owner[%s]\t table[%s]\t scn[%d]\t alLastTime[%s]\n" , alOwner , alTable , scn , alLastTime);
                if(ilMaxScn < scn)
                {
                    ilMaxScn = scn;
                    strcpy(pLastTime , alLastTime);
                }
            }
            EXEC SQL CLOSE curs2;
        }
    }


    return 0;
}

main.cpp

#include <stdio.h>
#include "oracle_handler.h"


int main()
{
    char alLastTime[128] = {0};

    if(Login() != 0)
    {
        printf("Func Login error!\n");
        return -1;
    }
    if(QueryLastTime(alLastTime) != 0)
    {
        printf("Func QueryLastTime error!\n");
        return -1;
    }
    if(Logout() != 0)
    {
        printf("Func Logout error!\n");
        return -1;
    }

    puts("-------------------------------------");
    printf("LastTime[%s]\n" , alLastTime);

    return 0;
}

执行结果：

（因数据量较少，遍历下来几百毫秒左右）