平面数据的导出与导入_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
|
