Home » Developer & Programmer » Forms » Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" (Oracle Database)
Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638245] Mon, 08 June 2015 05:40 Go to next message
minkadam
Messages: 4
Registered: June 2015
Junior Member
I need Distinct Data from Table. i am using below query to find Distinct Records from Database but

it will gives below error.

"select distinct CAST(PPFT.ImageBLOB AS VARBINARY(MAX)) FROM Images PPFT
Failed.
S1000: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis"

My Query :

select distinct CAST(PPFT.ImageBLOB AS VARBINARY(MAX)),* FROM Images PPFT
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638246 is a reply to message #638245] Mon, 08 June 2015 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Varbinary doesn't appear to be an oracle datatype
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638248 is a reply to message #638246] Mon, 08 June 2015 05:51 Go to previous messageGo to next message
minkadam
Messages: 4
Registered: June 2015
Junior Member
Then what is way to apply DISTINCT clause for BLOB type of Data.

How to Resolve my Issue.
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638252 is a reply to message #638248] Mon, 08 June 2015 06:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If such a query works OK in SQL*Plus, create a view which uses it and then - in a form - select from that view.
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638253 is a reply to message #638252] Mon, 08 June 2015 06:14 Go to previous messageGo to next message
minkadam
Messages: 4
Registered: June 2015
Junior Member
No,Its not working in SQL*Plus.Is there any Other way to resolve the same issue

[Updated on: Mon, 08 June 2015 06:35]

Report message to a moderator

Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638254 is a reply to message #638253] Mon, 08 June 2015 06:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know what "SQLRunner Software" is. Is it, in any way, related to Oracle Forms (as you put the question into that forum)?

I've never heard of "VARBINARY" datatype, so I Googled for it. It appears that it is not listed among Oracle 12c data types (the most recent one), but I found it for MySQL and Times ten databases. I might be wrong, but it seems that you aren't using Oracle, are you?
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638255 is a reply to message #638254] Mon, 08 June 2015 06:49 Go to previous messageGo to next message
minkadam
Messages: 4
Registered: June 2015
Junior Member
Yes. I am using Oracle only. I might be wrong in using VARBINARY data type as this was solution I got from somebody for SQL Server. Now, if VARBINARY is not available then how can I get DISTINCT values for BLOB column? SQL Server suggest to CAST it to VARBINARY. How to do it in Oracle?
Or is not not possible to get distinct BLOB values in Oracle?
Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638256 is a reply to message #638255] Mon, 08 June 2015 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use RAW(2000) instead of VARBINARY(MAX) but note that if you can do this (same thing in SQL Server) this means your column data type is not correct and should be RAW(2000) and not BLOB.

Re: Error while casting to VARBINARY, Casting to VARBINARY Causes Error - "missing right parenthesis" [message #638258 is a reply to message #638256] Mon, 08 June 2015 07:23 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DISTINCT, on a BLOB column, won't work (as you already noticed):
SQL> DESC a1_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(2)
 BLOB_CONTENT                                       BLOB
 BLOB_HASH                                          RAW(40)

SQL> SELECT DISTINCT blob_content FROM a1_test;
SELECT DISTINCT blob_content FROM a1_test
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB


If it suits your needs, calculate its hash value and select distinct hashes instead. For example:
SQL> DECLARE
  2     l_hash   RAW (40);
  3  BEGIN
  4     FOR cur_r IN (SELECT id, blob_content FROM a1_test)
  5     LOOP
  6        l_hash := DBMS_CRYPTO.hash (cur_r.blob_content, DBMS_CRYPTO.hash_sh1);
  7
  8        UPDATE a1_test
  9           SET blob_hash = l_hash
 10         WHERE id = cur_r.id;
 11     END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> COL blob_hash FORMAT a40;
SQL>
SQL> SELECT DISTINCT blob_hash FROM a1_test;

BLOB_HASH
----------------------------------------
14D0966C40D7A3FEF653361EC2D6776DB5A4673F
2DBF1AA0F0C68215156AFEB35E5FA42516285434
F4C8AD2C6F67CA585AD17ED70EE8167BCD6C4B68
B144A1FCADF95C98DDA27E537A4D0EF4C8453EC9
27DD799F685856627C0BFCB2B131E66B55D691A4
B77E6C247AD8CBDFCCC59FC66AC148235166ABCF
C0F44FE2DCC3B71238EF44F0E349A5765EB7C8E5
6DA0114E7BDCB0EA0191132DCBB8E27F3D0C25A6

8 rows selected.

SQL>
Previous Topic: RUN_PRODUCT without connecting to a database
Next Topic: please need help temporarly
Goto Forum:
  


Current Time: Thu Mar 28 12:25:58 CDT 2024