[펌]SQL*plus FAQ 및 TIPS

(V7.X ~ V8.X) : SQL*plus FAQ 및 TIPS ============================================= Q) Row wrapping없이 flat ASCII file을 만드는 방법 Q) 두 개의 Date type column 사이의 평균 시간을 구하는 방법 Q) Ampersand나 특수 문자를 갖는 데이타를 insert하는 방법 Q) SQL 내의 문자열 주변에 인용부호를 생성하거나 QUOTES를 생성하는 방법 Q) `LIKE` 연산자를 사용하여 case insensitive search를 할 수 있는 방법 Q) Column delimited flat file을 생성하는 방법 Q) Product_User_Profile에 관하여 1. Q) Row wrapping없이 flat ASCII file을 만드는 방법은 무엇입니까?     A) `SPOOL` command와 `SET LINESIZE` 와 `SET BUFFER` command를 사용하시기 바랍니다. sql query를 화일에 저장하기 위해서는 spool command를 사용하시면 됩니다. 화일 확장자를 기술하지 않으면, O/S에 따라서 다르지만, 대부분의 O/S에서는 .lst나 .lis를 사용합니다.     SQL*plus에서 flat file을 생성하기 위해서는 먼저 다음과 같은 SET command를 기술하여야 합니다.     SET NEWPAGE 0     SET SPACE 0     SET LINESIZE 80     SET PAGESIZE 0     SET ECHO OFF     SET FEEDBACK OFF     SET HEADING OFF 위의 command를 기술한 후에, spool command를 사용하시면 됩니다. 2. Q) 두 개의 Date type column 사이의 평균 시간을 구하는 방법은 무엇입니까?     A) 다음과 같은 두 개의 row를 가지는 date field가 있다고 가정합니다.     01-jan-96 01:10     01-jan-96 01:20 sql> select avg(to_number(to_char(a, `MMDDYYHHMI`))) from x; AVG(TO_NUMBER(TO_CHAR(A,`MMDDYYHHMI`))) ---------------------------------------                                101960115 즉, 1:10과 1:20 사이의 평균 시간은 1:15입니다. 3. Q) Ampersand나 특수 문자를 갖는 데이타를 insert하는 방법은 무엇입니까?     A) 해결 방법은 세 가지가 있습니다. 1> SQL*plus에서 SET DEFINE OFF나 SET SCAN OFF를 실행하여 substitution variable(&)을 turn off시키는 방법입니다. (Example)     sql> SET DEFINE OFF     sql> INSERT INTO table_name VALUES(`AT&T`);     /     1 row created 2> SET DEFINE ON 상태로 유지시키면서 substitution variable을 다른 non-alphanumeric 문자나 non-white space 문자(*, % 등등)로 대체시킨다.   (Example)     sql> SET DEFINE %     sql> INSERT INTO table_name VALUES(`AT&T`);     /     1 row created 3> SET ESCAPE ON 상태에서(DEFINE은 &로, SCAN은 ON상태로 유지) 특수 문자 앞에 escape 문자인 backslash(``)를 선행하도록 합니다. (Example)     sql> SET ESCAPE ON     sql> SHOW ESCAPE          ESCAPE "" (hex 5c)     sql> INSERT INTO table_name VALUES (`select * from emp where ename=&1`);     1 row created.     sql> select * from table_name;     COL1     ---------------------------------------------------------     SELECT * FROM EMP WHERE ENAME=&1 4. Q) SQL 내의 문자열 주변에 인용부호를 생성하거나 QUOTES를 둘 수 있습니까?     A) 해결 방법은 다음과 같습니다. 1> Single quotation mark를 생성하기 위해서는 4 single quote(````)를 사용하 시기 바랍니다. (Example)     sql> select ```` from dual; 위의 문장을 실행 시 return되는 결과는 다음과 같습니다. ` - ` 2> 문자열 주변에 single quote를 두기 위해서는 3 single quote 안에(```) 문자 열을 포함시키면 됩니다. (Example)     sql> select ```character string in quotes``` result          from dual; 위의 문장을 실행 시 return되는 결과는 다음과 같습니다. RESULT ----------------------------------------------- `CHARACTER STRING IN QUOTES` 3> 참고로, Literal single quote를 생성하기 위해 연속적인 single quote mark 를 사용하는 방법은 다음과 같습니다. (Example)     srw.do_sql(`select decode(dname, ``NONE``, NULL, ``A``)                 from dept                 where deptno = 10`); 위의 문장을 실행 시 return되는 결과는 다음과 같습니다.     select decode(dname, `NONE`, NULL, `A`)     from dept     where deptno = 10 (Example)     DECLARE        a varchar2(200);        q char(1) := ````;     BEGIN        a := ```this is a ` || q || `quoted`` string` || q;     END; 위의 문장을 수행 결과 스트링 a에는 `this is a `quoted` string` 이 저장됩니다. 5. Q) `LIKE` 연산자를 사용하여 case insensitive search를 할 수 있는 방법은 무엇입니까?     A) LIKE 연산자를 사용하여 비교하기 위해 필드에 `UPPER` 함수를 사용하시면 됩니다. 6. Q) Column delimited flat file을 생성하는 방법은 무엇입니까?     A) 해결 방법은 두 가지가 있습니다. 1> query 내에서 column 분리자를 concatenate시키면 됩니다. (Example) To delineate column text with TABs: ------------------------------------ sql> select deptno || chr(9) || dname from dept; DEPTNO || CHR(9) || DNAME ------------------------------------ 10        ACCOUNTING 20        RESEARCH 30        SALES 40        OPERATIONS CHR(9) 문자함수는 query 문 안에 TAB을 embedding하기 위해 사용됩니다. (Example) To deleneate column test with COMMAs: ------------------------------------- sql> select deptno || `,` || dname from dept; DEPTNO || `,` || DNAME ------------------------------------- 10,ACCOUNTING 20,RESEARCH 30,SALES 40,OPERATIONS Query 결과를 화일로 보내기 전에 다음과 같이 SET COMMAND를 수행하시기 바랍니다. SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SPOOL file_name 원하는 Query문 수행 후에는 다음과 같이 다시 setting 하시기 바랍니다. SPOOL OFF SET TERMOUT ON SQL*plus 버젼 3.0.x와 3.1.x에서는 컬럼 사이의 간격을 조정하기 위해 column 분리자를 명시해 주는 SET COMMAND가 지원되지 않습니다. 2> SQL*plus 3.2 이상의 버젼에서는 COLSEP command를 사용하시면 됩니다. Query 결과를 comma delimited flat file에 보내기 전에 다음과 같이 SET COMMAND 를 수행하시기 바랍니다. SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET COLSEP `,` SPOOL file_name 원하는 Query 문 수행 후에는 다음과 같이 다시 setting하시기 바랍니다. SPOOL OFF SET TERMOUT ON 7. Q) PUPBLD.SQL 스크립트를 찾지 못하여 public synonym을 drop할 수 없습니다. 해결 방법은 무엇이며, Product_User_Profile에 관하여 알고 싶습니다.    A) Product_User_Profile은 product-level security를 제공하기 위해 DBA가 수정할 수 있는 테이블입니다.     대부분의 오라클 product들은 SQL GRANT와 REVOKE command에 의해 제공되는 user-level security와 user role들을 추가한 product-level security를 제공하는 이 테이블을 사용합니다.       이 테이블을 생성하기 위해서는 SYSTEM으로 connect하여 PUPBLD.SQL이나 V7PUP.SQL을 돌려주어야 합니다.    PUPBLD.SQL 스크립트를 실행하면 SYSTEM 계정 내에 product_profile과 user_profile 테이블을 생성해 줍니다. 여기에는 두 개의 view가 있는데, product_privs와 user_privs가 그것입니다.     사용자들은 product_profile과 user_profile이라는 이름으로 조회할 수 있습 니다. 이 view를 조회하면 해당 계정에 관한 privileges와 profile들을 알 수 있습니다.     그런데, 현재 사용 중인 일부 product나 user profile structure는 제거하지 않기 때문에 manual하게 table, synonym, view들을 drop해 주어야 합니다.     만약, delete해야 할 내재하는 structure가 있다면, 다음의 SQL문을 SQL*plus 에서 수행해 주시기 바랍니다.     drop synonym product_user_profile;     drop table product_profile;     drop table product_user_profile;     drop view product_privs;     revoke select on product_privs from public;     drop public synonym product_profile;     drop synonym product_user_profile;     drop public synonym product_user_profile;     drop table user_profile;     revoke select on user_profile from public;     drop view user_privs;     revoke select, update, insert, delete on user_privs from public;     drop public synonym user_profile;