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

Procedure communictaion

Hi Everyone
I have a procedure attached with form (FOrm 6i, Oracle 8i DB depolyed on Application server 9).This procedure processes a series of transactions for a number of custommers say 100, one by one. The procedure takes long time to complete say 3 hours and is fired from Save button of a form. The form is having a message box . When the procedure completes a message like "Succesful Completeion " is displayed here. The user does not get communictaion in between.
Can I have a solution where the procedure return a message for each cosumer record, like customer 1 record completed. Aftyer some time customer 1 record completed.If I use a DBMS_OUTPUT.PUT_LINE , How can i fetch the message on form , when the proc is still running. Or is there any pther way. Please help
Prashant 
UIs this a Forms PL/SQL procedure or a database procedure? Either way, a procedure which takes three hours to run seems like it ought to be run asynchronously rather than through an interactive Form.
Cheers, APC
Blog : http://radiofreetooting.blogspot.com/ 
Better ask this in the Forms forum.
You could write some message on the status bar after each record, so the the user sees that something is still going on.
Also if I remember right there was a synchronize command to update the forms window. Just in case that the message can't be seen without synchronizing. 
if I remember right there was a synchronize command to update the forms window.synchronize() is to be avoided in web-deployed Forms. Also, if this is a database procedure, there will be no output to display until the procedure completes.
As is often the case, the OP has failed to provide us with enough information to answer their question in a sensible fashion.
Cheers, APC
Blog : http://radiofreetooting.blogspot.com/ 
Hi,
Call procedure once for each record (Will make it more Slower as it would increase communication time]
Or
After every record processed, enter one record in a temp table. Query the temp table after each fix period say 5 minutes and display update to user.
Regards,
Arpit Shah 
The procedure is a database procedure and the transactoin requires either all the records be commited or rolled back.

progress bar for a procedure on the server !!

Hello,
I want to show a status bar in a form which shouls show the execution progress of a procedure located on the server and not in the forms .
How can i do this ??
Any bright ideas ???
Thankx for your time
Nipun Suri 
simplest way is to have a button on the form and use set_item_property(width) to change its size to grow from 0 to 100 as the procedure is executing.
You'll need to get feedback from the procedure in the database to know how far you have gone and then call the set_item_property followed by synchronize;
How to get feedback from the procedure in the database ??
Can you show as example? 
exactly..how to get information on the execution status of the procedure ??
Is there any way by which we can send some sort of message to the forms runtime from inside the procedure code ??--this way we could put the message in different parts of the procedure and pass on the info to forms and then show the status in the forms runtime ??
But what is the way of passing info to forms ??
That is the big question?? 
exactly..how to get information on the execution status of the procedure ??
Is there any way by which we can send some sort of message to the forms runtime from inside the procedure code ??--this way we could put the message in different parts of the procedure and pass on the info to forms and then show the status in the forms runtime ??
But what is the way of passing info to forms ??
That is the big question?? 
one option is to run your procedure in batch using the dbms_job package.
Have the procedure signal that it reached specific stage in execution by flagging a message using either advance queuing or dbms_pipe package.
Have form wait for this alert or have the form check for an alert with a timer mechanism. 
If you or your DBA do not want to use DBMS pipe simple create a global temp table, available from 8i onwards. Global temp table are similar to pl/sql table & you can do any DML activities on it.
So lets assume your global table has two columns first total rows & second processed rows.
Before opeing cursor in your job simply count the rows that will be data for first column of Global Temp Table. Then within your procedure you can set a if clause to update the second columns for processed rows, say for increment of 5 or 10%. Accordingly you may increase the display length of progress bar.
Hope this will help.
Thanks
Satish 
With dbms_job package is possible to do something. But only to call stored procedure from forms there is no way that you can get response from that stored procedure (even if you fill two columns in global temp table how you can refresh forms when stored procedure is not in forms but in database??). This looks like black hole, forms become [not responding] until stored procedure finished so you can not refresh forms. 
Dragan,
if you'll call dbms_job from forms to invoke your procedure it will execute asynchroniscally and you'll be able to continue working in Forms while the procedure executes. And therefor you'll be able to get info from the procedure and react to it in forms.
Another option is to break your procedure into several steps each one in a different procedure and change the forms at the end of each step.

how to use synchronize in backend procedue

i am calling a backend procedure in form . i want to display some outputs on form and output will be keep on changing ... so tried to use synchronize.
but it doesnt support at the backend...
scenario is i am processing some data in the table and there are various checking... which i validating through back end procedure... but i want to display the process of every records so that i can see at which record it is processing... suppose i am processing thousand employee records in a table... so want to display employee no every time for each record...
so any suggestion will be helpful...
thanks
Edited by: Hi FRNzzz!! on Jan 19, 2012 6:45 PM 
Probably the way I'd do it is to call the backend procedure from dbms_scheduler so it runs in the database. The backend procedure can then log output into a logging table. Meanwhile your form is then free to query the logging table to see what is happening.
hth
Steve 
i want to display some outputs on form and output will be keep on changing And how does your procedure do that? If, for instance, you want to use dbms_output to display intermediate results, that's not going to work (not even in sqlplus).
The output of a procedure is usually seen after the procedure finishes. So, how does this procedure produce changing output? In the way Steve suggests (store output in a table and commit every time)? 
i have edited my question... if it can u to understand... 
Run your procedure with the scheduler as Steve suggested, instrument your code using dbms_application_info and query v$session and v$session_longops in your form.
cheers 
What you DON'T want to do is is COMMIT every n (ie: 100) rows that you've processed. Create a second logging procedure with an autonomous transation that will commit to the log table and then call that every n rows. Committing in PL/SQL every n rows is a bad idea (see asktom.oracle.com for reasons why).
Christians suggestion of dbms_application_info is another good way as it'll show up in sessions (we also do this as a standard trigger on each Form as it starts). 
thanks... i read whatever u suggested... hmm its quite difficult for me to understand and how to use... 
ahhh, but then what you are doing is not straightforward SQL updates so there is a price to pay ... :) 
thanks to all.
but don't know my query is clear to you all not ... anyways in simple i am trying to make you all understand..
see there is a form with display item A. and in that form in a button trigger i am calling a backend procedure XYZ
xyz(:some_form_input_value, :A) -- here :A output value which i am returning through procedure as shown below
now
in that procedure i have written something like that
procedure xyz (A in number, B out number) is
begin
for i in ( select * from emp)
loop
B:=i.empno;
end;
so whenever B value gets change it should in my display item A at form level..
hope it will help...
thanks 
We perfectly understand your problem. But there is no way to just quit in the middle of a procedure, do something else and resume where you left. So either you break up your procedure in smaller parts, or use the method to call the procedure in the background, log with the tool of your choice (dbms_application_info, or a homegrown log mechanism - it's up to you) and query those log infos to see where the procedure actually is. dbms_application_info for example has the benefits that it is already there and various tools already use it (enterprise manager,...) so you can see what's going on not only from your form but from various other tools. If you are on forms 11 you could even use AQ to write in a message queue in your stored procedure and read this queue in forms. It is also not forbidden to mix all the things to match your requirements.
cheers

Questions regarding procedures

1. How do we develop a procedure to create our own print statement?
2. How do we write a procedure to accept for example deptno as input from an employee table and print all the details of employees ( name, number, job etc) with their grade from a salary table?
Thanks 
Homework?
What have you tried so far? 
user8706975 wrote:
1. How do we develop a procedure to create our own print statement?Print on what, where and how?
PL/SQL cannot "+print+" anything. It is a background server process. It is not connected to a mouse or keyboard to read user input. It is not connected to a computer screen or printer to provide user output.
2. How do we write a procedure to accept for example deptno as input from an employee table and print all the details of employees ( name, number, job etc) with their grade from a salary table?Agree with Tubby. Sounds like homework.
The correct way to do this is to create a procedure that accepts the data from the client software as parameters. The client program needs to interact with the user.
It then creates a reference cursor for the required rows and returns that to the client. The client program uses this reference cursor, fetches from it, and displays the data to the user.
Details on REF CURSORs in the [Oracle® Database PL/SQL User Guide and Reference|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1392].
As for the client part - I assume you will be using SQL*Plus. You need to research how to use bind variables in SQL*Plus (the VAR command in SQL*Plus) - you need to use that to call the stored procedure and receive (as output variable) the reference cursor.
As for displaying the ref cursor in SQL*Plus - that's the easy part. SQL*Plus sports a PRINT command that does that for you.
All this information is readily available in the Oracle manual, in these forums and on the net. And hopefully part of this homework assignment is teaching you how to use the resources available (documentation and the Net) to research and solve a problem. That is as important as writing the right code at the end of the day.

A strange issue on PL/SQL and Crystal report

A Crystal rpt file (version=8.5) is called through CRAXDRT.DLL by a VB6 exe. This rpt file has two linked sub-reports. There are 3 Oracle stored procedures behind the main report and two sub-reports. I found that the stored procedure behind the main report is always called twice within the same Oracle session when the VB based report is run. Did someone else ever meet the similar situation and know why?
Thanks in advance! 
How can you tell it was called twice ?
Do you have any queries or reports which shows that?
My best guess is it should be calling twice.
Ss 
Thanks for your response. In fact, the stored procedure for the main report is responsible for querying data and inserting them into a oracle table (report table); the stored procedures for the sub-reports only summarize the data stored in the above report table. The main stored procedure are not called in the stored procedures for the sub-reports. I attach a field consisting of the current Oracle PL/SQL session number and system time to each recod in the report table. So I can trace the process of inserting data by the main stored procedure and found that the main stored procedure actually insert the same data twice within the same Oracle session.
Thanks

Categories

Resources