Hello,
I would like to know about dbms_job.
Can I run a dbms_job, while doing select
(should it have a special pragma for that ?
What I know is pragma restrict_reference(wnds, wnps),
but I didn't find any pragma that can update somehow in the database, or
run a procedure / dbms_job etc...).

Need sample code, please.

Thanks

Please do not cross and multipost to every group you can spell.

And no it is not possible, not without using pl/sql and looping through
individual records
(which won't scale).

--
Sybrand Bakker
Senior Oracle DBA

I have found :
PRAGMA AUTONOMOUS_TRANSACTION,
and put it at the function body.

Now I can update while select.

Thanks, anyway.

Show a working example.
How are you going to call this procedure?

--
Sybrand Bakker
Senior Oracle DBA

A sample code :
(I have Oracle 9i,
I don't know if previous versions works well).

(
report_maker is table
- temporary - no index - just for the sample :

CREATE TABLE REPORT_MAKER (

REPORT_MAKER_ID NUMBER (9) NOT NULL,

REPORT_NAME VARCHAR2 (30) NOT NULL,

LAST_TIMESTAMP DATE,

USER_ID NUMBER)

)

CREATE OR REPLACE package S_GENERAL is

function getReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date;

function updateReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date;

pragma restrict_references(getReportTimeStamp, wnds, wnps);

end S_GENERAL;

/

CREATE OR REPLACE package body S_GENERAL is

function getReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date is

cursor c is

select LAST_TIMESTAMP

from REPORT_MAKER

where REPORT_NAME = p_report_name

and user_id = p_user_id;

res date;

begin

res:= null;

fetch c into res;

close c;

return res;

end;

function updateReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date is

res date;

PRAGMA AUTONOMOUS_TRANSACTION;

begin

res:= sysdate;

update report_maker

set LAST_TIMESTAMP = res

where REPORT_NAME = p_report_name

and user_id = p_user_id;

if sql%notfound then

insert into report_maker(

REPORT_MAKER_ID,

REPORT_NAME,

LAST_TIMESTAMP,

USER_ID)

values(

SEQ_REPORT_MAKER.nextVal,

p_report_name,

res,

p_user_id);

end if;

commit;

return res;

end;

end S_GENERAL;

/



and in the select statement :

select s_general.updateReportTimeStamp('xxx', 0)

from dual;

also,
before each fetch c,
open c, of course.

One little problem,
is that when I do select statement,
Oracle first update the db, and after that retrive,
so if I do :
select my_updated_column, my_package.update_column(...)
from dual;

I get on the result of that query :
my_updated_column is the result after the column is updated.
first : update the column
second : retrive the column

What if I want to do :
first : retrive the column,
second : update the column.

Thanks

Don't try to be 'smart, try to *learn* Oracle.
The code you produced so far is *NOT* the way to do things *PROPERLY*.
But then again your mileage may vary: you look like the typical
developer which rather wants to hack himself out, than to read any
manual.

And NO: I'm not going to write it for you.

--
Sybrand Bakker
Senior Oracle DBA

> Don't try to be 'smart, try to *learn* Oracle.
> The code you produced so far is *NOT* the way to do things *PROPERLY*.
> But then again your mileage may vary: you look like the typical
> developer which rather wants to hack himself out, than to read any
> manual.
>
> And NO: I'm not going to write it for you.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

I don't want to write perfectly.
I want to sale and find solution to complicated products, that works
The circumstances are complicated to understand,
and sometime you should do things in not *PROPERLY* way.
and I didn't ask you to write any code,
there can always be someone else who does.

Thanks, anyway
Eitan.
Also Oracle DBA (5 OCPs).

Eitan wrote:
> One little problem,
> is that when I do select statement,

One huge problem. As Sybrand says rather clearly what you
are doing has bad idea written all over it.

By focusing on "doing it" rather than "doing it the right way"
you are making a mess that likely someone else will have to
clean up. State the business problem not your proposed, and
ill-advised, solution and you'll find things work better.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org