Skip to Main Content
Forums

Oracle Database Discussions

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.

Use of DBMS_UTILITY.EXPAND_SQL_TEXT

mpatzwahlAug 4 2022 — edited Aug 4 2022

Hi,
i am playing arround with the package DBMS_UTILITY.EXPAND_SQL_TEXT
I thought that is the sql text that the optimizer uses before it starts (doc says only for Views, but it works for other statements also) Testcase:
WITH function sql_text (text IN CLOB)
RETURN CLOB IS
ret CLOB;
BEGIN
DBMS_UTILITY.EXPAND_SQL_TEXT (
input_sql_text => text,
output_sql_text => ret);
RETURN ret;
END;
select sql_text('select d.deptno,ename from emp e right outer join dept d on e.deptno=d.deptno where ename is null') from dual;
=>SELECT "A1"."QCSJ_C000000000300001_2" "DEPTNO","A1"."ENAME_0" "ENAME" FROM (SELECT "A3"."ENAME" "ENAME_0","A3"."DEPTNO" "QCSJ_C000000000300000","A2"."DEPTNO" "QCSJ_C000000000300001_2" FROM "MUNIQ"."EMP" "A3","MUNIQ"."DEPT" "A2" WHERE "A3"."DEPTNO"="A2"."DEPTNO") "A1" WHERE "A1"."ENAME_0" IS NULL

OK test it: Result is empty, but the original Statement produce this output
DEPTNO ENAME
--------- ---------
40 null

So why is the output wrong ?
Thanks
Marco

Comments

Ashish Dave-Oracle
either there is Synchronization issue or
element which was there at time of recording is not there while playback ..could be defect
or name or id of elemet changed
1 person found this helpful
647280
You should create a better custom dynamic variable to identify this element. Referencing a element by Index is not recommended or reliable. A recorded Index of 41 for the element inside form QUERYTEXTCOMPONENTS indicates that there are a lot of elements inside that recorded form, and that there is probably some dynamic nature to the page.

You should change the match to be i.e. formelement[INPUT](name="something" | id = "something")
1 person found this helpful
1 - 2

Post Details

Added on Aug 4 2022
#general-database-discussions
3 comments
388 views