Pl/sql Bind Variables For Rectangular Prism Volume Calculation

I've worked on this question and have it working properly for substitution variables declared, but I'm having trouble getting it to calculate properly for BIND variables. I've bee

Solution 1:

As noted on the answer to your previous question, and in APC's comment, bind variables aren't giving you much here, but it seems to be an exercise, so... The code you have displays the values OK with dbms_output. To use PRINT instead, you can't declare d_volume in the PL/SQL block as it'll be out of scope when you exit the block, so you need to make that a variable as well:

VARIABLE d_length NUMBER;
VARIABLE d_height NUMBER;
VARIABLE d_width NUMBER;
VARIABLE d_volume NUMBER;

BEGIN
    :d_length := &q_length;
    :d_height := &q_height;
    :d_width := &q_width;

    :d_volume := :d_length * :d_height * :d_width;
END;
/

print d_length
print d_height
print d_width
print d_volume

Which in SQL*Plus, with set verify off to remove some cruft, gives:

Enter valuefor q_length: 3
Enter valuefor q_height: 4
Enter valuefor q_width: 5

PL/SQLprocedure successfully completed.


  D_LENGTH
----------3


  D_HEIGHT
----------4


   D_WIDTH
----------5


  D_VOLUME
----------60SQL>

Curiously that doesn't quite work in SQL Developer (3.1.07 or 3.2.20); the line :d_volume := :d_length * :d_height * :d_width; doesn't assign a value as expected, so it's reported as null. You can do select :d_length * :d_height * :d_width into :d_volume from dual; instead, which makes some sense as they are 'placeholders in SQL statements'. It appears you still can't then reference :d_volume within the block (i.e. it's reported as null if you dbms_output it), but it is shown by print.

BEGIN
    :d_length :=&q_length;
    :d_height :=&q_height;
    :d_width :=&q_width;

    select :d_length * :d_height * :d_width into :d_volume from dual;
    dbms_output.put_line('d_volume inside the block: '|| :d_volume);
END;
/

anonymous block completed
d_volume inside the block: 

D_LENGTH
-3

D_HEIGHT
-4

D_WIDTH
-5

D_VOLUME
--60

---

Interestingly, dbms_output.put_line(':d_volume'); shows something like :ZSqlDevUnIq8 in SQL Developer; in SQL*Plus it shows :d_volume.