Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Add support for the VALUES() constructor

Lukas EderJul 11 2016 — edited Jul 30 2018

Technically, an INSERT statement always transfers data from one table to another, regardless if we're using the INSERT .. VALUES syntax or the INSERT .. SELECT syntax. This is because the VALUES() constructor is in fact a standalone table according to the SQL standard. The following syntax is thus a valid query in SQL (as implemented by SQL Server and PostgreSQL):

VALUES(1), (2)

The above query can currently be emulated (quite tediously) in Oracle using:

SELECT 1 FROM dual

UNION ALL

SELECT 2 FROM dual

Of course, the syntax is not restricted to producing only one column. Here's a two-column version:

VALUES(1, 'a'),

      (2, 'b')

And finally, this feature probably depends on derived column lists being available (), which is the only way to give such a table (and its columns) a name:

SELECT *

FROM (

  VALUES(1, 'a'),

        (2, 'b')

) t(a, b)

Now, this construct is really extremely useful when producing ad-hoc cross joins and other data with hard-coded constant tables.

Related:

This request is now referenced as: ENH 28424374 - ADD SUPPORT FOR THE ISO SQL STANDARD VALUES() CONSTRUCTOR

Comments

_AZ_

i think i should elaborate that I do expect to receive only one row ( from the select). Anything more (or less) should be deemed an error.

Answer

Well, with the code you have above you *will* get an error: TypeError: 'NoneType' object is not iterable. The reason for that is that fetchone() returns None if there are no rows left to fetch. That error isn't too helpful, though. You will need to do something along these lines:

row = cursor.fetchone()

if row is None:

   raise Exception("Hey, only one row was returned!")

tim, val = row

You will want to replace the Exception message with something a bit more meaningful, of course!

Marked as Answer by _AZ_ · Sep 27 2020
_AZ_

thank you @Anthony . Is there a better approach that i should.could use ( vs fetchone or overall ) ?

You're welcome. That approach works and is reasoanble. If you want to check for too many rows as well, you can do fetchall() which will return an array and check the length of the array instead. If you're worried about getting back too many rows with fetchall() you can also use fetchmany(2) which will tell you if there are 0, 1, or 2 rows available.

1 person found this helpful
1 - 4

Post Details

Added on Jul 11 2016
#database-ideas, #database-ideas-ideas, #under-review-voting-still-open
2 comments
2,998 views