Sqldeveloper to define variable of RAW type-CodePudding

I'm using SQL developer (Oracle's SQL developer) and have a table with column of raw type. Example values in this column are:

ABE478C1388D9015875646AD16010B13
D78ABC797BC898AA9710BC090913B15C

I want to use these values inside my SELECT statement as variables, which I define beforehand:

define carHashId = 'ABE478C1388D9015875646AD16010B13'

When I use this variable inside the WHERE clause: WHERE hid = &&carHashId I always get the following error:

*Cause:    An identifier with more than 128 bytes was specified,
           or a password identifier longer than 30 bytes was specified.
*Action:   Specify at most 128 bytes for identifiers,
           and at most 30 bytes for password identifiers.

What can I do about this?

CodePudding user response：

Use HEXTORAW() select HEXTORAW('ABE478C1388D9015875646AD16010B13') from dual ;

Note that utl_raw.cast_to_raw('ABE478C1388D9015875646AD16010B13') will give '4142453437384331333838443930313538373536343641443136303130423133' which is probably not what you want.

CodePudding user response：

This is a substitution variable, so you need to include quotes when using it:

define carHashId = ABE478C1388D9015875646AD16010B13

select '&carHashId' from dual

The quotation marks in the define statement don't do anything in SQL Developer. You could use any of the following and ge the same result:

define carHashId = ABE478C1388D9015875646AD16010B13

define carHashId = 'ABE478C1388D9015875646AD16010B13'

define carHashId = "ABE478C1388D9015875646AD16010B13"