DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: sqlplus (concatenation single quotes)

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    sqlplus (concatenation single quotes)

    I am building some DML statements from a query. This query contains
    a where clause whose values I need to encapulate in single quotes.

    The current query:

    select 'delete from OPS\$ORACLE.object_baseline where
    owner = ' || owner || ' and object_name = ' || object_name || '
    and table_name = '|| table_name || ' and column_name = ' || column_name from
    (select owner,object_name,table_name,column_name from object_baseline where owner=upper('${OWNER}')
    minus
    select owner,a.index_name,a.table_name,column_name from
    dba_indexes a,dba_ind_columns b where a.index_name=b.index_name
    and owner=upper('${OWNER}') and a.table_name not like 'SYS%')

    The current output:

    delete from OPS$ORACLE.object_baseline where owner = YYY and object_name = XXX_PK and table_name = XXX and column_name = ID

    I want to encapuslate the values 'YYY', 'XXX_PK', 'XXX' and 'ID' within
    single quotes. I tried tried many different combinations and I am unable
    to get this to work. Can someboody please provide an example.

    Thanks to all who answer
    Last edited by BeefStu; 02-09-2011 at 09:27 AM.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ${OWNER} looks like an Unix environmental.

    Inside sqlplus script you should use a bind variable, like
    Code:
    :owner
    - variable has to be defined and populated before using it. Alternative would be a replacement variable like '&&1'.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    This is part of a .ksh script that is run through cron.

    I am trying to figure out the correct sequence of characters I need to encapulate the values in the where clause.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by BeefStu View Post
    This is part of a .ksh script that is run through cron.

    I am trying to figure out the correct sequence of characters I need to encapulate the values in the where clause.
    Once shell script calls sqlplus you are not in Kansas anymore errrr... Unix anymore, you are in sqlplus so you have to use sql syntax; either pure sql or pl/sql block - that includes variables.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2006
    Posts
    195
    viola!!

    The trick is the end of the phrase has to have 4 single quotes to make it work with sqlplus. I am guessing the fourth quote matches the first quote before he delete and the other 3 quotes end the last value in the where clause.


    select 'delete from OPS$ORACLE.object_baseline
    where owner = ''' || owner || ''' and object_name = ''' || object_name || '''
    and table_name = ''' || table_name || ''' and column_name = ''' || column_name || '''' rest of sql statement .......

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    I hate that multiple single quote stuff - you can concatenate strings and use chr(39) to build your separate single quotation marks. A few extra keystrokes, but very cut and dry as to what is taking place. There are also several notes on My Oracle Support about how to use single quotes, from one, two, three and four in a row, depending on the context.

  7. #7
    Join Date
    Jul 2006
    Posts
    195
    I'm with you and the multiple single quote stuff!! I will look into the chr(39) next time I need to do something like this. This stupid thing took me 1/2 the day to get right.

    Thanks for the advise everybody

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width