Home » SQL & PL/SQL » SQL & PL/SQL » How to use default value (11.2.0.3.0)
How to use default value [message #577939] Thu, 21 February 2013 23:15 Go to next message
rishwinger
Messages: 132
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
dname value=ACCOUNTING

Re: How to use default value [message #577942 is a reply to message #577939] Fri, 22 February 2013 00:19 Go to previous messageGo to next message
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 #577943 is a reply to message #577942] Fri, 22 February 2013 00:47 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Delna for your reply

your code would work just fine but my main concern is what is the use of default in Pl/SQL anonymous block
if we can use it

I can use if condition or decode condition to check if value passed is NULL update it with default value
but the purpose of default value is defeated
Re: How to use default value [message #577951 is a reply to message #577939] Fri, 22 February 2013 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2    l_deptno INTEGER DEFAULT 10;
  3    v_dname  VARCHAR2(200);
  4  BEGIN
  5    l_deptno:='&deptno';
  6    SELECT dname INTO v_dname FROM dept WHERE deptno=nvl(l_deptno,10);
  7    dbms_output.put_line('dname value='||v_dname);
  8  EXCEPTION
  9  WHEN no_data_found THEN
 10    dbms_output.put_line('error');
 11  END;
 12  /
Enter value for deptno: 40
dname value=OPERATIONS

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 
dname value=ACCOUNTING

PL/SQL procedure successfully completed.

Regards
Michel
Re: How to use default value [message #577967 is a reply to message #577951] Fri, 22 February 2013 02:36 Go to previous message
cookiemonster
Messages: 10989
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:
l_deptno:='&deptno';

become:
l_deptno:='';

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).
Previous Topic: CASE STMT VS DECODE
Next Topic: record event if no rows are returned between two timestamp
Goto Forum:
  


Current Time: Wed Oct 01 23:13:00 CDT 2014

Total time taken to generate the page: 0.04674 seconds