SQL & PL/SQL :: Merge Data Into CLOB
Apr 12, 2011
I'm trying to write a package which will allow my users to send emails, based on templates, from our system. The email body templates are stored as text in a CLOB column in a EMAIL_TEMPLATES table, along with an email_id. Here's an example:
Quote:Dear [dearname],
I write with regard to your child, [childname] who is attending [schoolname].
Now, I'm trying to write a procedure that will take an email_id and the fields such as dearname, childname, etc., merge the data into the template and return the final body of the email.
The email body will then finally be inserted into a standard HTML email template and be sent using UTL_MAIL.
I was planning on using a series of nested REPLACE functions but I'm sure there must be a tidier way than that. Particularly as I would like the procedure to be as flexible as possible to allow me to easy adapt it to receive additional fields for different email templates in future.
View 3 Replies
ADVERTISEMENT
Jan 25, 2011
I have two tables:
Table 1:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null
Table 2:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null
Both tables are the same layout however I need to merge the data to a DB View on demand. What would the SQL look like to join like this?
View 7 Replies
View Related
Sep 28, 2011
I'm writing a PHP page to display some data from an Oracle database. Unfortunately, I can't copy the code because it's proprietary. One of the columns in the db is of type CLOB. I'm having trouble getting the data from the CLOB column.
The way the code is now, is there is a query string read into a variable, and the query string variable is read into a function that then retrieves the data from the db. Once the result set is returned, it is parsed to get data from all of the columns. The issue is that I've never worked with CLOB data before, so I'm having some difficulty extracting the data for that column. I know I can use the DBMS_LOB.READ function, but I'm not sure how to apply it in this case. The following is a generic form of the query string I'm using with b.clob_col being the column I'm having issues with.
$queryString = <<<EOD
SELECT a.col1,
a.col2,
a.col3,
b.col4,
b.col5,
b.clob_col,
from table1 b
inner join b.col4
on blah1
inner join a.col2
on blah2
where blah
and blah
EOD;
View 1 Replies
View Related
Aug 29, 2013
Environment:Oracle 11.2.0.3 EE on Solaris.
I am trying to extract XML data stored in a CLOB and produce a flat file for the user's consumption. They have sent me what they think is the XSD for the XML data but when I look at the data in the CLOB I don't see the tags documented in the XSD. I'd like to produce an XSD based on the actual data to compare with what they've sent. Is this possible? I am able to query the XML data using the tags I see in the data using XML Table and it's working fine.
View 2 Replies
View Related
Jan 5, 2012
I want to know whether is it possible to send CLOB data automatically using CDC...i usually have to run a procedure manually to send such data to remote locations.
View 1 Replies
View Related
Aug 21, 2006
I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.
Below are the structures of both tables;
SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)
[code]....
The Table contains 5000 records. how to convert CLOB to VARCHAR2.
I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.
View 10 Replies
View Related
May 28, 2010
I need to pass a large data into one of the tables where the column is declared as CLOB before which I was checking with the sample code as below which is throwing an error.
I was trying to insert the CLOB data into the table as below.
create table t1
( x number,
y clob
);
insert into t1(x) values (2)
declare
l_clob t1.y%type;
[code].....
The error I am getting is:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at line 161
View 1 Replies
View Related
Apr 15, 2010
I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.
example ->
Data in clob field will be->
H|12|1233|fff|sss
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
in detail table.
End of excercise will redult-> 1 record in header and 5 records in detail table.
I need to implement it in my project.
View 7 Replies
View Related
Sep 11, 2013
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
View 13 Replies
View Related
Jun 13, 2012
i have a problem when i try to insert a large character string of nearly 1 lac characters (code of html) in a clob column of my test table, then i get an error "ORA-01704: string literal too long" , i didnot understand that why clob column is not storing this data.
View 1 Replies
View Related
Dec 5, 2012
I have a table created as below
create table tab123( x clob);
Inserted a row as below
insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>
[code]....
Now i want to retrieve the value present under a given xpath.
View 7 Replies
View Related
Aug 3, 2012
I have a CLOB column in one of my tables (Table1), which stores very large (150MB+) XML files. I have new/another table (Table2) in the DB where I have an XMLType column. I want to take the CLOB data (xml) from table1 and remove some part of that and store the rest into to the XMLType column of Table2.
I want to remove the data inside the XML tags
<Attachments>
very long data goes here... which I don't need, which should be replaced with a single word
</Attachments>
store the CLOB to XMLType column after removing the unwanted data.
View 2 Replies
View Related
Nov 15, 2007
i am trying insert data from one DB to other DB table. one field data type is LONG in first DB table, Same field data type in other DB is CLOB.
i used TO_LOB function to convert from LONG to CLOB data type.
My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.
View 3 Replies
View Related
Aug 16, 2012
I want to convert a clob datatype to varchar data type.I have a clob data type in the below format.
-----------------------------------------------------------------------------------------
<HTML><HEAD><TITLE>Tata Communications Limited</TITLE><BODY leftMargin=20 topMargin=10 rightMargin=20 marginheight=10 marginwidth=10><Table border="0" cellpadding="0" cellspacing="0"
-----------------------------------------------------
I want to insert all the values into varchar datatype.provide the sample code how to convert.
View 1 Replies
View Related
May 24, 2010
I have a requirement where in I have to store large data in one of the database columns using stored procedure.
I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.
Is there any restriction in the data size to be passed to the stored procedure?
View 9 Replies
View Related
Apr 12, 2012
Iam using Oracle client 11g(32 bit) and Oracle database 11g on Unix server.
I have the created the following procedure to load XML file located in TEST_XML oracle directory( which is mapped to
DB/bulk01/REFLIB/REFDV/LOAD_XML) on the server.
Create or replace procedure TEST_LOAD_XML IS
dest_clob clob;
src_clob BFILE := BFILENAME('TEST_XML', 'arenas.xml');
dst_offset NUMBER := 1;
src_offset NUMBER := 1;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning NUMBER;
[Code]....
when I execute the above procedure I get ORA-22288 file or LOB operation FILEOPEN failed No such file or directory error.
Looks like it did not find my oracle directory 'TEST_XML'
do I have to do anything like mapping? if so since my database is on unix server how do I map?
View 4 Replies
View Related
Nov 24, 2012
I am migrating data from DB2 to Oracle. I used DB2 export to extract the data specifying lobsinfile clause. This created all the CLOB data in one file. So a typical record has a column with a reference to the CLOB data. "OUTFILE.001.lob.0.2880/". where OUTFILE.001.lob is the name specified in the export command and 0 is the starting position in the file and 2880 is the length of the first CLOB.
When I try to load this data using sqlldr I'm getting a file not found.Attached is a copy of the control file and output from testing
PS. I cant use the DB2 option LOBSINSEPFILES which creates a separate file for each CLOB column because the table has over 14 million rows....and creating 14 mil files causes OS inode problems...
Attached File(s)
sqlldr.txt ( 2.05K )
Number of downloads: 3
View 5 Replies
View Related
Oct 6, 2010
I am facing a problem with utl_http.write_text in my pl/sql application. My requirement is to write data of size>32k. So I used a clob variable in write_text. But still it is showing numeric or value error when the data size is above 8k.
I have read that chunked transfer encoding will work. But I couldn't find out how this is done.
View 5 Replies
View Related
Aug 21, 2012
How to load the CLOB data into table..in the attached file 18 column has clob data it's appear like new line..Using external table how to load. i tried it's not working..
View 12 Replies
View Related
Oct 14, 2013
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 28 Replies
View Related
May 4, 2011
Some materialized views get status broken on refreshment, but only sometime. When I try to refresh them manually I get following message:
"ORA-01400: cannot insert NULL into...".
But I know for sure that there are no NULL values in the master table, MV and master tables are declared in the same way and all columns in master tables are NOT NULL columns. Another ting is that this error I get only on columns with data type CLOB.
View 1 Replies
View Related
Aug 2, 2012
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
View 15 Replies
View Related
Dec 14, 2012
My ORACLE DB version is:
('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');
I have this XML data stored in a CLOB column:
<Activity>
<Changes>
</Changes>
<Inserts>
</Inserts>
<Definition>
[code]....
I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.
View 3 Replies
View Related
Aug 13, 2012
I would like to run below query on all tables, however it doesnt work on clob and long datatypes
select * from owner.table_name
minus
select * from owner.table_name@remote_db;
from dba_tables
where owner in ( '....');
ORA-00932: inconsistent datatypes: expected - got CLOB
How can I compare the data of clob and long datatypes using dblink ?
View 2 Replies
View Related
Sep 13, 2010
code to append data of 32k to exisitng CLOB field in an oracle table..
View 1 Replies
View Related
Aug 29, 2012
To remove the last comma end of string and load the Clob data into table. create table test(name clob)
View 2 Replies
View Related
Oct 11, 2011
I want to spool CLOB data into flat file ?
View 6 Replies
View Related
Oct 6, 2011
I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
View 1 Replies
View Related
Jun 18, 2012
I'm loading data from text file separated by TAB and i got the error below for some lines. Event the column is CLOB data type is there a limitation of the size of a CLOB data type. The error is:
Record 74: Rejected - Error on table _TEMP, column DEST.
Field in data file exceeds maximum length
I'm using SQL Loader and the database is oracle 11g r2 on linux Red hat 5. Here are the line causing the error from my data file and my table description for test:
create table TEMP
(
CODE VARCHAR2(100),
DESC VARCHAR2(500),
RATE FLOAT,
INCREASE VARCHAR2(20),
COUNTRY VARCHAR2(500),
DEST CLOB,
[code]........
View 3 Replies
View Related
Sep 8, 2011
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
View 8 Replies
View Related
