OraFAQ Forum: Forms » how deal with primary key while using for loop in forms 6i

Home » Developer & Programmer » Forms » how deal with primary key while using for loop in forms 6i (forms 6i)

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

how deal with primary key while using for loop in forms 6i [message #586061]

Mon, 03 June 2013 03:54

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Hii every one,

I am having template table as

template_name   varchar2(100) -- primaykey
ex_col           varchar2(100)
tab_col		varchar2(100)

Now on the form 6i i've two columns with following data

Column A			Column2 B
-------------------------------------
route                           a_route
time                            a_time
id                              a_id


and a text field where the name of the template will be entered.
e.g
text filed - testing

In template table i want to insert above values, so template table will look like this

template_name      ex_col          tab_col
-----------------------------------------------------
testing             route           a_route
                    time            a_time
                    id              a_id

since tempate_name is primary key, i how should i construct for loop for insert.
Please help me.

[MERGED by LF]

[Updated on: Tue, 04 June 2013 04:04] by Moderator

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586063 is a reply to message #586061]

Mon, 03 June 2013 04:15

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You can't, not that way.

Two options I can think of: alter your "templates" table and either add a new primary key column (a number sequence is a candidate), or add another column into the primary key constraint (so it'll become a composite key) (for example, make TEMPLATE_NAME + EX_COL a primary key combination).

Another option is redesign: create another table which contains information about templates. Then, in your current "template" table, TEMPLATE_NAME won't be a primary but foreign key (pointing to a new "template" table). Current "template" table will have to be altered in order to store a primary key value. Here's an example:

SQL> create table my_templates
  2    (id_template   number     constraint pk_mytempl primary key,
  3     template_name varchar2(100)
  4    );

Table created.

SQL> create table template
  2    (id_templ      number     constraint pk_templ primary key,
  3     id_template   number     constraint fk_templ
  4                                references my_templates (id_template),
  5     ex_col        varchar2(100),
  6     tab_col       varchar2(100)
  7  );

Table created.

SQL>

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586078 is a reply to message #586063]

Mon, 03 June 2013 05:59

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thank u Littlefoot
for your reply. i got the point. But now i am having problem regarding applying for loop for the same.

Thanks
Sameer

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586083 is a reply to message #586078]

Mon, 03 June 2013 06:08

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then you need to show us what you've done and explain what the problem is.

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586086 is a reply to message #586083]

Mon, 03 June 2013 06:33

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thanks for reply
here is my code

declare
	ex   varchar2(100);
	tc   varchar2(100);
begin
	if :T5 is null then
	message('plaease enter the name'); message('.');
	else
		for i in 1..28 loop
			insert into template(template_name, ex_col, tb_col)
			values(:T5,excl_col,tab_col);
		end loop;
end if;
	commit;
	message('data inserted successfully..'); message('.');
end;

on my forms there were 28 records of excl_col and 28 records of tab_col
the would be like this

EXCL            TAB
----------------------------
EXCL_COL1       TAB_COL1
EXCL_COL2       TAB_COL2
EXCL_COL3       TAB_COL3
    .               .
    .               .

Above code just runs 28 times i also want to make it dynamic i.e only 6 exc_col and 6 tab_cols are there then loop should go only upto 6th loop. Currently i am fetching only template_name in database but other two columns were null, as i am passing nothing there.
I don't understand how should i pass and store these values to oracle table.

Thanks again
Sameer.

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586087 is a reply to message #586086]

Mon, 03 June 2013 06:38

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Where's the data you are trying to insert stored when this process is run?
In fields in the form?

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586088 is a reply to message #586086]

Mon, 03 June 2013 06:40

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Loop until :SYSTEM.LAST_RECORD is true (there are plenty of examples here, on the forum, and on the internet - search for them if you are unsure of how to use it).

For EXCL_COL and TAB_COL, insert item's values (just like you did it with :T5 item).

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586102 is a reply to message #586087]

Mon, 03 June 2013 07:23

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thanks for the reply cookiemonster and Littlefoot

Please find the attachment of the screen shot. you will get the idea what i am trying to do.
1. By clicking the browse button i select the file.
2. file name is shown in the file name field.
3. Click on column button, all column heading get displayed in Excel column.
4. list item of Table column is provided to map the column with excl column.
5. Then give the template name.
6. Then click on save button. on the save button i've written above code.

Thanks again
Sameer.

Attachment: sample.png
(Size: 159.04KB, Downloaded 690 times)

Report message to a moderator

Re: how deal with primary key while using for loop in forms 6i [message #586104 is a reply to message #586102]

Mon, 03 June 2013 07:27

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's nothing I'd like to add to my message #586063.

Report message to a moderator

lov and cursor problem [message #586154 is a reply to message #586061]

Tue, 04 June 2013 03:39

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

I've a table with following details

template_name              ex_col                          tb_col
-----------------------------------------------------------------------------------
test1                      abcd                             efgh
test1                      ok                               done
test1                      plane                            flight
test1                      uk                               us
more                       idea                             air
more                       detail                           are
more                       here                             there
more                       a                                b

on the form i've provide a lov which will select hte template name. onece the template name is selected i want to display reocrd of that template_name on my form.
I also provide text fields on the form. As i am new to oracle forms i am confuse where, what and how to write code for the same.
my expected result is like this


    lov selected - more
	
	column A                              column B
---------------------------------------------------------------------------------	
	idea                                air
        detail                              are
        here                                there
        a                                   b

please suggest something for the same.
Regards
Sam.

Report message to a moderator

Re: lov and cursor problem [message #586155 is a reply to message #586154]

Tue, 04 June 2013 03:45

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That would be a master-detail form. Master would contain only one item - a template name. Once it is selected, perform query which would populate details.

Report message to a moderator

Re: lov and cursor problem [message #586159 is a reply to message #586155]

Tue, 04 June 2013 04:02

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thanks for reply,
yes that's right, but can i do it without master-details relationship. Because i've only one block. can i do it with the help of cursor.

Thanks
Sameer

Report message to a moderator

Re: lov and cursor problem [message #586161 is a reply to message #586159]

Tue, 04 June 2013 04:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't know; probably you can, but that requires some programming. In my opinion, you should rely as much as possible on Forms built-in processing and let Forms do the dirty job.

How much would it cost if you created additional (master) block and do what I said previously?

Report message to a moderator

Re: lov and cursor problem [message #586164 is a reply to message #586161]

Tue, 04 June 2013 04:23

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

ok i will try with master detail relationship.
Thanks

Report message to a moderator

Re: lov and cursor problem [message #586506 is a reply to message #586161]

Fri, 07 June 2013 02:31

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

I've modify my form and finally created a master detail relationship,
I've written 'execute_query' on when-new-form-instance. When the form runs nothing gets displayed on the form.

Thank you.
Sameer

Report message to a moderator

Re: lov and cursor problem [message #586507 is a reply to message #586506]

Fri, 07 June 2013 02:38

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You have 2 blocks - master and detail. Are both of them DATA blocks? They should be. Also, items should be mapped to corresponding table columns. Otherwise, EXECUTE_QUERY won't fetch anything.

Report message to a moderator

Re: lov and cursor problem [message #586518 is a reply to message #586507]

Fri, 07 June 2013 03:26

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thanks Littlefoot, for quick reply.
Things you've suggested are already present, but still it doesn't work.

Thanks again

Report message to a moderator

Re: lov and cursor problem [message #586519 is a reply to message #586518]

Fri, 07 June 2013 03:28

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

- Is master table empty?
- Is there any WHERE condition in data block property palette that prevents data to be displayed?
- Is there any PRE-QUERY trigger that modifies block properties?

Report message to a moderator

Re: lov and cursor problem [message #586525 is a reply to message #586519]

Fri, 07 June 2013 03:45

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

which block is the cursor in when the form starts up?
Are you getting any errors?
If you execute the query with the standard button after the form starts does it work?

Report message to a moderator

Re: lov and cursor problem [message #586527 is a reply to message #586525]

Fri, 07 June 2013 04:09

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thanks Littlefoot and cookiemonster, for quick reply.
well

     1> Master table is not empty.
     2> so far no where condition is specified to the block.
     3> There is no PRE-Query.

i just add one line in when-new-form-instance

go_item('mst_template.template_name');
execute_query;

and now template name gets displayed in the field, but child records are not get displayed. and error called

FRM-40505:ORACLE error:unable to perform query

occurs. I am working on that. please suggest something.

Thanks again.

Report message to a moderator

Re: lov and cursor problem [message #586529 is a reply to message #586527]

Fri, 07 June 2013 04:24

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

When you get that error, go to Help and choose "Display error". It will tell you the reason. If you don't know what it means, copy/paste "display error" output over here.

Report message to a moderator

Re: lov and cursor problem [message #586546 is a reply to message #586527]

Fri, 07 June 2013 06:06

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

I got the error, i am using different columns to inset the records. That's why i try to avoid to use the the master details relationship. what i want to achieve is,
i've a form for importing excl to oracle with column mapping. i.e i've display excl column heading on the form. In front of each execl column heading i've provide a list item which contains table column heading so user can map his excl column with table column
and according to data gets insert into the database table.

There are 28 excel and table column headings on the form every time user will have to map the column. Its very time consuming job.That's why i've decide to provide template to user. e.g user first time map excel columns with table column, i've save this mapping as 'template 1' . next time when user runs the form he/she needs to just select template-1 from lov. and that template get displayed on the forms field. Then user will press the process button and data gets inserted into respective table
Now I've created two tables

1. mst_template
 -------------------------------------------
 template_name        varchar2(100) ---primary key.
 
 2. template
 --------------------------------------------------
 template_name   varchar2(100) --- foreign key (ref. tempalte_name - mst_template)
 ex_col          varchar2(100)
 tb_col          varchar2(100)

Now my problem is, I try the master detail relationship but it doen't works becase on my form the condition is

 excel col                                   table col
 ---------------------------------------------------------------
 excl_col1                                 Tab_col1
 excl_col2                                  tab_col2
      .                                         .
      .                                         .  
      .                                         .
excl_col28                                Tab_col28

i've establish the master detail relationship but getting error

FRM-40505:ORACLE error:unable to perform query

can i achive it by using cursor? without using master detail relationship

please help

Thanks
Sameer

Report message to a moderator

Re: lov and cursor problem [message #586548 is a reply to message #586546]

Fri, 07 June 2013 06:09

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You still didn't say what causes the error. I told you: run the form. It will fail with FRM-40505. Click "Help" in Forms menu, click "Display error". Copy its contents over here.

Report message to a moderator

Re: lov and cursor problem [message #586552 is a reply to message #586548]

Fri, 07 June 2013 06:16

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So redesign your form so that matches the table structure.

Report message to a moderator

Re: lov and cursor problem [message #586556 is a reply to message #586548]

Fri, 07 June 2013 06:42

sameer_da
Messages: 74
Registered: April 2013
Location: India

Member

Thank you Littlefoot and cookimonster,
this is the error i am getting
ORA-00904: "TAB_COL28": invalid identifier

As already said i am using different column name on the form. That's why i don't want to use the master detail relationship. because my 90% code is done, only one thing is remaining i.e display the data on the form.

can i do it with the help of cursor?

any suggestions?

thanks again

Report message to a moderator

Re: lov and cursor problem [message #586558 is a reply to message #586556]

Fri, 07 June 2013 06:48

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The suggestion remains the same - redesign the form to match the table structure.

Can you do it with a cursor - yes.
Will it be more work than redesigning the form to work with forms default functionality - almost certainly.
Is it more likely to have bugs than if you redesign the form - almost certainly.
Is it going to make maintaining the form harder going forward - almost certainly.

Report message to a moderator

Previous Topic:	provide excel sheet with column header only in oracle forms 6i
Next Topic:	connection string in form builder 6i to SYS user

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:58:12 CDT 2024