When the following error occurs like this

"oracle input pattern or replacement parameters exceed 32K size limit ... CLOB ..."

 

CREATE OR REPLACE FUNCTION REPLACE_CLOB( p_origin                          IN CLOB
                                	, p_search_text                     IN VARCHAR2
                                	, p_replace                         IN CLOB
) 
RETURN CLOB 
IS
    l_pos pls_integer;
    out_replace_clob CLOB := p_origin;
BEGIN
    l_pos := instr(p_origin, p_search_text);

    IF l_pos > 0 THEN
        WHILE l_pos > 0 LOOP
            out_replace_clob := substr(out_replace_clob, 1, l_pos-1)
            || p_replace
            || substr(out_replace_clob, l_pos + LENGTH(p_search_text));

            l_pos := instr(out_replace_clob, p_search_text);
        END LOOP;
        RETURN out_replace_clob;
    END IF;

    RETURN p_origin;
END REPLACE_CLOB;

-- v_huge_sql := REPLACE_CLOB(v_huge_sql, '[[REPLACE_LOCATION_IN_QUERY]]'    , v_huge_replace_text);

 

be the happy Gosu.

woojja ))*

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

반응형

The maximum number of columns allowed in a table of ORACLE are 1000.

(in 8i, 9i, 10g, 11g, 12c, 18c and 19c)

(ORACLE 7 had 254 columns limit.)

 

be the happy Gosu.

woojja ))*

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

반응형

There is a code snippet in the query that I don't understand, but i write them down. ^^;

SELECT TO_CHAR(TO_DATE(ROWNUM+2454832, 'J'), 'YYYYMMDD') AAA
FROM (
    SELECT LEVEL M FROM DUAL
    CONNECT BY LEVEL <= 2556
)
ORDER BY AAA

Result :

 

DateFormat 'J' Option is

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

 

Format Models

The total length of a datetime format model cannot exceed 22 characters. The default datetime formats are specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. You can c

docs.oracle.com

 

What is the principle of this snippet?

SELECT LEVEL M FROM DUAL
CONNECT BY LEVEL <= 2556


I can guess why, but I don't understand the principle.

Is there anyone who can teach me? ^^;

 

There is a saying in Korea.

If you don't understand, calm down and read(memorize) it.

^^;

 

be the happy Gosu.

woojja ))*

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

 

반응형


-- 계정 : TEST_ACT, TEST_ACT2, TEST_ACT3, TEST_ACT4, TEST_ACT5

DROP TABLE TEST_ACT.TEST_TABLE;
CREATE TABLE TEST_ACT.TEST_TABLE
(
  COLUMN1 VARCHAR2(20) NOT NULL,
  COLUMN2 VARCHAR2(20) NOT NULL,
  COLUMN3  VARCHAR2(40) NOT NULL,
  CREATE_DATE DATE DEFAULT SYSDATE NOT NULL,
  CREATE_USER_ID  VARCHAR2(40) DEFAULT 'woojja' NOT NULL,
  UPDATE_DATE     DATE DEFAULT SYSDATE NOT NULL,
  UPDATE_USER_ID  VARCHAR2(40) DEFAULT 'woojja' NOT NULL
)
TABLESPACE TBL_SPC
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );
  
COMMENT ON TABLE TEST_ACT.TEST_TABLE
  IS 'Test Table';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.COLUMN1
  IS 'COLUMN1';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.COLUMN2
  IS 'COLUMN2';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.COLUMN3
  IS 'COLUMN3';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.CREATE_DATE
  IS '생성일자';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.CREATE_USER_ID
  IS '생성자아이디';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.UPDATE_DATE
  IS '수정일자';
COMMENT ON COLUMN TEST_ACT.TEST_TABLE.UPDATE_USER_ID
  IS '수정자아이디';
  
ALTER TABLE TEST_ACT.TEST_TABLE
  ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (DIVISION_CODE, PRODUCT_FAMILY_CODE, PROJECT_CODE, NPI, PRODUCTION_SITE_CODE, COMPONENT_CODE)
  USING INDEX 
  TABLESPACE TEST_SPACE
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

GRANT SELECT ON TEST_ACT.TEST_TABLE TO TEST_ACT2;

=>
GRANT SELECT, INSERT, UPDATE, DELETE ON TEST_ACT.TEST_TABLE TO TEST_ACT2 WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, DELETE ON TEST_ACT.TEST_TABLE TO TEST_ACT3;
GRANT SELECT, INSERT, UPDATE, DELETE ON TEST_ACT.TEST_TABLE TO TEST_ACT4;
GRANT SELECT ON TEST_ACT.TEST_TABLE TO TEST_ACT5;  



-- TEST_ACT2 에서 실행
DROP SYNONYM  TEST_TABLE;
CREATE SYNONYM TEST_TABLE FOR TEST_ACT.TEST_TABLE;

반응형

+ Recent posts