0

Hi everyone I'm new to PL/SQL ,however I'm wrting a small code that a prompt a user to input a 2 numbers and display the numbers using DBMS_output.Put_line . but I get a compilation error ,below is my code ,I'm using "Oracle SQL developer"

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE n_no_1 number(8,2); n_no_2 number(8,2); BEGIN

DBMS_output.put_line('Enter Value for no 1'); &n_no_1; DBMS_output.put_line('Enter value for no 2'); &n_no_2;

DBMS_OUTPUT.PUT_LINE('The value of No 1 is' || n_no_1 );

DBMS_OUTPUT.PUT_LINE('The value of No 2 is' || n_no_2 );

END;

/

Improve this question

2 Answers 2

Reset to default
2

These 2 lines are your problem, however, not for the reasons mentioned in other answer:

&n_no_1;

&n_no_2;

In SQL, you can use the ampersand (&) to trigger something called "Macro substitution".

When the compiler comes across something like this (ie &n_no1), it prompts the user to input a value for it to substitute in it's place.

So if you enter "Hello". Your code becomes:

DBMS_output.put_line('Enter Value for no 1');
Hello;

And as you can see, that would fail, if you had just typed that out.

What you want to do is to assign that value to a variable, like this:

n_no_1 := '&n_no_1';

That gets "replaced" by this:

n_no_1 := 'Hello';

which compiles - and runs - just fine.

That all said, this is NOT the best way to do this, although this appears to be a learning excercise ?

Look up the PROMPT and ACCEPT keywords .. you can use those in SQL (outside your BEGIN / DECLARE / END block) to capture the values first in a neater fashion :)

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve032.htm#SQPUG052

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve005.htm#SQPUG026

Found an additional link here worth a good read. Explains a lot more than what you're looking at, but it discusses substitution variables, and other similiar things (and some other unrelated things :) )

https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

the compilation now is successfuly I change the code little bit but I don't get the out put ? this the code now SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_number1 number(8,2):=&n_number1; n_number2 number(8,2):=&n_number2; BEGIN DBMS_OUTPUT.PUT_LINE(n_number1); DBMS_OUTPUT.PUT_LINE(n_number2); END; /
Sorry Ditto, but that is wrong from the very beginning. "Macro substitution" is not "in SQL." Macro substitution is a feature of SQLPlus and other front-ends based on it, such as SQL Developer. The substitution is made BEFORE SQL Developer submits a text string (the PL/SQL program) to the interpreter ("compiler") for processing. Matthew's answer is correct and yours is incorrect.
@ditto this is what I did before the begin and now is working n_number1 number(8,2):=&n_number1;
-1

The &variable syntax is not PL/SQL: it is part of SQL Developer. I see what you are trying to do and syntax errors, but there's no point in correcting them because it's not going to work in the end.

The reason is: you cannot accept user input via PL/SQL at runtime.

Improve this answer

4 Comments

I agree with you about & syntax is not part of the PL/SQL,but in this tutorial I can see that it access user input via sql developer you can run the first code and see plsqltutorial.com/plsql-nested-block
"The reason is: you cannot accept user input via PL/SQL." sorry, this isn't entirely true. Macro substitution allows you to "accept user input via PL/SQL" just fine. It's not pretty, but it does allow you.
They are using the & syntax of their client (e.g., SQL Developer) to collect and set the value at compile time. In your example, it looks like you are trying to collect the user input at runtime. In an anonymous PL/SQL block, compile time and runtime may be very close to one another, but there is a difference.
The main point is that the substitution is processed by the client program (SQLPlus or SQLDeveloper etc.) before a text stream (the procedure or function, with the substitution already performed) is sent to the PL/SQL interpreter. Your answer is 100% correct. The PL/SQL processor itself, if it could be accessed directly, would have no idea what to do with &.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.