In SQL Developer, I want to call a script like this:
@"path\to\script.sql" develop
and use the value develop in my script as a table prefix like this:
SELECT * FROM <parameter>_table; which should then evaluate to SELECT * FROM develop_table
Is something like this possible in SQL Developer?
See the article below. It will answer your question.
https://blogs.oracle.com/opal/sqlplus-101-substitution-variables#2_7
I'll make an example for you, based off that blog article.
Creating a dummy table:
CREATE TABLE t(x NUMBER, t VARCHAR2(255));
INSERT INTO t(x, t) VALUES (1, 'Example');
COMMIT;
The script below has been saved in C:\Users\William. The filename is example.sql.
SELECT *
FROM &1
;
Now, from SQL Developer, I execute:
@C:\Users\William\example.sql t
Note that when you pass a parameter to a script like this, you are passing the text value that is stored in implicitly-named substitution variables. The substitution variable is named according to its order (e.g., &1 then &2 then &3 etc.).
This is the script output:
old:SELECT *
FROM &1
new:SELECT *
FROM t
X T
-- ----------
1 Example
You should probably take some time to consider other solutions to the problem you are solving. You may not need to execute a script via SQL developer. Perhaps you'd be better off creating a procedure that generates dynamic SQL, based off parameters you feed to the procedure. You would then use EXECUTE IMMEDIATE on the dynamic SQL statement inside the procedure.
I personally might find that technique more useful when performing actions like ETL (as opposed to queries). Then again, I'm no expert, and there're probably even better solutions out there.
