Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WebFocus Error Message

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] WebFocus Error Message
 Login/Join
 
Gold member
posted
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,


WebFocus7.6.2, WebFocus 7.1.1,Windows
HTML, PDF and Excel
 
Posts: 71 | Registered: June 30, 2009Report This Post
<JG>
posted
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
 
Report This Post
Expert
posted Hide Post
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...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
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,


WebFocus7.6.2, WebFocus 7.1.1,Windows
HTML, PDF and Excel
 
Posts: 71 | Registered: June 30, 2009Report This Post
Virtuoso
posted Hide Post
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.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
<JG>
posted
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.
 
Report This Post
Gold member
posted Hide Post
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....Smiler

thanks....
Anmol...


WebFocus7.6.2, WebFocus 7.1.1,Windows
HTML, PDF and Excel
 
Posts: 71 | Registered: June 30, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WebFocus Error Message

Copyright © 1996-2020 Information Builders