You Asked

Hi Tom,

I need to translate this sentence from Visual Basic to Oracle Forms 5.0.6.22.0.

For Each fld In recset.Fields
...
Next fld

Where
recset.Fields: it's a collection that contains all stored Field objects of a Recordset object.
fld: it's a Field of the collection.

I've seen a similar sentence with a record from a cursor,

FOR record_name IN
{ cursor_name [(cursor_parameter_name[,
cursor_parameter_name]...)]
| (select_statement)}
LOOP
seq_of_statements
END LOOP [label_name];

but I'm working with a recordset from a MapObjects OCX form ESRI. Can it be done?

Thank you very much.

and Tom said...

This functionality is available in the EXEC_SQL builtin package in forms (DBMS_SQL on the server, use EXEC_SQL when in forms, DBMS_SQL when in a stored procedure).

It allows you to iterate over rows by fetching them and to iterate over columns using a positional index (eg: you can ask for the i'th column of a given row).

Here is an example. If I had a form with a block T and a text item X (and X was sufficiently large), this would take all of the rows in EMP and put a comma separate list of values into the field :t.x

declare
connection_id EXEC_SQL.CONNTYPE
default exec_sql.default_connection;
cursorID EXEC_SQL.CURSTYPE;

nIgn number;
columnValue varchar2(4000);
ncols number;
sep varchar2(1);
colName varchar2(40);
colLen number;
coltype number;
tmp varchar2(20000);
BEGIN
IF exec_sql.is_connected = FALSE THEN
Message('No primary connection. Please connect.');
RETURN;
END IF;

cursorID := EXEC_SQL.OPEN_CURSOR;
EXEC_SQL.PARSE(cursorID, 'select * from emp', exec_sql.V7);
nIgn := EXEC_SQL.EXECUTE(cursorID);

for i in 1 .. 1000 loop
begin
exec_sql.describe_column( connection_id, cursorId, i,
colName, colLen, colType );
exec_sql.define_column(cursorID, i, columnValue,
4000 );
exception
when exec_sql.invalid_column_number then
ncols := i-1;
exit;
end;
end loop;

while (exec_sql.fetch_rows(cursorID) > 0)
loop
sep := '';
for i in 1 .. ncols loop
EXEC_SQL.COLUMN_VALUE(cursorID, i, columnValue);
tmp := tmp || sep || columnValue;
sep := ',';
end loop;
tmp := tmp || chr(13) || chr(10);
END LOOP;

EXEC_SQL.CLOSE_CURSOR(cursorID);
EXEC_SQL.CLOSE_CONNECTION;
:t.x := tmp;

END;

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

why this exits oth loops?

A reader, February 22, 2004 - 11:58 am UTC

Hi

I have this pl/sql code

declare
xx number := 1;
begin
for i in (select 1 from dual union all select 2 from dual)
loop
for x in (select username from user_tab_partitions)
loop
exit when xx > 1;
dbms_output.put_line(x.table_name);
xx := xx + 1;
end loop;
end loop;
end;
/

I thought exit only exits the inner loop but it doesnt, it exits both... I even tried using labels but it does not work neither

What I want is exit nly the inner loop but proceed processing the outer loop... How can I achive this?

February 22, 2004 - 12:44 pm UTC

ops$tkyte@ORA9IR2> declare
  2     xx number := 1;
  3  begin
  4     for i in (select 1 from dual
  5               union all
  6               select 2 from dual)
  7     loop
  8         dbms_output.put_line( 'top of loop 1' );
  9         for x in (select * from user_tab_partitions)
 10         loop
 11             dbms_output.put_line( 'top of loop 2' );
 12             exit when xx > 1;
 13             dbms_output.put_line(x.table_name);
 14             xx := xx + 1;
 15             dbms_output.put_line( 'bottom of loop 2' );
 16         end loop;
 17         dbms_output.put_line( 'bottom of loop 1' );
 18     end loop;
 19  end;
 20  /
top of loop 1
top of loop 2
MYEMP
bottom of loop 2
top of loop 2         <<<=======
bottom of loop 1      <<<=======
top of loop 1         <<<=======
top of loop 2
bottom of loop 1
 
PL/SQL procedure successfully completed.


It does just exit the inner loop.   that shows we went from the top of loop2 to the bottom of loop 1 and back to the top again...

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

why this exits oth loops?

More to Explore

PL/SQL demos

PL/SQL docs

Using a Recordset in a Loop Statement

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

why this exits oth loops?

More to Explore

PL/SQL demos

PL/SQL docs