Home » Developer & Programmer » Reports & Discoverer » ORA-24323 Value not allowed in the Report Builder
ORA-24323 Value not allowed in the Report Builder [message #138179] Tue, 20 September 2005 10:43 Go to next message
pandeabh
Messages: 2
Registered: September 2005
Location: Mumbai
Junior Member
Hi All,

I have one report which is running in the production instance, now i want to modify that report. But when i'm opening that report in the report buider of the deve instance with minute changes it throws an error ORA-24323 Value not allowed and it drops the connection to that instance.

If i run that same query with hard coded values instead of bind variables it is not giving any error. And if i replace any of that hard coded value with parameter then it throws this error.

Can anyone pls help me.


1. AND ai.org_id = NVL(ai.org_id,ai.org_id) -- will run successfully
2. AND ai.org_id = NVL(1485,ai.org_id) -- will run successfully

3. AND ai.org_id = NVL(:org_id,ai.org_id) -- will throw an error

Pls reply ASAP, thanks in advance
Re: ORA-24323 Value not allowed in the Report Builder [message #138194 is a reply to message #138179] Tue, 20 September 2005 12:36 Go to previous messageGo to next message
kiran
Messages: 503
Registered: July 2000
Senior Member
Error says that you are not passing the parameter value which is mandatory and you are passing only a null value.

try something like following (just guessing there would had been some conversion issue)

AND ai.org_id = NVL(to_number(:org_id),ai.org_id)

--Kiran.

Re: ORA-24323 Value not allowed in the Report Builder [message #138269 is a reply to message #138194] Wed, 21 September 2005 01:41 Go to previous messageGo to next message
pandeabh
Messages: 2
Registered: September 2005
Location: Mumbai
Junior Member
Hi Kiran,

Thanks for Replying!

I haven't created any parameter for the query. Since Reports will automatically create it if i specify like :org_id, but before creating it is thorwing the error.

So there is no mandatory parameter.

And I tried to_number(:org_id) but it is not working.

Thanks & Regards
Abhijit Pande
Re: ORA-24323 Value not allowed in the Report Builder [message #182527 is a reply to message #138179] Sun, 16 July 2006 05:08 Go to previous messageGo to next message
samruddhi
Messages: 1
Registered: July 2006
Location: i
Junior Member

Hi,
i got the same error that to in a simple select query. this query is running fine in toad. but when i copy it into REPORT BUILDER it is firing ora-24323 error.
please tell me its solution.

here is the query-

SELECT Q1_POL_SYS_ID,
Q1_END_NO_IDX,
Q1_END_SR_NO,
Q1_POL_NO,
Q1_ASSR_NAME,
Q1_DIVN_CODE,
Q1_DEPT_CODE ,
Q1_PROD_CODE,
Q1_CLM_SYS_ID,
Q1_CLM_NO,Q1_INTM_DT,
Q1_LOSS_DT,
Q1_LOSS_CODE,
Q1_DIVN_NAME,
Q1_DEPT_NAME,Q1_PROD_NAME,Q1_NAT_OF_LOSS,--EST_PROV_AMT,REV_PROV_AMT,B.PAID_AMT,
SUM(NVL(EST_PROV_AMT,0)) Q1_EST_PROV_AMT,
SUM(NVL(REV_PROV_AMT,0)) Q1_REV_PROV_AMT,
SUM(NVL(PAID_AMT,0)) Q1_PAID_AMT,
((SUM(NVL(EST_PROV_AMT,0)) -
SUM(NVL(REV_PROV_AMT,0)))-
(SUM(NVL(PAID_AMT,0)))) Q1_OS_AMT
FROM
(SELECT PS_POL_SYS_ID Q1_POL_SYS_ID,
PS_END_NO_IDX Q1_END_NO_IDX,
PS_END_SR_NO Q1_END_SR_NO,
PS_POL_NO Q1_POL_NO,
PS_ASSR_NAME Q1_ASSR_NAME,
CLS_POL_DIVN_CODE Q1_DIVN_CODE,
CLS_DEPT_CODE Q1_DEPT_CODE ,
pgipk_code_desc.FN_GET_PROD_NAME( CLS_PROD_CODE) Q1_PROD_CODE,
CLS_SYS_ID Q1_CLM_SYS_ID,
CLS_CLM_NO Q1_CLM_NO,
CLS_INTM_DT Q1_INTM_DT,
TRUNC(CLS_LOSS_DT) Q1_LOSS_DT,
CLS_LOSS_CODE Q1_LOSS_CODE,
PGIPK_CODE_DESC.FN_GET_DIVN_NAME(CLS_POL_DIVN_CODE )
Q1_DIVN_NAME,
PGIPK_CODE_DESC.FN_GET_DEPT_NAME(CLS_DEPT_CODE)
Q1_DEPT_NAME,
PGIPK_CODE_DESC.FN_GET_PROD_NAME(CLS_PROD_CODE) Q1_PROD_NAME,
PGIPK_CODE_DESC.FN_GET_PC_DESC('NAT_OF_LOSS',CLS_LOSS_CODE) Q1_NAT_OF_LOSS,
SUM(DECODE(PCES_PROV_TYPE,1,NVL(PCES_PROV_LC_1,0),0) +
DECODE(PCES_PROV_TYPE,2,NVL(PCES_PROV_LC_1,0),0)) EST_PROV_AMT,
SUM(DECODE(PCES_PROV_TYPE,3,NVL(PCES_PROV_LC_1,0),0) +
DECODE(PCES_PROV_TYPE,4,NVL(PCES_PROV_LC_1,0),0)) REV_PROV_AMT
FROM PGIS_CLAIM A,
PGIS_CLM_EST B,
PGIS_PREM_SUMM C
WHERE PCES_CLS_SYS_ID = CLS_SYS_ID
AND PCES_POL_SYS_ID = PS_POL_SYS_ID
AND PCES_END_NO_IDX = PS_END_NO_IDX
AND PCES_END_SR_NO = PS_END_SR_NO
AND CLS_DIVN_CODE >= NVL(:REP_VALUE_1,0)
AND CLS_DIVN_CODE <= NVL(:REP_VALUE_2,0)
AND CLS_DEPT_CODE >= NVL(:REP_VALUE_3,0)
AND CLS_DEPT_CODE <= NVL(:REP_VALUE_4,0)
AND CLS_PROD_CODE >= NVL(:REP_VALUE_5,0)
AND CLS_PROD_CODE <= NVL(:REP_VALUE_6,0)
AND TRUNC(PCES_PROV_APPR_DT) <= NVL(:REP_VALUE_7 , SYSDATE)
/*AND PCES_SR_NO = (SELECT MAX(PCES_SR_NO)
FROM PGIS_CLM_EST
WHERE PCES_CLS_SYS_ID = CLS_SYS_ID
AND TRUNC(PCES_PROV_APPR_DT) <= :REP_VALUE_7)*/
AND (PCES_CLOSE_YN = '0'
OR (PCES_CLOSE_YN = '1' AND TRUNC(PCES_CLOSE_DT) > NVL(:REP_VALUE_7 , SYSDATE)))
--AND PCES_PROV_REF_TYPE = '1'
GROUP BY
PS_POL_SYS_ID,
PS_END_NO_IDX,
PS_END_SR_NO,
PS_POL_NO,
PS_UW_YEAR,
PS_FM_DT || ' To ' || PS_TO_DT,
PS_ASSR_NAME,
CLS_POL_DIVN_CODE,
CLS_DEPT_CODE,
CLS_PROD_CODE,
CLS_LOSS_CODE,
CLS_SYS_ID,
CLS_CLM_NO,
CLS_INTM_DT,
CLS_LOSS_CODE,
TRUNC(CLS_LOSS_DT)) A,
(SELECT CLS_SYS_ID Q1_CLM_SYS_ID_B,
CLS_CLM_NO Q1_CLM_NO_B,
SUM(NVL(CS_PAID_AMT_LC_1,0)) PAID_AMT
FROM PGIT_CLM_SETL,--PGIS_CLM_PAYM , PGIT_CLM_EST
PGIS_CLM_EST,
PGIS_CLAIM
WHERE CS_POL_SYS_ID = PCES_POL_SYS_ID
AND CS_CE_SYS_ID = PCES_SYS_ID
AND CS_POL_END_NO_IDX = PCES_END_NO_IDX
AND CS_POL_END_SR_NO = PCES_END_SR_NO
AND CLS_SYS_ID = PCES_CLS_SYS_ID
AND CLS_DIVN_CODE >= NVL(:REP_VALUE_1,0)
AND CLS_DIVN_CODE <= NVL(:REP_VALUE_2,0)
AND CLS_DEPT_CODE >= NVL(:REP_VALUE_3,0)
AND CLS_DEPT_CODE <= NVL(:REP_VALUE_4,0)
AND CLS_PROD_CODE >= NVL(:REP_VALUE_5,0)
AND CLS_PROD_CODE <= NVL(:REP_VALUE_6,0)
AND TRUNC(CS_APPR_DT) <=NVL( :REP_VALUE_7 ,0)
AND (PCES_CLOSE_YN = '0'
OR (PCES_CLOSE_YN = '1' AND TRUNC(PCES_CLOSE_DT) > NVL(:REP_VALUE_7, SYSDATE) ))
--AND PYM_PROV_REF_TYPE = '1'
GROUP BY CLS_SYS_ID,
CLS_CLM_NO ) B
WHERE Q1_CLM_SYS_ID = Q1_CLM_SYS_ID_B
AND Q1_CLM_NO = Q1_CLM_NO_B
GROUP BY
Q1_POL_SYS_ID,
Q1_END_NO_IDX,
Q1_END_SR_NO,
Q1_POL_NO,
Q1_ASSR_NAME,
Q1_DIVN_CODE,
Q1_DEPT_CODE,
Q1_PROD_CODE,
Q1_CLM_SYS_ID,
Q1_CLM_NO,
Q1_INTM_DT,
Q1_LOSS_DT,
Q1_LOSS_CODE,
Q1_DIVN_NAME,
Q1_DEPT_NAME,
Q1_PROD_NAME,
Q1_NAT_OF_LOSS
ORDER BY
Q1_CLM_SYS_ID

regards,
Samruddhi
Re: ORA-24323 Value not allowed in the Report Builder [message #182570 is a reply to message #182527] Mon, 17 July 2006 01:57 Go to previous messageGo to next message
shashikoor
Messages: 25
Registered: April 2006
Location: india
Junior Member
hi,

i am also getting the same error.
ora-24323 value not allowed
and the losing the connection.

my database and listerner version is 9.2.0.1.0 and and report builder is 6.0.8.26.0. will there be any problem with different versions. because this report builder is built using
the following.
"
Report Builder 6.0.8.26.0
ORACLE Server Release 8.0.6.0.0
Oracle Procedure Builder 6.0.8.21.0
Oracle ORACLE PL/SQL V8.0.6.3.0 - Production
Oracle CORE Version 4.0.6.0.0 - Production
Oracle Tools Integration Services 6.0.8.18.0
Oracle Tools Common Area 6.0.8.18.0
Oracle Toolkit 2 for Windows 32-bit platforms 6.0.8.25.0
Resource Object Store 6.0.8.21.0
Oracle Help 6.0.8.25.0
Oracle Sqlmgr 6.0.8.18.0
Oracle Query Builder 6.0.7.1.0 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle ZRC 6.0.8.22.0
Oracle Express 6.0.8.3.5
Oracle XML Parser 1.0.2.1.0 Production
Oracle Virtual Graphics System 6.0.5.38.0
Oracle Image 6.0.8.25.0
Oracle Multimedia Widget 6.0.8.25.0
Oracle Tools GUI Utilities 6.0.8.25.0 "

but i am connected to
"Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production "


pls give suggestions.


thank you.
shashanka.

Re: ORA-24323 Value not allowed in the Report Builder [message #204938 is a reply to message #182527] Wed, 22 November 2006 13:40 Go to previous messageGo to next message
pmurthy
Messages: 1
Registered: November 2006
Junior Member
This error also occurs when you are not connected to the database in your Report Builder. Your query will work fine in SQLPlus or Toad, but before you copy-paste it in report builder, make sure you are connected to the database.
Re: ORA-24323 Value not allowed in the Report Builder [message #252957 is a reply to message #204938] Fri, 20 July 2007 23:54 Go to previous message
kalyogi
Messages: 11
Registered: July 2007
Junior Member
Hi Murthy,

Its a very valuable suggestion.

Regards,
Kalyan
Previous Topic: Doubts
Next Topic: Deploying Oracle Reports 9i in Oracle 9iAS
Goto Forum:
  


Current Time: Sat Dec 10 03:20:31 CST 2016

Total time taken to generate the page: 0.22282 seconds