Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL*Plus how to accept text variable from prompt?

Im very beginner in psql and i have a question.

Here is the code:

SET serveroutput ON
ACCEPT myVariable PROMPT "Input value: ";

BEGIN
  dbms_output.put_line('My input variable is: '||&myVariable);
 END;

Question is very simple: How can i pass text to my variable? If i input a number it is works correctly and i can read in the log my number, but if i pass a text like "mytext" instead of a number, i got an error:

    old:BEGIN


     dbms_output.put_line('My input variable is: '||&myVariable);
     END;


    new:BEGIN

  dbms_output.put_line('My input variable is: '||mytext);
 END;

    Error starting at line 5 in command:
    BEGIN
      dbms_output.put_line('My input variable is: '||&myVariable);
     END;
    Error report:
    ORA-06550: 2 sor, 50 oszlop:
    PLS-00201: identifier 'MYTEXT' must be declared
    ORA-06550: 2 sor, 3 oszlop:
    PL/SQL: Statement ignored
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
like image 468
Adam Varhegyi Avatar asked May 21 '13 15:05

Adam Varhegyi


Video Answer


3 Answers

You have to specify the data type as part of the ACCEPT statement. If none is given, it assumes a number.

Try ACCEPT myVariable CHAR PROMPT 'Input value: '; instead.

like image 160
Matthew Strawbridge Avatar answered Nov 08 '22 08:11

Matthew Strawbridge


You have to enclose the character substitution variable in quotes when you use it, if it's a string, otherwise Oracle tries to interpret the value as an object name. You can see that on the 'new' version (shown because you have set verify on), and sexta13 alluded to that too. So you would do:

 dbms_output.put_line('My input variable is: '||'&myVariable');

But you don't need to concatenate the value in this case (whether it's a number or s string):

 dbms_output.put_line('My input variable is: &myVariable');
like image 44
Alex Poole Avatar answered Nov 08 '22 10:11

Alex Poole


You don't have MYTEXT variable declared anywhere.

dbms_output.put_line('My input variable is: '||mytext); -- here is the error. It should be &myVariable.
like image 37
sexta13 Avatar answered Nov 08 '22 10:11

sexta13