PL / SQL vs Sql Server -- Inquiry on Basics (RawData)

Question

I have been contract programming for well over a decade now, and the vast majority of the customers I've served have been running SQL Server vice Oracle. Of the few customers that used Oracle, their needs were simple.

Now I have need to initialize a CTE dataset with data provided on the query line, and I've learned of a thing which is simplistic in SQL Server but Oracle is resistant to it...

SELECT 1 as 'A', 2 AS 'B' -- works in T-SQL, not in PL/SQL
UNION ALL
SELECT 3,4

Can anyone tell me what is going on here? Is this a security consideration for Oracle where SQL Server is simply less restrictive?

Have you tried appending `FROM DUAL` to the `SELECT`s? It's a while since I've worked on this, but IIRC, in Oracle you always need to select from a table, and `DUAL` is a "dummy" table for situations like this. — slothrop, Jul 19 '22 at 17:23
A `from` clause is required in Oracle (and for what it's worth, the ANSI standard). — William Robertson, Jul 19 '22 at 22:30
By the way what you seem to be working with is Oracle SQL. PL/SQL is the programming language. — William Robertson, Jul 19 '22 at 22:31

Hardryv · Answer 1 · 2022-07-19T17:36:15.580

0

Thank you slothrop, I'd forgotten all about 'dual'... it's been over a decade since I even had to consider it.

Oddly in all my searches this morning I saw no other references to it.

Here is a functioning example:

SELECT 1 AS "A", 2 AS "B" FROM DUAL
UNION ALL
SELECT 3, 4 FROM DUAL
UNION ALL
SELECT 5 ,6 FROM DUAL;

edited Jul 19 '22 at 17:36

answered Jul 19 '22 at 17:26

Hardryv

755
7
12

PL / SQL vs Sql Server -- Inquiry on Basics (RawData)

1 Answers1