OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Package

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Package [message #564747]

Tue, 28 August 2012 06:28

satheesh_ss
Messages: 54
Registered: July 2012

Member

Dear friends,

I am having doubt in getting output for this package program....Pls can anyone help me in getting the proper output.

Here is my package Specification and body.

Package Specification:

CREATE OR REPLACE PACKAGE outputPackage
IS
PROCEDURE printLine (val IN VARCHAR2);
PROCEDURE printLine (val IN DATE);
PROCEDURE printLine (val IN NUMBER);
PROCEDURE printLine (val IN BOOLEAN);
END outputPackage;

Package Body:

CREATE OR REPLACE PACKAGE BODY outputPackage
IS
PROCEDURE printLine (val IN DATE) IS
BEGIN
printline(TO_CHAR (val, 'MM/DD/YYYY HH24:MI:SS'));
END;

PROCEDURE printLine (val IN NUMBER) IS
BEGIN
printLine (TO_CHAR (val));
END;

PROCEDURE printLine (val IN BOOLEAN) IS
BEGIN
IF val
THEN
printLine ('TRUE');
ELSIF NOT val
THEN
printLine ('FALSE');
ELSE
printLine ('');
END IF;
END;

PROCEDURE printLine (val IN VARCHAR2)
IS
BEGIN
IF LENGTH (val) > 80
THEN
DBMS_OUTPUT.put_line (SUBSTR (val, 1, 80));
printLine (SUBSTR (val, 81));
ELSE
DBMS_OUTPUT.put_line (val);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.enable (1000000);
printLine (val);
END;
END outputPackage;

Here, i tried to get the output using this parameters using sql*plus:

SQL> begin
outputpackage.printline('07-nov-1987');
end;

07-nov-1987

PL/SQL procedure successfully completed.

SQL> begin
outputpackage.printline('satheesh');
end;

satheesh

PL/SQL procedure successfully completed.

Here watever input i am giving,i am getting the same thing as a output..I dont know wat is the reason.

------------------------------------------------------------------------------------------
Pls let me know how to run this package....Show me how to run the subprograms of the given package.

[Updated on: Tue, 28 August 2012 06:30]

Report message to a moderator

Re: Package [message #564750 is a reply to message #564747]

Tue, 28 August 2012 06:40

cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) You've been asked before, several times, to follow How to use [code] tags and make your code easier to read?, if you can't be bothered making your posts readable, why should we bother to help you?
2) Output looks correct to me.

Report message to a moderator

Re: Package [message #564751 is a reply to message #564750]

Tue, 28 August 2012 07:02

satheesh_ss
Messages: 54
Registered: July 2012

Member

Dear cookiemonster, Definitely i will try to update my program with the correct code format from the next time....Thanks For your concern.

When coming to the question, In the above program, i have given the output format should be like this TO_CHAR (val, 'MM/DD/YYYY HH24:MI:SS'));

I mean : 01/01/2012 12:12:05 -------> something Like this....

Y i cannot get the output like this....

Report message to a moderator

Re: Package [message #564752 is a reply to message #564751]

Tue, 28 August 2012 07:07

flyboy
Messages: 1669
Registered: November 2006

Senior Member

satheesh_ss wrote on Tue, 28 August 2012 14:02

When coming to the question, In the above program, i have given the output format should be like this TO_CHAR (val, 'MM/DD/YYYY HH24:MI:SS'));

I mean : 01/01/2012 12:12:05 -------> something Like this....

Like this:

SQL> select TO_CHAR ('satheesh', 'MM/DD/YYYY HH24:MI:SS') from dual;
select TO_CHAR ('satheesh', 'MM/DD/YYYY HH24:MI:SS') from dual
                *
ERROR at line 1:
ORA-01722: invalid number

? Why? As you are passing parameter with VARCHAR2 data type, so the procedure with VARCHAR2 parameter data type is invoked.

Report message to a moderator

Re: Package [message #564753 is a reply to message #564751]

Tue, 28 August 2012 07:11

cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester

Senior Member

satheesh_ss wrote on Tue, 28 August 2012 13:02

Dear cookiemonster, Definitely i will try to update my program with the correct code format from the next time....Thanks For your concern.

Don't try, Do. If you can't format your code you're in the wrong job.

Anything in quotes is a string. To convert a string to a date you need to use to_Date.
Alternatively pass sysdate to your package.

Report message to a moderator

Re: Package [message #564754 is a reply to message #564753]

Tue, 28 August 2012 07:25

satheesh_ss
Messages: 54
Registered: July 2012

Member

Thanks cookie, I am getting the correct answer for the 1st subprogram compilation....One more doubt, For the 3rd subprogram in the package...how do i pass value for the BOOLEAN...If i use quote thn it il invoke as a VARCHAR2....Thn what is the method to pass value for the BOOLEAN.

Report message to a moderator