programing

SQLPLUS를 사용하여 CSV 형식의 파일로 스풀하려면 어떻게 해야 합니까?

sourcejob 2023. 2. 28. 23:27
반응형

SQLPLUS를 사용하여 CSV 형식의 파일로 스풀하려면 어떻게 해야 합니까?

CSV 출력 포맷으로 쿼리를 추출합니다.유감스럽게도 고급 SQL 클라이언트나 언어를 사용할 수 없습니다.SQLPLUS를 사용해야 합니다.

제가 그걸 어떻게 합니까?

필드 사이에 공백이 생기지만 다음을 사용할 수도 있습니다.

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths
set numw X       -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name 
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;

출력은 다음과 같습니다.

    TABLE_PRIVILEGE_MAP           ,SYSTEM                        
    SYSTEM_PRIVILEGE_MAP          ,SYSTEM                        
    STMT_AUDIT_OPTION_MAP         ,SYSTEM                        
    DUAL                          ,SYSTEM 
...

이 방법은 모든 필드를 입력하고 쉼표로 연결하는 것보다 훨씬 덜 지루할 것입니다.원하는 경우 간단한 sed 스크립트를 사용하여 쉼표 앞에 표시되는 공백을 제거할 수 있습니다.

이런 게 먹힐지도... (내 SED 실력이 녹슬어서 손이 필요할 것 같아)

sed 's/\s+,/,/' myfile.csv

12.2 를 사용하고 있는 경우는, 간단하게 다음과 같이 말할 수 있습니다.

set markup csv on
spool myfile.csv

이 명령어는 Dimensional Table(DW; 차원 테이블)의 데이터를 추출하는 스크립트에 사용합니다.그래서 다음 구문을 사용합니다.

set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off

spool output.dat

select '|', <table>.*, '|'
  from <table>
where <conditions>

spool off

그리고 효과가 있다.출력 파일 포맷에는 sed를 사용하지 않습니다.

비슷한 문제가 보이네요...

SQLPLUS에서 CSV 파일을 스풀해야 하는데 출력에 250개의 열이 있습니다.

SQLPLUS 출력 포맷을 번거롭게 하지 않기 위해 실행한 작업:

set linesize 9999
set pagesize 50000
spool myfile.csv
select x
from
(
select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

문제는 열 머리글 이름이 손실된다는 것입니다.

추가할 수 있습니다.

set heading off
spool myfile.csv
select col1_name||';'||col2_name||';'||col3_name||';'||col4_name||';'||col5_name||';'||col6_name||';'||col7_name||';'||col8_name||';'||col9_name||';'||col10_name||';'||col11_name||';'||col12_name||';'||col13_name||';'||col14_name||';'||col15_name||';'||col16_name||';'||col17_name||';'||col18_name||';'||col19_name||';'||col20_name||';'||col21_name||';'||col22_name||';'||col23_name||';'||col24_name||';'||col25_name||';'||col26_name||';'||col27_name||';'||col28_name||';'||col29_name||';'||col30_name from dual;

select x
from
(
select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

힘든 건 알지만 나한테는 효과가 있어

최신 버전의 클라이언트 도구에서는 쿼리 출력 형식을 지정하는 여러 옵션이 있습니다.나머지는 클라이언트 도구에 따라 파일을 스풀하거나 출력을 파일로 저장하는 것입니다.다음은 몇 가지 방법입니다.

  • SQL*Plus

SQL*Plus 명령을 사용하여 포맷하여 원하는 출력을 얻을 수 있습니다.SPOL을 사용하여 출력을 파일로 스풀합니다.

예를들면,

SQL> SET colsep ,
SQL> SET pagesize 20
SQL> SET trimspool ON
SQL> SET linesize 200
SQL> SELECT * FROM scott.emp;

     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM,    DEPTNO
----------,----------,---------,----------,---------,----------,----------,----------
      7369,SMITH     ,CLERK    ,      7902,17-DEC-80,       800,          ,        20
      7499,ALLEN     ,SALESMAN ,      7698,20-FEB-81,      1600,       300,        30
      7521,WARD      ,SALESMAN ,      7698,22-FEB-81,      1250,       500,        30
      7566,JONES     ,MANAGER  ,      7839,02-APR-81,      2975,          ,        20
      7654,MARTIN    ,SALESMAN ,      7698,28-SEP-81,      1250,      1400,        30
      7698,BLAKE     ,MANAGER  ,      7839,01-MAY-81,      2850,          ,        30
      7782,CLARK     ,MANAGER  ,      7839,09-JUN-81,      2450,          ,        10
      7788,SCOTT     ,ANALYST  ,      7566,09-DEC-82,      3000,          ,        20
      7839,KING      ,PRESIDENT,          ,17-NOV-81,      5000,          ,        10
      7844,TURNER    ,SALESMAN ,      7698,08-SEP-81,      1500,          ,        30
      7876,ADAMS     ,CLERK    ,      7788,12-JAN-83,      1100,          ,        20
      7900,JAMES     ,CLERK    ,      7698,03-DEC-81,       950,          ,        30
      7902,FORD      ,ANALYST  ,      7566,03-DEC-81,      3000,          ,        20
      7934,MILLER    ,CLERK    ,      7782,23-JAN-82,      1300,          ,        10

14 rows selected.

SQL>
  • SQL 개발자 버전 4.1 이전

또는 SQL Developer의 새 힌트를 사용할 수도 있습니다.

/*csv*/

예를 들어 SQL Developer 버전 3.2.20.10에서 다음을 수행합니다.

여기에 이미지 설명 입력

이제 출력을 파일에 저장할 수 있습니다.

  • SQL 개발자 버전 4.1

SQL Developer 버전 4.1의 새로운 기능으로 sqlplus 명령과 마찬가지로 다음 명령을 사용하여 스크립트로 실행합니다.쿼리에 힌트가 필요 없습니다.

SET SQLFORMAT csv

이제 출력을 파일에 저장할 수 있습니다.

오래된 스레드인 것은 알지만, 열 제목 아래의 밑줄을 제거할 수 있는 밑줄 옵션에 대해서는 아무도 언급하지 않았습니다.

set pagesize 50000--50k is the max as of 12c
set linesize 10000   
set trimspool on  --remove trailing blankspaces
set underline off --remove the dashes/underlines under the col headers
set colsep ~

select * from DW_TMC_PROJECT_VW;

조잡하지만,

set pagesize 0 linesize 500 trimspool on feedback off echo off

select '"' || empno || '","' || ename || '","' || deptno || '"' as text
from emp

spool emp.csv
/
spool off

쿼리를 명시적으로 포맷하여 다음 행에 따라 구분된 문자열을 생성할 수 있습니다.

select '"'||foo||'","'||bar||'"'
  from tab

출력 옵션을 적절히 설정합니다.옵션으로 SQLPlus의 COLSEP 변수를 사용하면 필드가 서로 연결된 문자열을 명시적으로 생성할 필요 없이 구분된 파일을 생성할 수 있습니다.단, 콤마 문자를 포함할 수 있는 열에는 따옴표를 붙여야 합니다.

sqlplus 프롬프트에서 콜 이름을 하나씩 편집하는 대신 "set colsep"을 사용하는 것을 선호합니다.출력 파일을 편집하려면 sed를 사용합니다.

set colsep '","'     -- separate columns with a comma
sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv

SQL*Plus를 .dbms_sql ★★★★★★★★★★★★★★★★★」dbms_outputcsv(실제로는 ssv)를 작성합니다.제 githup 저장소에서 찾을 수 있습니다.

필드의 값에는 쉼표와 따옴표가 포함될 수 있으므로 CSV 출력 파일이 올바르지 않기 때문에 권장되는 응답 중 일부는 작동하지 않습니다.필드의 따옴표를 바꾸고 큰따옴표로 바꾸려면 Oracle에서 제공하는 REPLACE 함수를 사용하여 작은따옴표를 큰따옴표로 변경합니다.

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
       '"'   || replace(col1, '"', '""') || 
       '","' || replace(col2, '"', '""') ||
       '","' || replace(coln, '"', '""') || '"' ) -- etc. for all the columns
from   yourtable
/
spool off

또는 필드에 작은 따옴표를 사용하는 경우:

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
'"'   || replace(col1, '''', '''''') || 
'","' || replace(col2, '''', '''''') ||
'","' || replace(coln, '''', '''''') || '"' ) -- etc. for all the columns
from   yourtable
/
spool off

vi 또는 vim을 사용하여 SQL을 쓰고 컨트롤 A와 함께 colsep를 사용합니다(vi 및 vim의 경우 ctrl-A 앞에 ctrl-v를 붙입니다).라인사이즈 및 페이지사이즈를 합리적인 크기로 설정하고 트림스풀과 트림아웃을 켜야 합니다.

파일로 압축해 주세요.그러면...

sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g'  {spooled file}  > output.csv

그 SED는 대본이 될 수 있어요.Ctrl+A의 앞뒤에 있는 *는 불필요한 공간을 모두 꾹꾹 눌러서 빼냅니다.굳이 sqlplus에서 html 출력을 활성화했지만 네이티브 csv는 활성화하지 않은 것은 훌륭하지 않습니까???

데이터 내의 콤마를 처리하기 때문에 이 방법을 사용합니다.나는 그것들을 세미콜론으로 바꾼다.

sqlplus를 사용하여 csv 파일을 생성하는 데 문제가 있습니다.출력에 열 헤더를 한 번만 사용하고 행이 수천 개 또는 수백만 개일 경우 반복되지 않을 정도로 페이지 크기를 크게 설정할 수 없습니다.해결책은 pagesize = 50으로 시작하고 헤더를 구문 분석한 다음 select를 pagesize = 0으로 다시 발행하여 데이터를 가져오는 것입니다.아래 bash 스크립트를 참조하십시오.

#!/bin/bash
FOLDER="csvdata_mydb"
CONN="192.168.100.11:1521/mydb0023.world"
CNT=0376
ORD="0376"
TABLE="MY_ATTACHMENTS"

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 50;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE} where rownum < 2;
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +3 | head -n 1 > ./${ORD}${TABLE}.headers
}

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 0;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE};
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.headers > ${FOLDER}/${ORD}${TABLE}.csv
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +2 | head -n -1 >> ${FOLDER}/${ORD}${TABLE}.csv
}

1994년에 CSV에 테이블을 덤프하기 위해 순전히 SQLPlus 스크립트를 작성했습니다.

스크립트 코멘트에 기재된 바와 같이 Oracle의 누군가가 Oracle Support 노트에 내 스크립트를 포함시켰지만 그 내용은 포함되지 않았습니다.

https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql

또한 이 스크립트는 SQL*LOADER용 제어 파일 및 파라미터 파일도 구축합니다.

spool D:\test.txt

    select * from emp
    
    spool off

csv 힌트를 사용할 수 있습니다.다음의 예를 참조해 주세요.

select /*csv*/ table_name, tablespace_name
from all_tables
where owner = 'SYS'
and tablespace_name is not null;

언급URL : https://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus

반응형

'programing' 카테고리의 다른 글

Angular 식에서 문자열에서 Int로 해석하려면 어떻게 해야 합니까?  (0) 2023.03.05
각진 서비스에서는 어떻게 $on을 사용할 수 있습니까?  (0) 2023.03.05
Angular를 사용하는 로그인 화면이 있는SPA의 예JS 및 ASP에 접속합니다.NET Web API 2?  (0) 2023.02.28
ORA-28040: 일치하는 인증 프로토콜 예외 없음  (0) 2023.02.28
ASMX 파일에서 JSON을 출력하는 방법  (0) 2023.02.28

'programing'의 다른글

  • 현재글SQLPLUS를 사용하여 CSV 형식의 파일로 스풀하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바