Home » Developer & Programmer » Reports & Discoverer » Formula Column (Oracle 10g)
Formula Column [message #498217] Wed, 09 March 2011 04:54 Go to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
I am having some problem with the code below. It does not give neither give any error during compilation nor give expected results. I have wrote this code behind a formula column which take the image from a server. The name of the image and location is carried out from database. When ever there is new record new name of the image is posted in this formula column. But when the file is not present in the location it gives error. When the error occur i cant able to view the report. The error says 'You cant run the report without layout'. When all images are present in the directory then report runs fine. Kindly help me out so that I can run the report even when there is no image.

function CF_QID_IMAGE return Char is

  l_file_exists BOOLEAN;
  l_file_len    NUMBER;
  l_blocksize   BINARY_INTEGER;
image_link varchar2(50);

begin 

SELECT J.MEANING into image_link
FROM PDADEV.JLS_LOOKUP J
where J.CODE = 'QIM'
and   J.ENABLED = '1';

--image_link := image_link||'R'||to_char(Q_1.QIDNO)||'.gif'
--return (image_link||'Q'||to_char(:QIDNO)||'.gif');
--exception when others then return null;
--end;

--BEGIN

  utl_file.fgetattr(
    location    => image_link,
    filename    => 'Q'||to_char(:QIDNO)||'.GIF',
    fexists     => l_file_exists,
    file_length => l_file_len,
    block_size  => l_blocksize);
  IF l_file_exists THEN
  	dbms_output.put_line('File found.');
    return (image_link||'Q'||to_char(:QIDNO)||'.GIF');
  ELSE
  	dbms_output.put_line('File not found.');
    return (image_link||'Q00000000000.GIF');
  END IF;
END;


[EDITED by LF: applied [code] tags to preserve formatting]

[Updated on: Wed, 09 March 2011 05:05] by Moderator

Report message to a moderator

Re: Formula Column [message #498227 is a reply to message #498217] Wed, 09 March 2011 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DBMS_OUTPUT won't work in Reports so ... remove it. SRW.MESSAGE should be used instead. Put it into the formula column's PL/SQL function so that you could trace its execution.

What is "Q00000000000.GIF"? Does this image exist? If not, perhaps you could create a "dummy" (empty) image that would be used whenever there's no real image in a directory.
Re: Formula Column [message #498304 is a reply to message #498227] Wed, 09 March 2011 09:38 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
yes i know that DBMS_OUTPUT will not work and it is not working either. =) I forget to remove this from code. Anyways I will do so

yes, "Q00000000000.GIF" is the default image or you can say as a dummy (empty) image file in the directory. I want that when there is no image of that name then this image should come so that I can able to see the report...
Re: Formula Column [message #498343 is a reply to message #498304] Wed, 09 March 2011 13:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so - did you use SRW.MESSAGE to see what happens when you run the report? Maybe you should use just two "items" (what is that report displaying, anyway?) - one that has image, and one that doesn't have it (so that you wouldn't have to wait too long).
Re: Formula Column [message #498424 is a reply to message #498217] Wed, 09 March 2011 23:15 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
when there is proper image to every record then the report runs fine. Displays the report.

and when if a report occur one record which doesnt has the picture save in the directory then it displays an error like "You are not able to view the report without having report layout"

yes i have tried the code with SRW.MESSAGE but i didnt come to some solution
Re: Formula Column [message #498439 is a reply to message #498424] Thu, 10 March 2011 00:31 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
i think there is some problem with the location parameter. I am passing location of the directory as '\\pda_apps2\pdalib\image\qid\'

and i have seen every where on internet that the location of directory is of like this C:\del... etc

tell me if this procedure utl_file.fgetattr can be used in oracle report builder 10g
Re: Formula Column [message #498441 is a reply to message #498439] Thu, 10 March 2011 00:39 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If Builder doesn't complain about UTL_FILE.FGETATTR while compiling a report, then it can be used. Obviously, it isn't complaining.

Directory name format might depend on your operating system. Which one do you use?
Re: Formula Column [message #498446 is a reply to message #498441] Thu, 10 March 2011 00:45 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
The environment i am working in is Windows XP and Oracle 10g..



Re: Formula Column [message #498449 is a reply to message #498446] Thu, 10 March 2011 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where are the images located? On your PC or on a server? If on the server, which operating system does it use?
Re: Formula Column [message #498454 is a reply to message #498449] Thu, 10 March 2011 01:04 Go to previous message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
images are located on the server and the server's operating system is Windows server 2003
Previous Topic: Oracle Reports - Template (merged)
Next Topic: Text Only in Last Page
Goto Forum:
  


Current Time: Thu Mar 28 19:53:01 CDT 2024