Home » Developer & Programmer » Forms » Major Problem and Confusion in The code
Major Problem and Confusion in The code [message #383797] Fri, 30 January 2009 05:01 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi Friends,
I have a written a code(pasted below), which actually transferrs the data to an excel sheet from forms.
But the problem i am facing is , when even there is a null value in any of the column, then the execution stops, and a error message pops up saying that 'Invalid Forms Configuration'.
Kindly please help me out in solving this. I am eagarly waiting for your answer.

declare
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
args ole2.list_type;
rowcount integer;
cursor c1 is select PAR_TYPE,PAR_DESCR,PHYS_ID,CHAR_ID from PARAMETERS
order by PAR_TYPE,PAR_DESCR,PHYS_ID,CHAR_ID;

begin

application := ole2.create_obj('Excel.Application');
ole2.set_property(application,'Visible','True');
workbooks := ole2.get_obj_property
(application,'Workbooks');
workbook := ole2.invoke_obj(workbooks,'Add');
worksheets := ole2.get_obj_property
(workbook,'Worksheets');
worksheet := ole2.invoke_obj(worksheets,'Add');

rowcount := 0;

for rec1 in c1 loop
rowcount := rowcount + 1;
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,1);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.PAR_TYPE);
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,2);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.PAR_DESCR);
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,3);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.PHYS_ID);
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,4);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.CHAR_ID);
ole2.release_obj(cell);
end loop;
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
end;


In the below mentioned columns, there are null values present.
CHAR_ID AND PHYS_ID.
Re: Major Problem and Confusion in The code [message #383805 is a reply to message #383797] Fri, 30 January 2009 05:59 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

this is the thing what i did..

i m using nvl for all columns lime

nvl(col1,'')


do like this it will print blank.
Re: Major Problem and Confusion in The code [message #383809 is a reply to message #383805] Fri, 30 January 2009 06:09 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi ganesh,

thanks a lot for the info, but where do i have to use this NVL, i mean,

ole2.set_property(cell,'Value',rec1.CHAR_ID)


I am printing the values like this, here how can i use NVL function, please advise..
Re: Major Problem and Confusion in The code [message #383810 is a reply to message #383797] Fri, 30 January 2009 06:15 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

do like this
ole2.set_property(cell,'Value',nvl(rec1.CHAR_ID,''))


or give nvl to each column that may be null in select statement.
Re: Major Problem and Confusion in The code [message #384136 is a reply to message #383797] Mon, 02 February 2009 05:09 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi friends,

I am still facing the same problem, i.e, i am getting a "forms execution stopped" error, when ever a null value is encountered in a column, that has to be printed on a excel sheet.
if there are no null values, then my code is working perfectly fine, but when there are null values, there is a error, and the execution stops. I have placed my code below. Kindly check this and help me out in getting through this.

declare
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
args ole2.list_type;
rowcount integer;
cursor c1 is select PAR_TYPE,PAR_DESCR,PHYS_ID,CHAR_ID from PARAMETERS
order by PAR_TYPE,PAR_DESCR,PHYS_ID,CHAR_ID;

begin

application := ole2.create_obj('Excel.Application');
ole2.set_property(application,'Visible','True');
workbooks := ole2.get_obj_property
(application,'Workbooks');
workbook := ole2.invoke_obj(workbooks,'Add');
worksheets := ole2.get_obj_property
(workbook,'Worksheets');
worksheet := ole2.invoke_obj(worksheets,'Add');

rowcount := 0;

for rec1 in c1 loop
rowcount := rowcount + 1;
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,1);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.PAR_TYPE);
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,2);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',rec1.PAR_DESCR);
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,3);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',NVL(rec1.PHYS_ID,''));
ole2.release_obj(cell);
args := ole2.create_arglist;
ole2.add_arg(args,rowcount);
ole2.add_arg(args,4);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',NVL(rec1.CHAR_ID,''));
ole2.release_obj(cell);
end loop;
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
end;



ole2.set_property(cell,'Value',NVL(rec1.PHYS_ID,''));


ole2.set_property(cell,'Value',NVL(rec1.CHAR_ID,''));


in the above mentioned two columns, i am having null values, even after using NVL function, i am still facing the same problem. Please help me out in this.
Thanks a lot in advance.
Re: Major Problem and Confusion in The code [message #384152 is a reply to message #383797] Mon, 02 February 2009 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as oracle is concerned '' is null.
Try putting a space between the quotes.
Re: Major Problem and Confusion in The code [message #384172 is a reply to message #383797] Mon, 02 February 2009 08:35 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hello Sir,

Thanks a lot for the Reply, but somehow or the other, things are becoming complex at my end, now i have a requirement where in i am supposed to print even the special characters,
For ex, i have a column value like '$Suhas$$$$_$_$',for which i am not able to print, even after using the replace statement. Kindly help me out how to print these values.

The column name is char_id

i am using something like replace(nvl(char_id,''),'$',null) which is unfortunately not working. Sad, Please help.
Re: Major Problem and Confusion in The code [message #384175 is a reply to message #383797] Mon, 02 February 2009 08:56 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well first off you still haven't put a space between the quotes for your nvl.
Secondly if null is giving you problems replacing characters with null doesn't seem a particularly good idea.
If you have a value which is just $ you're going to end up with null.
Thirdly unable to print can mean all sorts of things since as far as I can see you're not printing anything - you're creating a file.

So.
Fix your code to make sure you don't get nulls.
If you're still getting problems after that describe the issue in detail.
Previous Topic: Using SQL*Loader in Forms
Next Topic: Forms that's open into the browser but not the whole form...
Goto Forum:
  


Current Time: Sat Dec 03 08:23:07 CST 2016

Total time taken to generate the page: 0.06978 seconds