In my SQL script I can define variable like:
DEFINE x=100;
and use it like:
CREATE TABLE A&x AS ...;
the result will be CREATE TABLE A100 AS ... (concatenated string).
But I'd like to get CREATE TABLE A100B AS ... in similar query (B suffix):
CREATE TABLE A&xB AS ...;
But Oracle/SQL understands there is xB variable
How to separate variable (name) inside SQL:
CREATE TABLE A&{x}B AS ...;
This does not work!
(e.g. like ${x} i PHP)
It is just a tiny, little dot.
SQL> define x=100
SQL> create table a&x.b as select * from dual;
old 1: create table a&x.b as select * from dual
new 1: create table a100b as select * from dual
Table created.
SQL> select * from a100b;
D
-
X
SQL>
Here, in case you missed it:
create table a&x.b
^
|
here
What's the point? The SQLPlus concatenation character. By default, it is a dot. It tells SQLPlus where the variable name ends.
If you ask for more help:
SQL> help set
SET
---
Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page
SET system_variable value
where system_variable and value represent one of the following clauses:
APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
<snip>
---------------------
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
---------------------
Here it is!
