CODEWITHSUNDEEP
sqloracle-database
CorayThan
CorayThan

Reputation: 17825

How to use sql def variable

I'm trying to declare a variable in a simple sql script to create some tables in a schema, but it's not working.

Here's a snippet of my sql script:

DEF SCHEMA_NAME = MY_SCHEMA;

CREATE TABLE &SCHEMA_NAME.BOOK
(
    BOOK_ID            INTEGER
);

That's creating a table with the table name MY_SCHEMABOOK instead of a table named BOOK in the schema MY_SCHEMA.

The script output in Oracle Sql Developer says:

old:CREATE TABLE &SCHEMA_NAME.BOOK
(
    BOOK_ID            INTEGER
)
new:CREATE TABLE MY_SCHEMABOOK
(
    BOOK_ID            INTEGER
)
table MY_SCHEMABOOK created.

If that helps. Also, it seems that changing it to &SCHEMA_NAME..BOOK does make it work like I want it to, but having to put two periods doesn't make sense to me. Can anyone tell me what I'm doing wrong?

Upvotes: 1

Views: 160

Answers (1)

Kirill Leontev
Kirill Leontev

Reputation: 10931

SET CON[CAT] {. | c | ON | OFF}

Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.

SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2722

SQL Developer works the same way - your first period is interpreted as a substitution variable terminator. So, it is perfectly valid to use two consequent periods in this case.

Upvotes: 1

Related Questions