Systimestamp in PL/SQL

Toad for Oracle & DevOps Toolkit
1

When I select a function of systimestamp(1) into c7, all is well. When I assign c7 := a function of systimestamp(1) , I get an error PLS-000306. See code below:

PQRPSB01> l

1 declare c7 varchar2(9);

2 begin

3 select trim(to_char(systimestamp(1),‘hh24missff’)) into c7 from dual;

4 --c7 := trim(to_char(systimestamp(1),‘hh24missff’)) ;

5 dbms_output.put_line(c7);

6* end;

PQRPSB01> /

1609119

PL/SQL procedure successfully completed.

PQRPSB01> /

1609138

PL/SQL procedure successfully completed.

PQRPSB01> edit

Wrote file afiedt.buf

1 declare c7 varchar2(9);

2 begin

3 --select trim(to_char(systimestamp(1),‘hh24missff’)) into c7 from dual;

4 c7 := trim(to_char(systimestamp(1),‘hh24missff’)) ;

5 dbms_output.put_line(c7);

6* end;

PQRPSB01> /

c7 := trim(to_char(systimestamp(1),‘hh24missff’)) ;

                *

ERROR at line 4:

ORA-06550: line 4, column 21:

PLS-00306: wrong number or types of arguments in call to ‘SYSTIMESTAMP’

ORA-06550: line 4, column 1:

PL/SQL: Statement ignored

PQRPSB01>

Can somebody explain why?

Thanks,

Carl

2

Systimestamp is supported for inline assignment but systimestamp(1) is not, Strange !

U can try

c7 := substr(to_char(systimestamp,‘hh24missff’);

as the below statement gives the same results

select to_char(systimestamp(1),‘hh24missff’), substr(to_char(systimestamp,‘hh24missff’),0,8) from dual

3

Thank you Shahid_013, but I need the tenths of a second.

I have an ugly work-around if I need to use assignment instead of select into:

c7:= trim(to_char(sysdate,‘hh24mi’))

||trim(replace(to_char(round(to_char(systimestamp,‘ss.ff’),1),‘09.9’),’.’));

Ugly, like I said, but it works.

I’m more interested in the why of it

i.e.WHY systimestamp(1) is not supported for inline assignment…

Thanks,

Carl

4

would do it like this:

DECLARE

v_desired_timstamp varchar2(22);

CURSOR c_timestap

IS

 SELECT systimestamp systimestamp

       ,to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff') string      

       ,systimestamp(2) rounded_fraction_precicion

       ,substr(to_char(systimestamp(2),'yyyy-mm-dd hh24:mi:ss.ff'),1,22) as_desired

 FROM   dual;

BEGIN

FOR r_timestap in c_timestap

LOOP

 v_desired_timstamp := r_timestap.as_desired;

END LOOP;

dbms_output.put_line('v_desired_timstamp: '||v_desired_timstamp);

END;