Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Compiles with warnings if I include an IF statement (Oracle 9.2.0.4)
Procedure Compiles with warnings if I include an IF statement [message #420987] |
Thu, 03 September 2009 10:07  |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
Hi all,
I have a Soted procedure which compiles without problem until I include an IF statement around an 'CURSOR IS <SQL goes here>' statement.
the first bit of the stored procedure, which includes the new IF statement is as follows:
create or replace
procedure p_glpost (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2, i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_incInternal in VARCHAR2, i_chargeable in VARCHAR2, i_trialPost in VARCHAR2, i_postReport in
v_overallMatchCount NUMBER DEFAULT 0;
v_misMatch NUMBER DEFAULT 0;
v_matchCount NUMBER DEFAULT 0;
v_matchID NUMBER DEFAULT 0;
--declare run-time parameters
v_fromDate VARCHAR2(10):= substr(i_startDate, 9, 2)||'/'||substr(i_startDate, 6,2)||'/'||substr(i_startDate, 1, 4);
v_toDate VARCHAR2(10):=substr(i_endDate, 9, 2)||'/'||substr(i_endDate, 6,2)||'/'||substr(i_endDate, 1, 4);
v_accountPeriod VARCHAR2(10):=substr(i_accountPeriod, 9, 2)||'/'||substr(i_accountPeriod, 6,2)||'/'||substr(i_accountPeriod, 1, 4);
v_entity VARCHAR2(10):= i_entity;
v_transType VARCHAR2(1):= i_transType;
if v_transType = 'L' or v_transType = 'X' or v_transType = 'E' then
CURSOR c_wip
IS
SELECT TO_CHAR(wip.transno) AS TRANSNO,
CASE WHEN wip.transtype = 'L' then 'LABOUR'
WHEN wip.transtype = 'X' then 'EXPENSE'
WHEN wip.transtype = 'E' then 'EQUIPMENT'
WHEN wip.transtype = 'M' then 'MATERIAL'
WHEN wip.transtype = 'A' then 'ADJUSTMENT'
WHEN wip.transtype = 'AU' then 'WRITEUP'
WHEN wip.transtype = 'AD' then 'WRITEDOWN'
end as TRANSTYPE,
wip.entity,
wip.charge_code,
wip.company_code,
wip.project_code,
wip.projectclass,
wip.resource_code,
wipv.currency_code,
wipv.amount,
cst.dsti_revenue_class,
wip.wipclass,
wip.clientclass,
wip.departcode
FROM niku.ppa_wip wip,
niku.ppa_wip_values wipv,
niku.pac_mnt_projects pac,
niku.odf_ca_project cst
WHERE wip.entity = v_entity
AND cst.id = pac.id
AND wip.transtype = v_transType
AND wip.transno = wipv.transno
AND wipv.currency_type = 'HOME'
AND pac.project_code = wip.project_code
AND (wip.transdate BETWEEN to_date(v_fromDate, 'dd/mm/yyyy') AND to_date(v_toDate, 'dd/mm/yyyy'))
AND NOT EXISTS
(SELECT gl.transno
FROM niku.dsti_gl_control gl
WHERE gl.transno = wip.transno);
r_wip c_wip%ROWTYPE;
else ......
The IF statement is qualifying an input parameter.
Basically I want to examine the parameters and run one of two different SQL scripts depending on the value.
The errors I seem to be getting back from the compiler are:
Error(32,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
Error(34,10): PLS-00103: Encountered the symbol "C_WIP" when expecting one of the following: := . ( @ % ;
Error(75,3): PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor
can anybody advise please?
thanks,
Matt
|
|
|
|
|
|
|
|
Re: Procedure Compiles with warnings if I include an IF statement [message #421077 is a reply to message #421021] |
Fri, 04 September 2009 02:37   |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
no need to be rude.
The close parenthesis are there in my SP - that must have been a copy paste error.
the only bit that stopped the SP working was when I tried to use an IF in the declaration section.
I am new to SP and PL/SQL so was unaware that I could not use IF in that place.
If the Cursor declaration does not run the query, then it must execute when the cursor is OPENED.
Please can someone confirm.
Thanks for your help,
Matt
|
|
|
Re: Procedure Compiles with warnings if I include an IF statement [message #421079 is a reply to message #421077] |
Fri, 04 September 2009 02:48   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
matpj wrote on Fri, 04 September 2009 09:37 | no need to be rude.
The close parenthesis are there in my SP - that must have been a copy paste error.
the only bit that stopped the SP working was when I tried to use an IF in the declaration section.
I am new to SP and PL/SQL so was unaware that I could not use IF in that place.
|
If you post code here and ask why it is not working, don't protest if people tell you why it is not working. joy_division showed you a flaw in your code. The fact that you posted something different than your code cannot be blamed on him.
|
|
|
Re: Procedure Compiles with warnings if I include an IF statement [message #421086 is a reply to message #421079] |
Fri, 04 September 2009 03:17   |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
ok fair enough, but I did state that it compiles fine until I add an IF statement.
anyway, i'm not going to get into a pointless argument.
I saught help.
I found the reason why it was not working (IF statement used in the wrong place) and I am very grateful for everyone's time in helping me with this.
Matt
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 16:16:44 CST 2025
|