Re: SQL problem...

Don't be shy, use DBMS SQL and create your cursors on the fly.

Do NOT assume that bind variables will help you out. When you use bind variables, the explain plan is different than when you hard code the value. I KNOW this doesn't make any sense, but it is true.

You can either create your text file on the client or server side. However, since you aren't talking about any significant amount of data, I would bring it all over to the client and use the text_io package in Forms.

--
Paul Dorsey
Dulcian, Inc.
www.dulcian.com
212 595 7223
Jimmy wrote in message <35DF4B61.43C4_at_comp.polyu.edu.hk>...


>Hello all,

> I developed Forms4.5 application. It involves running SQL statements in

>SQL*Plus. The Forms permit the user to choose column value to be

>retrieved with different where cause. The Forms may generate over 80

>statements as follows:

>

> select A, B from T where A = 'Test1';

> select A, B from T where A = 'Test2';

> select A, B from T where A = 'Test3';

> ...

> select A, B from T where A = 'Test80';

>

> The results of each statement is spooled to different files. I know

>that if I use bind variable in the where cause, the processing is

>faster. So I want to create the cursor. However, I find two problems in

>my situation.

>

> 1) How can I save the result of each statement in a text files? I don't

>want to use DBMS_PUTLINE since the rows retrieved may be over 100 in

>each statement. Moreover, the SQL statement may retrieve 50 columns in

>each statement.

>

> 2) The columns to be retrieved may be different in the next processing.

>For example, the second processing may:

>

> select A, B, C from T where A = 'Test1';

> select A, B, C from T where A = 'Test2';

> select A, B, C from T where A = 'Test3';

> ...

> select A, B,C from T where A = 'Test80';

>

> How can I solve these problems?

>

>Thanks,

>JImmy