Hi all,

I need to migrate an Oracle Forms block to Formspider. The block is based on a view which is already a little bit slow because it is a join on 8 tables and some of them have outer joins. The block has a post-query that calls a procedure to determine 10 (!) lookup fields. The procedure is complex so it also takes some time to execute for each row.

For reasons i will never understand Formspider does not have a post-query functionality on row level. The only way to simulate the Oracle Forms post-query is to create a view with 10 extra lookup columns. As far as i know each lookup column has to get its value with a function. So, instead of 1 execution of a procedure, the view needs 10 executions of a function. This view becomes horrible slow offcourse.

How do i solve this problem? Is there a way to create this view with 10 lookup fields that are calculated in 1 time with the procedure?

Kind regards, Jan Willem

asked 02 Oct '15, 04:02

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231436
accept rate: 0%


Hi Jan-Willem

Maybe after executeQuery use

api_datasource.getrows(in_datasourcename_tx IN varchar2, io_rows_t IN OUT NOCOPY tt_rows, in_includedeletedrows_yn IN varchar2 DEFAULT 'N')

Then loop the rows and execute the procedure to get the 10 lookup values and update the table row with the return values from the procedure.

Might be worth a try.

Best regards, Michiel

link

answered 02 Oct '15, 05:30

Michiel%20A's gravatar image

Michiel A
5161648
accept rate: 13%

Thanks Michiel.

This could be a good idea, however, sometimes there are thousands of rows queried... I do not have experience yet with so many rows in a panel, but will this go fast enough?

One of the advantages of the post-query trigger in Oracle Forms is that it only fires the first time a row is shown. Because of this, users do not experience any delay because of the execution of the procedure.

I am thinking about the "selectionChanged" trigger? Then i retrieve the rows with only null values and the selectionChanged trigger executes the procedure to get the values. Will this work?

Or is there perhaps a sql-genius who can make a view with columns derived in one procedure... Somekind of sub-select perhaps. Perhaps i should ASK TOM.

Kind regards, Jan Willem

link

answered 02 Oct '15, 06:18

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231436
accept rate: 0%

Hi Jan,

I think it should be possible to turn the procedure into a function which returns the right result but execute the logic only once.

So something like;

select my_package.f_myOldProc(input1,input2,input3,'output1') output1,
       my_package.f_myOldProc(input1,input2,input3,'output2') output2,
       etc...etc...etc..
from....

To summarize, the trick in the f_myOldProc is that the first time it runs, it calculates all 10 output values but returns only 1 of them, caching the rest in a PL/SQL table or global variables. The other nine times the function is executed, it directly return the previously calculated values.

You may need to do some plumbing to ensure you clear the global variables etc... but this seems to me like a viable solution.

Kind Regards,
Yalim

link

answered 02 Oct '15, 07:50

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Hi Yalim,

offcourse the function could store the values in session variables or any other global variables and when they exist, return the "cached" values.

However, then you need to be 100% sure that the Oracle database executes the function one row at the time and for all columns in one row in a sequence. Otherwise the variables will be mixed up...

I will first try the "selectionChanged" option. That fires when the focus changes and then i know the input parameters. Almost all parameters are displayed in an overflow area, so nobody will notice that they are "calculated" when the user navigates to the row.

Kind regards, Jan Willem

link

answered 02 Oct '15, 09:00

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231436
accept rate: 0%

By the way, another option could be materialized views...

(02 Oct '15, 09:01) Jan Willem V...

Hi Jan,

No this is not correct. The solution can be created in a way that the order Oracle fires the function is irrelevant.

Kind Regards,
Yalim

(02 Oct '15, 09:10) Yalim Gerger ♦♦

Why will the selectionChanged event not work?

The event is already there to synchronise the datasource in the grid and 5 panels for the overflow area.

(02 Oct '15, 09:16) Jan Willem V...

I did not say it would not work. I would solve this issue in the view either as I described or the way İbrahim described. That's all.

(02 Oct '15, 09:21) Yalim ♦♦

Pfffff. probably because api_component.setvalue can not set values in a specific row of a grid...

(02 Oct '15, 09:22) Jan Willem V...

In the mean time i discovered that selectionChanged can really not be used because api_component.setvalue can not address one row. As already mentioned in my last comment.

So i am stuck to the view "solution" of which i do not understand anything.

(02 Oct '15, 09:49) Jan Willem V...

Hi Jan,

We don't have a setValue for Grid rows because we can use the api_datasource.setColumnValue to achieve the same thing. The Grid is only a window to the rows in the datasource.

Kind Regards, Yalim

(02 Oct '15, 10:21) Yalim ♦♦

Hi Yalim,

so... perhaps selectionChanged with api_datasource.setColumnValue would work... But Ibrahim's solution looks promising... i only need some help in constructing the view.

KR JW

(02 Oct '15, 10:32) Jan Willem V...
showing 5 of 8 show 3 more comments

Hi Jan,

I think you can also achieve your request using a function which returns your 10 output in a custom XML format like:

<root>
<output1>value1</output1>
<output2>value2</output2>
..
<output10>value10</output10>
</root>

After retrieveing this XML from your function with 3 parameters, you can just parse it inside SQL:

Assuming that the xmltype('<root><output1>..</output1><output2>..</output2></root>') parts are the values returned by your function;

with resultset as (select 'row1' as rowName, xmltype('<root><output1>1</output1><output2>2</output2></root>') as functionOutput from dual
                  union all
                  select 'row2', xmltype('<root><output1>11</output1><output2>22</output2></root>') from dual
                  union all
                  select 'row3', xmltype('<root><output1>111</output1><output2>222</output2></root>') from dual
                  )
SELECT resultset.rowName,
       output1,
       output2
FROM resultset, 
     xmltable( '/root'
                passing resultset.functionOutput
                columns 
                    output1             VARCHAR2(4000) path 'output1',
                    output2             VARCHAR2(4000) path 'output2'
              );

I think you can do the same with a function returning an object type instead of an XMLtype and querying the returned object.

Hope this helps,
Ibrahim

link

answered 02 Oct '15, 09:16

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

edited 02 Oct '15, 09:17

Oh dear... i think i am stuck in the middle ages %#$@&$($^@*!#

(02 Oct '15, 09:41) Jan Willem V...

Sorry Ibrahim, i think you are some kind of wizard with such code...

That said, I know how to make that function :) So suppose i have function myFunction that needs three input parameters (table1.column1, table1.column2, table1.column3) and it delivers this output for five values:

<root>
<myfunction_result_1>value</myfunction_result_1>
<myfunction_result_2>value</myfunction_result_2>
<myfunction_result_3>value</myfunction_result_3>
<myfunction_result_4>value</myfunction_result_4>
<myfunction_result_5>value</myfunction_result_5>
</root>

Normally i create views like this:

create or replace view myComplexView
 ( column1
 , column2
 , column3
 , column4
 , column5
 , column6
 , column7
 , column8
 , column9
 , column10
 , myfunction_result_1
 , myfunction_result_2
 , myfunction_result_3
 , myfunction_result_4
 , myfunction_result_5
 )
as select table1.column1
   ,      table1.column2
   ,      table1.column3
   ,      table1.column4
   ,      table1.column5
   ,      table1.column6
   ,      table2.column1
   ,      table2.column2
   ,      table2.column3
   ,      table2.column4
   ,      myFunction (table1.column1, table1.column2, table1.column3)
   ,      ??????
   from   myBaseTable1 table1
   ,      myBaseTable2 table2
   where  table1.joincolumn = table2.joincolumn
;

But in this case this is wrong, because myFunction should only be called once and the XML-output should be converted somehow in your manner to the virtual columns myfunction_result_1 thru 5.

A little help would be appreciated a lot.

Kind regards, Jan Willem

link

answered 02 Oct '15, 10:06

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231436
accept rate: 0%

Hi Jan,

In this case, you can create your view such as:

create or replace view myComplexView
 ( column1
 , column2
 , column3
 , column4
 , column5
 , column6
 , column7
 , column8
 , column9
 , column10
 , myfunction_result_1
 , myfunction_result_2
 , myfunction_result_3
 , myfunction_result_4
 , myfunction_result_5
 )
as select resultset.column1
    ,     resultset.column2
    ,     resultset.column3
    ,     resultset.column4
    ,     resultset.column5
    ,     resultset.column6
    ,     resultset.column7
    ,     resultset.column8
    ,     resultset.column9
    ,     resultset.column10
    ,     myfunction_result_1
    ,     myfunction_result_2
    ,     myfunction_result_3
    ,     myfunction_result_4
    ,     myfunction_result_5
   from(
         select table1.column1
         ,      table1.column2
         ,      table1.column3
         ,      table1.column4
         ,      table1.column5
         ,      table1.column6
         ,      table2.column1 as column7
         ,      table2.column2 as column8
         ,      table2.column3 as column9
         ,      table2.column4 as column10
         ,      myFunction (table1.column1, table1.column2, table1.column3) as functionOutput
         from   myBaseTable1 table1
         ,      myBaseTable2 table2
         where  table1.joincolumn = table2.joincolumn
     ) resultset
     , xmltable( '/root'
                passing resultset.functionOutput
                columns 
                    myfunction_result_1       VARCHAR2(4000) path 'myfunction_result_1',
                    myfunction_result_2       VARCHAR2(4000) path 'myfunction_result_2',
                    myfunction_result_3       VARCHAR2(4000) path 'myfunction_result_3',
                    myfunction_result_4       VARCHAR2(4000) path 'myfunction_result_4',
                    myfunction_result_5       VARCHAR2(4000) path 'myfunction_result_5'
              )
;

Hope this helps,
Ibrahim

link

answered 02 Oct '15, 10:29

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

Many thanks Ibrahim. This is really incredible... Kind regards, Jan Willem

(02 Oct '15, 10:51) Jan Willem V...

Hi Jan,

You are welcome, I'm glad that it helped.

Regards,
Ibrahim

(02 Oct '15, 11:00) Ibrahim Sand... ♦♦

Hi Ibrahim, this method works!

Perhaps it is important to know that myFunction has to return XMLTYPE otherwise it will not work. In my case myFunction first builds up a variable of varchar2 and in the end that is return with "return xmltype(v_varchar)".

Kind regards, Jan Willem

(03 Oct '15, 12:08) Jan Willem V...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×5
×3
×2
×2

Asked: 02 Oct '15, 04:02

Seen: 1,266 times

Last updated: 03 Oct '15, 12:08


© Copyright Gerger 2017