Home » SQL & PL/SQL » SQL & PL/SQL » Why does Oracle rename the bind variable? (Oracle 11.2.0.3)
Why does Oracle rename the bind variable? [message #667461] Sun, 31 December 2017 04:40 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7620
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>
When you post code, in future please remember to use [code] tags, as I did.
Re: Why does Oracle rename the bind variable? [message #667464 is a reply to message #667461] Sun, 31 December 2017 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because this is the way all programming language, and so PL/SQL, have to send SQL containing bind variables to the SQL engine: using ":<variable name>" syntax.
Each (pre)compiler or interpreter may modify the statement you give it before sending it to the SQL engine, PL/SQL removes the comments and converts the statement in upper case:
SQL> declare
  2    v_max_salary integer;
  3    v_id integer;
  4  begin
  5     v_id := 100000;
  6     select /* Michel */ max(sal) into v_max_salary from scott.emp where empno < v_id;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> set recsep wrap recsepchar '-' lines 80
SQL> select sql_text from v$sql where lower(sql_text) like '%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
--------------------------------------------------------------------------------
declare   v_max_salary integer;   v_id integer; begin    v_id := 100000;    sele
ct /* Michel */ max(sal) into v_max_salary from scott.emp where empno < v_id; en
d;
--------------------------------------------------------------------------------
SELECT MAX(SAL) FROM SCOTT.EMP WHERE EMPNO < :B1
The first row is the PL/SQL statement you sent to PL/SQL engine.
The second one is the SQL statement PL/SQL sent to the SQL engine.

Re: Why does Oracle rename the bind variable? [message #667465 is a reply to message #667464] Sun, 31 December 2017 11:11 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Thank you, Michel, I have learnt something today.

That makes being on call on the last day of the year worth while Smile
Re: Why does Oracle rename the bind variable? [message #667466 is a reply to message #667465] Sun, 31 December 2017 22:11 Go to previous messageGo to next message
SmithXu
Messages: 5
Registered: December 2017
Junior Member
Thanks Michel.
Re: Why does Oracle rename the bind variable? [message #667471 is a reply to message #667464] Mon, 01 January 2018 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
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
SQL*Plus sends the statement as you wrote it.

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
When you use the standard ":<var>" syntax Perl sends the statement as you wrote it.
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
Perl internally replaced "?" by a standard name ":p1". It would be ":p2" for the second "?" and so on. The value is passed, here, via the call to "selectrow_array" function.

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
In this case, JDBC replaces the placeholder variables "?" by ":1" (":2", ":3"...); the values are passed calling setXXX functions.

Any language you use, in the end, the SQL engine received a ":<var>" as this is its only supported syntax.

Re: Why does Oracle rename the bind variable? [message #667511 is a reply to message #667471] Wed, 03 January 2018 04:57 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Michel,

Nice example for understanding the concept.
icon14.gif  Re: Why does Oracle rename the bind variable? [message #667531 is a reply to message #667511] Thu, 04 January 2018 05:56 Go to previous message
_jum
Messages: 530
Registered: February 2008
Senior Member
Thanks Michel, the scripts go directly to my private wiki.

[Updated on: Thu, 04 January 2018 05:58]

Report message to a moderator

Previous Topic: Query contain MODEL cause "column not allowed here" in select statement
Next Topic: Procedure with multiple search filter
Goto Forum:
  


Current Time: Sat Oct 20 22:47:50 CDT 2018