What is wrong with this sql statement?

Energize · 28 Feb 2007 at 12:37

I'm doing an oracle sql programming course atm and have been learning everything from the oracle manuals, however the code in chapter 7 doesn't work on the iacademy oracle application express website.

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_number

This is the given code and I have been told it should work but when I type it into the webpage it says "ORA-01008: not all variables bound".

Any ideas? It seems very incompetetant of oracle to either provide code that doesn't work or run a website that doesn't execute it properly.

Dr_Evil · 28 Feb 2007 at 12:44

sql doesn't know what the "&employee_number" means in the last line.

happytechie · 28 Feb 2007 at 13:27

& is a command line argument or an input parameter for the script / statement. You need to declare it in the block and pass a value in for it.

It's not at all unusual for Oracle to publish code snippets that don't work with the version of the product that you are using.

Energize · 28 Feb 2007 at 13:42

Can you give me an example of what I need to do?

It says in the manual that that statement is supposed to prompt the user to enter an ID.

Page 5 in that pdf document.

"When running a report, users often want to restrict the data returned dynamically. iSQL*Plus provides this flexibility by means of user variables. Use an ampersand (&) to identify each variable in your SQL statement. You do not need to define the value of each variable."

"The example on the slide creates an iSQL*Plus substitution variable for an employee number. When the statement is executed, iSQL*Plus prompts the user for an employee number and then displays the employee number, last name, salary, and department number for that employee."

happytechie · 28 Feb 2007 at 13:58

if you run that script in an SQL*Plus window it should prompt you..

SQL> SELECT employee_id, last_name, salary, department_id
2 FROM employees
3 WHERE employee_id = &employee_number;
Enter value for test:

that's against an 8.1.7.0.0 instance that I have here using SQl*Plus 8.1.7.0.0 as well

you can read all about parameterized queries in the SQL manual I presume that executing SQL on the server via a iSQL*Plus session is exactly the same, but I've never used it.

Haircut · 28 Feb 2007 at 14:00

Where exactly are you running this?

In SQL*Plus it should prompt you to enter a value for the bind variable.

If you want to get it working then change the SQL statement to have the actual employee number that you would enter instead of &employee_number.

happytechie · 28 Feb 2007 at 14:01

have you asked your teacher for help as I presume that he is the one asking you to learn about SQL ?

what version of the database are you connecting to, and what version of iSQL*Plus are you using?

Energize · 28 Feb 2007 at 14:42

happytechie said:
have you asked your teacher for help as I presume that he is the one asking you to learn about SQL ?

what version of the database are you connecting to, and what version of iSQL*Plus are you using?

Yes I asked him and he has no idea why he doesn't work, he said he's just skipped that section in previous years which isn't great to hear.

I am using the sql command processor on the website: http://iacademy.oracle.com

How do I find out what version Iam using?

On the frontpage it says "Learn SQL and PL/SQL on the internet. With Oracle Academy, you can build database objects and leverage the full power of the Oracle 10g database all from your favorite browser.".

happytechie · 28 Feb 2007 at 15:09

I don't have a login there but that's fine;

It looks like a default 10g install with the web apps enabled, I don't have a 10g box here and I don't have the spare day to install it I'm afraid.

Your school is paying a lot of money for that connection so I'd go back and hassle your teacher and get him to sort it out. When you use a &var name in a script it 'should' prompt you for a value. If it isn't something is wrong.

Paul

edit:

have you tried setting verify on like it tells you on page 14 ?

Energize · 28 Feb 2007 at 15:21

When I try the verify command I get "ORA-00922: missing or invalid option"

I'll see him again on friday, until then I'll email oracle and see if they can find out why it isn't working.

This was on the about page if it means anything.

About Application Express:

Database Version Information
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

Product Build: 2.2.1.00.04
Schema Compatibility: 2006.02.24
Last DDL Time: 01/11/2007 08:36:58 PM
Host Schema: HTMLDB_PUBLIC_USER
Application Owner: FLOWS_020200
Workspace ID: *********
Workspace Name: *********
Current User: ********
Language Preference: en-gb
Current Time (on server): 02/28/2007 09:30:36 AM

NLS_CHARACTERSET: AL32UTF8
DAD CHARACTERSET: UTF-8
JOB_QUEUE_PROCESSES: 10