0

I need to do a report with a procedure of every salesman in 6 fake companies if they sold more then the number the user entered. i'm in sql developper with a oracle Db.

I asked to ppl in my class how they did and the thing they did doesnt work for me, i always get error on the accept/prompt.

/
accept temp default '100';
/
 when '&temp' < 90.00 then raise too_cold;

I also tried using it like that:

accept temp prompt 'Input degree (numerically in degrees F)?:';

I want to show a prompt with words so that the user knows what to enter and use the number i get.

Abra
  • 19,142
  • 7
  • 29
  • 41
Tommy Roy
  • 13
  • 3
  • Add a data type for your `temp` variable, as shown in this _Stack Overflow_ question: [SQL*Plus how to accept text variable from prompt?](https://stackoverflow.com/questions/16674252/sqlplus-how-to-accept-text-variable-from-prompt) – Abra Jun 04 '19 at 00:41
  • Erreur(4,13): PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . ( @ % ; not null range default character still get that – Tommy Roy Jun 04 '19 at 00:51

1 Answers1

1

Help yourself.

SQL> help accept

 ACCEPT
 ------

 Reads a line of input and stores it in a given substitution variable.

 ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
 [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]

Here we go:

SQL> set ver off
SQL>
SQL> accept temp number default 20 prompt 'Enter department number: '
Enter department number: 10
SQL> select deptno, ename from emp where deptno = &temp;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER

SQL>

If nothing's entered, the default value takes place:

SQL> accept temp number default 20 prompt 'Enter department number: '
Enter department number:
SQL> select deptno, ename from emp where deptno = &temp;

    DEPTNO ENAME
---------- ----------
        20 SMITH
        20 JONES
        20 SCOTT
        20 ADAMS
        20 FORD

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • If it is a procedure, then make it accept a parameter (`create procedure px (par_temp in number default 20) as ...`) and proceed from there. Note that it won't return data as `SELECT` does. Perhaps you should use a function that returns ref cursor instead. – Littlefoot Jun 11 '19 at 05:08