Skip to content

Instantly share code, notes, and snippets.

@velll

velll/bind_sens_plans.sql

Last active August 29, 2015 13:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save velll/8915034 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save velll/8915034 to your computer and use it in GitHub Desktop.
Download ZIP
Bind_sensitive_plans
Raw
bind_sens_plans.sql
DECLARE
num_N_WRITEOFF_SUM NUMBER;
BEGIN
EXECUTE IMMEDIATE '
SELECT NVL(SUM(GM.N_SUM), 0)
FROM SD_GOOD_MOVES GM,
(SELECT GMT.N_DOC_ID,
D.N_DOC_ID N_D_DOC_ID,
DS.N_DOC_ID N_DS_DOC_ID
FROM SD_DOCUMENTS D,
SI_DOC_SUBJECTS DS,
SD_GOOD_MOVES_T GMT
WHERE D.N_DOC_TYPE_ID = '||SS_CONSTANTS_PKG_S.DOC_TYPE_Invoice||'
AND D.N_DOC_STATE_ID IN ('||SS_CONSTANTS_PKG_S.DOC_STATE_Actual||',
'||SS_CONSTANTS_PKG_S.DOC_STATE_Prepared||',
'||SS_CONSTANTS_PKG_S.DOC_STATE_Executed||')
AND D.D_END >= :dt_D_BEGIN_DATE
AND D.N_DOC_ID = DS.N_DOC_ID
AND DS.N_DOC_ROLE_ID = '||SS_CONSTANTS_PKG_S.SUBJ_ROLE_Receiver||'
AND DS.N_ACCOUNT_ID = :num_N_ACCOUNT_ID
AND DS.C_ACTIVE = ''Y''
AND GMT.N_DOC_ID = D.N_DOC_ID
AND GMT.N_DOC_ID = DS.N_DOC_ID
AND (GMT.D_BEGIN BETWEEN :dt_D_BEGIN AND :dt_D_END OR
GMT.D_END BETWEEN :dt_D_BEGIN AND :dt_D_END OR
:dt_D_BEGIN BETWEEN GMT.D_BEGIN AND GMT.D_END)
GROUP BY GMT.N_DOC_ID,
D.N_DOC_ID,
DS.N_DOC_ID) DOC
WHERE GM.N_DOC_ID = DOC.N_DOC_ID
AND GM.N_DOC_ID = DOC.N_D_DOC_ID
AND GM.N_DOC_ID = DOC.N_DS_DOC_ID
AND GM.N_MOVE_TYPE_ID = '||SS_CONSTANTS_PKG_S.GM_TYPE_WriteOff||'
AND GM.D_OPER > :dt_D_BEGIN
AND GM.D_OPER <= :dt_D_END
AND GM.C_ACTIVE = ''Y'''
INTO num_N_WRITEOFF_SUM
USING TO_DATE('11.01.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS'),
4572661464591,
TO_DATE('11.01.2014 12:58:37', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('10.02.2014 15:29:57', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('11.01.2014 12:58:37', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('10.02.2014 15:29:57', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('11.01.2014 12:58:37', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('11.01.2014 12:58:37', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('10.02.2014 15:29:57', 'DD.MM.YYYY HH24:MI:SS');
END;
1 :DT_D_BEGIN_DATE 01/11/2014 00:00:00 1 DATE YES 10.02.14 15:29:57
1 :NUM_N_ACCOUNT_ID 4572661464591 2 NUMBER YES 10.02.14 15:29:57
1 :DT_D_BEGIN 01/11/2014 12:58:37 3 DATE YES 10.02.14 15:29:57
1 :DT_D_END 02/10/2014 15:29:57 4 DATE YES 10.02.14 15:29:57
1 :DT_D_BEGIN 01/11/2014 12:58:37 5 DATE YES 10.02.14 15:29:57
1 :DT_D_END 02/10/2014 15:29:57 6 DATE YES 10.02.14 15:29:57
1 :DT_D_BEGIN 01/11/2014 12:58:37 7 DATE YES 10.02.14 15:29:57
1 :DT_D_BEGIN 01/11/2014 12:58:37 8 DATE YES 10.02.14 15:29:57
1 :DT_D_END 02/10/2014 15:29:57 9 DATE YES 10.02.14 15:29:57
DECLARE
num_N_WRITEOFF_SUM NUMBER;
BEGIN
EXECUTE IMMEDIATE '
SELECT NVL(SUM(GM.N_SUM), 0)
FROM SD_GOOD_MOVES GM,
(SELECT GMT.N_DOC_ID,
D.N_DOC_ID N_D_DOC_ID,
DS.N_DOC_ID N_DS_DOC_ID
FROM SD_DOCUMENTS D,
SI_DOC_SUBJECTS DS,
SD_GOOD_MOVES_T GMT
WHERE D.N_DOC_TYPE_ID = '||SS_CONSTANTS_PKG_S.DOC_TYPE_Invoice||'
AND D.N_DOC_STATE_ID IN ('||SS_CONSTANTS_PKG_S.DOC_STATE_Actual||',
'||SS_CONSTANTS_PKG_S.DOC_STATE_Prepared||',
'||SS_CONSTANTS_PKG_S.DOC_STATE_Executed||')
AND D.D_END >= :dt_D_BEGIN_DATE
AND D.N_DOC_ID = DS.N_DOC_ID
AND DS.N_DOC_ROLE_ID = '||SS_CONSTANTS_PKG_S.SUBJ_ROLE_Receiver||'
AND DS.N_ACCOUNT_ID = :num_N_ACCOUNT_ID
AND DS.C_ACTIVE = ''Y''
AND GMT.N_DOC_ID = D.N_DOC_ID
AND GMT.N_DOC_ID = DS.N_DOC_ID
AND (GMT.D_BEGIN BETWEEN :dt_D_BEGIN AND :dt_D_END OR
GMT.D_END BETWEEN :dt_D_BEGIN AND :dt_D_END OR
:dt_D_BEGIN BETWEEN GMT.D_BEGIN AND GMT.D_END)
GROUP BY GMT.N_DOC_ID,
D.N_DOC_ID,
DS.N_DOC_ID) DOC
WHERE GM.N_DOC_ID = DOC.N_DOC_ID
AND GM.N_DOC_ID = DOC.N_D_DOC_ID
AND GM.N_DOC_ID = DOC.N_DS_DOC_ID
AND GM.N_MOVE_TYPE_ID = '||SS_CONSTANTS_PKG_S.GM_TYPE_WriteOff||'
AND GM.D_OPER > :dt_D_BEGIN
AND GM.D_OPER <= :dt_D_END
AND GM.C_ACTIVE = ''Y'''
INTO num_N_WRITEOFF_SUM
USING TO_DATE('30.09.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS'),
4577975788501,
TO_DATE('30.09.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.10.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('30.09.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.10.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('30.09.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS'),
TO_DATE('30.09.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.10.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS');
END;
/
0 :DT_D_BEGIN_DATE 09/30/2013 00:00:00 1 DATE YES 10.02.14 15:21:36
0 :NUM_N_ACCOUNT_ID 4577975788501 2 NUMBER YES 10.02.14 15:21:36
0 :DT_D_BEGIN 09/30/2013 23:59:59 3 DATE YES 10.02.14 15:21:36
0 :DT_D_END 10/01/2013 00:00:00 4 DATE YES 10.02.14 15:21:36
0 :DT_D_BEGIN 09/30/2013 23:59:59 5 DATE YES 10.02.14 15:21:36
0 :DT_D_END 10/01/2013 00:00:00 6 DATE YES 10.02.14 15:21:36
0 :DT_D_BEGIN 09/30/2013 23:59:59 7 DATE YES 10.02.14 15:21:36
0 :DT_D_BEGIN 09/30/2013 23:59:59 8 DATE YES 10.02.14 15:21:36
0 :DT_D_END 10/01/2013 00:00:00 9 DATE YES 10.02.14 15:21:36
SELECT sql_id,
fetches,
executions,
first_load_time,
plan_hash_value,
child_number,
is_bind_sensitive,
is_bind_aware,
is_shareable,
last_active_time,
sql_fulltext
FROM v$sql
WHERE sql_id = 'gha6946j24qu6'
select * from table(dbms_xplan.display_cursor('gha6946j24qu6', 0))
SELECT * FROM TABLE(dbms_xplan.display_cursor('gha6946j24qu6', 1))
SELECT * FROM TABLE(dbms_xplan.display_cursor('gha6946j24qu6', 2))
SELECT * FROM TABLE(dbms_xplan.display_cursor('gha6946j24qu6', 3))
SELECT CHILD_NUMBER, NAME, VALUE_STRING, POSITION, DATATYPE_STRING, WAS_CAPTURED, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE
WHERE sql_id = 'gha6946j24qu6'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment