Using bind variables in SQL*Plus

mark two links for quick reference

Declare bind variables in SQL*Plus

Using bind variables in SQL*Plus

You can declare a bind variable in SQL*Plus though, and select into that:

var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);

declare
    l_id varchar2(80) :='test123';
begin
    select test_quote, test_id
    into :l_test_quote, :l_test_id
    from order_link
    where id = l_id;
end;
/

print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.

In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:

var l_id varchar2(80);

exec :l_id := 'test123';

select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.

Learning PL/SQL

Driven by application PL/SQL troubleshooting, as the Pro*C doesn't work .  while succeeds to OPEN CURSOR for a very complex SQL in less than 1 second (manually run it takes few minutes). The processing part is taken care by PRO*C.

From http://docstore.mik.ua/orelly/oracle/langpkt/ch01_09.htm, it says,
"You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set. "



However, I doubt "the processing" includes " EXECUTE . Reason being, from my 10046 tracing comparison of  without FETCH or with FETCH,  there is no EXECUTION PLAN showed for without FETCH.

alter session set events '10046 trace name context forever, level 12' ;

     OPEN emp_cv FOR SELECT first_name, salary FROM employees where employee_id < emp_id;


--WITHOUT fetch

SELECT FIRST_NAME, SALARY
FROM
 EMPLOYEES WHERE EMPLOYEE_ID < :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)
********************************************************************************

alter session set events '10046 trace name context off'

--corresponding 10046 trace file of WITHOUT FETCH processing

=====================
PARSING IN CURSOR #1 len=65 dep=1 uid=33 oct=3 lid=33 tim=1242222958636520 hv=3716011877 ad='4cd98690'
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID < :B1
END OF STMT
PARSE #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222958636514
BINDS #1:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=f6fe60ac  bln=22  avl=03  flg=05
  value=106
EXEC #1:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222958636745
WAIT #3: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222958636793
EXEC #3:c=10000,e=3030,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,tim=1242222958636870
WAIT #3: nam='SQL*Net message from client' ela= 236 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222958637166



-- with  FETCH processing

    OPEN emp_cv FOR SELECT first_name, salary FROM employees where employee_id < emp_id;
     loop
      fetch emp_cv into emp_dataPkg.er;
     exit when emp_cv%notfound;
      dbms_output.put_line(emp_dataPkg.er.name || ' - ' || emp_dataPkg.er.sal);
    end loop;
   CLOSE emp_cv;








********************************************************************************

SELECT FIRST_NAME, SALARY
FROM
 EMPLOYEES WHERE EMPLOYEE_ID < :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7      0.00       0.00          0         13          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         13          0           6

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=13 pr=0 pw=0 time=104 us)
      6   INDEX RANGE SCAN EMP_EMP_ID_PK (cr=7 pr=0 pw=0 time=120 us)(object id 12082)

********************************************************************************

select condition
from
 cdef$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=67 us)

********************************************************************************

alter session set events '10046 trace name context off';

--corresponding 10046 trace file of WITH FETCH processing

=====================
PARSING IN CURSOR #1 len=65 dep=1 uid=33 oct=3 lid=33 tim=1242222802758295 hv=3716011877 ad='4cd98690'
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID < :B1
END OF STMT
PARSE #1:c=0,e=548,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1242222802758286
=====================
PARSING IN CURSOR #4 len=42 dep=2 uid=0 oct=3 lid=0 tim=1242222802759562 hv=844002283 ad='4cf6f080'
select condition from cdef$ where rowid=:1
END OF STMT
PARSE #4:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1242222802759554
BINDS #4:
kkscoacd
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=f6f62148  bln=16  avl=16  flg=05
  value=00006F44.001D.0001
EXEC #4:c=0,e=362,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1242222802760102
FETCH #4:c=0,e=99,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1242222802760255
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=31 op='TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=67 us)'
BINDS #1:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=f6f6254c  bln=22  avl=03  flg=05
  value=106
EXEC #1:c=0,e=3997,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,tim=1242222802762431
FETCH #1:c=0,e=108,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762631
FETCH #1:c=0,e=24,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762862
FETCH #1:c=0,e=18,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802762950
FETCH #1:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763030
FETCH #1:c=0,e=14,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763108
FETCH #1:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1242222802763185
FETCH #1:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,tim=1242222802763257
STAT #1 id=1 cnt=6 pid=0 pos=1 obj=12080 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=13 pr=0 pw=0 time=104 us)'
STAT #1 id=2 cnt=6 pid=1 pos=1 obj=12082 op='INDEX RANGE SCAN EMP_EMP_ID_PK (cr=7 pr=0 pw=0 time=120 us)'
WAIT #3: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222802763502
EXEC #3:c=10000,e=6638,p=0,cr=15,cu=0,mis=0,r=1,dep=0,og=1,tim=1242222802763589
WAIT #3: nam='SQL*Net message from client' ela= 359 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1242222802764056
=====================

Using PRAGMA EXCEPTION_INIT learned today

Here is more http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI

%ROWCOUNT before and after COMMIT

LIQY@XE> declare
2 begin
3 insert into t1 values (100);
4 dbms_output.put_line(SQL%ROWCOUNT);
5 COMMIT;
6 dbms_output.put_line(SQL%ROWCOUNT);
7 end;
8 /
1
0

PL/SQL procedure successfully completed.

In 8i the output is the same , however behavior in 10g & 11g is as above.

I think it is related to COMMIT.

Understanding roles in Oracle stored procedures

By Bob Watkins, Special to ZDNet Asia
24 May 2007

One of the trickiest parts of Oracle's security model is the way that roles (collections of database privileges) interact with stored procedures, functions, and packages. Object privileges in Oracle can be granted directly to the user or indirectly via a role.

Suppose an HR user grants some permissions on the EMPLOYEES table to user ABEL:

GRANT select, insert, update, delete ON employees TO abel;

This directly grants the four privileges mentioned to the user named ABEL. On the other hand, suppose an HR user did this:

GRANT select, insert, update, delete ON employees TO hr_role;

If ABEL has been granted the role HR_ROLE, he now has these privileges indirectly via that role.

Either way, ABEL now has the SELECT privilege on the table HR.EMPLOYEES. If ABEL selects data from the table directly via the SELECT statement, it doesn't matter how he obtained permission. However, if ABEL tries to create stored procedures, functions, or packages that SELECT from this table, it makes a big difference whether he was granted permission directly or via a role.

Oracle requires that permissions to non-owned objects in a stored procedure be granted directly to the user. Roles are temporarily turned off during compilation, and the user has no access to anything granted through them. This is done for performance and security reasons. Roles can be dynamically activated and deactivated via the SET ROLE command, and it would be a large overhead for Oracle to constantly check which roles and permissions are currently active.

The following code shows a short stored procedure that updates the HR copy of employees (the code assumes that a synonym, EMPLOYEES, is used to stand for HR.EMPLOYEES). When Abel tries to compile this under the first case above with direct rights, the compilation succeeds. When he tries to compile it under the second case above with only indirect rights, the compilation fails.

CREATE OR REPLACE PROCEDURE update_emp (
p_employee_id IN NUMBER
,p_salary IN NUMBER
)

AS
v_department_id employees.department_id%TYPE;

BEGIN
SELECT department_id INTO v_department_id
FROM employees
WHERE employee_id = p_employee_id;


UPDATE employees
SET salary = p_salary
WHERE employee_id = p_employee_id;


IF v_department_id = 100 THEN
UPDATE local_employees
SET salary = p_salary
WHERE employee_id = p_employee_id;
END IF;



END;

/

One interesting fact is that granting to PUBLIC is the same as granting to all users directly. PUBLIC is often thought of as a role, but it isn't. It's a collection of users and not a collection of permissions. If the permissions on HR.EMPLOYEES had been granted to PUBLIC, ABEL would have been able to create his stored procedure. While it's not recommended in the case of an EMPLOYEES table, any table that is granted to PUBLIC can be freely used in stored procedures.