Send CLOB Data Automatically Using CDC?
Jan 5, 2012I 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
ADVERTISEMENT
Application Express :: How To Automatically Send Report After It Is Created
Sep 16, 2013I am beginner . I found this tutorial how to save and access report.Storing and Accessing Reports in the Database In that tutorial report is saved onclick.Is there a way to automatically email report everytime report is generate or I should first save it (PDF, CSV,..) with SQL script and then email it? I also found that in Interactive report there is page process that can send e-mails. Is it possible that page process send e-mail with report or report should be also saved before sending? The reason for this is that we want to have a history of created reports.
View 6 Replies View RelatedSQL & PL/SQL :: Set / Send / Input CLOB Value To Function?
Jan 19, 2012Trying to set/send/input CLOB value to function.
I tried:
Clob size is about 4-5KB of data.
DECLARE clobz CLOB:=EMPTY_CLOB();
BEGIN
SELECT '<claim>'+
'<lbo_id>34</lbo_id>'+
'<claim_type_id>11</claim_type_id>'+
'<claim_subtype_id>11</claim_subtype_id>'+
[code]...
Error:
Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
[code]...
SQL & PL/SQL :: Sending Mail Through UTL_SMTP / Unable To Send CLOB In It
Sep 14, 2010I am sending mail thru UTL_SMTP. But i am unable to send the CLOB in it.If i break the CLOB into varchar2 then also it doesn't work.
Variables:-
vBuffer VARCHAR2 (4000);
l_amount BINARY_INTEGER := 4000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
[code].....
While using the write_data procedure it is giving error as "Invalid OPeration"...how we can send the mail with CLOB.
Any Data Compression Wizards That Automatically Suggest Level At Which Data Should Be Compressed
Jun 29, 2011whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?
View 3 Replies View RelatedData Guard :: Recovery Is Not Running Automatically
Sep 5, 2013in a RAC multi-node environment with single instance dataguard..Recovery is not running automatically I have to re-run the recovery command to make them sync and this command works for already archived log files no upcoming archive log file applied I have to re-run the recovery command again for newly archived log files.
View 2 Replies View RelatedReplication :: Materialized View Is Not Refreshing Data Automatically
Feb 14, 2012I have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:
CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...
Above query should be refreshing the data every minute but I realized that its not doing it.
SQL & PL/SQL :: Send Data To Webservice
Jun 15, 2010we have to send data in XML Format to a webservice. We hate the data in a oracle table and have a XSD File. Is it possible to create the XML and send it to the service all from PL/SQL ?
View 6 Replies View RelatedForms :: Upload Data Using 6i While Compiling Form Automatically Closed
Dec 6, 2010i have create database link with msaccess2000 its working fine in forms 6i i am trying to upload data by using forms 6i while compiling form is automatically closed my code is this
begin
insert into baba1
(select *
from baba1@access_db
where dte between to_date(:block3.fdate,'dd-mon-yyyy')
and to_date(:block3.ldate,'dd-mon-yyyy')
[code]....
Data Guard :: Archive Log Cannot Send To Standby
Feb 15, 2012The archive log can not send to the standby database, how to do?
primary database spfile:
*.db_name=oracl
*.db_unique_name=oracl
[Code]....
Error 12170 received logging on to the standby
Error 12170 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'oraclbak'
Error 12170 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'oraclbak'
ORA-12170: TNS:Connect timeout occurred
*** 2012-02-15 08:31:50.678 60679 kcrr.c
PING[ARCq]: Heartbeat failed to connect to standby 'oraclbak'. Error is 12170.
*** 2012-02-15 08:31:50.680 58941 kcrr.c
kcrrfail: dest:2 err:12170 force:0 blast:1
kcrrwkx: nothing to do (end)
JDeveloper, Java & XML :: Send Data From Client To Server?
Jan 6, 2011I want to send data from my window based standalone application to my web based application.
View 1 Replies View RelatedGet Data From CLOB
Sep 28, 2011I'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;
SQL & PL/SQL :: Reading Data From XML Message And Send Result To Net Using Bulk Collect
Aug 15, 2013We want to send the following XML message as IN parameter to the procedure
<?xml version="1.0" encoding="UTF-8" ?>
- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
- <s:Body>
- <CalculateWEDBCached xmlns="http://wedb.GGG.Services/Contracts/">
- <skuItems xmlns:a="http://schemas.datacontract.org/2004/07/wedb.GGG.Services.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
- <a:SkuItem>
[code].......
I need a procedure to get the records from the table for every SKU of XML message from the table and take one record for each SKU based on order precedence.And send the set of output records to the .net code. The order precedence is
If MODEL is not null that record should be high precedence and consider max lead_time
If MODEL and VK_UNIT is NULL then the precedence goes to STATE_ID.
The parameters in the procedure are
CREATE OR REPLACE PROCEDURE sku_proc
(
p_bu_id IN number,
p_model IN varchar2,
p_vk_unit IN XMLTYPE,
p_country_id IN varchar2,
[code].......
Query to get the data from the table based on the parameters.
SELECT bu_id,
model,
vk_unit,
country_id,
state_id,
Lead_Time,
[code].......
Here every SKU from XML message should be passed as p_vk_unit to VK_UNIT column in the query
210-39348
210-39348
210-39348
405-12132
340-30904
340-30904
340-30904
403-10890
403-10890
709-10007
Based on the above query assume we got the following records.
BU_ID MODEL VK_UNIT COUNTRY_ID STATE_ID LEAD_TIME FC
0 M123 210-39348 AB 0 20 A
0 M123 210-39348 AB 0 30 B
0 NULL 210-39348 AB 0 10 C
0 M123 405-12132 AB 0 10 A
0 NULL 340-30904 AB 0 30 C
0 M123 340-30904 AB 0 20 B
0 M123 340-30904 AB 0 10 A
0 NULL 403-10890 AB 0 10 B
0 M123 403-10890 AB 0 20 A
0 M123 709-10007 AB 0 10 B
0 NULL NULL AB 0 20 B
0 NULL NULL AB 1 30 A
Th final query has to return the following result to the OUT parameters p_Lead_Time p_FC of the procedure.
LEAD_TIME FC
30 B
10 A
20 B
20 A
10 B
30 A
How to implement my above requirement using BULK COLLECT.
SQL & PL/SQL :: Merge Data Into CLOB
Apr 12, 2011I'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.
XML DB :: Create XSD From XML Data In CLOB
Aug 29, 2013Environment: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.
SQL & PL/SQL :: Inserting Data From Clob To Varchar2?
Aug 21, 2006I 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.
SQL & PL/SQL :: Insert CLOB Data To The Table?
May 28, 2010I 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
SQL & PL/SQL :: Reading Data From Clob Field
Apr 15, 2010I 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.
SQL & PL/SQL :: Print Data From A Clob In XML Format?
Sep 11, 2013I 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?
SQL & PL/SQL :: Insert Data In Clob Column?
Jun 13, 2012i 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 RelatedPL/SQL :: Retrieve XML Data In Clob Type?
Dec 5, 2012I 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.
PL/SQL :: Remove / Replace Some Data In CLOB
Aug 3, 2012I 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.
How To Convert Long Data Type To CLOB
Nov 15, 2007i 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.
SQL & PL/SQL :: Convert Clob To Varchar Data Type
Aug 16, 2012I 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.
SQL & PL/SQL :: Passing CLOB Data As Input Parameter?
May 24, 2010I 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?
SQL & PL/SQL :: Loading XML File Into Clob Data Type
Apr 12, 2012Iam 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?
SQLLDR With CLOB Data In Single Flat File?
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
Need Clob Variable To Write Large Volume Of Data?
Oct 6, 2010I 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.
Server Utilities :: Loading CLOB Data Into Table
Aug 21, 2012How 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 RelatedJDeveloper, Java & XML :: Looping And Querying Data From XML Clob
Oct 14, 2013Our 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.
