Home » Developer & Programmer » Forms » populating multi-record block
populating multi-record block [message #80770] |
Tue, 19 November 2002 09:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Tyler
Messages: 123 Registered: January 2002
|
Senior Member |
|
|
Hello All,
I am not allowed to populate a multi-record db block with an "execute_query", I have to loop through the db and populate each record in the multi-block with a cursor loop value. How do I do this? I have written the loop, but how do I assign the value that is retrieved each loop to a single row of the multi-block?
s_status shows the current value of the loop, now how do I assign in to only one row of the multi-record block?
DECLARE
s_sqlstr VARCHAR(1000);
s_status VARCHAR(100);
connection_id EXEC_SQL.CONNTYPE;
c_cur EXEC_SQL.CursType;
c_col1Len NUMBER;
i_rows INTEGER;
n_counter NUMBER := 0;
BEGIN
s_sqlstr := 'SELECT marital_status FROM marital_status_class';
connection_id := EXEC_SQL.DEFAULT_CONNECTION;
c_cur := EXEC_SQL.OPEN_CURSOR;
EXEC_SQL.PARSE(c_cur, s_sqlstr);
c_col1Len := 100;
EXEC_SQL.DEFINE_COLUMN(c_cur, 1 , s_status,c_col1Len );
WHILE n_counter < 10 LOOP
n_counter := n_counter+1;
i_rows := EXEC_SQL.EXECUTE(c_cur);
WHILE EXEC_SQL.FETCH_ROWS(c_cur) > 0 LOOP
EXEC_SQL.COLUMN_VALUE(c_cur, 1, s_status);
message(s_status); <-------------------------------------------------------use this var. to populate
END LOOP;
END LOOP;
EXEC_SQL.CLOSE_CURSOR(c_cur);
EXCEPTION
WHEN EXEC_SQL.INVALID_CONNECTION THEN
message('ERROR:Unexpected Invalid Connection error from EXEC_SQL');
WHEN EXEC_SQL.PACKAGE_ERROR THEN
message('ERROR:Unexpected error from EXEC_SQL: '
||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(connection_id))
||EXEC_SQL.LAST_ERROR_MESG(connection_id)|| 'Query string = ' || s_sqlstr);
END;
|
|
|
Re: populating multi-record block [message #80771 is a reply to message #80770] |
Tue, 19 November 2002 10:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
F. Tollenaar
Messages: 64 Registered: November 2002
|
Member |
|
|
Just out of curiosity. Why can't you use execute_query ??
You can always change queryability (sic) programmatically:
begin
go_block(block_name)
set_block_property(block_name, query_allowed, true);
execute_query;
set_block_property(block_name, query_allowed, false);
exception
when others
then
set_block_property(block_name, query_allowed, false);
raise;
end;
hth
Frank
|
|
|
Re: populating multi-record block [message #80775 is a reply to message #80770] |
Wed, 20 November 2002 00:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
F. Tollenaar
Messages: 64 Registered: November 2002
|
Member |
|
|
This procedure fills a multi-record block of emp with data from emp.
procedure Jan IS
l_sqlstr VARCHAR(1000);
r_emp emp%rowtype;
l_connection_id EXEC_SQL.CONNTYPE;
l_cur EXEC_SQL.CursType;
l_rows INTEGER;
BEGIN
l_sqlstr := 'SELECT * FROM emp';
l_connection_id := EXEC_SQL.DEFAULT_CONNECTION;
l_cur := EXEC_SQL.OPEN_CURSOR;
EXEC_SQL.PARSE(l_cur, l_sqlstr);
EXEC_SQL.DEFINE_COLUMN(l_cur, 1 , r_emp.empno);
EXEC_SQL.DEFINE_COLUMN(l_cur, 2 , r_emp.ename, 10);
EXEC_SQL.DEFINE_COLUMN(l_cur, 3 , r_emp.job, 9);
EXEC_SQL.DEFINE_COLUMN(l_cur, 4 , r_emp.mgr);
EXEC_SQL.DEFINE_COLUMN(l_cur, 5 , r_emp.hiredate);
EXEC_SQL.DEFINE_COLUMN(l_cur, 6 , r_emp.sal);
EXEC_SQL.DEFINE_COLUMN(l_cur, 7 , r_emp.comm);
EXEC_SQL.DEFINE_COLUMN(l_cur, 8 , r_emp.deptno);
l_rows := EXEC_SQL.EXECUTE(l_cur);
go_block('EMP');
first_record();
WHILE EXEC_SQL.FETCH_ROWS(l_cur) > 0
LOOP
EXEC_SQL.COLUMN_VALUE(l_cur, 1, r_emp.empno);
:emp.empno := r_emp.empno;
EXEC_SQL.COLUMN_VALUE(l_cur, 2, r_emp.ename);
:emp.ename := r_emp.ename;
EXEC_SQL.COLUMN_VALUE(l_cur, 3, r_emp.job);
:emp.job := r_emp.job;
EXEC_SQL.COLUMN_VALUE(l_cur, 4, r_emp.mgr);
:emp.mgr := r_emp.mgr;
EXEC_SQL.COLUMN_VALUE(l_cur, 5, r_emp.hiredate);
:emp.hiredate := r_emp.hiredate;
EXEC_SQL.COLUMN_VALUE(l_cur, 6, r_emp.sal);
:emp.sal := r_emp.sal;
EXEC_SQL.COLUMN_VALUE(l_cur, 7, r_emp.comm);
:emp.comm := r_emp.comm;
EXEC_SQL.COLUMN_VALUE(l_cur, 8, r_emp.deptno);
:emp.deptno := r_emp.deptno;
set_record_property(get_block_property('emp', current_record), 'EMP', status, query_status);
next_record;
END LOOP;
EXEC_SQL.CLOSE_CURSOR(l_cur);
EXCEPTION
WHEN EXEC_SQL.INVALID_CONNECTION
THEN
message('ERROR:Unexpected Invalid Connection error from EXEC_SQL');
WHEN EXEC_SQL.PACKAGE_ERROR
THEN
message('ERROR:Unexpected error from EXEC_SQL: '||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(l_connection_id)));
message(EXEC_SQL.LAST_ERROR_MESG(l_connection_id)|| 'Query string = ' || l_sqlstr);
message('');
END;
hth
Frank
|
|
|
Re: populating multi-record block [message #80777 is a reply to message #80775] |
Wed, 20 November 2002 08:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Tyler
Messages: 123 Registered: January 2002
|
Senior Member |
|
|
This code entails NEXT_RECORD... This built in can only be used when records already exist, not if you are trying to populate and unpopulated block.
Regardless I figured it out with the built in CREATE_RECORD and a loop...
But Thanks anyway...
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 02:13:34 CDT 2024
|