OraFAQ Forum: Forms » inserting in 2 datablocks

Home » Developer & Programmer » Forms » inserting in 2 datablocks

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

inserting in 2 datablocks [message #121637]

Tue, 31 May 2005 05:21

jonmich005
Messages: 53
Registered: April 2005

Member

Hello

I have 2 datablocks, one with intervals and one with actions.
My datablock interval exists out of an id and a name. It has 20 reocrds.
Now my other datablock exists out of tl_grid_int_1 to tl_grid_int_20.

When I click on a button, I can insert a record in my datablock interval. I know how to do this but I can insert them in the middle of all my other items. So every item after my inserted record has to move one place to th right.

Now my question is how can I also do this for my other datablock.
I have the following code:

while act<100 loop
update lds_tl_grid
set tl_grid_int_2 = (select tl_grid_int_1 from lds_tl_grid where tl_grid_veh_id = :global.vehicle and tl_grid_scen = :global.scenario and tl_grid_id = act)
where tl_grid_veh_id = :global.vehicle
and tl_grid_scen = :global.scenario
and tl_grid_id = act;
commit;
execute_query;
act:=act+1;
end loop;

The tl_grid_int_2 has tobe replaced by something like tl_grid_int_||int_id, but this doesn't work.

Is this not possible in a select-statement?
What else can I do?

Greetings Jonas

Report message to a moderator

Re: inserting in 2 datablocks [message #121781 is a reply to message #121637]

Wed, 01 June 2005 00:36

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Looks like you need to use some dynamic SQL. Do a search on google for 'dbms_sql'.

Alternatively, go to http://www.oracle.com/technology/documentation/index.html. Press 'Search', enter 'dbms_sql', select the 10.1 documentation suite (first hot link), go down to 'PL/SQL Packages and Types Reference' and there are 53 references.

David

Report message to a moderator

Re: inserting in 2 datablocks [message #121807 is a reply to message #121781]

Wed, 01 June 2005 02:54

jonmich005
Messages: 53
Registered: April 2005

Member

I'm sory, I was forgotten that I already put this thread here.

And if I want to work with an array, how does this work???

I need to use the define array staement but how can I do this?

Greetings

Report message to a moderator

Re: inserting in 2 datablocks [message #121809 is a reply to message #121807]

Wed, 01 June 2005 03:05

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I don't know about the define array statement.

Just put the 'dbms_sql' inside the loop so that it builds the update statement that you need.

David

ps Use standard.commit to save your changes.

Report message to a moderator

Re: inserting in 2 datablocks [message #121811 is a reply to message #121809]

Wed, 01 June 2005 03:16

jonmich005
Messages: 53
Registered: April 2005

Member

Where do I have to put dbms_sql?
How does it know what of dynamic sql it should use?
The standard.commit doesn't work because iit asks if I want to save my changes at the end.

greets

Report message to a moderator

Re: inserting in 2 datablocks [message #121812 is a reply to message #121811]

Wed, 01 June 2005 03:25

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You replace your 'update' statement with a set of dbms_sql steps.

This is copied from the manual to which I referred.

declare
   stmt             varchar2 (200);
   emp_no_array     dbms_sql.Number_Table;
   emp_addr_array   dbms_sql.Varchar2_Table;
   c                number;
   dummy            number;
begin
   for i in 0 .. 9
   loop
      emp_no_array (i)    := 1000 + i;
      emp_addr_array (I)  := get_new_addr (i);
   end loop;

   stmt   := 'update emp set ename = :name_array where empno = :num_array';
   c      := dbms_sql.open_cursor;
   dbms_sql.parse (c, stmt, dbms_sql.native);
   dbms_sql.bind_array (c, ':num_array', empno_array);
   dbms_sql.bind_array (c, ':name_array', empname_array);
   dummy  := dbms_sql.execute (c);
   dbms_sql.close_cursor (c);
exception
   when others then
      if dbms_sql.is_open (c) then
         dbms_sql.close_cursor (c);
      end if;

      raise;
end;

Modify 'stmt' to concatenate your array number to the field name root.

The standard.commit DID work, it just means that something changed in the form AFTER that save.

David

Report message to a moderator

Re: inserting in 2 datablocks [message #121816 is a reply to message #121812]

Wed, 01 June 2005 03:40

jonmich005
Messages: 53
Registered: April 2005

Member

I really don't get it how I can work with that.
I'm sorry

Report message to a moderator

Re: inserting in 2 datablocks [message #121819 is a reply to message #121816]

Wed, 01 June 2005 03:52

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

In your initial post you have the statement:

update lds_tl_grid
   set tl_grid_int_2 = (select tl_grid_int_1
                          from lds_tl_grid
                         where tl_grid_veh_id = :global.vehicle
                           and tl_grid_scen = :global.scenario
                           and tl_grid_id = act)
 where tl_grid_veh_id = :global.vehicle
   and tl_grid_scen = :global.scenario
   and tl_grid_id = act;

and you said

Quote:

The tl_grid_int_2 has tobe replaced by something like tl_grid_int_||int_id, but this doesn't work.

so make the 'stmt' line look like

stmt :=
'update lds_tl_grid' ||
'   set tl_grid_int_' || int_id ||' = (select tl_grid_int_1' ||
'                          from lds_tl_grid' ||
'                         where tl_grid_veh_id = <:global.vehicle>' ||
'                           and tl_grid_scen = <:global.scenario>' ||
'                           and tl_grid_id = act)' ||
' where tl_grid_veh_id = <:global.vehicle>' ||
'   and tl_grid_scen = <:global.scenario>' ||
'   and tl_grid_id = act';

you will have to build dbms_sql.bind_array statements to handle each of the global that are enclosed by '<>'.

David

[Updated on: Wed, 01 June 2005 03:53]

Report message to a moderator

Re: inserting in 2 datablocks [message #121820 is a reply to message #121819]

Wed, 01 June 2005 03:56

jonmich005
Messages: 53
Registered: April 2005

Member

Do I also have to put all those quotes?

Report message to a moderator

Re: inserting in 2 datablocks [message #121822 is a reply to message #121820]

Wed, 01 June 2005 04:00

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

If it is on more than one line - yes.

And you will have to make 'stmt' longer than varchar2(200), try 2000.

[Updated on: Wed, 01 June 2005 04:01]

Report message to a moderator

Re: inserting in 2 datablocks [message #121826 is a reply to message #121819]

Wed, 01 June 2005 04:21

jonmich005
Messages: 53
Registered: April 2005

Member

I have tried it on another way.

I used 20 if statements.This is probably not good programming but it works.
What should I do then?

Report message to a moderator

Re: inserting in 2 datablocks [message #121948 is a reply to message #121826]

Wed, 01 June 2005 19:37

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Why don't you just base your second block on the intervals. Then when you double-click a particular field you can fire the when-mouse-double-click trigger and, if the last entry is empty, move the previous to the next until you free up the field in which you double clicked?

David

Report message to a moderator

Previous Topic:	Plz Help Me
Next Topic:	ifbld60.exe Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 01:55:41 CDT 2024