CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Elisabeth
Elisabeth

Reputation: 21216

Run multiple Insert statements with dynamic schema name

I want to run 100 Insert statements of the below Insert`s in a Script. The schema name db1 and db2 must used in the placeholder variable SourceDatabase and TargetDatabase.

DECLARE
SourceDatabase VARCHAR2(50) := 'DB1';
TargetDatabase VARCHAR2(50) := 'DB2';

BEGIN

Insert Into TargetDatabase.TableName (SELECT  * FROM SourceDatabase.TableName);
Insert Into TargetDatabase.TableName (SELECT  * FROM SourceDatabase.TableName);
Insert Into TargetDatabase.TableName (SELECT  * FROM SourceDatabase.TableName);
...
Commit

END;

How can I write that in a dynamic way that Oracle accepts this statement?

Upvotes: 2

Views: 2192

Answers (2)

Justin Cave
Justin Cave

Reputation: 231761

Do you really mean "database" in the Oracle definition of the term? Or do you mean "schema"? The pseudocode you posted appears to be assuming that SourceDatabase and TargetDatabase are schemas in a single database. If you really mean to indicate that they are separate databases, you would needd to use database links to query the remote tables.

Assuming you mean schemas

DECLARE
  l_src_schema varchar2(30) := 'Source';
  l_dest_schema varchar2(30) := 'Destination';
  l_sql_stmt varchar2(4000);
BEGIN
  l_sql_stmt := 
    'INSERT INTO ' || l_dest_schema || '.table_name ' ||
    '  SELECT * FROM ' || l_src_schema || '.table_name';
  dbms_output.put_line( 'Preparing to execute: ' || l_sql_stmt;
  execute immediate l_sql_stmt;
END;

Note that it's generally a good idea to generate the dynamic SQL statement in a separate variable that you can print out because that makes debugging the code much easier. Otherwise, if there is an error, there is no way to retrieve the actual SQL statement that your code tried to execute.

Upvotes: 4

Ben
Ben

Reputation: 52893

You'd have to use execute immediate.

DECLARE
SourceDatabase VARCHAR2(50) := 'DB1';
TargetDatabase VARCHAR2(50) := 'DB2';

BEGIN

execute immediate 'Insert Into '||  TargetDatabase || '.TableName 
                   (SELECT  * 
                      FROM ' || SourceDatabase || '.TableName)';
...
commit;

END;

, or explicitly state the DB name.

begin

   insert into DB2.TableName select * from DB1.TableName;
   commit;

end;

Upvotes: 2

Related Questions