Skip to Main Content

SQL & PL/SQL

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.

combining 2 seperate recordsets with related records

474007Oct 19 2007 — edited Nov 6 2010

What is the easiest to combine 2 seperate recordsets with related records?

The following example has two different recordsets F1 and F2 but are joined with F0.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> with t0 as
  2  (
  3     select 1 as id, 'Example 1' as f0 from dual union all
  4     select 2 as id, 'Example 2' as f0 from dual
  5  ),
  6  t1 as
  7  (
  8     select 1 as id, 'a' as f1 from dual union all
  9     select 1 as id, 'b' as f1 from dual union all
 10     select 2 as id, 'aa' as f1 from dual union all
 11     select 2 as id, 'bb' as f1 from dual union all
 12     select 2 as id, 'cc' as f1 from dual union all
 13     select 2 as id, 'dd' as f1 from dual
 14  ),
 15  t2 as
 16  (
 17     select 1 as id, 'x' as f2 from dual union all
 18     select 1 as id, 'y' as f2 from dual union all
 19     select 1 as id, 'z' as f2 from dual union all
 20     select 2 as id, 'ww' as f2 from dual 
 21  )
 22  select f0,f1,f2
 23  from t0, t1, t2
 24  where t0.id = t1.id
 25  and t0.id = t2.id;

F0        F1 F2
--------- -- --
Example 1 a  x
Example 1 b  x
Example 1 a  y
Example 1 b  y
Example 1 a  z
Example 1 b  z
Example 2 aa ww
Example 2 bb ww
Example 2 cc ww
Example 2 dd ww

10 rows selected.

desired output:

F0        F1 F2
--------- -- --
Example 1 a  x
Example 1 b  y
Example 1    z
Example 2 aa ww
Example 2 bb 
Example 2 cc 
Example 2 dd 

Comments

Jose Aróstegui

Please find a simple test case with HR Schema here:

https://dl.dropboxusercontent.com/u/18609389/TestSequenceError.zip

Thanks!

Jose Aróstegui

Hi,


I've reproduced the same test case in Jdev 12.1.2 and everything works fine. The main difference between the EO.xml is that 12.1.3 adds trustMode:

<TransientExpression trustMode="untrusted">

               <![CDATA[(new oracle.jbo.server.SequenceImpl("DEPARTMENTS_SEQ", adf.object.getDBTransaction())).getSequenceNumber()]]>

</TransientExpression>

Which seems to be the reason of the exception. Any idea why?

Regards,

Jose.

Timo Hahn

You are right. The normal operation to set the PK with a sequence via a groovy expression does not work any longer.

And yes the reason is the added trustMode="untrusted property. If you look at the error message for the exception

JBO-25152: Calling the constructor for class {0} is not permitted.

Cause: An attempt was made within an untrusted expression to call a constructor not on the list of allowed constructors.

Action:Add the constructor to the list of allowed constructors in adf-config.xml.

Level: 1

Type: ERROR

Impact: Security

it suggests that you can add the constructor (not sure which one) to the allowed constructors. I have not found any information on this, bit I'll see what comes up when I log a bug with your test case.

Timo

Message was edited by: Timo Hahn --------------------------------------------------------------------------------------------------------- Workaround: edit the trustMode property for the attribute and set it to 'trusted'

Timo Hahn
kdario

It seems that expressions are executed by default in untrusted mode.

So you should change trustMode="untrusted"  to "trusted"(in xml or through Property Inspector).

If you invoke java method from expression then you can also use @AllowUntrustedScriptAccess annotation on such method.

Dario

User_HWHT9

I've bounced this issue internally to at the very least get it documented, if not more, will update the ADFEMG issue once I have an update.

Chris Muir

Oracle ADF Product Management

Jose Aróstegui

Thanks everybody for your help. This forum is great :-)

Jose Aróstegui

... I guess I deserve some points for my status, no? :-)

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 16 2007
Added on Oct 19 2007
11 comments
4,193 views