Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Diptiman.

Asked: June 04, 2012 - 11:02 am UTC

Last updated: June 04, 2012 - 11:32 am UTC

Version: 11.0.0

Viewed 1000+ times

You Asked

Hi,
I've come across function "THE ( )" in one of the Sub-programs and following is the example code:

SELECT sec_name, paragraph_heading, sec_disp_flg, text_disp_flg, text_con
FROM THE (
SELECT CAST (
fn_get_all_sections (i_param_1, i_param_2) AS text_tbl)
FROM DUAL);
INFO-1: "fn_get_all_sections":
(a) It's a FUNCTION
(b) Input parameters are VARCHAR2
(c) Return type is NESTED TABLE (of type OBJECT) Pipelined.
INFO-2: "text_tbl"
(a) It's a NESTED TABLE (of type OBJECT)

Nowhere in the Oracle document I was able to find the definition for the "THE ( )" function.

and Tom said...

It is deprecated syntax from version 8.0

replaced with the TABLE() clause.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Find this. No, find "this". No, find "this ()" --- arg

Duke Ganote, June 05, 2012 - 10:07 am UTC

In the documentation THE and TABLE are termed operands. Examples:

1. THE
http://docs.oracle.com/cd/A58617_01/server.804/a58236/04_colls.htm#20188

"The operand of THE is a subquery that returns a single column value for you to manipulate. The column value must be a nested table. Otherwise, you get a runtime error. Because the value is a nested table, not a scalar value, Oracle must be informed, which is what operator THE does."

1. TABLE
http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a77069/11_elems.htm#9801

"The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value."

I've whined before about hard-to-find terms like Q (for quoting) or DATE :)

3. DATE
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2076092100346846217

Pedantry Rules

AndyP, June 06, 2012 - 4:31 am UTC

Well, in fact those are termed operators, not operands

The operands are the things supplied to the operators ie the things operated on. So in this case the operands are the select statements or subqueries

But yes, it's hard to find stuff like that in the docs

pedanticism is my middle name, kinda

Duke Ganote, June 07, 2012 - 9:49 am UTC

AndyP is correct
http://docs.oracle.com/html/A85397_01/operator.htm
Contextually, I'd considered the INSERT to be the outer operator (as in "the records on which the SELECT operates"), and the THE (sic) clause to be an operand -- which consists of an inner operator (THE) and its operand (the inner SELECT).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.