When DO You Use a Semicolon?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 6 Aug 2003 10:51:15 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703FEF5A9@lnewton.leeds.lfs.co.uk>

Hi Tim,

Been there !!

The semicolon is an SQLPLus statement terminator, or, a PL/SQL statement terminator.

When passing SQL toOracle from Access, Delphi etc etc etc, don't put a semi-colon at the end - unless the code beingpassed is PL/SQL.

Cheers,
Norm.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: Tim Marshall [mailto:tmarshal_at_morgan.ucs.mun.ca] Posted At: Wednesday, August 06, 2003 6:52 AM Posted To: server
Conversation: When DO You Use a Semicolon? Subject: When DO You Use a Semicolon?

I'm passing Oracle statements to an Oracle server via an Access app using Access pass through queries. In constructing the appropriate SQL, I've been tacking a semicolon at the end. For example:

SELECT FB_BLDG_NAME "Building", FD_NAME "Facility", FB_PK FROM TMA.F_FACILITIES, TMA.F_BUILDINGS WHERE FD_PK = FB_FD_FK UNION Select '<Any Building>', '<Any Facility>', 0 from TMA.F_BUILDINGS ORDER BY 2,1;

However, when constructing something similar to the above, but with a case expression, I kept getting ORA-00911: invalid character. When I dropped the semicolon and passed the statement as below:

SELECT GRP_TAG_NO || ' ' || GRP_DESCRIPTION "Group", CASE WHEN GRP_TYPE = 'R' THEN 'Area' WHEN GRP_TYPE = 'E' THEN 'Equipment' WHEN GRP_TYPE = 'T' THEN 'Tool' WHEN GRP_TYPE = 'A' THEN 'Asset' WHEN GRP_TYPE = 'V' THEN 'Vehicle' ELSE '<Any>' END "Type", GRP_PK FROM TMA.F_GROUPS UNION SELECT '<Any>', '<Any>', 0 FROM TMA.F_GROUPS ORDER BY 1

Why does this occur, and when should I use the semicolon? Oracle server is version 8.1.7.4.0.

Thanks very much in advance for any help on this.

-- 
Tim - http://www.ucs.mun.ca/~tmarshal/
 ^o<  
 /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^  "Want some?" - Ditto

Received on Wed Aug 06 2003 - 04:51:15 CDT