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  |
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  |
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 |
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 |
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 |
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 #674779 is a reply to message #674738] |
Sat, 16 February 2019 13:01 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Hello & welcome to OraFAQ!
It is nice to see a new member who answers someone's question. Though, have a look at message's upper right corner - it displays date when that question was posted. The original dates in 2005, the last message in 2007 while today's year is 2019 so ... just a suggestion: focus to more recent problems, those people have probably sorted it out during the last 12-14 years.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 16:35:38 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
