I am getting the following error
I am calling a stored proc from the report
which is taking some parameters.
When I execute the report i am getting following error.

"(FOC1400) SQLCODE IS 936 (HEX: 000003A8)
: ORA-00936: missing expression"

Any Help Would be greatly appreciated..

Thanks in advance.
Anmol

This message has been edited. Last edited by: Kerry,

without seeing any code it is impossible to even begin to suggest anything.

I do not believe there are any mind readers contributing to the forum

Check out to see if the file is locked out by another user... Or, if it's available... Or, if your connection is correct / available...

Guys,
Here is the proc which is called by my fex file

v_str_select :='SELECT tf.taac_fund_n AS NAME_F,
tss.supp_sys_c AS SYSTEM_TRS,
'||p_shr_ty_c||' AS TYOE_SHRE,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_cert_shr_q),''N/A'') AS TEDNN_FICATE_GETRR,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_non_cert_shr_q),''N/A'') AS STAR_NON_FICATE_SETRR,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_pd_unstl_shr_q),''N/A'') AS DNIGN_SETTLER_SETTER,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_pend_shr_q),''N/A'') AS GETFII_DENFED_REFSED,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_pur_ord_shr_q),''N/A'') AS FRDEFG_IUTRN_ORUET_RESETRF,
DECODE(TRIM(tss.supp_sys_c),''DART'',TO_CHAR(tsbt.end_red_ord_shr_q),''N/A'') AS GEDND_DEMPTIONRED_RREDO_FRTYG,
'||CASE p_shr_ty_c
WHEN '''GTE''' THEN 'tsbt.supp_end_prc_shr_q'
WHEN '''GTDAR''' THEN 'tsbt.supp_end_rte_shr_q'
WHEN '''DREF''' THEN 'tsbt.supp_end_prc_shr_q + tsbt.supp_end_rte_shr_q'
ELSE 'tsbt.supp_end_prc_shr_q + tsbt.supp_end_rte_shr_q'
END||' AS FOCDR_POUI_IOMNF_LOIKM
FROM t_supp_bal_trx tsbt,
t_fund tf,
t_supp_sys tss
WHERE tsbt.fund_sid = tf.fund_sid
AND tsbt.supp_sys_sid = tss.supp_sys_sid
AND tf.taac_fund_n IN '||CASE WHEN p_taac_fund_n = 'ALL'
THEN
'tf.taac_fund_n'
ELSE
'('||''||REPLACE(p_taac_fund_n,',',''',''')||''||')'
END||'
AND TRIM(tss.supp_sys_c) = '||CASE p_supp_sys_c
WHEN 'ALL'
THEN
'TRIM(tss.supp_sys_c)'
ELSE
''||TRIM(p_supp_sys_c)||''
END||'
AND tsbt.bus_cyc_d = '''||p_fund_bus_d||'''';


OPEN p_hxy_cer_f_rhs FOR v_str_select;


i m just using the string to execute in a curosr


This proc compiles fine in the sql Developer,but when i run the Report,
i am getting following error.

"(FOC1400) SQLCODE IS 936 (HEX: 000003A8)
: ORA-00936: missing expression"

Saying that it throwing an exception...
I think there must some quotation problem which the fex files are not able to identify..
but still i m not able to solve it..
What's wrong with this Proc..
I m really stuck with it..
Any help ASAP will be appreciated..
Thanks
Anmol

This message has been edited. Last edited by: Anmol,

I have no knowledge at all about stored procedures in Oracle, but I do wonder a bit if the syntax of wehat you posted is correct. Does it run outside of WebFocus - meaning if you use only the oracle tools to run it?
Reason I'm asking is when you start the select string there is an opening quote:
v_str_select := ' SELECT tf.taac_fund_n ....
Then this string is closed, a variable is inserted and is opened again:
'||p_shr_ty_c||' AS TYOE_SHRE,
And is ended again with:
' || CASE p_shr_ty_c
And at that point I loose track of the quotes.

When I try and compile as a procedure I am getting errors.

Error(4,1): PLS-00201: identifier 'V_STR_SELECT' must be declared
Error(4,1): PL/SQL: Statement ignored
Error(40,1): PL/SQL: Statement ignored
Error(40,6): PLS-00201: identifier 'P_HXY_CER_F_RHS' must be declared

Are we missing a piece of your proc? have you declared V_STR_SELECT correctly?

Playing with the code what is very obvious is that your use of ''' and '' and ' appears to be a very big part of the problem.
just look at the code in SQL Developer and you will see that you have a lot of key word sytax highlighting where I suspect you
should not.

I would suggest that you create the procedure so that it runs as a real request Starting with the
SELECT and returning the required value and then turn it in to a write to be called via p_hxy_cer_f_rhs.

Also If I remember correctly you cannot open multiple cursors in Oracle.

You will require a two stage process write the procedure in SQL call 1
execute the required procedure with the name of the first as an input variable.

Hello Guys,
Thanks for your replys.
Actually this proc runs fine, as far as the variable v_str_select concerned, i have declared it correctly,
and then the variables which i am using starting from 'p_' (like 'p_shr_ty_c') are the parameters which i am passing to the proc.

But Anyway...
I have resolved the issue..thankx for ur information.
I was right that was the problem of quotes..
which i have corrected...and now my report runs fine...
Once again thankx for the support to all those who replied my post... including me....

thanks....
Anmol...