How do I incorporate a PL/SQL parameter as a partial table name?
Posted on 2011-10-24
I am using a PL/SQL combination to test the migration of data between one source and two targets. There are a large number of SQL scripts required.
The SQL statements themselves are quite complex, but the difference between executing against the second target is literally a prefix on the table name, like this:
SELECT * FROM DB1_CUSTOMER
SELECT * FROM DB2_CUSTOMER
The SQL statements are stored within "test case" files, with each test case file containing at least one SQL statement.
I'm planning to run the SQL from a vbs pl/sql command line "controller", which looks like this:
I want to include a run time parameter into the SQL, so that the SQL only needs to be written/updated once and the controller can dictate at run time, which target the SQL will execute against.
So the SQL will look like:
SELECT * FROM &1_CUSTOMER
Hence, the PL/SQL command looks like this (to execute against DB1):
SQL> C:\SQL\Library\TestCase1.sql DB1
But, I get a "that table doesn't exist" error. (Probably) because the implementation above does not disseminate the &1 parameter from the rest of the table's actual name.
I've tried using () and | etc, but can't seem to get it right.
I'm considering resorting to including a Find/Replace function in my vbs as a pre-processor for each SQL script, but this work-around will add processing overhead to what is already a performance intensive exercise. This is why I would like to utilise an execution parameter.
Appreciate any help that can be provided.