OraFAQ Forum: Reports & Discoverer » no PL/SQL translation for the bindtype given for this bind variable

Home » Developer & Programmer » Reports & Discoverer » no PL/SQL translation for the bindtype given for this bind variable (Oracle Fusion Middleware 11g Reports - Errors)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

no PL/SQL translation for the bindtype given for this bind variable [message #458834]

Wed, 02 June 2010 02:37

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Dear,

I m using Oracle Fusion Middleware 11g Reports and facing same problem with OS window XP.

"no PL/SQL translation for the bindtype given for this bind variable"

how you solved this problem.

Kindly advise me.

ranamirfan@gmail.com

Looking forward to hearing from you.

thanks & regards,

Irfan

Report message to a moderator

no PL/SQL translation for the bindtype given for this bind variable [message #458838 is a reply to message #458834]

Wed, 02 June 2010 02:46

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Dear All,

I m using Oracle Fusion Middleware 11g Reports and in the Formula column query when I m using bind variable with same datatype then it gives an errors with OS window XP.

"no PL/SQL translation for the bindtype given for this bind variable"

Please see blow query.

/*

function CF_1FORMULA0007 return Number is
CR NUMBER(12,2);

begin

SELECT SUM(NVL(GL_TRANS_DETAIL.CREDIT_DC,0))INTO CR

FROM GL_TRANS_HEADER, GL_TRANS_DETAIL,GL_ORGANIZATION,GL_JOURNAL_CATEGORY

WHERE (GL_TRANS_DETAIL.GL_TRANS_HEADER_ID = GL_TRANS_HEADER.GL_TRANS_HEADER_ID)
AND (GL_TRANS_HEADER.GL_ORG_ID = GL_ORGANIZATION.GL_ORG_ID)
AND GL_TRANS_HEADER.REPORTING_DATE BETWEEN :FROMDATE AND :TODATE
AND GL_ORGANIZATION.GL_COMPANY_ID=:COMPANYID
AND GL_TRANS_HEADER.TRANS_TYPE NOT IN ('V','T')
AND GL_TRANS_DETAIL.GL_NATURAL_ACCT_ID=:GL_NATURAL_ACCT_ID
AND GL_TRANS_HEADER.GL_JOURNAL_CATEGORY_ID=GL_JOURNAL_CATEGORY.GL_JOURNAL_CATEGORY_ID
AND GL_JOURNAL_CATEGORY.IS_ZAKAT =:CATEGORYID ;

RETURN NVL(CR,0);

end;

*/

Kindly advise me.

ranamirfan@gmail.com

Looking forward to hearing from you.

thanks & regards,

Irfan

Report message to a moderator

Re: error in query block execution on different machine [message #458842 is a reply to message #458834]

Wed, 02 June 2010 02:58

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you try to recompile the report (Ctrl + Shift + K)?

Report message to a moderator

Re: error in query block execution on different machine [message #458860 is a reply to message #458842]

Wed, 02 June 2010 04:20

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Yes,recompiled the report (Ctrl + Shift + K).

Same error.

thankx

Irfan

Report message to a moderator

Re: error in query block execution on different machine [message #458861 is a reply to message #458860]

Wed, 02 June 2010 04:26

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you know which variable it's complaining about?
If so what is it's datatype and how is it used?

Report message to a moderator

Re: error in query block execution on different machine [message #458862 is a reply to message #458861]

Wed, 02 June 2010 04:33

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Please see blow query.
I created manual user parameter with data type varchar2(1) Name Categoryid and when I used in Formula column this gives error.

AND GL_JOURNAL_CATEGORY.IS_ZAKAT =:CATEGORYID ;

Is_Zakat variable also data type varchar2(1)

both variable which I created and compare in a quary have same data type varchar2.

/*

function CF_1FORMULA0007 return Number is
CR NUMBER(12,2);

begin

SELECT SUM(NVL(GL_TRANS_DETAIL.CREDIT_DC,0))INTO CR

FROM GL_TRANS_HEADER, GL_TRANS_DETAIL,GL_ORGANIZATION,GL_JOURNAL_CATEGORY

WHERE (GL_TRANS_DETAIL.GL_TRANS_HEADER_ID = GL_TRANS_HEADER.GL_TRANS_HEADER_ID)
AND (GL_TRANS_HEADER.GL_ORG_ID = GL_ORGANIZATION.GL_ORG_ID)
AND GL_TRANS_HEADER.REPORTING_DATE BETWEEN :FROMDATE AND :TODATE
AND GL_ORGANIZATION.GL_COMPANY_ID=:COMPANYID
AND GL_TRANS_HEADER.TRANS_TYPE NOT IN ('V','T')
AND GL_TRANS_DETAIL.GL_NATURAL_ACCT_ID=:GL_NATURAL_ACCT_ID
AND GL_TRANS_HEADER.GL_JOURNAL_CATEGORY_ID=GL_JOURNAL_CATEGORY.GL_JOURNAL_CATEGORY_ID
AND GL_JOURNAL_CATEGORY.IS_ZAKAT =:CATEGORYID ;

RETURN NVL(CR,0);

end;

*/

Regards,

Irfan

Report message to a moderator

Re: no PL/SQL translation for the bindtype given for this bind variable [message #458928 is a reply to message #458838]

Wed, 02 June 2010 11:47

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

What is the datatype of the bind variables CATEGORYID and COMPANYID?
Also the columns GL_JOURNAL_CATEGORY.IS_ZAKAT and GL_ORGANIZATION.GL_COMPANY_ID?
Crosscheck whether those are matching or not. Correct, if needed.

By
Vamsi

Report message to a moderator

Re: no PL/SQL translation for the bindtype given for this bind variable [message #460085 is a reply to message #458928]

Wed, 09 June 2010 09:16

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Thank you very much Dear All,

I Find out the solution of this error.
" no PL/SQL translation for the bindtype given for this bind variable "

when you received this error Just Save your report and close the existing report and then again open this report and compile it after that your report will be run with out errors.( I think this is report Builder 11g bugs. ).

Regards,

Irfan
KSA

Report message to a moderator