Prompt/accept sequence

The following shows the prompt/accept sequence for a query.

PROMPT Type the department you are looking for
ACCEPT dept NUMBER PROMPT "Department Number: "
SELECT fname, lname, dno
FROM employee
WHERE dno = &dept ;

SAVE empquery.sql

When this script is executed, the following output is shown:

SQL> START empquery.sql
Type the department you are looking for
Department Number: 5
old 3: WHERE dno = &dept
new 3: WHERE dno = 5

Now, I am totally confused on this one...I am supposed to come up with the data produced but all I am getting is error messages.
The above is the only feedback I have!
Does anyone know what exactly it is that I have to do here? If you need more input let me know. I'd really, really appreciate any help can get.
Thank you!!!

Don't use semicolon ( ; ) at the end of your query. It should be like that:

PROMPT Type the department you are looking for
ACCEPT dept NUMBER PROMPT "Department Number: "
SELECT fname, lname, dno
FROM employee
WHERE dno = &dept

SQL>Save myquery.sql

now run it

SQL>START myquery

Regards

Hi,
Thanks but sadly it didn't make a difference...

this is what the output was after entereing the prompt:

Type the department you are looking for
SQL> Department Number: SP2-0425: "SELECT fname, lname, dno " is not a valid n
umber
Department Number: SP2-0425: "FROM employee " is not a valid number
Department Number: SP2-0425: "WHERE dno = &dept " is not a valid number
Department Number:


pretty much the same with or without semicolon.
Thanks....

Did you copy and paste the query into the question window here? I created an employee table, inserted a couple of rows into it, and copied your script into myquery.sql. Then:

SQL> start myquery.sql
Type the department you are looking for
Department Number: 5
old 3: WHERE dno = &dept
new 3: WHERE dno = 5

FNAME LNAME DNO
--------------- --------------- ----------
David Walkerton 5

Can you give a step by step account of what you are doing? I can't seem to get there error.. Unless... This is the error I get, if I try to paste the query on the command line, but I can't get it using a saved script and the start command.

Anyhoo, if you let us know what you're doing, step by step, and we'll get you to the data you need.

Marty

Hi Marty,
First off, I want to thank you for your time and effort. I absolutely do not know how to go about this.
Please, can you explain to me how you create the mysql.sql script? I think that is the problem, I do not understand how to do this.
To answer your question, yes I did copy the statement into the window...I was just trying things out because I can't seem to find out how this is supposed to be working.


This is the help I received from a fellow student, but I can't seem to figure out how exactly to follow these instructions:

You need to save the SQL plus program file to disk from within SQL*Plus. To do this:
host vi
a
<paste in code>
<esc>
:w newfile.sql
<ctrl>z

I attached the exact problem for you maybe you can see what I am doing wrong.

Thanks,
Natalie

Hi Marty,
First off, I want to thank you for your time and effort. I absolutely do not know how to go about this.
Please, can you explain to me how you create the mysql.sql script? I think that is the problem, I do not understand how to do this.
To answer your question, yes I did copy the statement into the window...I was just trying things out because I can't seem to find out how this is supposed to be working.


This is the help I received from a fellow student, but I can't seem to figure out how exactly to follow these instructions:

You need to save the SQL plus program file to disk from within SQL*Plus. To do this:
host vi
a
<paste in code>
<esc>
:w newfile.sql
<ctrl>z

If this doesn't help I can attach the exact problem.
Thanks,
Natalie

Those seem to be valid, if not very helpful, instructions. I'll go over them, if that helps, cool, if not, we'll try something else. I'm going to assume nothing, so if you already know this, I apologize. I'm just trying to be a clear as possible. I'm also assuming you're on either a unix or a linux system, since your fellow student gave you instructions that included vi.

SQL> host vi
host is a sqlplus command that tells the sqlplus shell that your giving it an operating system command. vi is one of the more popular unix text editors.

So now your in vi, type ;
a
This puts you in edit mode, editing after where the cursor was.

<paste text>
usually a right in the editor window will do this.

<esc>
This takes you out of edit mode, and puts you back in command mode.

:w empquery.sql
The colon will kick you to the bottom of the screen, it says you're issuing a command. The 'w' is for write, or save, and, of course, the name of the file you're saving. If you edit more, and wish to save again, you can omit the file name, it will just save with the same name.

<ctrl>z This doesn't work on my system. I think the standard is
:q
for quit. And that will put you back at the SQL prompt.

You can find a vi quick command reference at http://drumlin.thehutt.org/vi/

After this, you will have empquery .sql in your current directory, and you can execute it using either:
SQL> start empquery.sql
or
SQL> @empquery.sql

I hope this helped in some small way. If not, we'll try again.

What class is this for? And what operating system are you working on?

Marty

Thank you very much....this works but what am I supposed to be seeing after start empquery.sql?
First it told me that I have to overwrite and existing empquery.sql (I never created one but ohhhhhhhhh well) I managed to overwrite (wow, I did something without needing help :-)
So, everything works but I get some data and an error message about a trigger that I created earlier. I am not sure what I am supposed to see after running the command but that is what I get:

SQL> start empquery.sql
INSERT INTO EMPLOYEE VALUES('John', 'B', 'Smith', 123456789, '09-JAN-55', '731
FONDREN, HOUSTON, TX', 'M' , 30000, 333445555, 5)
*
ERROR at line 1:
ORA-20601: Do not hire John Smith. They are only -51.3 years old.
ORA-06512: at "CM420A4.CHECK_AGE", line 19
ORA-04088: error during execution of trigger 'CM420A4.CHECK_AGE'

This is an advanced (online) databases class. I wish my Professor would give me as detailed answers as you do :-) I mean he tries but hey, what can I say.
Currently, I am working with Windows XP but I am working on this on my Universities server (UNIX).

I can't tell you how much I appreciate your help...after taking my first databases class I didn't feel like I really understood the subject matter so I took another one as an elective but I can't say that I am doing better with this one. But at least they give me some exercises to play around with actual sql statements etc....that helps (well only if you have someone you can ask when you get stuck)!

Thanks for your quick responses,
Natalie

Hi Marty,
I thought you would like to know that I solved the problem!

I found another way to do this (I personally think it is easier). I saved the file in UNIX. In UNIX you just need to type the command vi newfile.sql and the vi screen comes up. Simply paste in the code and hit the ESC key, then hit shift+zz to exit the vi editor.

Thanks for all your help,
Natalie