Oracle SQL Plus syntax

Question

Oracle SQL Plus syntax

Tom257

SSChampion

Points: 11543
More actions
September 13, 2010 at 6:00 am

#238056

I'm used to SQL Server. Oracle syntax is a bit different. I have Oracle SQL Plus (v9). I've solved most of my problems but need help with these 3.
How do I specify an alias for a table? T-SQL equivalent of FROM Table [/b]AS T[/b]
How do I stop the output from repeating the column headings every 10 items?
How do I load an existing script to re-run it?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Tom Brown (9/13/2010)
I'm used to SQL Server. Oracle syntax is a bit different. I have Oracle SQL Plus (v9). I've solved most of my problems but need help with these 3.
How do I specify an alias for a table? T-SQL equivalent of FROM Table [/b]AS T[/b]

don't include the AS keyword;

you can use the AS keyword for columns, but not for table names;:

--Oracle Syntax:

SELECT

COL1 AS CITY,

COL2 AS STATE

FROM TableName MyAlias;

How do I stop the output from repeating the column headings every 10 items?

run this group of settings in SQL plus before running the rest of your commands:

set linesize 1000;

set pagesize 48;

set arraysize 20;

set wrap off;

SET DEFINE OFF;

How do I load an existing script to re-run it?

not sure....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Tom Brown (9/13/2010)
I'm used to SQL Server. Oracle syntax is a bit different. I have Oracle SQL Plus (v9). I've solved most of my problems but need help with these 3.
How do I specify an alias for a table? T-SQL equivalent of FROM Table [/b]AS T[/b]
How do I stop the output from repeating the column headings every 10 items?
How do I load an existing script to re-run it?

Let me take the last one.

Assuming your script name is mysqlscript.sql located in /opt/oracle/scripts once you are logged into sqlplus just type

@/opt/oracle/scripts/mysqlscript.sql

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tom257 SSChampion Points: 11543 More actions · Answer 3

Thank-you both.

Apart from the cranky text editor and lack of syntax colouring, I'm quite enjoying a minor foray into Oracle.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Tom Brown (9/15/2010)
Thank-you both.
Apart from the cranky text editor and lack of syntax colouring, I'm quite enjoying a minor foray into Oracle.

Glad to help.

In regards to "the cranky..." you can always download - it's free! - Oracle's SQL Developer, you will feel much better, at least in terms of having a GUI interface 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ron Warshawsky Grasshopper Points: 23 More actions · Answer 5

You can use SQLS*Plus (http://www.memfix.com) - this is a free SQL Server tool that acts as SQL*Plus for Oracle