Very Computer

Hi,

 I'm starting my first sizable effort in PL/SQL programming and I'm
stuck against two small walls.  (My configuration is Oracle 7.3 and
Oracle Webserver Cartridges).

 One thing I want to do is easily work with dates.  I've seen functions
to change the date, but not times (except for the time zone stuff).  I'd
like to write a function that does the following:
  new_date := old_date + hours + minutes;
Ie. I'd like to take a date and add an hour and/or minutes to it.  I
realize I could do this by converting it to a character string and doing
all kinds of manipulations with it-- is there an easier way?

  Secondly, I'd like to generate _large_ highly random numbers.  The
random.rndint() utility with OWS2.1 generates randoms, but it seems to
have problems generating any number beyond 9999.  I'd like to generate a
number between 0 and 10^9 if possible.  I am currently planning to do
this by generating a number of them and concat'ing them together.  Are
there any public domain routines for generating integers?

  Lastly, a general question: is anyone doing anything inventive for
testing PL/SQL statements?  Currently I've been doing stuff like:

SQL> declare
      foobar number;
     begin
      foobar := mypackage.myfunct(242);
      insert into temp values(tochar(foobar));
     end;

and such.  I cannot believe there isn't a nice utility I should be using
to get the return values printed out to my display?

I'd appreciate any help that you can offer.  Many thanks for reading.

Regards,
Jason

-------------------------------------------------------------------------------


|\\|   Carnegie Mellon University    |//|                     FAX  412/361-8399
   In the end we will conserve only what we love, we will love only what we
          understand, we will understand only what we are taught.

On Tue,  3 Jun 1997 02:02:09 -0400, Jason C Miller

Quote:

>  Secondly, I'd like to generate _large_ highly random numbers.  The
>random.rndint() utility with OWS2.1 generates randoms, but it seems to
>have problems generating any number beyond 9999.  I'd like to generate a
>number between 0 and 10^9 if possible.  I am currently planning to do
>this by generating a number of them and concat'ing them together.  Are
>there any public domain routines for generating integers?

Quote:>  Lastly, a general question: is anyone doing anything inventive for
>testing PL/SQL statements?  Currently I've been doing stuff like:

>SQL> declare
>      foobar number;
>     begin
>      foobar := mypackage.myfunct(242);
>      insert into temp values(tochar(foobar));
>     end;

>and such.  I cannot believe there isn't a nice utility I should be using
>to get the return values printed out to my display?

SQL> SET SERVEROUTPUT ON
SQL> declare
      foobar number;
     begin
      foobar := mypackage.myfunct(242);
--      insert into temp values(tochar(foobar));
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(foobar));
     end;

After your PL/SQL block is executed, the value of foobar will be
printed on your SQL*Plus window.

There are some other techniques with other oracle packages witch can
be used, but are even more complicated and clumsy to use for debugging
purposes.

After Oracle8 is out third party vendors will provide true PL/SQL
de*s, but until then you will have to stick with DBMS_OUTPUT.

Quote:

>I'd appreciate any help that you can offer.  Many thanks for reading.

>Regards,
>Jason

Add 1 day  to system date:  sysdate + 1
Add 1 hour to system date:  sysdate + 1/24
Add 1 minute ---- " -----:  sysdate + 1/(24*60)

Unfortunately this doesn't handle DST changes. Suppose summertime begins (Finnish  
timezone) 30-Mar-1997 03:00 when clock is turned to be 30-Mar-1997 04:00. The
following query gives wrong result (there is no 03:30 in Finnish timezone in given
day):
SQL> alter session set nls_date_format='YYYY.MM.DD HH24:MI:SS';
SQL> select to_date('1997.03.30 02:30:00')+1/24 from dual;

TO_DATE('1997.03.30
-------------------
1997.03.30 03:30:00

Usually this is not a big issue and could be solved by storing critical date-columns
in UTC-time.

--

Timo Haatainen
Carelcomp Forest Oy