| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dates and Random Numbers in PL/SQL?
On Tue, 3 Jun 1997 02:02:09 -0400, Jason C Miller <jm+@andrew.cmu.edu> wrote:
>
>
>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?
Date type in oracle includes also time portion and all date arithmetics in oracle take time portion in account. With adding/subtracting dates the main unit is one day, but you can easily add/subtract hours or minutes by adding/subtatcing corresponding decimal portion of an unit. For example:
new_date := old_date + 1/24 + 10/60
or
new_date := old_date + (60+10)/(24*60)
will both add 70 minutes to new_date.
>
> 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?
You can get first random integer in the range of 0-9999 and multiply it by 10^5. Then get next random in the same range and multiply it by 10. Get one more random integer in the range 0-9. Finaly add all this three numbers together. That way you will get pretty equal distribution of randoms between 0 and 10^9.
> 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?
Most commonly used technique for server side PL/SQL debugging is using supplyed package called DBMS_OUTPUT witch can be used to display strings from PL/SQL blocks to your display within your SQL*Plus sessin. In your example you would write:
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
debuggers, but until then you will have to stick with DBMS_OUTPUT.
>
>I'd appreciate any help that you can offer. Many thanks for reading.
>
>Regards,
>Jason
Jurij Modic Republic of Slovenia tel: +386 61 178 55 14 Ministry of Finance fax: +386 61 21 45 84 Zupanciceva 3e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
 |
 |