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
ADVERTISEMENT
Sep 30, 2011
example for retrieve data from blob column using pl sql procedure in oracle?
View 2 Replies
View Related
Aug 8, 2012
My company already work with Oracle 10g, I developed an application using this database and asp.NET. It allows the management of BD and exploitation of these data by clients via ASP pages.
Now I must use Microsoft Exchange (especially the calendar) to retrieve data from the Oracle database and embed links to setup my ASP pages to the calendar.
Being new to Exchange, my questions are:
- It can be done?
- Should I use a particular driver?
- Some idea ?
View 0 Replies
View Related
Dec 10, 2012
I have kept xml in blob and i am retriving it as following.
select utl_raw.cast_to_varchar2(dbms_lob.substr(colblobforxml,2000,1)) from t1
the problem is, it only gets 2000 chars and i have more than 2000 chars.
View 2 Replies
View Related
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
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
Apr 5, 2013
I want to know how to retrieve or extract the blob datas from the database using pl/sql coding .
View 1 Replies
View Related
May 30, 2013
i have a column that contains 1 to 100 values but it has some missed data such as it does not has 50,45,34,23 etc..
so how could i find the missed data alone in a single query..
View 4 Replies
View Related
Feb 5, 2007
when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.
As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.
To give you an example:
select sum(BYTES)
from DBA_SEGMENTS
where owner = user
and segment_name = 'MY_TABLE'
group by SEGMENT_NAME
returns 262144
running:
SELECT sum(length(blob_column))
FROM my_table
returns 821333
There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.
I'm using Oracle 10.2.0.3.0 on Redhat
View 2 Replies
View Related
Apr 7, 2013
I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.
If the input is 'All' -> the variable :SELECTION is blank so if the SQL :
select name, address, ID from employee :SELECTION
will be
select name, address, ID from employee
If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'
so the SQL:
select name, address, ID from employee :SELECTION
should be
select name, address, ID from employee where ID in ('1234','9999')
However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.
View 1 Replies
View Related
Nov 10, 2011
Look into the below table:
TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|
Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format
TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|
I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
View 7 Replies
View Related
Dec 13, 2011
I have the following table with the some sample data:
|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |
The table works in such way:
1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.
2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.
My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.
How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.
View 2 Replies
View Related
Jan 20, 2012
How to retrieve first hundred records from a table ?
FYI
---
The table size is 5 GB
The table count is 127922653
Table has 14 columns
Table is partitioned as well.
The table has 10 partitions.
View 17 Replies
View Related
Sep 2, 2009
I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.
View 4 Replies
View Related
Nov 10, 2012
I am using oracle 11g database.
unforunatly i delete the data from main table. and i operated alter stmt.
now how do i retrieve the data..??
View 5 Replies
View Related
May 29, 2012
I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:
1. How to add item through block base on number of column which selected table has.
2. How to set value of table column in these mentioned column which added in the block.
3. And at the end retrieve the table data.
View 1 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Jun 17, 2012
i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding
DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....
just caling here EMPNO and :v_empno is fields on form...
View 1 Replies
View Related
Aug 11, 2011
base on performance it is better to retrieve data from view or mention the table names directly?
I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view
View 9 Replies
View Related
Nov 14, 2010
it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac 2.111.7.20.
this is my .NET code (which works fine on every environment it was installed in the last 2 years):
using (OracleConnection sqlConn = new OracleConnection())
{
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";
[code].......
this is the oracle procedure:
PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;
The problem that we saw was that on one of the client machines dr["Name"] threw exception :
Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)
We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])
How can it be? from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?
View 4 Replies
View Related
Nov 9, 2008
how to insert into a BLOB column. I have stored procedure with an input parameter of type BLOB now how do I insert this BLOB into a table.
View 2 Replies
View Related
Jul 21, 2011
I have a table with a column of type blob. Now i want to create a procedure which will insert into that table. But I don't like to create a directory. How can i solve this.
I want to insert a row like this:
insert into table x(image)
values('d:photo est.jpg');
View 2 Replies
View Related
Jun 7, 2013
I have these tables:
Products
CREATE TABLE "COMPDB"."PRODUCTS"
( "PRO_ID" NUMBER NOT NULL ENABLE,
"PRO_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"COMPETITOR" NUMBER NOT NULL ENABLE,
"CATEGORY" NUMBER NOT NULL ENABLE,
[Code]..
Now my problem:
In want to delete the blob column(The Content) in Products and the columns, which belong to this column(Filename,Mimetype,Last_update_date).
EMPTY_BLOB() doesn't work
View 4 Replies
View Related
Jul 31, 2013
on this query. I need to get a particular value from a column which is a BLOB datatype. Here is the sample
data ID TESTDATA1 Best Buy
00001234 12222 30 00 2
Lowes 00001234 12222 100 00 3
Walmart 00001234 12222 129 00 4
abc 00001234 12222 5000 00 5
Toshiba 00001234 12222 21 00 6
abcdefghij 00001234 12222 49 00
Where '00001234' is the Invoice , '12222' is Netamount field and highlighted in red is the $ amount for that invoice.
The only data i need query to return 12222 (netamount)= $amount. I tried using substr select substr((TESTDATA),19,26)test from TABLEA; But this gives me the only the first row but not other amount which have different positions.
Here is the desired output
OutputID TESTDATA 1
12222 30 2 12222 100 3
12222 129 4 12222 5000 5
12222 21 6 12222 49
View 11 Replies
View Related
Jan 20, 2012
example
My table mst_product(product_code,product_name) and its value is (190,toy),(198,tim),(280,bush),(jupiter,top),(AUTO2,auto),(281,tig),(282,com)...and so on
I want to make a query to select those items which is equal to and greater than 280. So how we make a query.How select those items which does not have any alphabetic letters.
View 4 Replies
View Related
Apr 9, 2012
I am trying to extract the BLOB column in Oracle Apex and show the contents of it as a popup message.
The files which are stored in the blob column are being zipped using windows zip function in Perl and being inserted in the dataabse using some perl program as follows,
my $zip = Archive::Zip->new();
my $zipfile = $zip->addFile( @$_->[1] ,basename(@$_->[0]));
While extracting the blob column we are using the utl_compress.lz_uncompress_extract() to extract the uncompressed version of file, but we are getting the following error as
"ORA-29294: A data error occurred during compression or uncompression".
I even tried the solution as mentioned in oracle forum
View 6 Replies
View Related
Mar 11, 2012
I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.
View 1 Replies
View Related
Jan 20, 2011
I want to store a pdf file into a database column of BLOB type. The pdf file on the client system not on the database server. Is there any way i can achieve this?
View 1 Replies
View Related
Mar 11, 2011
I have two tables with BLOB datatype. I am trying to copy from one column to another column , i am getting errors
CREATE OR REPLACE PROCEDURE update_blob_lob_copy(
v_id in integer,
v_string in varchar
)
as
auditlob lobdemoaudit.theblob%type;
sourcelob lobdemo.THEBLOB%type;
[code]...
View 3 Replies
View Related
Dec 24, 2010
I use Database 11g .
I have 2 BLOB columns include pdf files.
How can I create one BLOB column include one one pdf file? merge 2 pdf files into one file.
View 4 Replies
View Related