CODEWITHSUNDEEP
sqloracle-sqldeveloper
mTv
mTv

Reputation: 1366

Passing and using a parameter in a script in SQL Developer

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?

Upvotes: 0

Views: 5862

Answers (1)

dreamwork
dreamwork

Reputation: 96

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.

Upvotes: 1

Related Questions