[20171005]parsing.txt
--//如果一条sql语句输入有错,会在sharepool存在记录.参考链接:
--//jonathanlewis.wordpress.com/2017/10/03/parsing/
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.简单测试使用sys用户执行:
SYS@test> select user frrom dual;
select user frrom dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SYS@test> @ sharepool/shp4 bshhvh0ypcz6x 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = 'bshhvh0ypcz6x' or kglhdpar='bshhvh0ypcz6x' or kglhdadr='bshhvh0ypcz6x' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- -------------------- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF1298E2D8 000007FF12A711C8 select user frrom du 1 00 00 0 0 3111 3111 3111 1029078237 bshhvh0ypcz6x 0
父游标句柄地址 000007FF12A711C8 000007FF12A711C8 select user frrom du 1 000007FF129B5820 00 4072 0 0 4072 4072 1029078237 bshhvh0ypcz6x 65535
--//产生了父子游标,不过子游标的KGLOBHD0,KGLOBHD6 等于0.
--//参考链接计算sql_id http://blog.itpub.net/267265/viewspace-1063541/
SYS@test> select dbms_sql_translator.sql_id ('select user frrom dual') sql_id, dbms_sql_translator.sql_hash ('select user frrom dual') hash_value from dual;
SQL_ID HASH_VALUE
------------- -----------
bshhvh0ypcz6x 1029078237
--//不过在视图v$sql无法查询到.
SYS@test> select * from v$sql where sql_id='bshhvh0ypcz6x';
no rows selected
--//shp4.sql脚本:
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
--//继续看后面的讨论:
DECLARE
mcur NUMBER;
mstat NUMBER;
v_name VARCHAR2 (14);
BEGIN
mcur := DBMS_SQL.open_cursor;
DBMS_SQL.parse ( mcur ,'SSlect dname from dept where deptno = :deptno' ,DBMS_SQL.native);
-- DBMS_SQL.bind_variable (mcur, ':deptno', 20);
-- DBMS_SQL.define_column ( mcur ,1 ,v_name ,14);
-- mstat := DBMS_SQL.execute (mcur);
-- mstat := DBMS_SQL.fetch_rows (mcur);
-- DBMS_SQL.COLUMN_VALUE (mcur, 1, v_name);
-- DBMS_OUTPUT.put_line ('查询结果:' || v_name);
-- DBMS_SQL.close_cursor (mcur);
END;
/
DECLARE
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 7
select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like 'SSlect%' and kglnaobj not like '%kgl%';
SYS@test> select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like 'SSlect%' and kglnaobj not like '%kgl%';
KGLHDPAR KGLHDADR KGLOBT03 KGLNAOBJ
---------------- ---------------- ------------- ------------------------------
000007FF12A26F08 000007FF122F83E0 a50u3spg2590v SSlect dname from de
000007FF12A26F08 000007FF12A26F08 a50u3spg2590v SSlect dname from de
SYS@test> @ sharepool/shp4 a50u3spg2590v
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = 'a50u3spg2590v' or kglhdpar='a50u3spg2590v' or kglhdadr='a50u3spg2590v' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- -------------------- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF122F83E0 000007FF12A26F08 SSlect dname from de 1 00 00 0 0 3134 3134 3134 1579328539 a50u3spg2590v 0
父游标句柄地址 000007FF12A26F08 000007FF12A26F08 SSlect dname from de 1 000007FF12B3A470 00 4072 0 0 4072 4072 1579328539 a50u3spg2590v 65535
--//可以看看我以前的测试:http://blog.itpub.net/267265/viewspace-2141526/ => [20170703]SQL语句分析执行过程.txt
--//但是很奇怪的是如果修改DML语句:
DECLARE
mcur NUMBER;
mstat NUMBER;
v_name VARCHAR2 (14);
BEGIN
mcur := DBMS_SQL.open_cursor;
DBMS_SQL.parse ( mcur ,'INSERT INTO YY VALUESSS (1)' ,DBMS_SQL.native);
END;
/
DECLARE
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 7
--//注VALUESSS拼写错误故意的.
SELECT kglhdpar, kglhdadr, kglobt03, kglnaobj
FROM x$kglob
WHERE kglnaobj LIKE 'INSERT INTO YY VALUES%'
AND kglnaobj NOT LIKE '%kgl%'
AND kglnaobj NOT LIKE '%DECLARE%';
no rows selected
--//即使我建立表YY结果也一样,有点奇怪.....oracle有时候也太奇怪了...
--//改写如下:
DECLARE
mcur NUMBER;
mstat NUMBER;
v_name VARCHAR2 (14);
BEGIN
mcur := DBMS_SQL.open_cursor;
DBMS_SQL.parse ( mcur ,'INSERT INTO YY VALUES(1)' ,DBMS_SQL.native);
-- mstat := DBMS_SQL.execute (mcur);
-- DBMS_SQL.close_cursor (mcur);
END;
/
SELECT kglhdpar, kglhdadr, kglobt03, kglnaobj
FROM x$kglob
WHERE kglnaobj LIKE 'INSERT INTO YY VALUES%'
AND kglnaobj NOT LIKE '%kgl%'
AND kglnaobj NOT LIKE '%DECLARE%';
KGLHDPAR KGLHDADR KGLOBT03 KGLNAOBJ
---------------- ---------------- ------------- ------------------------------
000007FF13111E40 000007FF13110990 d7xszfs543j5q INSERT INTO YY VALUES(1)
000007FF13111E40 000007FF13111E40 d7xszfs543j5q INSERT INTO YY VALUES(1)
SYS@test> @ sharepool/shp4 d7xszfs543j5q 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = 'd7xszfs543j5q' or kglhdpar='d7xszfs543j5q' or kglhdadr='d7xszfs543j5q' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ------------------------ -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF13110990 000007FF13111E40 INSERT INTO YY VALUES(1) 0 000007FF131108D8 000007FF131114C8 4032 8088 3113 15233 15233 172082358 d7xszfs543j5q 0
父游标句柄地址 000007FF13111E40 000007FF13111E40 INSERT INTO YY VALUES(1) 0 000007FF13111D88 00 4072 0 0 4072 4072 172082358 d7xszfs543j5q 65535