| How to use default value [message #577939] |
Thu, 21 February 2013 23:15  |
 |
rishwinger
Messages: 101 Registered: November 2011
|
Senior Member |
|
|
Hi Experts,
I want to use default value in pl/sql , i'm executing below code
SET serveroutput ON
DECLARE
l_deptno INTEGER DEFAULT 10;
v_dname VARCHAR2(200);
BEGIN
l_deptno:='&deptno';
SELECT dname INTO v_dname FROM dept WHERE deptno=l_deptno;
dbms_output.put_line('dname value='||v_dname);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('error');
END;
/
SQL> @test.sql
Enter value for deptno: 40
dname value=OPERATIONS
I tried with NULL to use its default value but it uses NULL(which does make sense)
SQL> @test.sql
Enter value for deptno:
error
How to use the default value of the passed argument?
expected O/P
|
|
|
|
| Re: How to use default value [message #577942 is a reply to message #577939] |
Fri, 22 February 2013 00:19   |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Try this code with 10 as first input and no input (just enter) for second one
DECLARE
V_DEFAULT NUMBER := 5;
V_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM DUAL CONNECT BY LEVEL <= DECODE('&&INPUT1','',V_DEFAULT,'&&INPUT1');
DBMS_OUTPUT.PUT_LINE(V_COUNT);
SELECT COUNT(*) INTO V_COUNT FROM DUAL CONNECT BY LEVEL <= DECODE('&&INPUT2','',V_DEFAULT,'&&INPUT2');
DBMS_OUTPUT.PUT_LINE(V_COUNT);
END;
/
regards,
Delna
[Updated on: Fri, 22 February 2013 00:21] Report message to a moderator
|
|
|
|
|
|
|
|
| Re: How to use default value [message #577967 is a reply to message #577951] |
Fri, 22 February 2013 02:36  |
cookiemonster
Messages: 9160 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The simple problem is your code overrides the default.
You have a variable, you assign a default value to it, you then assign that variable to a user entered value.
It doesn't matter if the user doesn't enter value cause that just makes this line:
become:
That's still assigning a value to a variable, just that the value is null.
Defaults work just fine in anonymous PL/SQL blocks so long as you don't immediately override them.
If you want to default in a value in your example then you need to use nvl as Michel showed (and his example doesn't need the default on the variable declaration).
|
|
|
|