Home » SQL & PL/SQL » SQL & PL/SQL » Select and Update Record (Oracle 10g, SQL*Plus: Release 10.2.0.1.0, Windows 2003 Server, JDBC Tech)
Select and Update Record [message #337529] Thu, 31 July 2008 03:06 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi ,

I have got the following table structure.
SQL> DESC SMS_INBOUND;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MSG_ID                                    NOT NULL NUMBER(20)
 DNIS                                               NUMBER(10)
 SHORT_MESSAGE                                      VARCHAR2(200)
 MOBILE_NUMBER                                      NUMBER(10)
 RECEIVE_TIME                                       TIMESTAMP(6)
 [COLOR=red]READ_FLAG                                          NUMBER(2)[/COLOR]


Now i want to read 10 messages at particular interval, Lets say i am reading 10 messages using select statement and when to fire the select statement i am deciding through java scheduler.

but while i am reading a messages from the table using select statement i want to update the READ_FLAG value at the same time instance.

If it possible ?
PLUS some other application will be continuously performing insert in SMS_INBOUND table.

so use of the flag is to keep track on how many messages i have read using select statement for further processing.

How do i achieve that ?

Please suggest me some idea for the same.

Thanks & Regards,
Jigar Naik
Re: Select and Update Record [message #337534 is a reply to message #337529] Thu, 31 July 2008 03:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please don't rate your own questions (at least not before anyone has answered). The rating system is meant to distinguish special threads, not to be able to spot your own.

[Updated on: Thu, 31 July 2008 03:13]

Report message to a moderator

Re: Select and Update Record [message #337536 is a reply to message #337534] Thu, 31 July 2008 03:14 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ok i didn't knew...sorry.
Re: Select and Update Record [message #337552 is a reply to message #337536] Thu, 31 July 2008 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use the RETURNING clause on an UPDATE statement to achieve this effect:
create table test_0062 (col_1 number, col_2 number, col_3 number);

insert into test_0062 values (1,1,null);

declare
  r_0062  test_0062%rowtype;
begin

  update test_0062
  set    col_3 = 0
  where  col_1 = 1
  returning col_1,col_2,col_3 into r_0062;
  
  dbms_output.put_line(r_0062.col_1||'-'||r_0062.col_2||'-'||r_0062.col_3);
end;
/
Re: Select and Update Record [message #337570 is a reply to message #337529] Thu, 31 July 2008 05:06 Go to previous message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

You may do this...

1.Create Table

CREATE TABLE temp_msg (id NUMBER,msg VARCHAR2(100),last_read_date DATE );

2. Populate data

BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO temp_msg VALUES (i,'Msg='||i,NULL);
END LOOP;
END;

3. Create a function to populate last_read_date

CREATE OR REPLACE FUNCTION msg_read(p_id NUMBER) RETURN NUMBER
IS
PRAGMA autonomous_transaction;
BEGIN
UPDATE temp_msg SET last_read_date = SYSDATE
WHERE id = p_id;
COMMIT;
RETURN p_id;
END;

4. Create a view our the existing table using the function
msg_read.

CREATE VIEW temp_msg_v AS
SELECT msg_read(id) id, msg,last_read_date
FROM temp_msg

5. Now use the view to query the table.

SELECT * FROM temp_msg_v WHERE id = 5

Thanks,
Idris
Previous Topic: help to modify query (merged, merged, merged)
Next Topic: ORA-06502:number or value error:character string buffer too small(13327)
Goto Forum:
  


Current Time: Wed Dec 07 06:37:15 CST 2016

Total time taken to generate the page: 0.20849 seconds