Creating dynamic views?

[DavidH]

I would like to create a dynamic table or view using DLL calls.

Say, that I have a machine connected to my computer through a serial cable. Upon request, I could go out and query this device about how it's doing. It turns out that 4 of 10 of the device's submachines are up and running, and each one of them has an individual rate of how well it is performing.

So when I run "select * from mysubmachines"
I want some kind of result like:

ID ItemsPerHour
--- ------------
123 56
456 98
789 32
897 45

I think the problem is that I don't know how to return a variable number of identic parameters from the DLL. (Could PL/SQL Tables be the solution to this problem?)

 

[Marco Kalter]

PL/SQL Tables could be a solution, but I'm not sure how you could turn this into a result set. I guess you would have to insert the data into a temporary table first if the result set functionality is a requirement. I could be wrong though...

------------------
Marco Kalter
Allround Automations

 

[DavidH]

I checked in the Oracle 8.0.5 documentation, and it seemed like PL/SQL-tables was not an acceptable parameter to use in a DLL call.
And the question of how to turn it into a result set still remains.

So, it does not seem possible. The only way to do it is to make multiple DLL calls, at least one for each row and the ID must exist in some other table in the database. And there is some overhead for each call...

 

[Marco Kalter]

What exactly do you mean by a DLL call? Where is this DLL and who calls it?

------------------
Marco Kalter
Allround Automations

 

[DavidH]

Oracle calls the DLL (create procedure as external).
I find nowhere in the "parameters" clause that I could put in a PL/SQL Table or some other kind of array (except for varchars).

[This message has been edited by DavidH (edited 12 December 2000).]

 

[Marco Kalter]

That is probably true, but I would imagine that you can use a global package variable for this PL/SQL Table. If the DLL and the caller share the same session (by using ExtProcShare) you can use package variables to pass information between the DLL and the caller.

In the same way you could use a cursor variable.

Finally the option of a temporary table, filled in the DLL, is also still open.

------------------
Marco Kalter
Allround Automations

 

[DavidH]

A global package as well as a temporary tables doesn't feel safe enough (what happens if two sessions request my view at the same time?)

And the 2nd question is how to create the view? I can't see how my procedure is going to be called only once, and at the same time return several rows of data?

[This message has been edited by DavidH (edited 14 December 2000).]

 

[Marco Kalter]

Each session has its own package state, so using package variables will not be a problem. Temporary tables in Oracle8i are also session specific.

Your second question seems like a real problem though. You would first need to call the DLL, and subsequently select rows from the view. I don't see how the view could present the data from a DLL-call, unless the maximum number of rows are known when the view is created and can be defined in the view definition through a union.

------------------
Marco Kalter
Allround Automations

 

[DavidH]

Okey, it seems like a dead end or at least too much trouble to try to do it this way.

Anyway, thanks for your support. I hope you're aware of, that this forum is one of the main reasons your components are worth buying!