Where The Streets Have No Name

with tmp as (select 'a' as id, to_date('200305021120', 'yyyymmddhh24mi') as sdate, to_date('200305021300', 'yyyymmddhh24mi') as edate from dual union select 'b' as id, to_date('200305021300', 'yyyymmddhh24mi') as sdate, to_date('200305021400', 'yyyymmddhh24mi') as edate from dual)select * from (select to_date('200305020000', 'yyyymmddhh24mi') + (level - 1) * 0.5 / 24 as s, to_date('200305020000'..

출처 : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=83&page=1&position=1 rollup 을 활용하는 기본적인 예제이다. 예제1) all_objects 테이블에서 rownum 순서로 10개씩 object_id 합의 소계를 구해 나간다. 쿼리를 주의깊게 보고 응용해보면 좋을 것이다. SELECT owner, object_name, rn, SUM (object_id) FROM (SELECT owner, object_name, object_id, ROWNUM rn FROM all_objects WHERE ROWNUM

http://scidb.tistory.com/entry/Pipelined-Table-Function-의-사용 http://www.oracle-developer.net/display.php?id=207

https://community.oracle.com/message/1480795

https://community.oracle.com/message/1298899

출처 : http://www.orafaq.com/forum/t/4003/2/ CREATE OR REPLACE PROCEDURE reset_seq_to_1(p_seq_name IN VARCHAR2) AS v_seq NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO v_seq; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY -' || v_seq || ' MINVALUE 0'; EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO v_seq; EXECUTE IMM..

http://scidb.tistory.com/entry/About-DBMSXPLAN-1실행계획 http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html http://yjan.tistory.com/tag/dbms_xplan

CREATE OR REPLACE PACKAGE eap_async_call_pkg /******************************************************************************* * PROCEDURE : eap_async_call_pkg * * DESCRIPTIONS : 프로시저의 비동기 처리를 담당 * * * ******************************************************************************** * DATE AUTHOR DESCRIPTION * *------------------------------------------------------------------------------- * 2008...

https://community.oracle.com/thread/699262

출처 : http://forums.oracle.com/forums/thread.jspa?threadID=828118&tstart=195 SQL> with key_tbl as ( select '0' key,null parent_key from dual union all select '1','0' from dual union all select '2','0' from dual union all select '3','0' from dual union all select '1A','1' from dual union all select '2A','2' from dual union all select '2B','2' from dual union all select '3A','3' from dual union all..

Oracle 7.3까지는 join을 포함한 view는 무조건 DML operation이 불가능하였다. Oracle8에서는 updatable join view로 구분되는 join을 포함한 view들 중 일부는 직접 view를 변경하는 것이 가능해졌다. 이 updatable join view에 대한 제약 사 항 및 사용방법을 예제를 통해서 자세히 살펴본다. [참고] updatable join view를 제외한 다른 join view를 변경하기 위해서는 INSTEAD OF trigger를 사용하면 된다. 1. 기본적인 제약사항 updatable join view는 FROM절에 두개 이상의 table을 사용하는 join 형태로, 아 래에 나열한 것은 하나도 포함하지 않아야 한다. - DISTINCT operat..

출처 : http://www.뜨레.kr/zboard/view.php?id=plsql&page=3&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=desc&no=43

출처 : http://www.oracleclub.com/article/13624 --실수로 지운 데이터 살리기.. 예 kfm08ot1이라는 테이블의 bnk_cd ='04' 인 데이터를 실수로 삭제를 했다. commit; 도 완료된 상태라면.. 앞이 막막할것이다. 이럴땐 이렇게 데이터를 불러보자.. SELECT * FROM KFM08OT1 as of timestamp ( systimestamp - interval '10' minute) where bnk_cd = '04' 조회후 파일을 txt나 엑셀로 저장후.. 다시 임포트 해야 합니다.

출처 : http://blog.naver.com/inter999/140048698535 아래와 같은 Query을 Function으로 만들어서 Return 해야 한다면 일반적인 oracle datatype으로는 Table 형태의 결과를 Return 할 수 없고 User Defined Object을 생성하여 사용하면 된다. select d.DEPTNO, d.DNAME, e.EMPNO, e.ENAME from dept d, emp e where d.DEPTNO = e.DEPTNO 실행결과(간략) DEPTNO DNAME EMPNO ENAME 20 RESEARCH 7369 SMITH 30 SALES 7499 ALLEN 30 SALES 7521 WARD 20 RESEARCH 7566 JONES 30 SALES 76..

출처 : http://www.dbguide.net/blog/post/post_view.jsp?urlid=y2k0314&cnum=14539&pnum=8633 참고 : merge enhancements in 10g ☞ MERGE - 한번에 조건에 따라 INSERT,UPDATE 가 가능합니다. - 해당 ROW가 있으면 UPDATE, 없으면 INSERT 문장이 실행 됩니다. ◈syntax) MERGE INTO target_table_name USING (table|view|subquery) ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = val1[, col2 = val2…] WHEN NOT MATCHED THEN INSERT(...) VALUES(...) ME..

참고 : http://www.oracle-base.com/articles/8i/DIYDynamicViews.php The method described in this article is based on the DIY$: Do-It-Yourself Dynamic Views article found on the Oracle Magazine website. The example below allows the alert log to be read using SQL in a similar manner to the user_source view. First an object type is defined to hold each row of data: CREATE TYPE Alert_Row_Type AS OBJECT ..

참고 : http://forums.oracle.com/forums/thread.jspa?threadID=613194

SELECT SQL_ID, T.SQL_FULLTEXT, B.NAME BIND_NAME, B.VALUE_STRING BIND_STRING FROM V$SQL T JOIN V$SQL_BIND_CAPTURE B USING (SQL_ID) WHERE B.VALUE_STRING IS NOT NULL AND SQL_ID = '6zr5bx6zf51w7' ORDER BY B.NAME;

참고 : http://blogs.techrepublic.com.com/programming-and-development/?p=400 I’ve been working with Oracle databases for a little over a year now, and the part I really like best is PL/SQL. That’s their database programming language that lets you combine procedural code with SQL code. What I like most is that it lets you build your entire app in one place ? all the data selects and updates, all the..

참고 : http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the introduction of the DBMS_UTILIT..