Updating clob in pl sql
Updating clob in pl sql - Free hot sex beautiful girl online chating
I have a table in Oracle database with field with data type CLOB. I am storing XML string which is 10,000 characters long for each record.
ISTEMPORARY(my LOB)); END; / 1 declare 2 my Story CLOB; 3 revised Story CLOB; 4 my Gist VARCHAR2(100); 5 revised Gist VARCHAR2(100); 6 BEGIN 7 -- select a CLOB column into a CLOB variable 8 SELECT Story INTO my Story FROM print_media WHERE product_id=10; 9 -- perform VARCHAR2 operations on a CLOB variable 10 revised Story := UPPER(SUBSTR(my Story, 100, 1)); 11 -- revised Story is a temporary LOB 12 -- Concat a VARCHAR2 at the end of a CLOB 13 revised Story := revised Story
You can do this using the CHR() which takes an ASCII value as an argument.thanks, ally I am using dbms_metadata.get_ddl function to get the DDL of my composite partition.Within my PL/SQL procedure, if I declare a "long" variable to store the DDL and later execute the variable using "execute immediate", it works. I really need CLOB because I cannot guarantee my DDL for a composite partition is always less than 32K in size.WRITE(lob_loc, LENGTH(p_document), 1, p_document); END; / CREATE OR REPLACE PROCEDURE PRC_UD_CLOB ( p_document IN VARCHAR2, p_id IN NUMBER) IS lob_loc CLOB; BEGIN SELECT CLOBHOLDERDDOC INTO lob_loc FROM TBL_CLOBHOLDERDDOC WHERE CLOBHOLDERDDOCID = p_id FOR UPDATE; DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document); END; / CREATE OR REPLACE PROCEDURE PRC_RD_CLOB ( p_id IN NUMBER, p_clob OUT VARCHAR2) IS BEGIN SELECT CLOBHOLDERDDOC INTO p_clob FROM TBL_CLOBHOLDERDDOC WHERE CLOBHOLDERDDOCID = p_id; END; / Just wanted to update for anyone using newer releases of Oracle and have stumbled across this (like I did), you no longer have to use dbms_lob.write to write to CLOBs.Until now whenever we modify these files using notepad what we do is truncate the table and reload the data to the table...