Home » Developer & Programmer » Forms » FORMS 6i Urgent Solution
FORMS 6i Urgent Solution [message #166038] Tue, 04 April 2006 03:39 Go to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi,
1] Can we open form thru PL/SQL and run the form-level procedure on open form.
2] Can we create form-based procedure in PL/SQL for that
form name is the input parameter.
3] Can we call/open form in backend(not shown to user) and
do process the form-level procedure and exit form automatically.

Please revert back, its urgent matter.

Thanks in advance,

Sachin Khaladkar

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166126 is a reply to message #166038] Tue, 04 April 2006 12:14 Go to previous messageGo to next message
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
Senior Member
Hi Sachin,

1] Can we open form thru PL/SQL and run the form-level procedure on open form.

No, you cannot open a form from any kind of pl/sql stuff.


2] Can we create form-based procedure in PL/SQL for that
form name is the input parameter.

What is a form-based procedure? If it's a procedure in a form, you can of course take the form-name as a parameter to it.

3] Can we call/open form in backend(not shown to user) and
do process the form-level procedure and exit form automatically.

I don't know if this is possible ( maybe by setting forms width and height to 0?), but if you don't want to see that form, why use it for processing? Use a pl/slq package and you have no problems about this issue.

HTH,

Regards,

Rob Zijlstra

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166162 is a reply to message #166126] Tue, 04 April 2006 22:42 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi Rob,
I written the form level procedure for FORM blocks/fields documentation purpose, in procedure i am getting all the fields
necessary properties and insert into one table of each form, but
the problem is that i have to attach above procedure to each form and run thru some button or some trigger, but this procedure may be very lengthy, i have to dynamicaly process it,
Please advice me how to do it, i think u understand my problem,
so please let me know any other solution if u know.
Thanks for your reply,

Regards,
Sachin

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166192 is a reply to message #166162] Wed, 05 April 2006 01:54 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Just run the procedures from the SQL*Plus prompt. Remember that the '-s' option will make it run silently. Search this forum for 'sqlplus' to see examples.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166271 is a reply to message #166192] Wed, 05 April 2006 08:25 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
I written form-level procedure, how should i call it at sql prompt, is there any way to call it pl.tell me i am not aware.

Thanks for guidence

Regards,
Sachin K.

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166313 is a reply to message #166162] Wed, 05 April 2006 12:11 Go to previous messageGo to next message
SHYJU KT
Messages: 8
Registered: February 2004
Junior Member
can u send the code(part of it) that u have written ?????

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166400 is a reply to message #166271] Thu, 06 April 2006 02:56 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Take the form-level procedure and store it in the database, then run it from there via SQL*Plus.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166411 is a reply to message #166400] Thu, 06 April 2006 03:49 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
That form-level procedure having :system.<<built-ins>>, so it
should not compile in PL/SQL. Is ther any other way to compile in
PL/SQL or i have to change my procedure.

Thanks in advance,

Regards,
Sachin K

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166531 is a reply to message #166411] Thu, 06 April 2006 19:35 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Okay, sit back, take a deep breath, and think about this for a minute. What 'system' features could you NEED when you run the procedure from the SQL*Plus prompt? As SHYJU KT asked, "can u send the code(part of it) that u have written ?????" It is always easier for people trying to answer questions in this forum if the OP (original poster) places their code either between 'code' tags or as an attachment.

Consider rewriting your code so that the 'system' level variables are passed as parameters to your database resident procedure.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166833 is a reply to message #166531] Sun, 09 April 2006 22:47 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
As per SHYJU KT asked about code, i send herewith following is the code of procedure it will run in each individual form in when-new-form-instance trigger.Pl.study and tell me i have to change something for database procedure.
PROCEDURE chk_tab_columns IS
  first_blk   varchar2(50) := get_form_property(:system.current_form,first_block);
  last_blk    varchar2(50) := get_form_property(:system.current_form,last_block);
  fld_name    varchar2(50);
  tab_name  varchar2(50);
  fld_data_type    varchar2(50);
  tab_column_type  varchar2(50);
  fld_data_length  number;
  tab_data_length  number;
---  frm_name        varchar2(50) := get_form_property(:system.current_form,form_name);
  frm_name varchar2(50) :=:global.file;
BEGIN
 delete from form_table_excep;
 -----
 go_block(first_blk);
 loop
  if last_blk != :system.current_block then		
   	tab_name := get_block_property(:system.current_block,query_data_source_name);
--	message('table name 1'||tab_name);
--	message('block name 1'||:system.current_block);
	if tab_name is not null then	
	go_field(:system.current_block||'.'||get_block_property(:system.current_block,first_item));
	if get_item_property(:system.current_block||'.'||get_block_property(:system.current_block,first_item),required) = 'TRUE' then
		set_item_property(:system.current_block||'.'||get_block_property(:system.current_block,first_item),required,property_false);
	end if;
	loop
		if :system.current_block||'.'||:system.current_field != :system.current_block||'.'||get_block_property(:system.current_block,last_item) then
			fld_name := :system.current_block||'.'||:system.current_field;
			fld_data_type := get_item_property(fld_name,datatype);
			if fld_data_type = 'CHAR' then
				fld_data_type := 'VARCHAR2';
			end if;
			fld_data_length := get_item_property(fld_name,max_length);
--			message('field name 1'||fld_name);
--			message('table name 2'||tab_name);
--			message('field type 1'||fld_data_type);
			begin
			
			   select data_type,decode(data_type,'VARCHAR2',data_length,
			                                     'NUMBER',data_scale+data_precision,
			                                     'DATE', 11,data_length) 
			     into tab_column_type,tab_data_length
			   from user_tab_columns
			   where table_name =upper(tab_name)
			     and column_name = upper(substr(fld_name,instr(fld_name,'.')+1));
		  if tab_column_type != fld_data_type then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field
		  	 );
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	raise form_trigger_failure;
		  end if;
		  if tab_data_length != fld_data_length then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field
		  	 );
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---raise form_trigger_failure;
		  end if;
			exception when no_data_found then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,null,null,
		  	 'NON-BASE TABLES FIELDS --->'||:system.cursor_field
		  	 );				
			end;
		 next_item;
	 else
			fld_name := :system.current_block||'.'||:system.current_field;
			fld_data_type := get_item_property(fld_name,datatype);
			if fld_data_type = 'CHAR' then
				fld_data_type := 'VARCHAR2';
			end if;
			---message('field name 2'||fld_name);
			--message('table name 3'||tab_name);
			--message('field type 2'||fld_data_type);
			fld_data_length := get_item_property(fld_name,max_length);
		  begin
			   select data_type,decode(data_type,'VARCHAR2',data_length,
			                                     'NUMBER',data_scale+data_precision,
			                                     'DATE', 11,data_length) 
			     into tab_column_type,tab_data_length
			   from user_tab_columns
			   where table_name = upper(tab_name)
			     and column_name = upper(substr(fld_name,instr(fld_name,'.')+1));
		  if tab_column_type != fld_data_type then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field
		  	 );
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	raise form_trigger_failure;
		  end if;
		  if tab_data_length != fld_data_length then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field
		  	 );
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---raise form_trigger_failure;
		  end if;
			exception when no_data_found then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,null,null,
		  	 'NON-BASE TABLES FIELDS --->'||:system.cursor_field
		  	 );				
		  end;
		  exit;
	end if;
  end loop;
 end if;
 next_block;
else
   	tab_name := get_block_property(:system.current_block,query_data_source_name);
	--message('table name 4'||tab_name);
	--message('block name 2'||:system.current_block);
	if tab_name is not null then
	go_field(:system.current_block||'.'||get_block_property(:system.current_block,first_item));
	if get_item_property(:system.current_block||'.'||get_block_property(:system.current_block,first_item),required) = 'TRUE' then
		set_item_property(:system.current_block||'.'||get_block_property(:system.current_block,first_item),required,property_false);
	end if;
	loop
		if :system.current_block||'.'||:system.current_field != :system.current_block||'.'||get_block_property(:system.current_block,last_item) then
			fld_name := :system.current_block||'.'||:system.current_field;
			fld_data_type := get_item_property(fld_name,datatype);
			if fld_data_type = 'CHAR' then
				fld_data_type := 'VARCHAR2';
			end if;
			fld_data_length := get_item_property(fld_name,max_length);
			--message('field name 3'||fld_name);
			--message('table name 5'||tab_name);
			--message('field type 3'||fld_data_type);
		  begin
			   select data_type,decode(data_type,'VARCHAR2',data_length,
			                                     'NUMBER',data_scale+data_precision,
			                                     'DATE', 11,data_length) 
			     into tab_column_type,tab_data_length
			   from user_tab_columns
			   where table_name =upper(tab_name)
			     and column_name = upper(substr(fld_name,instr(fld_name,'.')+1));
		  if tab_column_type != fld_data_type then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field
		  	 );
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	raise form_trigger_failure;
		  end if;
		  if tab_data_length != fld_data_length then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field
		  	 );
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---raise form_trigger_failure;
		  end if;
			exception when no_data_found then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,null,null,
		  	 'NON-BASE TABLES FIELDS --->'||:system.cursor_field
		  	 );				
		  end;
		 next_item;
	 else
			fld_name := :system.current_block||'.'||:system.current_field;
			fld_data_type := get_item_property(fld_name,datatype);
			if fld_data_type = 'CHAR' then
				fld_data_type := 'VARCHAR2';
			end if;
			--message('field name 4'||fld_name);
			--message('table name 6'||tab_name);
			--message('field type 4'||fld_data_type);
			fld_data_length := get_item_property(fld_name,max_length);
		  begin
			   select data_type,decode(data_type,'VARCHAR2',data_length,
			                                     'NUMBER',data_scale+data_precision,
			                                     'DATE', 11,data_length) 
			     into tab_column_type,tab_data_length
			   from user_tab_columns
			   where table_name = upper(tab_name)
			     and column_name = upper(substr(fld_name,instr(fld_name,'.')+1));
		  if tab_column_type != fld_data_type then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field
		  	 );
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	message('FORM COLUMN DATA TYPE not MATCH with TABLE COLUMN DATA TYPE --->'||:system.cursor_field);
---		  	raise form_trigger_failure;
		  end if;
		  if tab_data_length != fld_data_length then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,tab_column_type,tab_data_length,
		  	 'FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field
		  	 );
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---message('FORM COLUMN WIDTH not MATCH with TABLE COLUMN WIDTH----->'||:system.cursor_field);
		  	---raise form_trigger_failure;
		  end if;
			exception when no_data_found then
		  	insert into form_table_excep
		  	values(frm_name,:system.current_block,:system.current_field,fld_data_type,fld_data_length,
		  	 upper(tab_name),:system.current_field,null,null,
		  	 'NON-BASE TABLES FIELDS --->'||:system.cursor_field
		  	 );				
		  end;
		  exit;
	end if;
  end loop;
 end if;
 exit;
end if;
end loop;
commit;
end;

Thanks in advance,

Sachin K.

[Updated on: Mon, 10 April 2006 02:21] by Moderator

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166880 is a reply to message #166833] Mon, 10 April 2006 02:26 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
This code analyses form items, it can not ever, any way, be run from the SQL*Plus prompt. Stop trying to do it, stop thinking about trying to do it. This is hardcore forms stuff, and looks interesting by the way.

If you want to run it in multiple forms then make it a Forms Library (PLL) file and call it from each form versus cutting and pasting the code into each form.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166882 is a reply to message #166880] Mon, 10 April 2006 02:37 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
Thanks for reply, As per your statement it is not possible to create database procedure is it right.
If i create a .pll file and attach each form and calling above procedure in when-new-form-instance trigger, this job again very hectic, If u have any short-cut to call each form auto run above procedure and give me output.

Thanks in advance.

Sachin K.

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166892 is a reply to message #166882] Mon, 10 April 2006 03:10 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Sorry, this is the pain of conversion and upgrade projects. Every form has to be handled one-at-a-time, reviewed, modified, and then tested. I assume that you will also want to remove this code before going into production. If so you can have a 'stub' in the production PLL that has 'begin null; end;' in it while in the development environment PLL you have all your code.

I worked at one installation that had a call to the PLL at the beginning of EVERY trigger and in the production PLL there was typically a 'begin null; end;' code stub, but it was there JUST IN CASE we wanted to do something for every form and did not want to have to change each form independantly.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166905 is a reply to message #166892] Mon, 10 April 2006 03:40 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
Thanks for reply, but i can't understand your solution, pl.explain me how should i go about it.

Thanks

Sachin K.

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #166960 is a reply to message #166905] Mon, 10 April 2006 08:58 Go to previous messageGo to next message
bdrufner
Messages: 42
Registered: August 2005
Location: Home of the Mardi Gras, N...
Member
Sachin,

Like David, I also find your PLSQL procedure interesting.

Please post description (columns, datatypes, etc) for table form_table_excep so that I might review code in debug mode.

Thanks,
Barry

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #167048 is a reply to message #166960] Tue, 11 April 2006 00:22 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Okay. You create two PLLs. They both have the same internal organisation, that is, the same procedure names with the same number and name of parameters, and they are in the same order, but in one you have your code and in the other you have 
PROCEDURE chk_tab_columns IS
BEGIN
  null;
END;
Then in your form you ALWAYS have the call to 'chk_tab_columns' but when you run it in development, as you have the 'full' PLL you will execute your checing logic, but in production, as you have the 'minimal' PLL the form will still call the PLL routine but no real work in done. This way you get to 'check' the tables and columns in your development environment and your users get to run their application without this 'techo' stuff popping up, and your team only has to maintain ONE version of the Forms.

David

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #167269 is a reply to message #167048] Wed, 12 April 2006 03:52 Go to previous messageGo to next message
Sachin Khaladkar
Messages: 12
Registered: September 2004
Junior Member
Hi David,
As per your reply,I have some doubt, please clear me.
1] Two diff .pll i have to create ? with diff name ?
2] Each form i have to call these two .pll procedures ?
3] How it will execute ?
Thanks in advance,

Sachin K.

Report message to a moderator

Re: FORMS 6i Urgent Solution [message #167441 is a reply to message #167269] Thu, 13 April 2006 01:30 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Sachin Khaladkar wrote on Wed, 12 April 2006 18:52

Hi David,
As per your reply,I have some doubt, please clear me.
1] Two diff .pll i have to create ? with diff name ?
2] Each form i have to call these two .pll procedures ?
3] How it will execute ?
Thanks in advance,

Sachin K.
1] Same name, this is a bit more difficult that if you had different names but each version only lives in one place. Big one in development, little one in production.
2] Each form calls only one PLL
3] In development you get the big code and in production the little code.

David

Report message to a moderator

Previous Topic: Align Image
Next Topic: FRM-40735:WHEN-VALIDATE-ITEM trigger raised unhandled exception VALUE_ERROR.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 09:51:11 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.