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.
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;
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.
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.
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
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 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?
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.
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.
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?
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
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.
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..
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.
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.
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.
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]........
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?
