Half the time, I want to know some answer that can be determined with a query. The other half of the time, I want to start from that answer and use it as an intermediate step towards some larger solution. (That's not even considering the third half... :-)
So, I have a bunch of .sql files that do SELECT statements. Sometimes, I end up editing those .sql files into new ones that do CREATE TABLE AS SELECT... using the same query. I don't like the proliferation of .sql files that are slight variations of each other. That's the aspect of SQL coding that I would most like to see be object-oriented, the many SQL scripts that differ from each other only in terms of connection string, table name, or other minor detail.
Now, I know the WITH clause is there for pulling out a query to reuse its results as part of a bigger query. But sometimes the natural-feeling technique is to do CREATE TABLE AS SELECT colname... and then several other queries with clauses WHERE colname IN SELECT * FROM newly_created_table.
I had a sense there must be some shortcut to turning the original query into a CTAS construct. And there is! Remember how SQL*Plus will happily substitute the contents of a script anywhere you start a line with an '@' sign? Well, turns out you can use this technique to substitute a script as the last half of a SQL statement:
- Create a .sql file that has a single SELECT statement.
- Issue a CTAS statement like so:
create table t1 as
@script_with_select_statement
The trick is to make sure the SQL script has no blank lines, as they will cause SQL*Plus to stop processing the command prematurely. '--' comments are OK, so you can comment and format the script using -- lines where you would normally use blank lines.
CTAS is a bit of a blunt instrument, especially if the same situation comes up again and again. I may adapt this technique to use with CREATE GLOBAL TEMPORARY TABLE t1... and INSERT INTO t1 SELECT... constructs.
Subscribe to:
Post Comments (Atom)
3 comments:
Hi!
Here's another trick that does the job: use edit commands on the sql*plus buffer. I just wrote a script "ctas.sql". It has one input parameter, the new table name. When you run it, it prepends "create table [new table name] as" to your last sql statement and executes this new statement. Here's ctas.sql:
0 create table &1 as
r
You can use it like this:
SQL> select *
2 from dual;
DUM
---
X
1 Zeile wurde ausgewählt.
SQL> @ctas my_copy_of_dual
1 create table &1 as
2 select *
3* from dual
alt 1: create table &1 as
neu 1: create table my_copy_of_dual as
Tabelle wurde erstellt.
SQL> select * from my_copy_of_dual;
DUM
---
X
1 Zeile wurde ausgewählt.
SQL>
For connection details, table names and other (small) things don't forget the power of SQL*Plus substitution variables (not to be confused with bind variables).
select * from &&table_name
This syntax is also supported by TOAD, which pops up a dialogue to get the values of all variables in a script (are you listening, SQL Developer team?).
You can combine the substitution variable with @ or @@:
@@&&script
@@ is used to get the script from the same directory as the calling script, by the way - saves you having to hard code directory names, and there's no need to pollute your sql directory with spool files.
Nice tip. I was thinking there was a better way to generate explain plans from a saved .sql file. I'll use this same technique for that. It never occurred to me to try this.
> explain plan for
2 @something.sql
Explained.
Thanks for the post.
Post a Comment