When I click on the ambulance I get an EXPLAIN PLAN ERROR “ORA-01745:
invalid host/bind variable name” for the following statement.
The statement however runs fine. Whine I F9 I get the VARIABLES dialogue. And
when I enter them and click OK it runs fine.
WITH REPORT_PARAMETERS AS (
SELECT
FG_FUND_PROFILE . FUND_CODE_KEY ,
FG_FUND_PROF ILE . GRNT_CODE GRANT_CODE ,
CASE
WHEN TO_NUMBER( && grant_month ) >= 6 THEN
SUBSTR(TO_CHAR(TO_NUMBER( && grant_year )+ 1 ), 3 , 2 )
ELSE SUBSTR(TO_CHAR( && grant_year ), 3 , 2 )
END FISCAL_YEAR ,
CASE
WHEN to_number( && grant_month ) >= 6 THEN
SUBSTR( ‘0’|| TO_CHAR(TO_NUMBER( && grant_month )- 5 ),- 2 )
ELSE SUBSTR( ‘0’|| TO_CHAR(TO_NUMBER( && grant_month )+ 7 ),- 2 )
END FISCAL_PERIOD
FROM erolla . FG_FUND_PROFILE2 FG_FUND_PROFILE
WHERE FG_FUND_PROFILE . FUND_CODE_KEY = TO_CHAR( && grant_fund_code )
AND FG_FUND_PROFILE . CHART_CODE_KEY = ‘1’
)
SELECT
FG_PAYROLL_DISTRIBUTION . FUND_CODE_KEY ,
FG_PAYROLL_DISTRIBUTION . FUND_TITLE ,
NULL TRANSACTION_DESC ,
0.00 ACCOUNTED_BUDGET ,
0.00 ENCUMBRANCE ,
JOB. ACCOUNT_CODE GENERIC_JOB
FROM REPORT_PARAMETERS ,
erolla . FG_PAYROLL_DISTRIBUTION2 FG_PAYROLL_DISTRIBUTION ,
(SELECT /* +LEADING(REPORT_PARAMETERS) */
UNIV_FISC_YEAR_KEY , UNIV_FISC_PERIOD_KEY , PAY_NUMBER , ID, ACCOUNT_CODE ,
ACCOUNT_TITLE ,
( POSITION_NUMBER || SUFFIX ) POS_SX , TRANSACTION_DATE , EARNING_CODE
FROM erolla . FG_PAYROLL_DISTRIBUTION2 FG_PAYROLL_DISTRIBUTION ,
REPORT_PARAMETERS
WHERE FG_PAYROLL_DISTRIBUTION . RUCL_CODE = ‘HGNL’
AND FG_PAYROLL_DISTRIBUTION . FUND_CODE_KEY = REPORT_PARAMETERS . FUND_CODE_KEY
AND FG_PAYROLL_DISTRIBUTION . CHART_CODE = ‘1’
AND TO_NUMBER( FG_PAYROLL_DISTRIBUTION . UNIV_FISC_YEAR_KEY ||
FG_PAYROLL_DISTRIBUTION . UNIV_FISC_PERIOD_KEY )
FISCAL_PERIOD )
) JOB
WHERE
FG_PAYROLL_DISTRIBUTION . FUND_CODE_KEY = REPORT_PARAMETERS . FUND_CODE_KEY
AND FG_PAYROLL_DISTRIBUTION . CHART_CODE = ‘1’
AND TO_NUMBER( FG_PAYROLL_DISTRIBUTION . UNIV_FISC_YEAR_KEY ||
FG_PAYROLL_DISTRIBUTION . UNIV_FISC_PERIOD_KEY )
FISCAL_PERIOD )
AND FG_PAYROLL_DISTRIBUTION . PAY_NUMBER = JOB. PAY_NUMBER
AND FG_PAYROLL_DISTRIBUTION .ID = JOB.ID
AND FG_PAYROLL_DISTRIBUTION . TRANSACTION_DATE = JOB. TRANSACTION_DATE
AND ( FG_PAYROLL_DISTRIBUTION .SOURCE = JOB. EARNING_CODE OR SUBSTR(
FG_PAYROLL_DISTRIBUTION .SOURCE,LENGTH( FG_PAYROLL_DISTRIBUTION .SOURCE)- 3 , 3
) = JOB. EARNING_CODE )
AND FG_PAYROLL_DISTRIBUTION . UNIV_FISC_YEAR_KEY = JOB. UNIV_FISC_YEAR_KEY
AND FG_PAYROLL_DISTRIBUTION . UNIV_FISC_PERIOD_KEY = JOB. UNIV_FISC_PERIOD_KEY
AND FG_PAYROLL_DISTRIBUTION . POSITION_NUMBER || FG_PAYROLL_DISTRIBUTION .
SUFFIX = JOB. POS_SX
;
When I prefix it with “ explain plan for ”
And then run “ select * from table( dbms_xplan.display (null,null, ‘ALL’
)); ” I get the following plan.
Plan hash value: 2055535362