Home » SQL & PL/SQL » SQL & PL/SQL » relational() function (DB12.2.0.1)
relational() function [message #669980] Mon, 28 May 2018 02:56 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can anyone cast any light on this function? It is being shown in some ora-1555 messages I have. I think it is some code generated by APEX, but one can invoke it directly:
orclx> select * from relational(dept);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

orclx>

Thank you for any insight.
Re: relational() function [message #669988 is a reply to message #669980] Mon, 28 May 2018 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Seems someone also encountered it with Data Pump:
https://community.oracle.com/thread/1042943
An answer is given to solve the problem (Bug 3654707) but does not explain the function.

This site mentions the RELATIONAL hint which is "similar to RELATIONAL function" and "instructs the optimizer to convert the Object to a relational table".

___end of my findings___



Re: relational() function [message #669989 is a reply to message #669988] Mon, 28 May 2018 09:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for spotting that bug. It was indeed during an export, with flashback_time=sysdate. The error in the alert log is this,
2018-05-28T03:42:20.836071-04:00
APROD(3):ORA-01555 caused by SQL statement below (SQL ID: 2f6nxzfsmh15x, SCN:
APROD(3):0x000000000f294402
APROD(3):):
2018-05-28T03:42:20.836277-04:00
APROD(3):SELECT * FROM RELATIONAL("REDACTED"."REDACTED")
and in the log of the export I see this,
ORA-31693: Table data object "REDACTED"."REDACTED" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8_1943085625$" too small
So I shall have to raise a TAR. A soul destroying process.





[Updated on: Mon, 28 May 2018 09:38]

Report message to a moderator

Re: relational() function [message #670007 is a reply to message #669989] Tue, 29 May 2018 08:00 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Mon, 28 May 2018 10:36

So I shall have to raise a TAR. A soul destroying process.
I am glad I see others still call it a TAR, like me.

I am also glad (not really glad) it is not just me who considers doing so not an enjoyable experience.
Re: relational() function [message #670009 is a reply to message #670007] Tue, 29 May 2018 08:29 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's definitely not just you
Previous Topic: duplicate part of text message
Next Topic: update huge table every n rows
Goto Forum:
  


Current Time: Thu Mar 28 06:53:51 CDT 2024