Re: Problems making dynamic cursor for webserver

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.

rmissoort_at_wisdom.nl (Rudie Missoorten) wrote:

>
>
>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
 

-- 
 Dipl. Inform. Gerhard Möller  -- Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.DE
                                        ICQ PIN: 2804938
OFFIS                   | | | | | |     Tel.:    0441/9722-122
Escherweg 2             | | | | | |     Sekr.:   0441/9722-113 oder -101
D-26121 Oldenburg       |O|F|F|I|S|     Fax:     0441/9722-102