Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: SQL Plus instruction

    In SQL Plus, if I type in
    SQL > ed

    and then in the resulting Notepad type in something like

    grant connect to smithj;
    grant connect to jonest;
    /

    I get an error on the ';'. But if I put the same syntax, minus the '/' in a text file, and call it:

    SQL > @myscript.sql

    It executes fine. Can someone tell me what I'm doing wrong in SQL Plus, and perhaps show me how to run multiple lines of code in SQL Plus, if it's possible?

    Thanks,
    Chuck

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    When you edit in sqlplus, you are editing a single command line... To execute it, you do not put in the ';'... Exit out of the notepad and "/" to execute ... You are trying issue more that 1 statement ...

    HTH
    Gregg

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So you're saying that

    grant connect to smithj
    grant connect to jonest
    /

    will work?

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    This doesn't work, and neither does:

    grant connect to smithj
    /
    grant connect to jonest
    /

    Are you saying that I just can't run more than one statement, or my syntax is off?

    -cf
    Last edited by chuck_forbes; 07-15-04 at 13:05.

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    When you type "ed", without giving a file name, sqlplus will open a new file (overwriting any existing file), and put the contents of the sql buffer (the sql you see when you type "list" in sqlplus) into the file. When you exit the editor (whether you saved or not is irrelevant) sqlplus will read that entire file into the sql buffer, and if the last line is "/" then it will execute it as one statement (could be a single sql stmt, could be a pl/sql anonymous block, sqlplus doesn't care).

    What you could do is type "ed my_grants.sql". This open a file of the name you gave. You put your original sql statements into it (with a ";" at the end of each stmt, but no "/" at the end), and save and exit the file. Then in sqlplus again, type "@my_grants".
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    http://download-west.oracle.com/docs...a82950/toc.htm

    to interpret,
    SQLPlus is A command line processor, meaning one command at a time.
    grant connect to smithj; Is one command, the semi colon ends the command and instructs sqlplus to execute.
    grant connect to jonest; is the next command, the semi colon (or /) ends the command, and instructs sqlplus to execute.
    When you use a file such as myscript.sql, imbedding multiple commands, then the @ instructs sqlplus to search for the file name, then execute the commands, one at a time.

    --------------------
    Give a man a fish, you feed him for a day, Teach a man to fish, you turn him into a liar.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •