report
mo, February 27, 2003 - 11:53 am UTC
Tom:
I have one pl/sql procedure that generates an html form for a shipping request. The form can be printed by clicking on a "PRINT" button.
If a user logs in and finds that there are 5 requests waiting for him. Now I want to print all 5 from the list page without going to each form by clicking a button.
is there a way to control the browser print function using pl/sql or you would do this through javascript. Problem with javascript it will print whatever page is displayed and in this case i need to run 5 forms using pl/sql in the background.
Any hints?
Thank you,
February 27, 2003 - 7:55 pm UTC
not with plsql -- plsql runs on the server -- you would have to have plsql write some javascript of some sort.
Don't know, not much of a javascript programmer
reports
mo, February 27, 2003 - 9:20 pm UTC
Tom:
Actually you gave me a good hint. The print button will be a submit button to a procedure that reads the various request numbers. BAsed on that it wil create one big page that has the different forms separated by page breaks. At the end I will have a javasctipt tags that do a window.print() which will print the 5 forms at once. then I close the page and re-submit the original one where user was.
reports
mo, March 22, 2003 - 12:20 pm UTC
Tom:
I have a few html reports created by pl/sql procedures. I know you can not control format a lot by html but I am trying to create a header/footer using pl/sql.
How would you do it. Would you count the number of records on each page and put a page break and a new header every time you hit that count? Would not that work differently on varios printers?
March 22, 2003 - 12:44 pm UTC
what is a header and footer in the context of html??
what is a page break in the contet of html??
repots
mo, March 22, 2003 - 5:21 pm UTC
Tom:
Page breaks can be created in HTML using style tags or style sheets.
What I mean by header and footer is a piece of data that will always print at the top and bottom of each page. My question is can you somehow control it using pl/sql. can pl/sql control page formats or the only way to do it using a counter based on number of records?
March 23, 2003 - 5:02 pm UTC
think about it...
think about how you generate a page with plsql....
you can answer this yourself -- of course you have to code the logic. PLSQL is a general purpose programming language, period.
generating HTML reports
krishna, April 15, 2003 - 6:27 pm UTC
web based reports(html) can be generated from SqlPlus using mark on option.
generating HTML reports
krishna, April 15, 2003 - 6:28 pm UTC
web based reports(html) can be generated from SqlPlus using mark up option.
html reports
mo, September 15, 2003 - 4:37 pm UTC
Tom:
I created headers in an HTML report using pl/sql. I count 30 records on a page and then close the table tag, insert a hard page and open a new table tag.
My problem is that one column of the record (item_description) can vary from day to day. This results in unordered report. The page break will not take effect until it hits every 30th record.
Is there a way I can have pl/sql count number of lines on a page insted of records before i print a page break?
Thank you,
September 15, 2003 - 7:59 pm UTC
er? don't really know what you mean
if by lines you mean "lines the browser displays" -- not really -- that would purely be a function of the width of the screen. I run at 3200x1200, but someone at 1024x768 would see something totally different.
report
mo, September 16, 2003 - 12:02 am UTC
Tom:
Well most users use 800 x 600 resolution. But my objective is to get a header on each page on a html report.
i print stock number, description, and quantity. if description is one line everything comes out fine when i insert a page break/new header every 30 record count. Problem is that some items have 2 -6 line descriptions. This screws up the printout. SO I may get a page and a half and then place a page break and start on the 3rd page.
I was asking if there is a way i can use pl/sql to count number of lines/words in description or the end of line in the printout and use a counter and then base the page break based on that counter (every 66 lines) rather than record number which will not work in this case.
September 16, 2003 - 7:58 am UTC
no, think about it -- if the user changes the size of the window - the browser wraps things differently.
if the use used "ctl+" or "ctl-" to change the font size -- the browser wraps things differently.
there is no way, no way -- for you to predict how many lines things will take unless you start using a <pre> tag (like I do). But then your users will have to scroll right and left when they make the windows too small.
for DISPLAY this sounds like you want a frame -- top frame has headers, bottom frame has data. user scrolls bottom frame and headers stay there. Give users a "print" button to get a report formatted nicely for printing.
report
mo, September 16, 2003 - 2:17 pm UTC
Tom:
Thanks for the great hint. But what do you mean by
< Give users a
"print" button to get a report formatted nicely for printing.>
Are you saying use HTML frames for display reports only, and use <PRE> for printing reports with headers or would the frame also be printed on each page?
Do I need two reports (one display and one for printing)?
September 16, 2003 - 6:07 pm UTC
yes, that is what i'm saying.
the one that gives you the best "gui" is generally not the same one that gives you the best "printing" view.
Many web pages have a "view me" and "print me" version.
I'm not saying use the <PRE> tag. I'm just saying -- consider having two views, two pages. one that looks good on screen. one that looks good on paper.
Use CSS
Yong Wu, September 16, 2003 - 5:59 pm UTC
CSS may help you on this
September 16, 2003 - 9:38 pm UTC
Tyler, friend of Tom here:
1. Check out the following link for page breaks in CSS:
</code>
http://www.javascriptkit.com/dhtmltutors/pagebreak.shtml
2. Try the following link to tell you how to use different style rules depending on whether someone is viewing the page on screen or printint it (@media screen, @media print):
http://www.w3schools.com/css/css_mediatypes.asp
3. You can't actually print from JavaScript as this would be a security flaw, you can only popup the print dialogue.
4. This forum is not a good place to find HTML related information. Your problem of how to print from Oracle is solved with the PL/SQL Web Toolkit, ie htp.p .
I would suggest the following links for good HTML resources:
http://www.w3schools.com http://alistapart.com (check out the archives)
http://zeldman.com <code>(he is to html what Tom is to Oracle)
oracle reports..
vj, September 22, 2003 - 3:50 am UTC
We use oracle 9i r2 database
I have been using Oracle Reports for over 3-4 years and the current product which we are generating brought up an discussion on what the reporting tool should be..
the front end is : JSP
middle tier : websphere
backend : 9ir2
Since i know the kind of restrictions in crystal reports , business objects i strongly recommended using ORACLE REPORTS. But i dont know the constraints of using the oracle reports for the above mentioned arch.
To name a few..
1. Like crytal reports, the report server should run in application server and nothing should be installed in the client machine except the viewer download
2. Invoking the reporting engine (oracle reports) from the JSP with different parameter
Any help is highly appreciated..
Rgds
September 22, 2003 - 7:51 am UTC
reports runs fully as a middle tier application.
i need to know
huss, September 29, 2003 - 3:17 pm UTC
hi tom,
i need in to know , what is the optimal way to run the report on web
i use web.show_document but i face many problems like send all parameter clear test attached to the url
plus some users able to copy the url to past it any were to access the rep without pass throw normal way and etc.
September 29, 2003 - 4:35 pm UTC
i don't do forms, haven't touched it since 1995. try otn.oracle.com -> discussion forums.
Report serrver and Commercial Applications
Govind Agnihotri, September 29, 2003 - 8:18 pm UTC
My two cents -- Reports -- Dollars ???
The complexity and and the undocumented documentation of the 9ias Report server will be too much to handle, unless you have a research group in your think tank to disect this application.
The infrastructure, the IASDB and all the associated processess take lot of resources and preseverence to get a hint of( with the help of IAS architects mid you).
I would not suggest this version of reports, probably lower version are more sinmpler.
Gov
independent !!!
vj, November 03, 2003 - 5:56 am UTC
Tom,
First of all thanks for the reponse on "oracle reports..".
as i mentioned, we are trying to use the oracle reports over the other reporting tool. My client is asking me, do i have to buy an Oracle 9ias just for Oracle Reports ? (he uses Websphere app server otherwise)
Can i buy just Reports Server only !!!
information required
reader, November 03, 2003 - 2:21 pm UTC
can we design drilldown button in oracle 9i reports.
November 03, 2003 - 6:13 pm UTC
don't know, don't really use reports myself. sorry. you can try the discussion forums on otn.oracle.com, there is one for developer
web reports
mo, November 07, 2003 - 1:35 pm UTC
Tom:
I am trying to test web pdf reporting via report builder. I downloaded Developer 6i zip file to c:\reports6i (9i would not run on win 98) unzipped it in c:\reports6i and when I tried to install using the default home directory it gave me the message:
"Location specified "c:\OraHome1" is already used as an oracle 817 production" . It can not be used as an Oracle Home for 'win95'.
I selected c:\OraHome2 instead and it installed fine. When I ran report builder to create a report I could not connect to the database I have. It gave this message:
REP-0501: Unable to connect to specified database.
ORA-12154. TNS: could not resolve service name.
I checked the tnsnames.ora file in both homes and it has the service for the database. In face I always connect using sql*plus without any problem.
Do you know what is the problem here and how I can fix it?
Thank you
reports
mo, November 10, 2003 - 4:26 pm UTC
Tom:
I am trying to connect from Reports 6i to an oracle database 8.1.7 running on IBM/AIX. It is not on windows.
I did a search on tnsnames.ora on the whole C drive and I got this:
C:\OraHome1\network\ADMIN
C:\OraHome1\network\ADMIN\SAMPLE
C:\OraHome2\NET80\ADMIN
C:\OraHome2\NET80\ADMIN\SAMPLE
C:\OraHome2\NET80\TNSAPI
C:\reports6i\win32\SQLNET\8_0_6_0_0-1181195\install\NET80\CLIENT
C:\reports6i\win32\SQLNET\8_0_6_0_0-1181195\install\NET23\CLIENT
I copied the main tnsnames.ora to all of them except the \SAMPLE one. I connect fine with the file using SQL*PLUS for windows and SQL NAvigator for windows. The TNS entry is correct. I also copied the sql*net file.
ISSD.XXX.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XX)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ISSD)
)
)
I am still getting:
REP-0501: Unable to connect to specified database.
ORA-12154. TNS: could not resolve service name.
Is it failing because the Reports 6i is not compaible with the Net 80 client software or for other reasons? Do I have to install another oracle client?
Thank you,
November 10, 2003 - 4:33 pm UTC
what connect string are you using in the report -- is it issd.xxx.com or just issd?
also, if you use
(description=(address_list=(address=.....)))
in the connect string (INSTEAD of issd.xxx.com in the report) -- does that work?
reports
mo, November 11, 2003 - 8:23 am UTC
Tom:
I am using "issd" as my connect string for "Database". I did not quite understand what you mean by your second question.
Strangely when I booted up the PC this morning and tried to connect it worked!!
1. I created a test report based on emp table and saved it as pdf file on the C:\. However I could not save it to oracle database which I believe I must do in order to run it using a link from a web page? or does the report get saved on the unix directory and the report server call it from there using the database defined in the server DAD?
2. Do I have to recompile the RDF file created by Reports 6i for Windows using Reports 6i compiler on unix?
Thank you,
November 11, 2003 - 11:49 am UTC
use issd.xxx.com, your sqlnet.ora must not have had a default domain in the configuration for reports.
1) i don't do reports, never ran the product. i do know the report does not have to be saved into the database -- just deployed to your application server.
2) not sure, try otn.oracle.com -> discussion forums. there are people there that use reports every day...
jsp based reports
reader, November 17, 2003 - 3:02 pm UTC
Limiting Default Parameter Values in Reports
Subodh Deshpande, April 20, 2004 - 9:12 am UTC
Hi Tom,
In reports, in parameter forms, i want to display only two values (e.g. priner,cache) for the parameter say destype, the values available are printer, cache, file, email, screen, preview etc..
also i want to set such type restriction for other parameters like destname, mode etc..
is there any generic setting which will set the environment of report or i have to do it for each report, how should i do it for default parameters suppled with each report..
i searched on otn and i found nearly 707 threads and none was satisfying my doubt.please help me...
thanx in advance...subodh
April 20, 2004 - 9:24 am UTC
how about starting a new thread with the specific question -- for I
a) never have done a report
b) don't print stuff ;)
so, I'm not sure if there is a way or not.
HTML DB and Oracle reports
RK, April 21, 2004 - 12:53 am UTC
Tom,
I plan to use HTML DB ( part of 9i and 10g)to create web pages.
Is it possible to use Oracle reports in the middle tier to generate reports as HTML DB does not have any reporting feature?
April 22, 2004 - 11:34 am UTC
RK,
I wouldn't say that HTML DB does not have any reporting features, it's just that they are web based reports only (for now) and you may be looking for strictly printed reports. We integrated Oracle Reports with HTML DB in a big project I was on last year. I know very little about Oracle Reports, but I know the reports server was called through an HTML DB process via PL/SQL. In fact, I believe this specific need was the reason we added "PL/SQL DBMS Job" type processes to HTML DB. This way, a DBMS Job is created to run once and call Oracle Reports asynchronously. When we used a standard PL/SQL anonymous block type process, the page would submit but not return until the Reports server finished printing the report, which can take a minute or more. This was not a desireable UI, so we added the DBMS Job type.
So, to answer your question, yes, HTML DB can be integrated with Oracle Reports by calling Reports through a process.
Thanks,
Tyler
HTML DB Reports
Mark A. Williams, April 22, 2004 - 3:48 pm UTC
pdf
mo, August 05, 2004 - 3:55 pm UTC
Tom:
Is there a way to use PDF Fillable forms with the oracle 8i AS or 9IAS.
Basically user fills out the PDF form and data is saved into oracle table. I wonder if it works like HTML submit button and you can use javascript validation?
August 05, 2004 - 8:35 pm UTC
no ideas from me on that one. sorry.
acrobat pdf form
mo, August 09, 2004 - 5:16 pm UTC
Tom:
Adter doing some research, I found you can create acrobat pdf forms using Adobe Acrobat 6.0. Acrobat supports HTTP, XML and ODBC and integrates with web browsers. Actually it has some advantage over HTML, in that its appearance is the same and does not depend on browser or monitor settings.
Actually a tax form like 1040 you can't do it using HTML. You scan the form and save it as an image and adobe will convert it to PDF and allows you to add fields for the form. You can also create a button to submit the form to a web server just like HTML. You also do not nned to create a report since you can print the pdf form.
My question here how can i create the a PDF data-filled form by using record from the oracle database for edit.
1. In 9IAS we print the html tags and set the fields to the value in the database. However here I do not think pl/sql can print the pdf file format since it is not tag based more complicated that HTML tags. Do you agree or have any comments?
2. A company called "PureForms" that sells software "PF-merge for Oracle" will allow database record merge with pdf forms. Does oracle have anything that does that?
Thank you,
August 09, 2004 - 8:20 pm UTC
I quite simply have no experience or background in this.
anyone else is (as always) free to followup.
PDF Forms
mo, September 13, 2004 - 1:15 pm UTC
Tom:
If you save a filled PDF form for this into oracle as a BLOB:
www.ita.doc.gov/ooms/sf1.pdf
1. Would you be able to retrieve it from the database for display/edit/print using IAS/URL in PDF format?
2. I guess you would not be able to query on specific fields since the whole form in saved as one BLOB? is this correct?
Thank you,
September 13, 2004 - 3:27 pm UTC
PDF
mo, September 13, 2004 - 3:52 pm UTC
Tom:
Thanks for the link.
Would this BLOB retrieval for PDF file be a read- only mode like an image? or the user should be able to edit the data displayed for "form fields" and save it again into the database?
September 13, 2004 - 8:28 pm UTC
you can do fileupload as well with mod_plsql -- check out the docs, it is documented.
the pdf would be downloaded to the client, the client would have to "upload" it later using this particular technique.
alok dubey, December 08, 2004 - 7:10 am UTC
Respected Sir,
We have three tire architecture application.In the back end we have oracle9i, in the middle we have o9ias and then we have a browser. The user will open the browser and access our database through application server.
We have to show some of the reports generated by the application software over the website.We have also a web server.The reports are so complicated.Kindly advise me whether I should use Report Builder or JSP to generate the reports over WebSite.
December 08, 2004 - 10:45 am UTC
up to you -- you'll find with reports you write less code, the work is done for you in many cases.
try them both out and see which is more efficient for you.
Info on Oracle Reports
Venkat, December 09, 2004 - 8:11 am UTC
The Latest version of Oracle reports is Reports 10G and it can be used to produce any of the popular output formats
1 JSP (YES!! now possible)
2 HTML
3 RTF
4 PDF
Just give a try and u will be amazed with what u can do
printing form
maha, April 11, 2005 - 5:51 am UTC
i have a page html where selected one option to give the form page .jsp creating by reports
The form can be printed by clicking on a "PRINT" button.
April 11, 2005 - 9:02 am UTC
cool?
generate
LL, April 13, 2005 - 12:47 pm UTC
I use pl/pdf package for generate pdf document from Oracle database...
pdf
mo, October 20, 2005 - 9:45 pm UTC
Tom:
1. Do you know what is pl/pdf that generates PDF out of oracle?
2. If you are tasked to automate (insert/update) a form like "TAX 1040" how would you do it using MOD_PLSQL? Would you use acrobat FDF, or HTML and then save data into database and merge it with a blank PDF for 1040 or else?
October 21, 2005 - 8:06 am UTC
1) you just said what it is....
2) insufficient data. I would not be using pdf, it would be, well, html.
pdf
mo, October 21, 2005 - 10:08 am UTC
Tom:
1. How can you create a 1040 tax form using HTML. It is impossible?
2. You can create an HTML form that captures data on 1040 but then how can you print the information on that 1040 format or electronically send it as a PDF file?
3. If I decide to save the PDF file as a BLOB in the database, then do you create an HTML form and ask the user to select it from his drive and then save it to oracle? This means the user will have two versions: one his machine and another in oracle. Is there a way to have the user one version: save the form into oracle, read it from oracle to edit it and re-save it back.
Thanks,
October 21, 2005 - 10:40 am UTC
1) nothing is impossible. what is a form, a bunch of tables. You could certainly mock up a form in html.
2) research pdf merge programs - outside of the database, this is not in my scope.
3) you need to design your application to meet your goals, I'm considering this out of scope. Not really a database problem
report
sage, December 12, 2005 - 7:18 am UTC
Dear All,
how can i make summation of 2 columns from 2 different groups in Data Model.
December 12, 2005 - 8:12 am UTC
you've asked this in multiple places and it still "doesn't really make sense"
Report
sage, December 12, 2005 - 8:33 am UTC
thanks for the quick response.
sorry if you didn't understand my question because it's my first post in this site.
i have the following queries:
1)Select subno,contrno,sum(billamount) +sum(billamount_int)/1507.5) unbilled,
from ivm_billing
where bi_ref is null and (billed<>'Y' or billed is null)
group by subno,contrno
2)select sum(ar_am_loc) billed,a.subno,a.contrno from ivm_invoice_record a,ivm_invoice_Detail b
where a.ar_Ref=b.ar_Ref
and a.contrno=b.contrno
group by a.subno,a.contrno
in "Data Model" the mentioned queries represent 2 different groupes
what i want is to calculate the sum of billed and unbilled amount in order to assign the total to a text field in the "Layout Model".
thanks in advance.
December 12, 2005 - 8:48 am UTC
I don't know what you mean by "Data Model" (if you are asking about Oracle reports, I have to confess, I've never ever used it - otn.oracle.com -> discussion forums would be a good place to ask about reports/forms questions)
Oracle Forms Vs PL/SQL web toolkit packages
Frank, January 17, 2006 - 11:00 am UTC
Hi Tom,
New Technical manager wants to implement all corporate internal applications in Oracle Forms.
We were using PL/SQL web toolkit packages to write custom web pages for all internal apps. We dont have any trouble in doing that.
Please give us an insight on this important decision. It totally affects majority of our development team.
Earlier HTML programmers used to give Oracle Programmers the GUI in html and all Oracle programmers used to do is connect them to database directly using PL/SQL.
Looking the issue financially, costs of HTML programmer is less than Oracle Forms Developer.
What are the advantages of Oracle Forms Vs Forms generated by regular PL/SQL web toolkit packages.
Thanks in advance.
January 17, 2006 - 4:07 pm UTC
I'd need to understand what the managers motivations where. They could be "reasonable" reasons.
I might suggest you turn them onto htmldb.oracle.com (and yourself if you are writing these by hand). Perhaps part of their concern is the "hand coded" aspect. htmldb could go along way to letting you stay with your existing infrastructure, but cut way way down on the amount of custom code you write.
Adobe LiveCycle
Tony Blakie, March 07, 2006 - 5:52 am UTC
In response to those talking about editable pdfs where for example a user may open a questionnaire, enter their details and save it.
Has anyone come across Adobe LiveCycle Reader Extensions?
bullets are not printed in PDF file
Alex, August 23, 2006 - 6:28 am UTC
Hi Tom,
I'm trying to create a pdf file from oracle report but i'm not getting the bullets in the pdf output from report. I'm using chr(7) to create the bullets.
I get the bullets when i create html or rtf output.
any idea please. what could be the soln.
Thanks
Alex
August 27, 2006 - 3:27 pm UTC
please utilize support (or try the forums on otn.oracle.com - I've never actually used reports myself)
web report
sam, September 24, 2006 - 12:49 pm UTC
Tom:
I have an on-demand inventory report that includes some complex formulas and takes 5 minutes. I took the query and scheduled a batch job every night and saved the results into a table instead so users will get a few second responses.
1. Is this what you usually do for complex reports that take time to run.
2. I need to create archived PDF copies of the report meaning that for 2004 the report should look at the 2004 data and so on.
SInce this would be a static report (does not change) i am thinking of creating a table and saving all those pre-calculated results in it. I just need to add "year" for the year.
Then the user can click on a link and I select records for that year.
what do you think of this solution and is there anyone better?
3. Would you recommend using a tool like adobe acrobat to convert an html report to pdf or use a reporting tool like crystal that can create a pdf format. I think the second option would cleaner format. do you agree?
Thank you
September 24, 2006 - 1:32 pm UTC
1) in oracle we call that a materialized view
2) well, the table wouldn't be an immutable pdf (that might be the goal of the pdf - that it cannot be changed in the future?) I would be happy with saving the data in a table with timestamps - would your users?
3) I've no opinion really on that, use whatever works for you.
report
sam, September 28, 2006 - 10:51 am UTC
Tom:
Is not there a difference between table and materialized view. I think an MV reports data real time while table and nightly batch job you will have a 24 hour delay?
2. what do you mean by adding a timestamp? I tend to think that it should be a static PDF file and not based on a table since someone may change data in table. Cant do it for a pdf file.
September 29, 2006 - 7:31 am UTC
materialized views are
o refresh on demand (could be years old)
o refresh on schedule (could be <schedule> old)
o refresh on commit - could be real time.
the first two, by far and large the most common, refresh on commit, rare.
your "table" and "batch" - as current as you want it to be.
I meant, save your "report data and the time of the report data (eg using a timestamp) in a table, not a pdf"
YOU were the one that said "table", not me.
report
sam, September 29, 2006 - 8:58 am UTC
TOm:
That is correct I said "table". But on a second thought I do not see any benefit of keeping the data in a separate holding table (just for report). For example, if the request was "archive report for all FY 2000 invoices", why create a table to hold those records and then generate a pdf. Just create a report to get the data from the real-data tables and save it in a pdf format and archive it.
Do you agree?
September 30, 2006 - 7:27 am UTC
yes, no, maybe.
You know all of the requirements, if the pdf
a) satisifies them all completely
b) is satisfactory for you
it would be fine with me, although you lose the "queryability" of the data if you ever wanted to do that.
oracle and crystal
sam, February 28, 2007 - 5:01 pm UTC
Tom:
Do you know if you have an oracle web application, whether you can call crystal SDK/APIs to run reports within the application. Crystal offers APIs for Java, COM and .NET so you can integrate reports inside the application.
However, I was not sure how you can do it with oracle because I do not think you can install these APIs inside the database and the application can't access them outside the database. I think this is more when code is residing on the app server?
February 28, 2007 - 5:12 pm UTC
you can call any code you want from your java code - sure.
I'm thinking "java in the middle tier" of course .
as to whether they could be loadjava'ed into the database, I don't know, doesn't sound like a good idea personally.
reports
A reader, April 16, 2009 - 5:55 pm UTC
Tom:
What tool do you usually recommend for end users to query/report on audit data stored in oracle.
Would you give them an ad-hoc tool or discoverer or html page or crystal report, etc?
April 17, 2009 - 9:31 am UTC
SMK, ummm
it is just data, be it audit data or financial data.
use whatever tool you currently use.
I use APEX, APEX does the auditing I need (it audits the actual APPLICATION, not row level changes which are useless to me). APEX has the reports sort of builtin to it.
PDF
sam, May 05, 2011 - 10:54 am UTC
Tom:
Is there any tools in the DB that can generate PDF format?
I do not think there is.
I have a crystal server where i can run a report and export it to PDF via URL call.
I want to be able to store the static PDF file into the oracle table.
Can I write an SP that calls the URL for crystal report to get an OPF and store that in the table. Do you think that will work?
currently when i run the URL, it will open an acrobat and display the PDF. Not sure if i assign this to pl/sql variable what it would have.
May 06, 2011 - 10:15 am UTC
Then you haven't read around enough ;)
http://www.oracle.com/technetwork/developer-tools/apex/documentation/index.html (that's from a link right off of apex.oracle.com, a place I know you've been to before)
Can I write an SP that calls the URL for crystal report to get an OPF and store
that in the table. Do you think that will work?You can write a stored procedure that invokes a URL, yes.
If the file system you create the pdf on is mounted on the database server, you would then be able to use dbms_lob.loadfromfile to load the pdf into the database yes.
If you had a URL that streams the pdf back to you (like it was downloading to a browser), you wouldn't need the file system access, you would just read the result of the URL and put it into a blob in the database directly.
currently when i run the URL, it will open an acrobat and display the PDF. Not
sure if i assign this to pl/sql variable what it would have.that is the URL you want. Read about UTL_HTTP. You have access the stuff in the body of the returned document.
PDF
A reader, May 09, 2011 - 12:24 pm UTC
Tom:
Excellent answer.
AS of now, i am not using APEX for this application. So the asnwer I assume was "NO" applied to 9iR2. Correct? there is no way around it.
The UTL_HTTP and calling a URL that generates the PDF and then uploading it to the table should do it for the time beging. Great hint.
May 09, 2011 - 2:22 pm UTC
Sam - SMK
what about 9i? this is the first you are mentioning 9i
PDF
sam, May 09, 2011 - 5:09 pm UTC
Tom:
I meant to say the link you provided for PDF solution is to use APEX.
IF we are not using APEX yet (9202 does not support it) , I do not think there is a way to generate PDF unless we use a 3rd party tool like PL/PDF or oracle reports. The database does not have built-in features.
May 09, 2011 - 6:03 pm UTC
apex was written originally on 9ir2. But yes, current versions are for 10.2 and above.
reporting tool
A reader, July 12, 2011 - 10:20 am UTC
Tom:
Would a j2ee ad hoc reporting tool like this require install of a 10g oracle application server before it works directly with oracle database? I am asking because we want to use Apache+mod_plsql for pl/sql application instead of buying the whole application server.
http://art.sourceforge.net/HomePage.html
July 13, 2011 - 1:55 pm UTC
Sam -
why wouldn't you ask the maker of a tool what is required?
for all I know, they don't work with our Application server and need "funky application server version 42".
Ask them please.
iSQL*PLUS
A reader, October 02, 2011 - 12:03 pm UTC
Tom:
1) We are hosting an 11g DB production database for a customer who wants to be able to do ad hoc queries and data updates over the web using SQL.
Do you consider iSQL*Plus as a valid tool for ad hoc users to perform SQL queries/reports and update/delete data over the web?
2) I worked at a shop once where they shut down the tool because of security concerns. We had one DB account for everyone.
Do you see the iSQL*Plus as a major security concern where anyone can do a google search and find out the about db server and then try to login and do some damage?
3) Would it be more secure to install it on a different box (web server) than DB box with a firewall between both.
4) Is discoverer the official ad hoc tool for oracle? That is a licensed product and i think we can do the same stuff with iSQL*plus.
October 03, 2011 - 9:53 am UTC
1) no, it is deprecated.
I would look at the sql workshop as part of APEX, application express. You can evaluate it here:
http://apex.oracle.com/ just requisition a workspace and you can play around.
2) the tool wasn't the issue, the policy of a single DB account was. If you want accountability, you'll have individual accounts. APEX can use database accounts OR application managed accounts. It might be what you are looking for.
3) Your security issues had nothing to do with networking. They had everything to do with your choice of a single account. isqlplus wasn't "insecure". Your choices of a single account was.
4) Discover (also a really old product name, superseded by OBIEE - business intelligence) would be overkill for a casual "every now and then" ad hoc query.
reports
sam, October 03, 2011 - 11:10 am UTC
Tom:
Thanks. great info. I will play with sql workshop to see the features available for ad hoc users.
iSQL*Plus must have been deprecated in 11g because I believe it was available in 10g. I wonder if some can still load the web application for it from 10g into 11g database.
This is the security concern I was refering to (not the fact we have one DB account)
http://www.orafaq.com/wiki/ISQLPlus <<<<If the web server hosting iSQLPlus are exposed to the Internet, search engines will index it, and expose your database to potentical hackers. This should be avoided at all costs. >>>>
I can create many accounts, but that would not mean the search engines will not index it.
October 03, 2011 - 12:23 pm UTC
deprecated <> not available
http://en.wikipedia.org/wiki/Deprecation We all need to be aware of the terminology of our profession.
that 'warning' in there is silly. They will not index your database, they crawler would have to be
a) providing a valid user password - which is doesn't have
b) then providing a ton of sql to dump your tables - which is doesn't do
c) then page through it - which it won't
Your single username is however a valid security concern and the fact you are not worried about it worries me. You don't understand what you should or should not be worried about.
That warning in that FAQ is true of ANY DATABASE APPLICATION that is web facing - especially if you use a single username/password (which means A TON of people know the credentials and a TON OF PEOPLE know that you don't know who they are)
Tell me Sam - how would a search engine index your database - if it didn't have a logon?
And furthermore, if your admins don't know how to set up a robots.txt to have things they do not want indexed indexed, you have a bigger security problem than something you read in a random FAQ.
isql*plus
A reader, October 03, 2011 - 1:02 pm UTC
Tom:
Thanks for the link. I guess if it is deprecated dont use it anymore.
<<Your single username is however a valid security concern and the fact you are not worried about it worries me. >>
Where do you see me saying i am not worried about this?
I am in the process of removing all this as we speak so each user have his own account but we would still have one application production account who most developers will be familiar with unless you normally hide that too from developers but it is still installed on client machines (so that is still a security hole).
Now concerning, how search engines index the page, I need to refresh my memory on that. But i think you are right you can select to have your webpage not indexed.
From your writing, you seem to think that they will index the userid/password. I think they mean the visited home blank login page so a hacker sees it and tries a couple of things.
since asktom is an HTMLDB application (similar to SQL workshop in 11g), can you select to not index it anymore so when anyone searches in Google, it wont show up anymore?
October 03, 2011 - 6:14 pm UTC
Where do you see me saying i am not worried about this?
Umm, right here Sam:
<quote>
This is the security concern I was refering to (not the fact we have one DB account)
</quote>
You said "I was worried about X, not about Y"
You should be worried about Y.
one application production account who most
developers will be familiar with unless you normally hide that too from
developer
none of the developers sould be "familiar" with the logon credentials of the production account.
I think they mean the visited home blank login page so a hacker sees it and
tries a couple of things.
that is semi-valid, but the lack of it being indexed does nothing to hide it. The URL is *well known*. the default port is *well known*.
To secure it you would set up your firewall to only allow http traffic on that port to happen from INSIDE your network. your firewall rules would prevent the outside world from hitting that port. It is not a "security" issue to have software running on the application server that can 'see' the outside world, if you have set up the proper rules for *everything*.
can you
select to not index it anymore so when anyone searches in Google, it wont show
up anymore?
I already answered that Sam, see above where I mentioned the robots.txt file.
ad hoc tool
A reader, October 03, 2011 - 7:07 pm UTC
<<that is semi-valid, but the lack of it being indexed does nothing to hide it. The URL is *well known*. the default port is *well known*.>>
Ok, tom your phone number is not listed and you are not listed in the white pages. How can I find out your number and where you live so i cant invite to dinner.
I can't unless I hire a private investigator. your unlisting made it a little hard for me to find out. this is the point of hiding things sometimes like sticking cash under the mattress or a basement tile which seems to more safer than depositing it in a bank these days.
<<To secure it you would set up your firewall to only allow http traffic on that port to happen from INSIDE your network>>.
I cant. My ad hoc users are outside the company.
I think really this whole thing is more of a web application like any banking application. WE have to establish some indivdual user accounts and password rules and then I do not care if the whole world sees it or not.
Hiding it helps a little but that is more a false sense of security.
Thank you for the great knowledge you gave me today. You are like a sea of knowledge that never ends.
October 04, 2011 - 11:14 am UTC
Sam - don't be silly in your analogy.
I know your hostname - it is out there.
I know the URL isqlplus uses - it is standard.
I know the default port - it is standard.
I do NOT need any stinkin web crawler to tell me this information, I'll just add it to my list of "standard urls" on "standard ports" and point my hacking tool at the internet with a list of hosts from whois or some other service that provides them and probe.
I *know* your address, that is how I'll invite MYSELF to dinner - get it? I don't need it indexed, I'll just try it.
And besides, since the isqlplus stuff ran on port 7777 by default - guess what WOULD NOT TYPICALLY HAPPEN. It would not get crawled. Not unless someone set up a page on port 80 and linked to it anyway.
But this is an entirely silly conversation Sam - because anyone that was concerned about this would have of course put into place firewall rules that would prevent the outside world from hitting it - and only permit their inside world. Get it - normal firewall stuff.
You can telnet to your web facing machine from the outside by default, unless you set up a firewall rule to prevent it. You can access many network services by default, unless you set up firewall rules. That is what a security administrator would do.
I cant. My ad hoc users are outside the company.
then guess what? Your ad-hoc interface is going to be just like isqlplus - it will have a logon screen, it will allow them to drop in arbitrary SQL commands. If you use APEX, if you use isqlplus, if you use X (where X is anything) you will have the same exact *security concerns* you have in your head right now.
WE have to establish some indivdual user accounts and password
rules and then I do not care if the whole world sees it or not.
YOU have to do a lot more than that - but that is the first step I told you about above, and hiding things (the big deal you were worried about) isn't your problem (and it won't be different with apex versus isqlplus vs X).
You have to ensure your database is hardened (no default accounts, or as few as possible. No default passwords. Enforcing password complexity on your end users - so they don't use "john/smith" to logon. etc etc etc). Your work is just starting
Report query
Tony, July 24, 2013 - 2:28 pm UTC
Hi Tom,
I have a query which takes about 10 mts to execute when report is invoked through UI ( Websphere application server). But the same query when ran in sqldeveloper is completing in 30 S. Can Websphere change oracle execution environment/session level oracle parameters so that query performance is affected.Is there anything related to websphere AS which can make a query run slower compared to its independent execution in sqldeveloper/sqlplus
Thank you
July 31, 2013 - 7:00 pm UTC
did you really run the entire query in sqldeveloper?
you know that sqldeveloper will have to run it, get the first N records and *stop*
suggest you
a) use sqlplus
b) use a script like:
exec dbms_monitor.session_trace_enable( waits=> true );
select ..........
exit;
c) use tkprof to analyze what you have - that'll show you the true time, not sqldeveloper time.
HTML/CSS Reports
THAKUR CHAND, August 21, 2023 - 9:43 am UTC
How can we devlop report through HTML/CSS in APEX by clicking on button and what is pl/sql code for that?
August 28, 2023 - 7:39 am UTC
Not sure what you mean? Almost any APEX report region (Classic Report, Interactive Report, etc) has a "Download" button to allow downloading the report in CSV, HTML or PDF mode.