平面数据的导出与导入

平面数据的导出与导入_1


较少数据的导出

1、	proc
/*
** 程序名称  load_acctlist.pc
** 用途：将外部文件数据加载到数据库
*/
#include <stdio.h>
#include <string.h>
#include <ctype.h>

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

static char *     USERID = NULL;
static char *   SQLSTMT = NULL;
static char *   ARRAY_SIZE = "10";

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void   sqlclu();



static void die( char * msg )
{
    fprintf( stderr, "%s\n", msg );
    exit(1);
}


/*
    this array contains a default mapping
    I am using to constrain the
       lengths of returned columns.  It is mapping,
    for example, the Oracle
       NUMBER type (type code = 2) to be 45 characters
    long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
 18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
  0, 0, 512, 2000 };


static void process_parms( argc, argv )
int    argc;
char *    argv[];
{
int    i;

    for( i = 1; i < argc; i++ )
    {
        if ( !strncmp( argv[i], "userid=", 7 ) )
              USERID = argv[i]+7;
        else
        if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
              SQLSTMT = argv[i]+8;
        else
        if ( !strncmp( argv[i], "arraysize=", 10 ) )
              ARRAY_SIZE = argv[i]+10;
        else
        {
            fprintf( stderr,
                    "usage: %s %s %s\n",
                     argv[0],
                    "userid=xxx/xxx sqlstmt=query ",
                    "arraysize=<NN>\n" );
            exit(1);
        }
    }
    if ( USERID == NULL  || SQLSTMT == NULL )
    {
        fprintf( stderr,
                "usage: %s %s %s\n",
                 argv[0],
                "userid=xxx/xxx sqlstmt=query ",
                "arraysize=<NN>\n" );
        exit(1);
    }
}

static void sqlerror_hard()
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    fprintf(stderr,"\nORACLE error detected:");
    fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}



static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA *    select_dp;
int     i;
int        j;
int        null_ok;
int        precision;
int        scale;
int        size = 10;

    fprintf( stderr, "Unloading '%s'\n", sqlstmt );
    fprintf( stderr, "Array size = %d\n", array_size );


    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
       EXEC SQL PREPARE S FROM :sqlstmt;
       EXEC SQL DECLARE C CURSOR FOR S;

    if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
                   == NULL )
        die( "Cannot allocate  memory for select descriptor." );

    select_dp->N = size;
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    if ( !select_dp->F ) return NULL;

    if (select_dp->F < 0)
    {
        size = -select_dp->F;
        sqlclu( select_dp );
        if ((select_dp =
                sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
                      == NULL )
        die( "Cannot allocate  memory for descriptor." );
        EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    }
    select_dp->N = select_dp->F;

    for (i = 0; i < select_dp->N; i++)
        select_dp->I[i] = (short *) malloc(sizeof(short) *
                                                array_size );

    for (i = 0; i < select_dp->F; i++)
    {
        sqlnul (&(select_dp->T[i]),
                &(select_dp->T[i]), &null_ok);
        if ( select_dp->T[i] <
                     sizeof(lengths)/sizeof(lengths[0]) )
        {
            if ( lengths[select_dp->T[i]] )
                 select_dp->L[i]  = lengths[select_dp->T[i]];
            else select_dp->L[i] += 5;
        }
        else select_dp->L[i] += 5;

        select_dp->T[i] = 5;
        select_dp->V[i] = (char *)malloc( select_dp->L[i] *
                                               array_size );

        for( j = MAX_VNAME_LEN-1;
             j > 0 && select_dp->S[i][j] == ' ';
             j--);
        fprintf (stderr,
                "%s%.*s", i?",":"", j+1, select_dp->S[i]);
    }
    fprintf( stderr, "\n" );


    EXEC SQL OPEN C;
    return select_dp;
}


static void process_2( SQLDA * select_dp, int array_size )
{
int    last_fetch_count;
int        row_count = 0;
short    ind_value;
char    * char_ptr;
int    i,
       j;

    for ( last_fetch_count = 0;
          ;
          last_fetch_count = sqlca.sqlerrd[2] )
    {
        EXEC SQL FOR :array_size FETCH C
                      USING DESCRIPTOR select_dp;

        for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
        {
            for (i = 0; i < select_dp->F; i++)
            {
                ind_value = *(select_dp->I[i]+j);
                char_ptr  = select_dp->V[i] +
                                  (j*select_dp->L[i]);

                printf( "%s%s", i?",":"",
                             ind_value?"(null)":char_ptr );
            }
            row_count++;
            printf( "\n" );
        }
        if ( sqlca.sqlcode > 0 ) break;
    }

    sqlclu(select_dp);

    EXEC SQL CLOSE C;

    EXEC SQL COMMIT WORK;
    fprintf( stderr, "%d rows extracted\n", row_count );
}



main( argc, argv )
int    argc;
char *    argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR   oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA    * select_dp;


    process_parms( argc, argv );

    /* Connect to ORACLE. */
    vstrcpy( oracleid, USERID );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
             oracleid.arr);

    EXEC SQL ALTER SESSION
      SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

    select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
    process_2( select_dp , atoi(ARRAY_SIZE));

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}
    

2、过程
create or replace function  dump_txt( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse(l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000);

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue);
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end ;


create or replace function  dump_txt( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse(l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000);

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            if l_columnValue is not null then
            l_columnValue:='"'||l_columnValue||'"';
            else
            l_columnValue:=nvl(l_columnValue,'NULL');
           end if; 
            utl_file.put( l_output, l_separator || l_columnValue);
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end ;

dump_txt的使用方法

1、建立一个系统文件目录 d:\ex_file
2、建立一个oracle目录，并将读写权限赋予scott用户
conn sys/sys@gx as sysdba
CREATE OR REPLACE DIRECTORY  extxt AS 'D:\ex_file';
grant read,write on directory extxt  to scott;

3、	导出某一个查询的结果
select dump_txt( 'select * from emp',',','EXTXT','emp.txt' )  from dual

emp.txt
5555,ggg,CLERK,7900,2008-07-22 00:00:00,0,,10
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MA & RTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7876,ADAMS,CLERK,7788,1987-04-23 00:00:00,1100,,20


数据导入

select dump_txt( 'select * from all_objects',',','EXTXT','all_object2.txt' )  from dual

create table t_dump as
select * from all_objects
where 1=2


select replace(dbms_metadata.get_ddl('TABLE','T_DUMP'),'"','') from dual


  CREATE TABLE SCOTT.T_DUMP 
   (  
  OWNER VARCHAR2(30) NOT NULL ENABLE, 
  OBJECT_NAME VARCHAR2(30) NOT NULL ENABLE, 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER NOT NULL ENABLE, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE NOT NULL ENABLE, 
  LAST_DDL_TIME DATE NOT NULL ENABLE, 
  TIMESTAMP VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY VARCHAR2(1), 
  GENERATED VARCHAR2(1), 
	SECONDARY VARCHAR2(1), 
	NAMESPACE NUMBER NOT NULL ENABLE, 
	EDITION_NAME VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE USERS 
  
 
drop table T_ext 

  CREATE TABLE T_ext
  (
  OWNER VARCHAR2(30), 
  OBJECT_NAME VARCHAR2(30), 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE, 
  LAST_DDL_TIME DATE, 
  TIMESTAMP1 VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY1 VARCHAR2(1), 
  GENERATED1 VARCHAR2(1), 
  SECONDARY VARCHAR2(1), 
  NAMESPACE NUMBER, 
  EDITION_NAME VARCHAR2(30)
  )
  ORGANIZATION external
  (
    TYPE oracle_loader 
    DEFAULT DIRECTORY EXTXT
    ACCESS PARAMETERS
    (
      RECORDS DELIMITED BY NEWLINE
      SKIP 1
      BADFILE EXTXT:'emp.bad'
      LOGFILE EXTXT:'t.log_xt'
      READSIZE 1048576
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
      REJECT ROWS WITH ALL NULL FIELDS
      (
  OWNER  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_NAME  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  SUBOBJECT_NAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_ID CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  DATA_OBJECT_ID  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_TYPE CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  CREATED date mask "yyyy-mm-dd hh24:mi:ss" TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' , 
  LAST_DDL_TIME date mask "yyyy-mm-dd hh24:mi:ss" TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ,  
  TIMESTAMP1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  STATUS CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  TEMPORARY1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  GENERATED1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  SECONDARY CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  NAMESPACE CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  EDITION_NAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'   
      )
    )
    location
    (
      EXTXT:'all_object2.txt',EXTXT:'all_object1.txt'
    )
  )
  PARALLEL
  REJECT LIMIT UNLIMITED

select * from T_ext
调试方法

  CREATE TABLE T_ext_in
  (
  OWNER VARCHAR2(30), 
  OBJECT_NAME VARCHAR2(30), 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE, 
  LAST_DDL_TIME DATE, 
  TIMESTAMP1 VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY1 VARCHAR2(1), 
  GENERATED1 VARCHAR2(1), 
  SECONDARY VARCHAR2(1), 
  NAMESPACE NUMBER, 
  EDITION_NAME VARCHAR2(30)
  ) parallel nologging
  
  
  insert /*+append*/ into T_ext_in
  select /*+parallel*/ * from T_ext
  --2.5s


SQL> conn scott/tiger
已连接。
SQL> @D:\ex_file\insert.SQL
时间好长…….


原因：
1、	绑定变量；
2、	多进程的直接加载；


如果从客户端加载文本数据，则用sql*loader