execute immediate

[fraze]

I'm having problems running execute immediate from a procedure. Basically I'm trying to create a database link from information stored in a table (list_of_tables) - using 9.2.0.5

select * into v_lod from list_of_databases where lod_sid = p_dbsid;

execute immediate 'create database link '||v_lod.lod_sid||'.world connect to '||v_lod.lod_username||
 ' identified by '||v_lod.lod_password||' using '||chr(39)||v_lod.lod_prmy_tns||chr(39)||';';

The error I'm getting is
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "DBACHECK.FRAZE_TEST", line 35
ORA-06512: at line 1

Any help appreciated

[gandolf989]

select * into v_lod from list_of_databases where lod_sid = p_dbsid;

execute immediate 'create database link ' || v_lod.lod_sid      ||
                  '.world connect to '    || v_lod.lod_username ||
                  ' identified by '       || v_lod.lod_password ||
                  ' using '''             || v_lod.lod_prmy_tns ||
                  ''';';

Does this work? You need to use 2 ' when you want to embed them.

[fraze]

Getting the same error unfortunately - I was using chr(39) which is the same as '''

[padders]

Lose the first semi-colon.

[fraze]

Excellent - all works now - Cheers for that