Thanks for you reply, tho not quite the answer I was looking for :(

>> Hence the reliance on dbms_sql cursors which
can be described and programatically bound to <<

Could you please elaborate on this? How would we use that in HTMLDB?

Appreciate the insight into HTMLDB.

Tom, is the method of using a "plsql block returning a query" the only option? I too would like to have all applications - Java, HTML DB or otherwise - share the same functions. Is this a feasible goal? If I understand correctly, it is possible but the non-HTML DB apps would lose some elegance, and maybe some efficiency. This is because Java, for example, would have to obtain a query string from the function, then call another function which uses dynamic SQL to execute the query represented by the string.

I had recently shown a developer an example from another AskTom question, as an example of what he could try to get working in HTML DB:

tkyte@OSI1.WORLD> create or replace type myScalarType as object
2 ( x int,
3 y date,
4 z varchar2(25)
5 )
6 /
Type created.

tkyte@OSI1.WORLD> create or replace type myTableType as table of myScalarType;
2 /
Type created.

tkyte@OSI1.WORLD> create or replace
2 function demo_proc( p_start_row in number,
3 p_end_row in number )
4 return myTableType
5 as
6 l_data myTableType := myTableType();
7 l_cnt number default 0;
8 begin
9 for x in ( select * from emp order by sal desc )
10 loop
11 l_cnt := l_cnt + 1;
12 if ( l_cnt >= p_start_row )
13 then
14 l_data.extend;
15 l_data(l_data.count) :=
16 myScalarType( x.empno,
17 x.hiredate,
18 x.ename );
19 end if;
20 exit when l_cnt = p_end_row;
21 end loop;
22
23 return l_data;
24 end;
25 /
Function created.

tkyte@OSI1.WORLD> select *
2 from the ( select cast( demo_proc(2,6) as mytableType )
3 from dual ) a
4 /

I suggested that in HTML DB he use a query similar to what you've got in the last four lines of this example -- the query calls a function which returns a Table Type, etc. First he would need to create the scalar object type, the table type, and the procedure. Would this not work? It seems you are saying that HTML DB would need to know the returned columns before runtime (I'm not experienced with HTML DB).

Any insight would be much appreciated.

"if you could write a routine to take ANY refcursor and

a) figure out how many columns it has...
b) what types they are
c) what their names are
d) and then fetch them <<<== really hard, this is done at compile time!"

ref cursor in PL/SQL = ResultSet in Java

Unless I am missing something,
this could be done in Java (and then invoked from PL/SQL
did not test this part - may be some issue here that I am
overlooking)

Consider the following (not complete and bullet proof
but demonstrates the idea)

-------------------
import java.sql.*;
import oracle.jdbc.*;
class DynamicQuery
{
public static void main(String args[]) throws SQLException
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection
("jdbc:oracle:thin:@rmenon-lap:1521:ora10g", "scott", "tiger");
String selectStmt = "select " + args[0] + " from emp";
System.out.println( "ARGS = " + args[0] );
System.out.println( "query = [" + selectStmt + "]" );
pstmt = conn.prepareStatement( selectStmt );
rset = pstmt.executeQuery();
printResultSet( conn, rset );
}
finally
{
pstmt.close();
conn.close();
}
} // end of main()
public static void printResultSet( Connection conn, ResultSet rset )
throws SQLException
{
ResultSetMetaData rsmd = rset.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
System.out.println( "num of columns: " + numberOfColumns );
while( rset.next() )
{
String rowValue = "";
for ( int j=0; j < numberOfColumns; j++ )
{
int columnType = rsmd.getColumnType( j+1 );
Object value = null;
switch( columnType )
{
case OracleTypes.VARCHAR:
value = rset.getString( j+1 );
break;
case OracleTypes.NUMBER:
value = new Integer( rset.getInt( j+1 ) );
break;
}
if( j == 0 )
rowValue = rowValue + value;
else
rowValue = rowValue + ", " + value;
}
System.out.println( rowValue );
}
}
} // end of program
----------------
B:>java DynamicQuery ename,empno
ARGS = ename,empno
query = [select ename,empno from emp]
num of columns: 2
SMITH, 7369
ALLEN, 7499
WARD, 7521
JONES, 7566
MARTIN, 7654
BLAKE, 7698
CLARK, 7782
SCOTT, 7788
KING, 7839
TURNER, 7844
ADAMS, 7876
JAMES, 7900
FORD, 7902
MILLER, 7934

B:>java DynamicQuery ename
ARGS = ename
query = [select ename from emp]
num of columns: 1
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

scott@ORA10G> create or replace and compile
2 java source named "demo"
3 as
4 import java.sql.*;
5 import oracle.jdbc.*;
6 public class demo
7 {
8 public static void print_result_set( ResultSet rset )
9 throws SQLException
10 {
11 ResultSetMetaData rsmd = rset.getMetaData();
12 int numberOfColumns = rsmd.getColumnCount();
13 System.out.println( "num of columns: " + numberOfColumns );
14 while( rset.next() )
15 {
16 String rowValue = "";
17 for ( int j=0; j < numberOfColumns; j++ )
18 {
19 int columnType = rsmd.getColumnType( j+1 );
20 Object value = null;
21 switch( columnType )
22 {
23 case OracleTypes.VARCHAR:
24 value = rset.getString( j+1 );
25 break;
26 case OracleTypes.NUMBER:
27 value = new Integer( rset.getInt( j+1 ) );
28 break;
29 }
30 if( j == 0 )
31 rowValue = rowValue + value;
32 else
33 rowValue = rowValue + ", " + value;
34 }
35 System.out.println( rowValue );
36 }
37 }
38 }
39 /

Java created.

scott@ORA10G> show errors;
No errors.
scott@ORA10G> create or replace procedure demo_from_plsql( p_cursor in sys_refcursor )
2 as
3 language java
4 name 'demo.print_result_set( java.sql.ResultSet)';
5 /

Procedure created.

scott@ORA10G> show errors;
No errors.
scott@ORA10G> set serveroutput on
scott@ORA10G> exec dbms_java.set_output( 100000);

PL/SQL procedure successfully completed.

scott@ORA10G> declare
2 l_cursor sys_refcursor;
3 l_cursor1 sys_refcursor;
4 begin
5 open l_cursor for select ename from emp;
6 demo_from_plsql( l_cursor );
7 open l_cursor1 for select ename, empno from emp;
8 demo_from_plsql( l_cursor1 );
9 end;
10 /
num of columns: 1
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
num of columns: 2
SMITH, 7369
ALLEN, 7499
WARD, 7521
JONES, 7566
MARTIN, 7654
BLAKE, 7698
CLARK, 7782
SCOTT, 7788
KING, 7839
TURNER, 7844
ADAMS, 7876
JAMES, 7900
FORD, 7902
MILLER, 7934

PL/SQL procedure successfully completed.

scott@ORA10G> spool off

forgot to sign it

"IN PLSQL -- htmldb is in PLSQL. true, could it call java stored procedures --
yes, but time the startup of that, time the memory consumption of that. It
defeats the environment."

I know htmldb is in PLSQL (have not used it but read about
it) and I knew the context you are
talking in. I just thought you may not have considered calling the java solution...After all invoking a java
stored procedure is also a sound technique provided
it solves the problem in the most efficient way
(in this case, may be not due to performance issues.)

Somewhere in asktom (cant find it now),I remember
having read in the context of your "desc" procedure about not being able to describe a ref cursor in PL/SQL
(which is what led me to believe that the java solution
may have escaped you in the above context)...
The Java based technique may work there as well...(performance not being of concern there.)

The technique using app contexts is, as you allude,
probably the best alternative in this case...

It is however a bit funny that you can do something
as basic as the above in Java but not in PL/SQL.

Did not mean to upset you in anyway:)
Never suggested or meant that someone needs to
rewrite html db or anything...

"I know all about java in the database..."

And I know that since I have the Expert one-on-one and
you cover it very well in chapter 19...

"It is funny that plsql is plsql and java is java and night is night and day is
day.... And night is not day, and java isn't plsql and plsql isn't java."

Not sure what that means;) All I said was I expect
this functionality to be in PL/SQL if it is already
there in Java. And the simple reason I expect it
to be in PL/SQL is that it is something close to db
- it is about ref cursors after all..

Just a bit disappointed that it is not in PL/SQL -
that is all...

HTMLDB is really an amazing system. I installed it in a 9.2.0.4 database on a little Sun Ultra-2 with 2 other databases on it (total RAM 1G) and the added load on the system is not even perceptible.

How the heck does HTMLDB manage to scale so well? It must have an ingenious architecture underlying it? Do you have any details you can share with us?

Thanks

Tom,

Thanks for the answer you gave me a few weeks ago. I had a developer look into your suggestion and he says, unfortunately, that it is not feasible for our purposes. The reason is that our application will be using Crystal Reports. Crystal Reports, for some odd reason, only allows procedure calls with an IN OUT REF CURSOR type parameter, along with any other parameters. No functions allowed.

I am thinking of abandoning our hopes for "one function (now one procedure) fits all". I would feel more comfortable with my decision if you, the Oracle Man, confirmed that it is not possible to have such a procedure work with HTML DB. Even if it is possible, I'm guessing the cure is going to be worse than the disease at this point.

Thanks!

Interesting as I had the exact same problem with our developers. They wanted to use REF CURSORs with HTMLDB. Were very insistant as it is an app dev "standard".

Why use REF CURSORs? Two answers come to mind:
- SQL abstraction. Have the developer deal with an API call
and not the actual SQL and concurrency and transaction
and SQL bind variables and other issues.
- Performance. Instead of shipping the entire SQL across
from the client to the server, a PL/SQL call given a ref
cursor pointer is faster.

In PL/SQL what do you deal with? In PL/SQL is either of these two answers/problems applicable?

I would be very concerned having to deal with PL/SQL developers that needs to deal with Oracle SQL via an REF CURSOR/SQL abstraction layer (kinds of raises the question why have that SQL bit in the PL/SQL then?). The lower network bandwidth footprint provided by REF CURSORs is not applicable as the PL/SQL code runs in the Oracle dedicated/shared server process.

REF CURSORs are great - when used under the right circumstances. But it is not a one-tool-fit-all-solutions.

Besides the issue of not having a describe function in PL/SQL for REF CURSORs (ref cursors in the context of PL/SQL are DBMS_SQL cursors), there are other issues with using it with HTMLDB specifically - prime for me is the ease of maintenance of HTMLDB reports.

Of what use is the (very useful, easy use) HTMLDB design interface when a report region does not contain the SQL and requires one to dig deeper in a very counter intuitive way to find the actual SQL to correct or modify it? How much more difficult will it make maintenance and debugging? How much more time will it add to development? All these cons for what benefit - just for the sake of using REF CURSORs as a single "standard" across applications?

Standards should never interfere with common sense and logic. :-)

I wasn't calling the use of REF CURSORs a "problem" with my developers, in fact I welcome it. Let me explain our situation...

What we were striving for was a single interface for multiple systems. We have three sets of development (I won't go there): a Delphi team, a .NET team and now an HTMLDB team.

The .NET team has been using packages and REF CURSORs exclusively since they started. At first I found it unusual, but we've been finding that applying fixes to that system (where SQL and PL/SQL is involved) is unbelivably easy - as opposed to making changes to their app and re-deploying. Every re-deploy for them is a nightmare. So changing/updating a query is as simple as modifying the underlying package body and *poof* the problem is fixed.

Since then, the Delphi team has gotten on-board with REF CURSORs... actually, they are putting more and more processing into the database, where it belongs. And they are loving it.

Lastly, we've deployed an HTMLDB application. Since each of these three systems (languages) use the same data and process [generally] the same things, we are attempting to put together a single interface for all the systems. Our stumbling point for that last sentence is that HTMLDB doesn't do REF CURSORs to return a result set.

I fully agree that standards should never interfere with common sense and logic. In this particular case, the standards *are* the common sense and logic.

We're looking into the suggestion that Tom provided a few posts back.

Dean, my reference to "a problem" was that we often grab the first and most familiar tool from the toolbox. As is the case with REF CURSORs and the Oracle developers I support.

For Delphi (an old personal favourite of mine) and .NET, using REF CURSORs make a lot of sense. In fact, Delphi can be used to develop .NET - thus I see that your Delphi and .NET teams will share a lot in common with standards.

For HTMLDB.. not. The core problem: HTMLDB needs to render the cursor contents. For that it needs to be able to describe the cursor's contents. This is simply not possible in PL/SQL using REF CURSORs. Period.

This does not mean you cannot roll your own. You can use REF CURSORs. You can use Java calls to describe them. You can write your own HTML renderer using UTL_HTP. And use HTMLDB dynamic PL/SQL regions for REF CURSOR reports.

But you loose a major amount of the flexibility and features and ease of use provided by HTMLDB. And for what? The ability to make REF CURSORs a standard across all development architectures? Thus my reference to common sense and logic.

The hammer in the toolbox makes a poor screwdriver. A screwdriver though makes an excellent chissel - but only if you do not have one in the toolbox. ;-)

For people out there that have experience with using both, how does Delphi compare with HTMLDB as a application development environment? They both seem to have the same "widgets", same declarative, procedural abilities, etc.

Comments? Thanks

VA from New Jersey, USA asked:

> For people out there that have experience with using both,
> how does Delphi compare with HTMLDB as a application
> development environment?

How does a truck compare to a pickup? Depends entirely what you want to use it for.

With Delphi you can write anything from COM objects, DLLs, NT services, games, stand-alone clients, web servers, POP servers, SMTP servers, client-server, etc etc. Heck, I even ported some of my Delphi apps to Linux (using Kylix) in less than an hour.

HTMLDB is a specialist tool. And do what it do (database centric web applications in the Oracle context) exceedingly well. Not something that I would consider doing in Delphi today - which I had done in the past using Delphi before discovering MOD_PLSQL.

Writing web services and CGIs for Oracle in Delphi? Nah. HTMLDB is a million times better at that.

> They both seem to have the same "widgets", same
> declarative, procedural abilities, etc.

Which is great.. The Delphi approach to an IDE and dealing with classes and objects, is still the best darn approach in my book. This personal opinion is backed up by the fact that Microsoft made the Delphi project lead an offer he could not refuse, which resulted in C# - and the C# language concepts and IDE roots clearly show its Delphi heritage.

Having HTMLDB using some of these features simply shows that HTMLDB is on the same sensible track of having an intuitive web-based IDE.

It sounds to me that was Dean is saying is not that he has his heart set on using ref cursors, but that he is committed to writing all database processing code one time, in one place, as close to the data as possibl, using pl/sql and packages. As has been discussed before, this is a great way to go about things, because the popular applications that interact with the data change over time.

The problems with just putting all of your sql in the htmldb app is that htmldb is still just an app, just another popular tool of the moment (although a really tremendous one, I love it!). But you would be rewriting your db code, taking it farther from the data it belongs with, spending time in the rewrite itself, and introducing the ability to make mistakes or at the very least have inconsistencies with all that new code.

I need to investigate Tom's earlier suggestion also, but it seems to me that you want to keep your sql in packages, and have one set of data processing procedures. But you may have more than one set of code that returns the results back to the app, custom built for the app that the data is being returned for, kind of like a device driver. But still this code should also be in plsql, but it may use ref cursors and may use dbms_sql, but it should be trivial code because all it does is return data that has already been processed by other packages.

Incidentally, the benefits I see in putting all of your sql in the htmldb app is that you can whip things up really easily, whether for proof of concept or for smaller projects. And it opens the doors of development to more of the "high end business users" but puts their work in a controlled environment, rather than scattered amongst 15 different tools on 100 different people's pcs, so that you can examine and access their work and use it as documentation and building blocks for developing in plsql the enterprise system.

Anyway just some thoughts as I continue to formulate my personalized best practices development philosophies.

Yes Scot, your first paragraph pretty much wraps up what I'm trying to accomplish. I certainly won't shoe-horn a solution into something that doesn't fit, and if I can't use REF CURSORs in HTMLDB, so be it.

I'm more than willing to overlook this "problem" because HTMLDB is a real winning solution. Moving to HTMLDB from our classic application (which by the way was written in Delphi) has reduced database resource usage by several orders of magnitude. I couldn't be happier.

In addition to that, now we don't have to deploy new executables and we don't have to install an Oracle client on everybody's desktop anymore.

-Dean

When you say "we could do it -- but you cannot.", is that "we" meaning "Oracle"? That it can be done, just not in the current version of PL/SQL? That when you designed HTMLDB, you went with what you had available at the time that made the most sense? I'm a little confused.

I think putting SQL in the app, in this case the app being in HTMLDB(sure it's "in the database" technically, but I see it as separate) is a departure from the packaged application development that's been preached.

What's occurring in SQL*Plus to handle and render the ref cursor?

scott@ORALOCAL> create or replace package emp_pkg
2 as
3 type emp_rc is ref cursor;
4 in_empno number;
5 procedure get_emp(in_empno in number, emp_res in out emp_rc);
6 end emp_pkg;
7 /

Package created.

Elapsed: 00:00:00.00
scott@ORALOCAL>
scott@ORALOCAL> create or replace package body emp_pkg
2 as
3 procedure get_emp(in_empno in number, emp_res in out emp_rc)
4 is
5 begin
6 open emp_res for select empno,ename, job from emp where empno = in_empno;
7 end get_emp;
8 end emp_pkg;
9 /

Package body created.

Elapsed: 00:00:00.00
scott@ORALOCAL>
scott@ORALOCAL>
scott@ORALOCAL> var rc refcursor
scott@ORALOCAL> exec emp_pkg.get_emp(7902,:rc);

PL/SQL procedure successfully completed.


EMPNO ENAME JOB
---------- ---------- ---------
7902 FORD ANALYST

Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
scott@ORALOCAL>

Tom,

I've been using DBMS_SQL to build a query builder engine and I'm butting my head against what seems a limitation with DBMS_SQL. You mention in this note that you can use the DBMS_SQL.describe method to describe the query and bind based on the returned datatype; however, in looking at the column_value and array_value methods it does not seem that they support anything other than scalar / primitive datatypes: VARCHAR2, NUMBER, CLOB, etc.

Am I left with using your context option listed in another note, or am I missing something?

Thanks for your response in advance?

Tom,

Thanks for your response.

I have essentially 2 types of UDT's one that contains scalar datatypes as well as other embedded Object Types and the other that is a nested table of the first. These UDT's are for structure only, since I'm storing information in relational tables and presenting this information in User Defined Types to a java consumer. He is using jPublisher to interrogate the UDT and create java structure on the mid-tier to accept this data. Our interface allows both singleton CRUD operations as well as batch. The Retrieve methods contain a filtering mechanism, which allows the developer restrict the amount of data brought back without actually having to put SQL Statements in the mid-tier. We have been able to fix the SELECT statement (i.e. it does not vary), so the SQL isn't really method 4; however, since execute immediate doesn't support a dynamic bind condition (I'm pretty sure that 10 g still doesn't support it on the using clause, right?), we opted to use DBMS_SQL (This was before we started using the Object and Collection Types and the interface was still relational). In trying to add these new methods, I've run into several limitations and problems with DBMS_SQL. In reviewing many of your responses dealing with dynamic sql, I've found references to possible alternative using DBMS_SESSION.set_context, which results in a complete rewrite of code that I have tested and vetted. However, before throwing the baby out with the bathwater, I thought that I would see if there was anything that I was missing.

Am I missing something with DBMS_SQL? Does it support array and column bind in the conditional as well as the return SELECT or am I destined to orphan another baby?

Thanks for your response in advance.

Tom,

I paired down the object types and SQL, so please forgive any minor syntax errors, but this should give you an idea of what I'm trying to accomplish. The SQL statement below is really in a VARCHAR2(4000) field and I want to execute it in dbms_sql.

Is this possible?

The table:

CREATE TABLE EMPLOYEES
(
EMP_ID NUMBER(10) NOT NULL,
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
RESUME VARCHAR2(240 BYTE),
)
/

The object types:

CREATE OR REPLACE TYPE EMP_OID_OOT AS OBJECT(
EMP_ID NUMBER(10)
,EMPNO NUMBER(4)
);
/

CREATE OR REPLACE TYPE EMP_OOT AS OBJECT(
EMP_OID EMP_OID_OOT
,EMPNO NUMBER(4)
,ENAME VARCHAR2(10)
,JOB VARCHAR2(9)
,HIREDATE DATE
,SAL NUMBER(7,2)
,COMM NUMBER(7,2)
,RESUME VARCHAR2(240)
);
/

CREATE OR REPLACE TYPE EMP_ONT AS TABLE OF EMP_OOT
/

Here's the SQL that I want to execute dynamically:

SELECT
THE_ROWNUM
,THE_ROWID
,emp_oot(
emp_oid
,EMPNO
,ENAME
,JOB
,HIREDATE
,SAL
,COMM
,RESUME
) emp_obj
FROM (
SELECT ROWNUM THE_ROWNUM
,EMP20.*
FROM (
SELECT EMP1.ROWID THE_ROWID
,emp_oid_oot(
EMP1.EMP_ID
,EMP1.empno
,EMP1.ename
) emp_oid
,EMP1.*
FROM EMPLOYEES EMP1
,EMPLOYEES EMP20
,DEPARTMENTS DEP10
WHERE EMP1.EMP_EMP_ID = EMP20.EMP_ID (+)
AND EMP1.DEP_DEP_ID = DEP10.DEP_ID
-- Dynamic SQL conditional statements go here...
) EMP20
WHERE ROWNUM <= :END_NDX
) EMP30
WHERE EMP30.THE_ROWNUM >= :START_NDX
/

Tom,

I was actually wanting to use the following syntax to poplulate the EMP_ONT

SELECT CAST( MULTISET(
... above SQL expression ...
) EMP_ONT ) from dual

but that is a bit of detail that I left...

Back to the point...

The reason I was using DBMS_SQL in the first place was that execute immediate doesn't allow a dynamic list on the USING clause (i.e. you have to know how many bind arguements at compile time. I don't know that list. I only know them at runtime). Your suggestion will work with globals, if

1. I put the entire PL/SQL block in a text string,
2. build the using clause dynamically within it, and
3. use an execute immediate to populate the global variables in the specification.

This was an option that I was considering. However, I was hoping that there was a more elegant, encapsulated solution. Since doing so exposes implementation details to my users, creates unwanted dependencies between packages that reference these variables, and allows developers to unwantingly modify package information.

I was hoping that DBMS_SQL had some feature that would allow binding to UDT column.

Is this possible?

Thanks for your response in advance.

Tom,

I read your response again and I see that you answered that it couldn't.

Thanks for the help.

Regards.

I had this requirement for an application developed in HTMLDB, but was just wondering about the concept / approach to use.

We have a requirement where the Users wanted to have an option of creating an item dynamically. We developed and deployed a very simple application but the users want to have the flexibility of adding new columns (without vaildations and processing - just data) to some of the existing regions. They are not IT people and want a simple interface to add columns without IT involvement.

I thought of two options.
(a) To pre-create around 10 items in the table with column names as customize1...customize10 with varchar2(1000). Have a configuration table which stores the column names and Label. Create a Report based on a Pl/SQL function returning query, and using HTMLDB_ITEM to dynamically populate them. Iam not very keen about this approach as do not want to create unnecessary columns and have to do a lot of manual processing for the data manipulation.
a.1. Can we create a form (not a report) based on a pl/Sql function returning query.

(b) To have an interface which does an "Alter Table .... add column ...." to create the new column. In this case how can we include this column to an existing form. Can we use HTMLDB_ITEM to add (create) columns to an existing region?

(c) Any other suggestion ?

It's not a good idea to try and create a 'Generic' application, but the application is quite simple and small and there is a cost involved in any changes made which the users are not willing to pay.

So you mean either of the approaches is alright. That I can either have a certain number of additional columns in the 5 (n) tables (OR) that I have an interface for the end users and call 'ALTER TABLE' through a button ?

I had raised it in HTMLDB Forum on how to handle in HTMLDB but didn't get a response yet.

Dear Tom,

Thank you for description of weak cursor type limitations, but I would like to add a question about strong ref cursor type

After defining strong ref cursor in the package the calling application can get the type of the expected variables via, for example, user_arguments. 

Is there a possibility to effectively use functions that return strong ref cursors in htmldb? 

For example, can I use the function like this (test_cur.getData) in html db?

Thanks in advance!

SQL> create or replace package test_cur is
  2  
  3    cursor getDataCur is select object_id, object_name from all_objects;
  4    
  5    type data is ref cursor return getDataCur%rowtype;
  6    
  7    function getData return data;
  8    
  9  
 10  end test_cur;
 11  /

Package created.

SQL> create or replace package body test_cur is
  2  
  3    function getData return data is
  4      result data;
  5    begin 
  6      open result for 
  7           select object_id, object_name from all_objects;
  8      return result;
  9    end;
 10    
 11    
 12  end test_cur;
 13  /

Package body created.

SQL> select object_name, package_name, argument_name, data_level, data_type, data_length
  2    from user_arguments 
  3   where package_name = 'TEST_CUR'
  4   order by object_id, object_name, data_level, position
  5  /

OBJECT_NAME     PACKAGE_NAME    ARGUMENT_NAME   DATA_LEVEL DATA_TYPE       DATA_LENGTH
--------------- --------------- --------------- ---------- --------------- -----------
GETDATA         TEST_CUR                                 0 REF CURSOR
GETDATA         TEST_CUR                                 1 PL/SQL RECORD
GETDATA         TEST_CUR        OBJECT_ID                2 NUMBER                   22
GETDATA         TEST_CUR        OBJECT_NAME              2 VARCHAR2                 30