[comp.databases] ORACLE Dynamic SQL Problem

carroll@nprdc.navy.mil (Larry Carroll) (09/17/90)

I have written a sql script generator which 
queries the agency table to get the values for 
agency and agency_id, then builds the sql script
to insert the data.

The problem I am running into is that I need to 
include a quote within a quote.  (there does not
seem to be a way of changing single to double with
the set command.)

example:

	select 'insert into aim.agency(agency_id, agency)' || '
	  ' || 'values (' || agency_id || ',' || agency || ');'
		                                 ^^^^^^
	  from agency;


        since agency returns the agency name, it needs to be 
                                 ^^^^^^
	enclosed with quotes for valid sql syntax.  If you
	use quotes around agency it will return the literal
	word agency.

	How can I get the quote in there? --Any ideas?

	
	Thanks in advance


					  	
	Larry Carroll			carroll@nprdc.navy.mil
					...}ucsd!nprdc!carroll
	Larry Carroll			carroll@nprdc.navy.mil
					...}ucsd!nprdc!carroll

rmanalac@oracle.com (Roderick Manalac) (09/20/90)

carroll@nprdc.navy.mil (Larry Carroll) writes:
	   select 'insert into aim.agency(agency_id, agency)' || '
	     ' || 'values (' || agency_id || ',' || agency || ');'
						    ^^^^^^
	     from agency;


	   since agency returns the agency name, it needs to be 
				    ^^^^^^
	   enclosed with quotes for valid sql syntax.  If you
	   use quotes around agency it will return the literal
	   word agency.

	   How can I get the quote in there? --Any ideas?

Try '''' || angency || '''' where you just had agency before.

Roderick Manalac
Oracle Corp.