Very Computer

we have a list of variables that users can select. These vars will be
columns in the dataset the user wants to see. We can easily create a
string
with this information that holds the select statement needed to get
the
information.

The big problem is that the columns in the select statement are not
constant. A column that is the third column in one query can be the
fifth or
the tenth column in the next query... simply because the user selected
more
variable he wants to see...

When you have to create a cursor with this information you get into
trouble
because you have to know the column names of the columns in your
select
string plus the local variables in which to store the information
during the
cursor loop... basically it is a different cursor every time !!!!!!

The only solution I could think of was to parse this select string
(the only
information we have) into a number of flag variables (true for a
selected
column and false for an unselectd one). You could then do a number of
if-then's to run through al the possible columns and create a cursor
column
only if the column was selected by the user. During the cursor loop
you
could use these flags again  when you're printing the columns to the
screen...

If somebody can point out the error of my reasoning and solve the
problem
much easier, I will be most happy !

Rudie Missoorten
Froukemaheerd 240
9736 RV Groningen
050-5421569

Hi Rudie,

have a look at the build-in PL/SQL package dbms_sql. dbms_sql.parse()
will do what you need. Basically you create a string containing your
dynamically generated DML/DDL (yes, you can do DDL) statement, open a
cursor, parse the string into the cursor and off you go. For example:

        curvar := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE( curvar,
                'SELECT ' || somecols ||
                ' FROM ' || sometables ||
                ' WHERE ' || someconstraints,
                DBMS_SQL.NATIVE );
        [...]

        Hope it helped, Gerhard.