you would issue a
SQL> set define off
in order to "escape" the & - & is just a special sqlplus character, not a special sql character.
and then
SQL> alter session set cursor_sharing=force;
before running the script generated by the code below.
the only other thing you have to "worry" about would be single quotes.
you would select:
ops$tkyte%ORA11GR1> create table emp as select replace( ename, 'A', 'A''' ) ename, empno, hiredate from scott.emp;
Table created.
ops$tkyte%ORA11GR1> drop table t;
Table dropped.
ops$tkyte%ORA11GR1> create table t as select replace( ename, 'A', 'A''' ) ename, empno, hiredate from scott.emp where 1=0;
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> begin
2 for x in (select ename, empno, hiredate from emp )
3 loop
4 dbms_output.put_line( 'insert into t ( ename, empno, hiredate ) values (' );
5 dbms_output.put_line( '''' || replace( x.ename, '''', '''''' ) || ''', ' );
6 dbms_output.put_line( x.empno || ', ' );
7 dbms_output.put_line( 'to_date( ''' || to_char(x.hiredate,'yyyymmddhh24miss') || ''', ''yyyymmddhh24miss'' ) );' );
8 end loop;
9 end;
10 /
insert into t ( ename, empno, hiredate ) values (
'SMITH',
7369,
to_date( '19801217000000', 'yyyymmddhh24miss' ) );
insert into t ( ename, empno, hiredate ) values (
'A''LLEN',
7499,
to_date( '19810220000000', 'yyyymmddhh24miss' ) );
insert into t ( ename, empno, hiredate ) values (
'WA''RD',
7521,
to_date( '19810222000000', 'yyyymmddhh24miss' ) );
insert into t ( ename, empno, hiredate ) values (
'JONES',
7566,
to_date( '19810402000000', 'yyyymmddhh24miss' ) );
....