All geek questions in one place

What does & 1 .. mean in an Oracle database

I saw this line in oracle script

SELECT COUNT(*) INTO version1 FROM &1..xxdt WHERE version = 3310;

I do not understand part &1.. I think xxdt is the name of the table, so what is this &1.. thing in front of it?

+4
source share
3 answers

&1 asks for a user-entered value. Notice how the entered mytable value mytable replaced with &1 below:

 SQL> SELECT COUNT(*) FROM &1 WHERE col1 = 12; Enter value for 1: mytable old 1: SELECT COUNT(*) FROM &1 WHERE col1 = 12 new 1: SELECT COUNT(*) FROM mytable WHERE col1 = 12 COUNT(*) ---------- 0

The dot ( . ) Adds each non-spatial character that follows the dot to the entered value. Note how the value of table after the point is added to the input my :

 SQL> SELECT COUNT(*) FROM &1.table WHERE COL1 = 12; Enter value for 1: my old 1: SELECT COUNT(*) FROM &1.table WHERE COL1 = 12 new 1: SELECT COUNT(*) FROM mytable WHERE COL1 = 12 COUNT(*) ---------- 0

Two points in &1..xxdt are not a special operator. The first dot means adding; the second point is literal. It looks like &1 in your example is used to query for the schema / owner name. The note below, as I entered ed and &1..mytable , is converted to ed.mytable :

 SQL> SELECT COUNT(*) FROM &1..mytable WHERE COL1 = 12; Enter value for 1: ed old 1: SELECT COUNT(*) FROM &1..mytable WHERE COL1 = 12 new 1: SELECT COUNT(*) FROM ed.mytable WHERE COL1 = 12 COUNT(*) ---------- 0

Addendum : David Aldridge's great suggestion is to include a quick explanation of SET DEFINE , which goes hand in hand with variable substitution. Here goes ...

The substitutions above are performed by SQLPlus, and its behavior can be controlled using SET DEFINE :

  • SET DEFINE ON allows you to replace and use a specific substitution character. This is usually the default SQLPlus value, and this was the case when I ran the queries above.
  • SET DEFINE <char> sets the replacement character. Ampersand ( & ) is the default. SQLPlus will only accept alphabetic characters, not spaces for the replacement character. Please note that I have never had to change this value for more than a decade of using Oracle.
  • SET DEFINE OFF will stop the substitution. Use this if you need to have the actual ampersand in your query or proc, because SQLPlus will treat the ampersand as a replacement character no matter where you put it, including in a string.
+8
source

I believe that ampersand is used for substitution variables. See http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php

Substitution Variables

Substitution variables are a feature of the SQL * Plus tool. They have nothing to do with how SQL is processed by the database server. When a substitution variable is used in an expression, SQL * Plus queries the input value and overwrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server does not know anything about the replacement variable. The following example illustrates this by repeating the previous test, this time using substitution variables.

0
source

If SET CONCAT is a period (.), And you want to add a period immediately after the replacement variable, use two periods together. For instance:

 define mycity = Melbourne spool &mycity..log

matches with:

 spool Melbourne.log

https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#9_7

0
source

Source: https://habr.com/ru/post/1485676/

More articles:


All Articles