| Why does Oracle rename the bind variable? [message #667461] |
Sun, 31 December 2017 04:40  |
 |
SmithXu
Messages: 5
Registered: December 2017
|
Junior Member |
|
|
1. I executed following PL/SQL
declare
v_max_salary integer;
v_id integer;
begin
v_id := 100000;
select max(emp_salary) into v_max_salary from T1 where emp_id < v_id;
end;
2. Then I checked the SGA with following SQL:
SELECT SQL_TEXT, SQL_FULLTEXT
FROM SYS.V_$SQLAREA
where PARSING_SCHEMA_NAME ='HR' and SQL_TEXT like '%T1%';
3. I found Oracle renamed the bind variable v_id to B1
SELECT MAX(EMP_SALARY) FROM T1 WHERE EMP_ID < :B1
It is interesting.
Anyone knows that why Oracle rename the bind variable and in which cases Oracle will do the conversion?
|
|
|
|
| Re: Why does Oracle rename the bind variable? [message #667462 is a reply to message #667461] |
Sun, 31 December 2017 05:49  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I do not get that effect:orclx>
orclx> declare
2 v_max_salary integer;
3 v_id integer;
4 begin
5 v_id := 100000;
6 select /* jw */ max(sal) into v_max_salary from T1 where empno < v_id;
7 end;
8 /
PL/SQL procedure successfully completed.
orclx> select sql_text from v$sql where sql_text like '%jw%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
select sql_text from v$sql where sql_text like '%jw%'
declare v_max_salary integer; v_id integer; begin v_id := 100000; select /* jw */ max(sal) into v_max_salary f
rom T1 where empno < v_id; end;
orclx> |
|
|
|
|
|
|
|
|
|
| Re: Why does Oracle rename the bind variable? [message #667471 is a reply to message #667464] |
Mon, 01 January 2018 05:05 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
To emphasize what I said, here are other examples.
With SQL*Plus, once you have declared the variable you can use it as bind variable in a statement. Note that the bind variable reference in the statement must have the same name than the variable you declared:
SQL> var id number
SQL> alter system flush shared_pool;
System altered.
SQL> select /* Michel */max(sal) from scott.emp where empno < :id;
MAX(SAL)
----------
1 row selected.
SQL> set recsep wrap recsepchar '-' lines 100
SQL> select sql_text from v$sql where lower(sql_text) like '%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
select /* Michel */max(sal) from scott.emp where empno < :id
With Perl, you have 2 ways to write a statement with bind variables, using ":<var>" syntax or using "?" syntax:
SQL> alter system flush shared_pool;
System altered.
SQL> host type t1.pl
use strict;
use DBI;
use DBD::Oracle;
my $dbh = DBI->connect ('dbi:Oracle:','michel','michel');
my $val = 0;
my $sql = 'select /* Michel */ max(sal) from scott.emp where empno < :perl_id';
my $sth = $dbh->prepare($sql);
$sth->bind_param(':perl_id', $val);
$sth->execute();
$sth->finish();
SQL> host perl t1.pl
SQL> select sql_text from v$sql where lower(sql_text) like '%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
select /* Michel */ max(sal) from scott.emp where empno < :perl_id
select /* Michel */ max(sal) from scott.emp where empno < :perl_id
Note that the bind variable name in the statement and the variable name in Perl script don't need to be the same ones, the relationship between the two is made by the call of "bind_param" function.
Using "?" syntax (known as placeholder syntax as each "?" marks the place where a variable will be used):
SQL> alter system flush shared_pool;
System altered.
SQL> host type t2.pl
use strict;
use DBI;
use DBD::Oracle;
my $dbh = DBI->connect ('dbi:Oracle:','michel','michel');
my $val = 0;
my $sql = 'select /* Michel */ max(sal) from scott.emp where empno < ?';
my $res = $dbh->selectrow_array($sql, undef, $val);
SQL> host perl t2.pl
SQL> select sql_text from v$sql where lower(sql_text) like '%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
select /* Michel */ max(sal) from scott.emp where empno < :p1
select /* Michel */ max(sal) from scott.emp where empno < :p1
You have the same syntax with Java and JDBC:
SQL> alter system flush shared_pool;
System altered.
SQL> host type t1.java
import java.sql.*;
class t1
{
public static void main (String args[]) throws SQLException
{
PreparedStatement stmt;
String sql;
int val;
ResultSet rset;
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mikb2","michel","michel");
sql = "select /* Michel */ max(sal) from scott.emp where empno < ?";
val = 0;
stmt = conn.prepareStatement(sql);
stmt.setInt(1, val);
rset = stmt.executeQuery();
stmt.close();
} catch (Exception e) { e.printStackTrace(); }
}
}
SQL> host java t1
SQL> select sql_text from v$sql where lower(sql_text) like '%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
select /* Michel */ max(sal) from scott.emp where empno < :1
Any language you use, in the end, the SQL engine received a ":<var>" as this is its only supported syntax.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
