(8.0.22)
select @@secure_file_priv;
drop table tab1;
drop table tab2;
create table tab1(col1 int primary key,col2 int);
create table tab2(col1 int primary key);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
insert into tab1 values(4,400);
insert into tab2 values(100);
insert into tab2 values(200);
insert into tab2 values(800);
select *
into outfile '/tmp/tab1.dat'
fields terminated by ',' optionally enclosed by '"'
from tab1
where exists ( select * from tab2 where tab2.col1=tab1.col2)
;
(12cR1)
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int primary key,col2 int);
create table tab2(col1 int primary key);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
insert into tab1 values(4,400);
insert into tab2 values(100);
insert into tab2 values(200);
insert into tab2 values(800);
commit;
expdp test/test directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.exp.log tables=tab1 tables=tab2 reuse_dumpfiles=yes
-- 必要な文字だけエスケープ
expdp test/test directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.exp.log tables=tab1 tables=tab2 reuse_dumpfiles=yes \
query=tab1:\"where exists \( select \* from tab2 where tab2.col1=KU$.col2\)\"
-- すべての文字をエスケープ
expdp test/test directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.exp.log tables=tab1 tables=tab2 reuse_dumpfiles=yes \
query=tab1:\"\w\h\e\r\e\ \e\x\i\s\t\s\ \(\ \s\e\l\e\c\t\ \*\ \f\r\o\m\ \t\a\b\2\ \w\h\e\r\e\ \t\a\b\2\.\c\o\l\1\=\K\U\$\.\c\o\l\2\)\"
(13)
drop table tab1;
drop table tab2;
create table tab1(col1 int primary key,col2 int);
create table tab2(col1 int primary key);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
insert into tab1 values(4,400);
insert into tab2 values(100);
insert into tab2 values(200);
insert into tab2 values(800);
copy ( select * from tab1 where exists ( select * from tab2 where tab2.col1=tab1.col2)) to '/tmp/tab1.dat' with ( format csv );
(2019)
drop table tab1;
drop table tab2;
create table tab1(col1 int primary key,col2 int);
create table tab2(col1 int primary key);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
insert into tab1 values(4,400);
insert into tab2 values(100);
insert into tab2 values(200);
insert into tab2 values(800);
bcp "select * from test.dbo.tab1 where exists ( select * from test.dbo.tab2 where test.dbo.tab2.col1=test.dbo.tab1.col2)" queryout "C:\tmp\tab1.dat" -T -c -t,
