Hi out there, I found this foru

[stigejg]

Hi out there,

I found this forums when studying Crystal reports, and had great deal og help here. And now I have started look into the great unknown (for me) called Oracle. And I am reading PL/SQL for the moment, and reading "teach yourself PL/SQL in 21 days".

When testing a program/prosedure from this book something stopped, and I wounder if someone can give me a tip.

The test was a program to demonstrate conversion functions, the code follows:

DECLARE
d1 DATE;
d2 DATE;
d3 DATE;
d4 DATE;
n1 NUMBER;
n2 NUMBER;
n3 NUMBER;
BEGIN
-- Some common dateformats
d1 := TO_DATE('1/1/02' , 'mm/dd/yy');
d2 := TO_DATE('1-1-1998', 'mm-dd-yyyy');
d3 := TO_DATE('Jan 1, 2000', 'mon dd, yyyy');
d4 := TO_DATE('1/1/02', 'mm/dd/rr');
DBMS_OUTPUT.PUT_LINE('d1 = ' || TO_CHAR(d1, 'dd-Mon-yyyy'));
DBMS_OUTPUT.PUT_LINE('d2 = ' || TO_CHAR(d2, 'mm/dd/yyyy'));
DBMS_OUTPUT.PUT_LINE('d3 = ' || TO_CHAR(d3, 'Day, Month dd, yyyy'));
DBMS_OUTPUT.PUT_LINE('d4 = ' || TO_CHAR(d4, 'Dy, Mon dd, yyyy'));
-- The dateformats runs perfect on my machine/database
-- Some examples of numberformats.
n1 := TO_NUMBER('123.99', '999D99');
n2 := TO_NUMBER('$1,235.95', '$9G999D99');
n3 := TO_NUMBER('7,235.95', '9G999D99');
DBMS_OUTPUT.PUT_LINE('n1 = ' || TO_CHAR(n1, '999D99'));
DBMS_OUTPUT.PUT_LINE('n2 = ' || TO_CHAR(n2, '$9G999D99'));
DBMS_OUTPUT.PUT_LINE('n2 = ' || TO_CHAR(n2, '9G999D99'));
-- The only bad thing here is that this numberformats generates an
-- error during program running.
END;
/

The dateformats wors fine, but the numberformats stopped.

I am using Oracle 8i personal edition on windows 98 norwegian editions.

This function works perfect, but here is no formats on the numbersection:

DECLARE
d1 DATE;
d2 DATE;
d3 DATE;
d4 DATE;
n1 NUMBER;
n2 NUMBER;
n3 NUMBER;
BEGIN
-- En del vanlige datoformater
d1 := TO_DATE('1/1/02' , 'mm/dd/yy');
d2 := TO_DATE('1-1-1998', 'mm-dd-yyyy');
d3 := TO_DATE('Jan 1, 2000', 'mon dd, yyyy');
d4 := TO_DATE('1/1/02', 'mm/dd/rr');
DBMS_OUTPUT.PUT_LINE('d1 = ' || TO_CHAR(d1, 'dd-Mon-yyyy'));
DBMS_OUTPUT.PUT_LINE('d2 = ' || TO_CHAR(d2, 'mm/dd/yyyy'));
DBMS_OUTPUT.PUT_LINE('d3 = ' || TO_CHAR(d3, 'Day, Month dd, yyyy'));
DBMS_OUTPUT.PUT_LINE('d4 = ' || TO_CHAR(d4, 'Dy, Mon dd, yyyy'));
n1 := 123.99;
n2 := 1235.95;
n3 := 7650.23;
DBMS_OUTPUT.PUT_LINE('n1 = ' || n1);
DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);
DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);
END;
/

Someon who can give me an explonation of what might be wrong?

Kind regard Stig

 

[Guest_imported]

I don't see anything wrong with the two examples of your code.
Although in the second example, you used 'n2' twice but that was not the reason why it did no work for you.
Try setting serveroutput on like
set serveroutput on
and run your code. It should work.
let me know the specific error message you are getting.
sokeh

 

[stigejg]

Hello again,

When I trying to run my first example I get this error message:

DECLARE
*
FEIL på linje 1:
ORA-06502: PL/SQL: numerisk feil eller verdifeil
ORA-06512: ved line 21

In englis it will sound like:

DECLARE
*
ERROR on line 1:
ORA-06502: PL/SQL: numeric error or value error
ORA-06512: on line 21

(line 19 is where the numeric example startes)

regard Stig

 

[Guest_imported]

The only thing I can think of is that the ' ' you included in variable n1 is causing your code to blow up.
Bear two things in mind:
You are already using to_number conversion type.
So since 123.99 is already numeric, you can leave out the ' '.
Secondly, the format mask 999d99 covers only 123.99 (as far as I know). so that additional ' ' is not going to work.
So try and replace n1 with this:
n1 := TO_NUMBER(123.99, '999D99'); and see what happens.
sokeh

 

[harris79]

The problem is the TO_NUMBER function, it takes an input string only-

n1 := TO_NUMBER('123.99')

The TO_NUMBER function is largely obsolete as Oracle can recognise that 123.99 (a number) is the same
as '123.99' a string.

Steve

 

[rcurva]

Are these data exactly you been using when the error occured, bec. I copied your script, verbatim, and tried on my sqlplus and it looks OK to me.

BNP->ED
1 declare
2 n1 number;
3 n2 number;
4 n3 number;
5 begin
6 n1 := TO_NUMBER('123.99', '999D99');
7 n2 := TO_NUMBER('$1,235.95', '$9G999D99');
8 n3 := TO_NUMBER('7,235.95', '9G999D99');
9 DBMS_OUTPUT.PUT_LINE('n1 = ' || TO_CHAR(n1, '999D99'));
10 DBMS_OUTPUT.PUT_LINE('n2 = ' || TO_CHAR(n2, '$9G999D99'));
11 DBMS_OUTPUT.PUT_LINE('n2 = ' || TO_CHAR(n2, '9G999D99'));
12* end;
BNP->/
n1 = 123.99
n2 = $1,235.95
n2 = 1,235.95

PL/SQL procedure successfully completed.

If you are feeding other data on this script, your data must be outside your format. You must be feeding values not compatible with this '999D99' format or your other fomats.