Can I call Oralce Graphics from Pl/SQL Procedure - SQL & PL/SQL

Hi,
I have this requirment of creating graphs which should take 50
diff values from a table as parameter.
Can I create a PL/SQL Procedure that will open Oracle Grpahics
and pass this 50 diff values one at a time and print it. Then
Close the Graph?I mean open 1st time pass 1st value close the
graph and open again and so on.
Any hint/suggestion is appreciated.
Thanks

PL/SQL is server based. Graphics is client-based so the answer
is No. You can however do want to want with a Form, and call
Graphics from that.
HTH, APC

Related

PL/SQL Table Urgent

Hi, How can I read the data of a PL/SQL Table into a ref cursor ?
Its necessary that I need to read it into the reference cursor.
Thanks
Feroz 
In the ref cursor you can fetch the data from the database.Since
PL/SQL tables are created in PL/SQL and cannot be stored in the
database it is not possible to place them in ref cursors.
If you can further elaborate on the requirement may be I can
suggest and alternate way of doing it.
Thanks for the reply. My requirement is , I am trying to build a
report which needs some calculations . This calculations is
tedious to do it in the back end. Because it needs 2 cursor. For
example bring all the items in one cursor and for each item do a
set of calculations and display it along with the item. Like you
do it in a post query in the form. But here for each item there
are 40 calculations to be done. So If I put in the report level,
I guess it will take forever to run. And I cant create a
temporary table. So I thought if I can dump the data in a PL/SQL
Table and then take the data and build the report. But report
supports only reference cursor to build the query. So there I
got stuck. If you can suggest me anything, that will be great.
Thanks
Feroz

PL/SQL in Report Builder

Hi, I am trying to run PL/SQL function in the Formula Column. This PL/SQL function will run a query and return the records into the report. Here is the PL/SQL function (called Test).
function TestFormula return varChar2 is
stuName varchar2(40);
cursor c1 is
select all stumst.stu_name
from stumst;
begin
open c1;
LOOP
fetch c1 into stuName;
EXIT WHEN c1%NOTFOUND;
--> dbms_output.put_line(stuName);
end loop;
return stuName;
close c1;
end;
What I trying to do in this PL/SQL function is to output all the records of the STUMST.STU_NAME. But the result I get from this PL/SQL function is the last record in the table. How do I display all the records in the STUMST.STU_NAME inside the loop.
P/S: I notice that the line (pointed with "-->") does not work. What is the correct statement to output the record within the loop?
Thank you. 
This is completely the wrongapproach. Report Builder allows us to build reports composed of interconnected SELECT statements: that is want it is supposed to do. There is no need for PL/SQL blocks like this.
If you are actually intending to do something more complicated than the example you've shown you should explain what it is you want to achieve and maybe we can help.
DBMS_OUTPUT is only for displaying records to the screen. If you want to return a result set you need to use a REF CURSOR. However, my experience with Reports Builder is too old to tell you whether it plays nicely with cursor variables.
Finally, you may be interested to know there is Reports.
Cheers, APC 
Andrew,
I'm sorry, but I do not agree with your comment "There is no need for PL/SQL blocks like this."
One of the reason that I have chosen Oracle reports is the PL/SQL capabilities that it has. Sure ref_cursors are good too, but writing PL/SQL blocks in a function column is one of the most powerful things that Oracle Reports has to offer.
Martin
Martin
You actively chose Reports because of its features rather than because it came bundled with iDS? I salute you! You are a rare gem.
Anyway. I wasn't dissing all PL/SQL merely the example given. It does nothing but a straightforward SELECT which I don't think that is a sensible use of a Reports FUNCTION column. But if that's what the guy wants to do then he's got to use a REF CURSOR. There is no other way of returning record sets from PL/SQL.
Now I happen to think that he would be better off better that query into his report straight, but then I haven't actually used Reports in years and my thinking may be old fashioned.
As an aside, I think writing PL/SQL in the Report is generally a bad idea. I like to have all the Functions in the database, it's a lot easier to re-use things that way.
Cheers, APC 
Hi,
"P/S: I notice that the line (pointed with "-->") does not work. What is the correct statement to output the record within the loop?"
Dbms_Output.Put_Line procedure is not supported by ORACLE reports/ and forms.
The equivalent in :
Oracle Reports : I don't remeber the exact package
procedure name but it starts with SRW
Try searching for "SRW" using online help in ORACLE reports builder.
SRW_Message (-123, 'This is Test');
Oracle Forms : Message Built-In
-- Shailender Mehta -- 
Dear Shailender,
Hi, thanks for your suggestion. But the SRW_MESSAGE is to prompt out the window telling the error message. e.g.
SRW_MESSAGE(100, 'PLEASE ENTER AGAIN');
What I would like to do is to configure a column in the report so that after the PL/SQL formula (e.g. Test) is compiled and run, I can choose that PL/SQL formula in the Property Inpector (Field -> Source) combo box.
Please help.
Thank you. 
As you may have gathered this is not a forum brimming with people who use Reports (some of has have historical experience). I think this sort of question is much better directed to the Reports forum. Use the link in my previous posting.
Cheers, APC

Stored Procedure for displaying data in Crystal Reports

Hi I am new to Oracle & Crystal Reports,so can any one please give me sample stored procedure to display data in Crystal report from Oracle by passing rwo input values. 
Refer the following site:-
http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp 
from site:-
Crystal Reports will only support the return of a single refcursor from Oracle. Even the latest and greatest version 10 doesn't allow you to return more than one recordset at a time. You could add one more step to your procedure that would join up the output of the multiple refcursors into a PL/SQL table and then fetch the result set out through a single refcursor. That way you are using temporary space that will be cleaned up as soon as the connection is terminated and will only be returning a single refcursor

PL&SQL IN OUT

Hi,
I want to use you Stored Procedures in an VB Code.
So, to get the infos I want I have an Stored Procedure with on IN Parameter an about 6 OUT Parameters.
The question is can I use the OUT Parameters for an another Procedure to open an update-page where I can see the current values of the specific row in the table and if I want to update then?
Regards
Marcel

How do write a stored Procedure in PL/SQL

Hi,
I am new to this forum. I am creating a webpage using Gridview in Visual Studio 2005.
How to write a Stored procedure in Oracle using PL/SQL?
I want to write a SP for the following query :
"Select S_No, Task_ID,Last_Act_Dt,Last_Act_Tm,Status from E002 Order by S_No"
Please help. 
I wil tell you something. A stored procedure is a piece of code which takes some inputs, does some calculations with/without the help database data based on the request from you and gives you certain outputs if needed.
In this case, I just see that you want to select data from the table and it is not going to change with any parameter that you give. In the first place, if you can do that in SQL, why do you need a PL/SQL procedure for that?
Cheers
Sarma. 
Thanks for your reply.
I am creating one webpage using Visual Studio 2005.
In the webpage,I am creating one table using Gridview in Oracle Database.
So,I want to create a SP in PL/SQL.
If I deploy my Project ,I want to change the query.so, SP can only change the query. 
In Oracle, you normally wouldn't create a stored procedure that just does a query. Depending on what you're attempting to do on the client side, and the client libraries involved, you may be able to write a stored procedure that returns a REF CURSOR and call that. This will put some limitations on what the client can do with the results, for example. The data would be read-only and scrolling back and forth in the result set would potentially rather slow and rather expensive in terms of client memory. That makes this generally a somewhat unattractive option.
You could create a view in Oracle and just query the view in your application code. If you needed to change the query, you could then just change the view.
Justin

Categories

Resources