Toad World® Forums

get EXPLAIN PLAN error in both Toad 9.7 and 10.1


#1

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