#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12

    Question 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!!!
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    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
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12
    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....
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    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
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12
    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
    Attached Files
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12
    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
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    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
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12
    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
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    12
    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

IMN logo majestic logo threadwatch logo seochat tools logo