SQL PLUS QUERY

[aaf6701]

I am a sql plus newbie. I am very familiar with SQL Query analyser, but am finding a great deal of difficulty with SQL Plus. How can I get a simple query to run?

I want
select * from customer_data where number_of_accounts > 10

when I type this into sqlplus and press enter, the next line just shows 2, if I press enter I get a 3. What am I doing wrong?
Thanks,
Alan

 

[oradba101]

You have to end the statement with a semi-colon ;

Regards

William Chadbourne
Oracle DBA

 

[aaf6701]

That is it. I feel ridiculous. But thanks!
Alan

 

[SantaMufasa]

Alan, Please accept my apologies for the non-intuitive idiosyncracies of SQL*Plus. SQL*Plus understands, and is able to process, commands from these "languages":

1) SQL*Plus (e.g. "connect <username/password>", "execute <some PL/SQL command>", "@<script-file name>", "set <SQL*Plus parameter>", "column <definition>", et cetera)

2) SQL (e.g., "SELECT...", "INSERT...", "UPDATE...", "DELETE...", "COMMIT..." (each are SQL Data-Manipulation-Language [DML] commands); "CREATE...", "ALTER...", "DROP...", "RENAME..." (each are Data-Definition-Language [DDL] commands); and "GRANT..." and "REVOKE..." (each are Data-Control-Language [DCL] commands)

3) PL/SQL (blocks of code that begin with "DECLARE" or "BEGIN" declarations and end with an "END;" statement.

From within SQL*Plus,

a) SQL*Plus commands begin execution via a <carriage-return>,

b) SQL commands begin execution via a ";" at the end of the SQL command or a "/" on a line of its own. <Carriage-returns> allow you to split up a SQL command onto multiple lines without beginning execution.

b) PL/SQL blocks begin execution via a "/" on a line of its own.

Here is a proof of concept for what is happening to you:

SQL> select
  2  *
  3  from
  4  s_region
  5  where
  6  id
  7  >
  8  2
  9  ;

        ID NAME
---------- --------------------
         3 Africa / Middle East
         4 Asia
         5 Europe

3 rows selected.

Note that execution did not begin until SQL*Plus sensed a ";".

Try that and let us know your findings/results.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[aaf6701]

SantaMufasa,

That almost worked, I think. I'm only getting column headings now. Do I need to set something first?
Thanks.
Al

 

[SantaMufasa]

Without seeing your code, that all sounds a bit peculiar. Prior to running your query, issue the SQL*Plus command:

set feedback on

...then re-run the query, followed by copying and pasting your entire interaction (from the "set feedback..." command down through the "SQL> " prompt following the results) to this thread so that we can take a look see.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]