Home » SQL & PL/SQL » SQL & PL/SQL » Wierd output for even parameter, correct output for an odd one...
Wierd output for even parameter, correct output for an odd one... [message #281646] Sun, 18 November 2007 22:49 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Why do I get a wierd output when I give even no. as parameter whereas I get correct output if the parameter is odd. Pls. help.

CREATE OR REPLACE PROCEDURE SQUR(NUM VARCHAR2) AS
A VARCHAR2(20);
B VARCHAR2(20);
C VARCHAR2(20);
D VARCHAR2(20);
E VARCHAR2(20);
BEGIN
A:=NUM;
IF A>0 THEN
B:=A*A;
DBMS_OUTPUT.PUT_LINE('*****************************************');
DBMS_OUTPUT.PUT_LINE('SQUARE OF'||' '||NUM||' '||'IS'||' '||B);
SELECT SQRT(NUM) INTO C FROM DUAL;
DBMS_OUTPUT.PUT_LINE('*****************************************');
DBMS_OUTPUT.PUT_LINE('SQUARE ROOT OF'||' '||NUM||' '||'IS'||' '||C);
DBMS_OUTPUT.PUT_LINE('*****************************************');
SELECT GREATEST(B,C) INTO D FROM DUAL;
DBMS_OUTPUT.PUT_LINE('LARGEST OF'||' '||B||','||C||' '||'AND'||' '||NUM||' '||'IS'||' '||D);
DBMS_OUTPUT.PUT_LINE('*****************************************');
SELECT LEAST(C,B) INTO E FROM DUAL;
DBMS_OUTPUT.PUT_LINE('SMALLEST OF'||' '||B||','||C||' '||'AND'||' '||NUM||' '||'IS'||' '||E);
DBMS_OUTPUT.PUT_LINE('*****************************************');
ELSE
DBMS_OUTPUT.PUT_LINE('WARNING : THE VALUE WHICH YOU ENTER SHOULD BE GREATER THAN ZERO');
END IF;
END;


SQL> EXEC SQUR(16);
*****************************************
SQUARE OF 16 IS 256
*****************************************
SQUARE ROOT OF 16 IS 4
*****************************************
LARGEST OF 256,4 AND 16 IS 4
*****************************************
SMALLEST OF 256,4 AND 16 IS 256
*****************************************
---------------------------------------------------------------------------------------------------------------------------
SQL> EXEC SQUR(9);
*****************************************
SQUARE OF 9 IS 81
*****************************************
SQUARE ROOT OF 9 IS 3
*****************************************
LARGEST OF 81,3 AND 9 IS 81
*****************************************
SMALLEST OF 81,3 AND 9 IS 3
*****************************************
Re: Wierd output for even parameter, correct output for an odd one... [message #281647 is a reply to message #281646] Sun, 18 November 2007 22:55 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Forgot to mention. The issue is in greatest and least functions
Re: Wierd output for even parameter, correct output for an odd one... [message #281652 is a reply to message #281647] Sun, 18 November 2007 23:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

why are you using varchar2 for passing Numbers and for storing numeric result ?

How can you say that Result is fine if Input is Odd.
Try with 49

Issue with Greatest /Least function is because ,

select greatest ('1000','20000','3') from dual ;

select greatest (1,2,3) from dual;


Thumbs Up
Rajuvan

[Updated on: Sun, 18 November 2007 23:50]

Report message to a moderator

Re: Wierd output for even parameter, correct output for an odd one... [message #281729 is a reply to message #281652] Mon, 19 November 2007 02:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As well as changing the types of A,B,C,D,E to Number, you don't need to use SELECT to calculate variables.

Instead of
SELECT GREATEST(B,C) INTO D FROM DUAL;

You can just have:
D := GREATEST(B,C);


This is both easier to understand, and quicker to execute.

Out of curiosity, did you do SQL Server before this?
Previous Topic: Dynamic query using bind variables - search query
Next Topic: TRIGGER
Goto Forum:
  


Current Time: Tue Dec 06 12:11:54 CST 2016

Total time taken to generate the page: 0.08714 seconds