Using Bind Variables in Dynamic SQL : Dynamic SQL « PL SQL Statements « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> create or replace function f_getEmp_tx(i_empNo VARCHAR, i_column_tx VARCHAR2)
  2  return VARCHAR2
  3  is
  4      v_out_tx VARCHAR2(2000);
  5      v_sql_tx VARCHAR2(2000);
  6  begin
  7      v_sql_tx := 'select '||i_column_tx|| ' from employee ' ||'where id=:var01';
  8      execute immediate v_sql_tx into v_out_tx using i_empNo;
  9      return v_out_tx;
 10  exception
 11      when others then return null;
 12  end;
 13  /

Function created.

SQL> select f_getEmp_tx('01','first_name') from dual;

F_GETEMP_TX('01','FIRST_NAME')
----------------------------------
Jason

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.








22.15.Dynamic SQL
22.15.1.Building DDL on the Fly
22.15.2.Building SQL on the Fly
22.15.3.Implicit COMMITS in DDL Statements
22.15.4.Using Quoted Strings with Dynamic SQL
22.15.5.Using Bind Variables in Dynamic SQL
22.15.6.Reusing Bind Variables in Dynamic PL/SQL