[Q] Constructing a Cursor in PL/SQL from a string passes from Pro*C

Hope someone would give me pointer/direction to the problem i am facing with.

Currently, i have a Pro*C program which summarizes a set of tables's data
and puts the result into temp. tables for later reporting purpose.

[Quoted] The program logic is kinda simple. A cursor is created by a big SQL statement then it is looped thru record by record, meanwhile each fetched row needs to do some processing on. ie, accessing at most 3 tables from database. Then writes to 2 tables.

The problem now is i am told the performance is kinda poor. it now runs 1 hour to 3 hours depending on the load of the server. (i wanted to get a more accurate statistics but this is another question)

What i am planning to do is to move all processing from the Pro*C to Pl/SQL.
Which means i have a big PL/SQL block in the Pro*C program. I don't know how
to declare a cursor in the pl/sql if the SQL statement is a string. ie, currently, my sql stmt is declared as varchar sqlstmt[5000] in C; because, conditions are not known until run time. have to construct from argv[1]...argv[n]

i try to use dbms_sql package with no success. if anyone knows how to do please shows me how.

thanks in advance

larry

larryleung_at_poboxes.com Received on Sat Apr 25 1998 - 16:25:38 CEST