Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure required (oracle 10g)
stored procedure required [message #424070] Wed, 30 September 2009 03:14 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member

hi iam having table as emrids
select * from emrids

property_name ezemrxid
ALCOHOL_ID 1503
ALERT_TRACK_ID 1500
ALLERGIC_ID 1505
ALLERGY_ID 1500
APPOINTMENT_ID 1509
APPOINTMENT_TYPE_ID 1503
AUDIT_QUERY_ID 1500
BILL_UPDATE_TXN_ID 1500
BILLING_TX_ID 1503
CAFFEINE_ID 1512
CC_ID 1500
CHESTXRAYRESULT_ID 1502


hi if i pass property_name as i/p parameter i want the corresponding emrid value to be incremeted by 1.
i.e if i give ALCOHOL_ID as i/p it should return the o/p as 1504.

please help me in givinf stored procedure for this

[Updated on: Wed, 30 September 2009 03:16] by Moderator

Report message to a moderator

Re: stored procedure required [message #424072 is a reply to message #424070] Wed, 30 September 2009 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't need to put the whole message in code tags, just the code.

Show us what you've tried so far.

Hint - a function is probably better than a stored procedure for this.
Re: stored procedure required [message #424074 is a reply to message #424072] Wed, 30 September 2009 03:25 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or Replace procedure Generateids(Param varchar2)
as
select ezEMRxID+1 from EMRIDS where PROPERTY_NAME=Param

Re: stored procedure required [message #424077 is a reply to message #424074] Wed, 30 September 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
After over 350 posts, is that really the level of your Oracle knowledge?

There is no UPDATE statement to record the new value.
Your Select statement has no INTO clause
Your procedure has no OUT parameter to return the variable in (hint - a function is a better choice than a procedure for this)
There is no END at the bottom of the procedure.

Try again.
Re: stored procedure required [message #424079 is a reply to message #424077] Wed, 30 September 2009 03:47 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i got it.


CREATE OR REPLACE PROCEDURE Generateids(Param Varchar2)

AS

  v_ermid INT;

BEGIN

 Update EmrIds set ezEMRxID= ezEMRxID+1 where Property_name=Param;

  Commit;

END;

/

begin

Generateids('UNCODED_PROBLEM_ID');

end;

/






Re: stored procedure required [message #424083 is a reply to message #424079] Wed, 30 September 2009 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
rajasekhar857 wrote on Wed, 30 September 2009 09:47
i got it.


Nope - you're still not returning the new value back.
Re: stored procedure required [message #424087 is a reply to message #424083] Wed, 30 September 2009 03:59 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
sorry to post again
my requirement is like this and i need SP for this
-- read the value xxx
-- increment by 1 and update the value yyy
--return xxx
Re: stored procedure required [message #424090 is a reply to message #424079] Wed, 30 September 2009 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your best bet would be to take that update statement and add a RETURNING clause to it to return the final version of ezEMRxID into a variable, which you will need to define.

Re: stored procedure required [message #424091 is a reply to message #424090] Wed, 30 September 2009 04:09 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Can you show me in doing such please
Re: stored procedure required [message #424096 is a reply to message #424091] Wed, 30 September 2009 04:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It has been mentioned in the past that you really need to put some time in and learn more Sql/Plsql.

So I'm going to help you do that.

Here is the syntax for the UPDATE statement. In there you will see a section about the RETURNING clause.

If you're going to keep usinga procedure (against my advice), you should replace the parameters section with
CREATE OR REPLACE PROCEDURE Generateids(p_param in  Varchar2
                                       ,p_value out number)

You can then put the value fromthe Returning clause into p_value and it will be passed back to the calling code.
Re: stored procedure required [message #424098 is a reply to message #424096] Wed, 30 September 2009 04:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
AS per Ms sql itried like this but not able to find in oracle.my mssql procedure is like this.
create or Replace Procedure GenerateIDs(Param varchar2)
as
begin
select ezEMRxID as OP from EmrIDS where Property_name = Param;
update EmrIDS set ezEMRxID = ezEMRxID+1 where Property_name = Param;
return
END
GO

Re: stored procedure required [message #424100 is a reply to message #424098] Wed, 30 September 2009 04:30 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
rajasekhar857 wrote on Wed, 30 September 2009 10:27
AS per Ms sql itried like this but not able to find in oracle.my mssql procedure is like this.
create or Replace Procedure GenerateIDs(Param varchar2)
as
begin
select ezEMRxID as OP from EmrIDS where Property_name = Param;
update EmrIDS set ezEMRxID = ezEMRxID+1 where Property_name = Param;
return
END
GO



Did you not understand JRowbottom's post or did you just not bother to read it?
Re: stored procedure required [message #424101 is a reply to message #424098] Wed, 30 September 2009 04:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Option 1) Use an UPDATE statement to update the table and return the updated value into p_value.

Option 2) Use an update statement like you have, and then run a SELECT statement to fetch the new value back from the table into p_value.
Re: stored procedure required [message #424102 is a reply to message #424100] Wed, 30 September 2009 04:37 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
iam very new to oracle do what got confused.please help in giving procedure
Re: stored procedure required [message #424103 is a reply to message #424102] Wed, 30 September 2009 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You've been posting here for the last 10 months, so you're not that new to Oracle.

I will help you write this procedure, but I will not write it for you.
You will learn much more by working out how to do it yourself than you will by my spoonfeeding you the answer.

Questions: Have you read the link I gave you about the UPDATE statement and the Returning clause?

Do you understand the two options I detailed in my previous post - if not, can you specify what you don't understand?
Re: stored procedure required [message #424110 is a reply to message #424103] Wed, 30 September 2009 04:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i have written like this.
CREATE OR REPLACE PROCEDURE GenerateIDS(p_Name VARCHAR2,p_Value OUT NUMBER)
AS
  V_ID INT;
BEGIN
  UPDATE EMRIDS SET ezEMRxID = V_ID+1
  WHERE PROPERTY_NAME=p_Name RETURNING ezEMRxid
  INTO p_Value;
END;
/


how to use execute with out parameter then
Re: stored procedure required [message #424120 is a reply to message #424110] Wed, 30 September 2009 05:13 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
ANSWER THE QUESTIONS THAT JRowbottom ASKED YOU (And YES I did mean to shout.)
Next post, do NOTHING but answer the questions asked of you
Re: stored procedure required [message #424128 is a reply to message #424110] Wed, 30 September 2009 05:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're nearly there - what are you setting the new value of ezEMRxID to?

You can call this procedure like this:
DECLARE
  v_out    number;
BEGIN
  GenerateIDS(<in_value>,v_out);

  dbms_output.put_line(v_out);
end;
/
or like this:
VARIABLE out_param number

exec generateIds(<in_value>,:out_param);

select :out_param from dual;


This is why I said you'd be better off writing this as a function - you could then do
SELECT generateIds(<in_value>) from dual; 
and get the value back;
Re: stored procedure required [message #424131 is a reply to message #424128] Wed, 30 September 2009 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Wed, 30 September 2009 11:22
This is why I said you'd be better off writing this as a function - you could then do
SELECT generateIds(<in_value>) from dual; 
and get the value back;


Actually you can't. Try that and you'll get:
ORA-14551: cannot perform a DML operation inside a query
Re: stored procedure required [message #424132 is a reply to message #424131] Wed, 30 September 2009 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're right.

We could make it Autonomous.....
Shocked
Re: stored procedure required [message #424134 is a reply to message #424070] Wed, 30 September 2009 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
do you really want to teach the OP how to misuse autonomous transactions?
Re: stored procedure required [message #424139 is a reply to message #424134] Wed, 30 September 2009 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It could get him out of a job working with Oracle, which might not be a bad thing....
Re: stored procedure required [message #424141 is a reply to message #424070] Wed, 30 September 2009 05:49 Go to previous message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Laughing
Previous Topic: Substitution Variables
Next Topic: Row Count in Header
Goto Forum:
  


Current Time: Sat Oct 01 17:52:37 CDT 2016

Total time taken to generate the page: 0.07075 seconds