SQL & PL/SQL :: Extracting Blob Data Using Oracle Function?

Jul 26, 2012

Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.

Table name: mr_test
Columns: id number
seo blob

CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2
IS
len NUMBER;

[Code]....

ORA-01489: result of string concatenation is too long

when I replcae the

"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;"
with
SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;
myvar1 := myvar1||myvar;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column

Oct 7, 2011

I have come one requirement where i need to extract data from a LONG RAW data type column.

View 7 Replies View Related

PL/SQL :: Extracting Data From XML Message

May 15, 2013

Currently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :

Message Reference     UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123               >(Cancelled)</UploadError>
456123               >4561</UserId>
456123               >1234</SecurityIdentifier

At the moment, I am extracting the required info as follows.

Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header

select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)

Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))

Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.

select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)

View 13 Replies View Related

PL/SQL :: Extracting Data From A String

Oct 4, 2013

This is my sample data: 

WITH table_data
AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL
UNION ALL
SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL
UNION ALL
SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL
[code].........

 I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above. 

View 4 Replies View Related

SQL & PL/SQL :: Extracting Data From Web Service Response

Mar 26, 2013

I need to extract tag values from REST web service response.

Webservice response

Quote:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>

I am storing above response in a table column 'output_xm' of xmltype datatype.

My requirement is to extract 'ResponseCode' & 'quantity' values from above response. For which I am using below query but it's not working.

SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';

View 2 Replies View Related

Client Tools :: Not Extracting All Of The Data

Jan 30, 2013

I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?

View 4 Replies View Related

PL/SQL :: Extracting Data From XML And Inserting Into Table

Aug 8, 2013

I have an XML  of the following format:

<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students> 

Now i have to insert this xml into DB , the table consist of following columns  ( row number , property name , value )  Expected out put is   (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is

1. how to get the tag names  and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?

View 8 Replies View Related

SQL & PL/SQL :: Extracting Data From XML File - Not Working?

Oct 4, 2012

I need to extract the values from XML file.I tried with method described in below links -

[URLs]....

My XML file is -

<?xml version="1.0" ?>
<!DOCTYPE main [
<!ELEMENT main (DATA_RECORD*)>
<!ELEMENT DATA_RECORD (STATE_CODE?,JOB_NAME?,WC_CODE?,JOB_ID?,STATUS?,LOG_MESSAGE?)+>
<!ELEMENT STATE_CODE (#PCDATA)>

[code]...

The query is not returning any rows.

View 3 Replies View Related

PL/SQL :: Extracting Data In A String Separated In Brackets

Jun 7, 2012

I have a scenario where I have to extract data in a string which is enclosed in [].

Here is what I want to do..

input:

[name_first] [name_mi] [name_last]

required output: this should return all the data that is enclosed in the brackets.
name_first
name_mi
name_last

I have tried the "REGEXP_SUBSTR" but my database version is 9i, and it is available only from 10g.

View 3 Replies View Related

Application Express :: Function Returning Blob Content

Sep 10, 2012

I am using the below function to return a blob (CSV) content, this is working fine..But i need to modify this function to display headline for the report and fixed width column in CSV ( then user no need to expand the each and every column ).

create or replace function GET_BLOB (
p_query varchar2
) return blob as

l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);
[code]....

View 10 Replies View Related

Extracting Table Relationships In Oracle

Sep 30, 2008

I have requirement of extracting the relationships between all tables in the Database(Oracle 10.2.02) created by CA Unicenter Servicedesk.

Tell me about any tools/scripts which I can use to extract this information. I can do it manually, but it is time consuming.

View 4 Replies View Related

Application Express :: BLOB Action - Implement File Upload And Download Function

Sep 11, 2013

In my project, I implement file's upload and download function by  "BLOB Support in Forms and Reports" of  official development document Advanced Programming Techniques When user click the BLOB column to download file, I want to trigger an action to update one table  for counting this file is downloaded one more times. But I can not find any "dynamic action" about this Blob column of Report.

View 1 Replies View Related

SQL & PL/SQL :: Fetching BLOB Data

Jun 20, 2012

I have two different database servers where I need to migrate table data from one schema to another schema in batch wise for eg say 100 rows. I used BULK COLLECT with LIMIT. But to access BLOB data from table I have facing errors. What could be other approache to do the same.

here pc_work is a table containing BLOB data in sourse schema. I am fetch data from this table to table t1_test_work using dblink but not working

[
declare
type array is table of test_work%ROWTYPE;
L_DATA array;
cursor C is select * from pc_work@prpctrg;
begin
open C;
LOOP

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Modify BLOB Data

Dec 24, 2010

I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.

The table count will be near to 10 million rows.

We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.

View 26 Replies View Related

SQL & PL/SQL :: Store Large XML Data In Blob?

May 8, 2010

Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.

But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Data From Blob Column?

Sep 30, 2011

example for retrieve data from blob column using pl sql procedure in oracle?

View 2 Replies View Related

SQL & PL/SQL :: Converting BLOB Data To Long Or Varchar

Feb 6, 2013

converting BLOB data into varchar2 or long .

we have function which convert long data and return it has varchar . But has part of Apps upgrade the Column has been converted into blob column.

How we need the same function to read the data from BLOB and return its as long or varchar2.

Somewhere i am making mistake..

CREATE OR REPLACE function GDS.test_alert_msg(v_rowid rowid) return varchar2 is

vblob blob;
i2 number;
amt number :=32767;
len number;
pos raw(32767);
position INTEGER := 10000;
my_vr raw(32767);

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Read Blob Data From Database Into Directory?

Feb 12, 2010

I can store my video in to my database.but i cannot read such file.....using this procedure

ORA-29285: file write error

CREATE OR REPLACE PROCEDURE Extract_bfile
(p_id IN NUMBER)
IS
vblob BFILE;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;

[code]....

View 4 Replies View Related

Cannot Create BLOB Data Type For A Column In Tavle

Jul 6, 2010

user@tank> create table filetable(id number(6), file_content BLOB);

0 rows affected (0.02 seconds)

vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL

I am new to Database field.

- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.

View 1 Replies View Related

Forms :: Read Data From Excel And Store It In Blob?

Aug 10, 2010

I am getting the file using CLIENT_GET_FILE_NAME. I need to read the data from the .xsl file and convert it into blob. The file should not be stored in DB.

View 8 Replies View Related

Windows :: How To Retrieve The Blob Column Data From Table

Mar 7, 2011

I use the oracle 10g database.I am trying to insert and retrive the image.Inserting an image is done.but while retrieving an image iam getting an run time exception in java "java.sql.sqlexception:general error".i am not able to understand this.

The code to insert the image is
FileInputStream fi=new FileInputStream(f);
int size = fi.available();
System.out.println("j"+size);
byte b1[] = new byte[size];
i=fi.read(b1,0,size);
System.out.println("i"+i);
st.executeUpdate("insert into image1 values('b1',"+k+")");
when i am retrieving the image i tried like this
ResultSet rs=st.executeQuery("select imagecolumn from tablename");
here iam getting an exception as i named above.

View 4 Replies View Related

SQL & PL/SQL :: Convert Data In NCLOB Column To BLOB Variable?

May 17, 2010

How can i convert data in NCLOB column to BLOB variable? NCLOB now is tored in UTF16. The data character set is ISO8859P2. Need to keep BLOB at unicode.

View 11 Replies View Related

Precompilers, OCI & OCCI :: Getting Trailing Characters Inserted For Blob Data?

Jul 15, 2010

I am seeing some trailing characters in the coloumn when we are inserting Blob. I am doing SQLBindParameter with SQL_C_BINARY and SQL_LONGVARBINARY as InputOutputType and ValueType respectively.Do you see any problem in this. I get this problem when I am running Oracle 11 g client on an Windows 2008 Server 64bit.When the same set of query is fired from Windows 2003 Server 32bit with Oracle 10g it works fine and no trailing character gets inserted.

View 6 Replies View Related

Server Administration :: Estimated DB Size If BLOB Data Type Is Included

Jul 7, 2010

We will create a new instance in our production server, but this time, part of it's table structure has a BLOB data type (re: <column name> blob(3000)). It's our first time to handle this kind of Oracle data type. What would be my estimate size for it's default tablespace?

View 5 Replies View Related

Forms :: Select Photo (BLOB) Data Into Developer6i Image Item?

Jun 21, 2010

I want to select BLOB (image) data from a table into image item of 6i form. How to do it. I am using Oracle 9i with developer6i.

View 2 Replies View Related

How To Send BLOB In Attachment In Oracle 10g

Feb 16, 2013

Actuall we requried to send and email from Oracle 10g database having attachements big in size aprox 10 to 30 MB.Is there any Custom Procedure or standard procedure for this.

We are using UTL_SMTP and UTL_MAIL (Only support RAW attachement upto 32 k in size).

View 3 Replies View Related

PL/SQL :: Oracle 9i Extract / Parse XML From BLOB

Sep 26, 2013

I have table with Blob column. In the blog column I have XML data (see below).  I wanted to parse xml and get the value of note '<GetTest>'. How do I achieve that? Sample Table:

create table SAMPLE_XML_TABLE
(
  ID           NUMBER not null,
  XMLSTRING    BLOB,
);  
<?xml version="1.0" standalone="no"?>
<FromResponse xmlns="http://www.somewebsite.com/FromResponse">
<Code>0000</Code>
[code]......... 

View 7 Replies View Related

Data Guard :: Move BLOB Images In DB To BFILE To Reduce Time And Size Of Backup?

Jan 22, 2012

We are planning to move BLOB images in our DB to BFILE to reduce the time and size of backup.

So when we have a physical dataguard setup, can we configure data guard to copy the OS level BFILE's to the DR site?

View 2 Replies View Related

Save PDF File Content To BLOB Field In Oracle?

Dec 6, 2008

how to write a procedure in oracle to Save pdf file content to BLOB field in Oracle....

View 11 Replies View Related

PL/SQL :: Delete Or Empty Single Field Containing BLOB In Oracle Table?

Jul 8, 2012

I have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.

what i am looking for is something like

alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved