INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

set Variable value

set Variable value

(OP)
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?

RE: set Variable value

CrystalProgDev,

The are a couple of ways to do this in Oracle:
  • 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].


  • CODE

    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 "+".

  • The New(er) Way: use the Oracle "q" operator. Example:

    CODE

    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 
Let us know if you have additional questions.

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

RE: set Variable value

(OP)
Thank you for your response

RE: set Variable value

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 sad I guess the book is too old

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

Have fun.

---- Andy

RE: set Variable value

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:

CODE

...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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close