목록PLSQL (6)

Where The Streets Have No Name

Consistent error logging greatly simplifies PL/SQL debugging

CREATE TABLE ERRORLOG ( ID NUMBER NOT NULL, TIME DATE NOT NULL, CODE VARCHAR2(9 BYTE) NOT NULL, MESSAGE VARCHAR2(2000 BYTE) NOT NULL, PACKAGE_NAME VARCHAR2(100 BYTE), PROCEDURE_NAME VARCHAR2(100 BYTE) NOT NULL, LOCATION NUMBER, PARAMETERS VARCHAR2(4000 BYTE) ); CREATE SEQUENCE ERRORLOG_ID_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ; C..
Developement/DataBase 2010. 10. 26. 15:03
sequence reset

출처 : 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..
Developement/DataBase 2008. 12. 22. 14:19
concurrent program을 이용한 procedure 비동기 호출

CREATE OR REPLACE PACKAGE eap_async_call_pkg /******************************************************************************* * PROCEDURE : eap_async_call_pkg * * DESCRIPTIONS : 프로시저의 비동기 처리를 담당 * * * ******************************************************************************** * DATE AUTHOR DESCRIPTION * *------------------------------------------------------------------------------- * 2008...
Developement/DataBase 2008. 12. 11. 14:38
PL/SQL 101 : Exception Handling

https://community.oracle.com/thread/699262
Developement/DataBase 2008. 12. 5. 17:31
PL/SQL에서 쿼리시 Array Processing 처리방법

---------------------------------------------- 임시 테이블 생성 ---------------------------------------------- connect scott/tiger; DROP TABLE bb; CREATE TABLE bb ( empno NUMBER, empname VARCHAR2(10) ); ---------------------------------------------- 풀이 ---------------------------------------------- DECLARE v_array_size CONSTANT INTEGER := 100; v_empno DBMS_SQL.NUMBER_TABLE; v_empname DBMS_SQL.VARCHAR2_..
Developement/DataBase 2007. 11. 23. 09:54
Handling Exceptions

1. Exception Types - Predefined Oracle Server ; 오라클에서 미리 정한 ERROR...(NO_DATA_FOUND등...) - Non-predefined Oracle Server ; 자주일어나는 error가 아니어서 미리 define되어 있지 않은 error - User-defined ; 오라클 서버 error가 아니라 user가 업무상 rule에 위배되는 사항을 exception처리 하는 것. 2. Predefined Exception Names Exeption Name Description ACCESS_INTO_NULL ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라. COLLECTION_IS_NULL ORA-06531 초기화되지 않은 n..
Developement/DataBase 2007. 8. 17. 14:14
이전 Prev 1 Next 다음