set Variable value

[CrystalProgDev]

I need to place the select statement into a variable. I have some thing like this

SELECTSTMT VARCHAR2 (2500)
:= 'TO_CHAR (Date_Col, 'MM/DD/RRRR'),'
+'col_nm1,'
+'" DECODE ('col_nm2,''0', 'Active Part',' DECODE (col_nm2,''1', 'Obsolete Part',DECODE(col_nm2, '2', 'In-Process Release'))) AS col_nm2val,"'
+' col_nm3 ,'
+'col_nm4 ,'
+ 'col_nm5';


It looks like there is some problem with single quotes. I tried with double quotes and single quotes. but no luck. Can any one please provide me the right way to do this?

 

[SantaMufasa]

CrystalProgDev,

The are a couple of ways to do this in Oracle:[ul][li]The Old Way: strings are bounded by single quotes ('), and within single quotes if you need/want a single quote, you must code with two successive single quotes ('') [not a single double quote].[/li]

select 'This is how to place '' characters, and carriage returns -> '||chr(10)||'into a string in Oracle' sample from dual;

SAMPLE
----------------------------------------------------------
This is how to place ' characters, and carriage returns ->
into a string in Oracle

(As you can see from above, you use two successive pipe symbols for string concatenation in Oracle...not "+".

[li]The New(er) Way: use the Oracle "q" operator. Example:

select q'~This is how to place ' characters, and carriage returns ->
into a string in Oracle~' sample from dual;

SAMPLE
----------------------------------------------------------------
This is how to place ' characters, and even carriage returns ->
into a string in Oracle

[/li][/ul]

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalProgDev]

Thank you for your response

 

[Andrzejek]

Santa, could you elaborate a little about the new(er) Oracle "q" operator, please?
I have an Oracle’s reference book from 1997 and there is nothing about it :-( I guess the book is too old

I would guess this is the 'secret':[tt]
select [blue]q'~[/blue]This is how to place ' characters, and carriage returns ->
into a string in Oracle[blue]~'[/blue] sample from dual;[/tt]

Have fun.

---- Andy

 

[SantaMufasa]

Andy,

Oracle SQL's "q operator" allows you to specify character strings without typical concerns for single-quote definitions and <end-of-line> definitions. You may use the "q operator" anywhere that you would use a character operator or character function.

The syntax of the "q operator" is:

...q'<left-delimiter><your string><right-delimiter>'

As a left- and right-delimiter, you may use any single numeric character (i.e., 0 thru 9), any single alphabetic character (i.e., a thru z or A thru Z), or most special characters (i.e., ~!@#$%^*_+=|\:;"/?), provided that the <left-delimiter> is the same character as the <right-delimiter>. You may also use the following "enclosure pairs": (), [], {}, <>. (e.g., if you use "[" as the left-delimiter, you must use "]" as the right-delimiter.)

Because of their special meanings or purposes in Oracle, you may not use these characters as "q operator" delimiters: ' (standard single quote) and & (unless you specify "set define off" in SQL*Plus).

If you have any additional questions about Oracle's "q operator", please follow up here.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.