rowcount from a transaction, and getdate() function 2

[vadimg]

hi. newbie to oracle.
I'm trying to log the number of records affected in a transaction.

what would be oracle's equivalent to sql server's:

insert...select....
insert log_table select @@rowcount, getdate()

thank you.

 

[carp]

I think what you are looking for is something like:

INSERT .... SELECT ....;
INSERT INTO log_table(rows, tx_date) 
   VALUES(SQL%rowcount, SYSDATE);

 

[vadimg]

thank you carp. SQL%rowcount is exactly what i was looking for.

btw. sysdate only gives me the date -- what if i need date+hour+seconds+ms --- is there like a sysdatetime or something like that?

 

[SantaMufasa]

Dave (Carp),

I'll bet you are using Oracle version 11C (the "C" is for "Carp") . In that version, "rows" will not be a reserved word and you will be able to refer to "SQL%rowcount" in a SQL DML statement.

Until I get a copy of 11C, "SQL%rowcount" is limited to use within a PL/SQL block. Here is an example:

set serveroutput on
declare
	rows_updated	number;
begin
	update s_emp set salary = salary * 1.1 where salary > 1000;
	rows_updated	:= sql%rowcount;
	dbms_output.put_line('There were '||rows_updated||' rows_updated.');
end;
/
There were 19 rows_updated.

PL/SQL procedure successfully completed.

Vadim, let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:16 (26Aug04) UTC (aka "GMT" and "Zulu"), 13:16 (26Aug04) Mountain Time)

 

[dbtoo2001]

You would use a formating command to_char, Here is an example:

select
TO_CHAR(sysdate,'dd-Mon-yyyy hh:mi:ss')
,TO_CHAR(sysdate + 1/(60*60*24),'dd-Mon-yyyy hh:mi:ss')
,TO_CHAR(sysdate + 1/(60*15*24),'dd-Mon-yyyy hh:mi:ss')
,TO_CHAR(sysdate + 2/(86400),'dd-Mon-yyyy hh:mi:ss')
,TO_CHAR(sysdate + 1/(60*3.9*24),'dd-Mon-yyyy hh:mi:ss')
from dual
/

 

[SantaMufasa]

Vadim,

In an Oracle "date" column, it automatically includes time down to seconds (not milli-seconds). The default output appearance of a date column shows "DD-MON-YY", but you can format the results to show any part of the date using the TO_CHAR(<date>,'<mask>') function:

SQL> select sysdate from dual;

SYSDATE
---------
26-AUG-04

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2004-08-26 12:36:46

To store milliseconds, you need to use Oracle's "TIMESTAMP" data item:

SQL> create table times (b timestamp(4));

Table created.

SQL> insert into times values(systimestamp);

1 row created.

SQL> insert into times
  2   values (to_timestamp('2003-12-13 00:56:44.123','yyyy-mm-dd hh24:mi:ss.ff3'));

1 row created.

SQL> select to_char(b,'yyyy-mm-dd hh24:mi:ss.ff3') from times;

TO_CHAR(B,'YYYY-MM-DDHH24:MI:
-----------------------------
2004-08-26 13:26:01.010
2003-12-13 00:56:44.123

SQL> select * from times;

B
---------------------------------------------------------------------------
26-AUG-04 01.26.01.0100 PM
13-DEC-03 12.56.44.1230 AM

Let us know if this helps with your resolution.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:28 (26Aug04) UTC (aka "GMT" and "Zulu"), 13:28 (26Aug04) Mountain Time)

 

[carp]

Oh sure, Dave (SantaMufasa) - go technical on me!
Yes, of course you are correct on both counts. I stand (or rather sit) corrected!