Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure For Insert and Update (Oracle 11.0)
Stored Procedure For Insert and Update [message #509334] Fri, 27 May 2011 04:45 Go to next message
dineshhp
Messages: 1
Registered: May 2011
Junior Member
Hi Experts
I am New in Oracle Database i Have Write A SP But Show me Error when i Compile It.

Create or Replace Procedure PREPAIDEXPENSE(v_OperationType varchar2(1))
v_ATTM_TXN_TYPES_CODE    ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_CODE%TYPE;
v_ATTM_TXN_TYPES_DESC    ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_DESC%TYPE;
v_ATTM_STATUS            ACC_TXN_TYPES_MST.ATTM_STATUS%TYPE;
v_ATSM_STAGE_ID          ACC_TXN_TYPES_MST.ATSM_STAGE_ID%TYPE;
v_PP_ACCOUNT_GL          ACC_TXN_TYPES_MST.PP_ACCOUNT_GL%TYPE; 
v_PP_EXP_GL              ACC_TXN_TYPES_MST.PP_EXP_GL%TYPE;                          
as
begin
IF v_OperationType = 'I' THEN
insert into ACC_TXN_TYPES_MST
(
ATTM_TXN_TYPES_CODE,
ATTM_TXN_TYPES_DESC,
ATTM_STATUS,
ATSM_STAGE_ID,
PP_ACCOUNT_GL,
PP_EXP_GL
)
values
(
v_ATTM_TXN_TYPES_CODE,
v_ATTM_TXN_TYPES_DESC,
v_ATTM_STATUS,
v_ATSM_STAGE_ID,
v_PP_ACCOUNT_GL,
v_PP_EXP_GL
); 
else

update  ACC_TXN_TYPES_MST set
ATTM_TXN_TYPES_DESC=v_ATTM_TXN_TYPES_DESC,
ATTM_STATUS=v_ATTM_STATUS,
ATSM_STAGE_ID=v_ATSM_STAGE_ID,
PP_ACCOUNT_GL=v_PP_ACCOUNT_GL,
PP_EXP_GL=v_PP_EXP_GL
where   ATTM_TXN_TYPES_CODE=v_ATTM_TXN_TYPES_CODE;

end If;
end;


Error:SQL command not properly ended

[Updated on: Fri, 27 May 2011 04:55]

Report message to a moderator

Re: Stored Procedure For Insert and Update [message #509337 is a reply to message #509334] Fri, 27 May 2011 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your update is missing a ;
Your parameter is invalid - you can't specify lengths on them.
You haven't populated your local variables either so even when it does compile it isn't going to work.
Re: Stored Procedure For Insert and Update [message #509339 is a reply to message #509334] Fri, 27 May 2011 04:56 Go to previous messageGo to next message
Littlefoot
Messages: 21507
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
  • parameter datatype should not have precision
  • IS/AS is misplaced
  • updated columns should be separated by comma
  • update statement lacks semi-colon statement terminator

Re: Stored Procedure For Insert and Update [message #509355 is a reply to message #509339] Fri, 27 May 2011 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
  • it is not properly formatted

Regards
Michel

[Updated on: Fri, 27 May 2011 05:47]

Report message to a moderator

Re: Stored Procedure For Insert and Update [message #509358 is a reply to message #509355] Fri, 27 May 2011 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 21507
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, but that wouldn't result in "SQL command not properly ended".
Re: Stored Procedure For Insert and Update [message #509362 is a reply to message #509358] Fri, 27 May 2011 06:31 Go to previous message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right, but that would help to find the error. Wink

Regards
Michel
Previous Topic: how to make general procedure oprint curosor
Next Topic: Comparing strings
Goto Forum:
  


Current Time: Sun Oct 20 23:52:34 CDT 2019