Tuning a sql with :1, :2...
SQL was generated by Peoplesoft application:
SELECT DISTINCT COMPANY, PAYGROUP, PAY_END_DT, TO_CHAR(PAY_END_DT, 'YYYY-MM-DD'),
OFF_CYCLE, PAGE_NUM, LINE_NUM, SEPCHK, FORM_ID, PAYCHECK_NBR, EMPLID,
NAME FROM PS_ZZ_PAY_CHK_VW
WHERE COMPANY LIKE 'DCG%'
AND PAYGROUP LIKE 'G9E%'
AND PAY_END_DT=TO_DATE(:1, 'YYYY-MM-DD')
AND OFF_CYCLE=:2 AND PAGE_NUM=:3
AND LINE_NUM=:4 AND SEPCHK=:5
AND ROWSECCLASS=:6
ORDER BY COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, LINE_NUM, SEPCHK;
Bind variables are :1, :2, etc.
Tried to assign some values to :1 and got the follwowing error:
SQL> variable 1 varchar2(20)
exec :1 := '2007-05-01'
'SP2-0553: Illegal variable name "1".
My question is: why the bind variables don't have a name? How to tune it if I can not touch the original code? Thanks.
Michael