Link to home
Start Free TrialLog in
Avatar of ginag
ginag

asked on

Execute Immediate with a variable needing to be within single quotes

This does not seem to work
EXECUTE IMMEDIATE    'select new_segment from ''
                          || p_object
                        || '' and booksid = '
                        || p_booksid
                        || ' and old_segment = ''
                                    || p_value '''
                   INTO m_newmapping;
Booksid of course is a number and does not require single quotes.  Tried numerous variations and still in error.
ASKER CERTIFIED SOLUTION
Avatar of dbmullen
dbmullen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

sql := q'[select new_segment from '|| p_object || ' and booksid = :x1 and old_segment = :x2]'
USING p_booksid, p_value INTO m_newmapping;
Oracle 10g allows you to define your own quoting mechanishm for string lieterals in both your sql and pl/sql statements. Use the character q' (q followed by a staringt quote) to designate the programmer-defiend delimter for your string literal.

then use EXECUTE IMMEDIATE string.
Avatar of ginag
ginag

ASKER

Was finally able to get it to work after moving p_object into a local variable and moving the using statement after the into statement but your input helped me to figure this out.  We are not in 10g so could not use the last suggestion.  Thanks for your input.