Toad World® Forums

Running a procedure


#1

I am attempting to run a procedure and/or function, it is not very intuitive. The only way I can get it to run is to go to stub, put a / at the bottom of the code and comment out:
–dbms_output.put_line(dbms_utility.format_error_backtrace);

I figured the commented out piece is a db version problem, so I haven’t said anything about it.

Am I making this more difficult than it is? I really have tried to get this working, I didn’t want to ask a question that was not the smartest, but I have to throw my hands in the air and give up at this point…


#2

there are 2 ways to execute stored objects:

  • Open the object and click the Run button in the toolbar or press F9. Then fill in the parameter and press Run the second time. The Execution Console will split into 2 and display the output parameter at the bottom.

  • Run the object to bring up the execution console. Generate a stub then press Run another time. The output values will be displayed in the binded variable window. Execute a stored program using stub may not work properly if your object return a complex parameter like CLOB or user-defined type. You SHOULDN’T have to comment out that line. The only thing it does is format the error message in case of exception.

I don’t quite get it why it doesn’t work for you. Did you press Run and nothing happen (i.e. nothing get displayed in the output window)? Or it doesn’t work as expected? What kind of parameter does it return?

Gwen


#3

When I click Stub I get this:
DECLARE
BEGIN
lcaprogrammer.daletest.do_nothing;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error ‘||TO_CHAR(SQLCODE)||’: '||SQLERRM, 1, 255));
dbms_output.put_line(dbms_utility.format_error_backtrace);
RAISE;
END;

I have to put a / at the end of it to get it to run… Then I get this message:
7:25:40 ORA-06550: line 7, column 35:
7:25:40 PLS-00302: component ‘FORMAT_ERROR_BACKTRACE’ must be declared
7:25:40 ORA-06550: line 7, column 1:
7:25:40 PL/SQL: Statement ignored
7:25:40 **** SCRIPT ENDED 6/2/2008 7:25:40 ****
7:25:40 End Script Execution

But if I comment out this line it then runs.
Now the progams I am running have only dbmsoutput.putline and there is no variables to monitor, all I want is the data in the output window. When I press run all it get is
7:34:12 Procedure/function do_nothing executed in 0.047 sec
7:34:12 End Procedure Execution
No output… It doesn’t really run, I have added a procedure that inserts into a table and ran and I still only get:
7:50:10 Start Procedure Execution …
7:50:10 Procedure/function insert_something executed in 0.047 sec
7:50:10 End Procedure Execution

Nothing was put in the table.

Let me know if you need something else, or if this is not helpful at all…

Here is the copy of what I ran:
PACKAGE DALETEST is
procedure do_nothing;
function go_nowhere return varchar2;
procedure do_less(myvalue in varchar2);
procedure insert_something (p_insert_string in varchar2);
end DaleTest;

PACKAGE BODY DALETEST is
procedure do_nothing is
begin
for i in 1 … 1000 loop
dbms_output.put_line(i || ‘. Dale’);
end loop;
end;

function go_nowhere return varchar2 is
begin
dbms_output.put_line(‘HERE’);
return ‘Nowheres ville’;
end;

procedure do_less(myvalue in varchar2) is
begin
dbms_output.put_line('My Value is: ’ || myvalue);
end;

procedure insert_something (p_insert_string in varchar2) is
begin
dbms_output.put_line('I am inserting: '|| p_insert_string);
insert into loz_temp (application_name, application_id, char15, date1)
values (‘DALETEST’, 1221, p_insert_string, sysdate);
end;
end DaleTest;


#4

Ok so from what I can understand, the problem here is it doesn’t display anything in the output window although there is dbms_output.put_line statement in your code. Is that right?
If it’s the case, simply turn the server output ON by clicking on this icon on the toolbar (see attachment). Another way to do it is tick the Direct results to Output option on the Execution Console and then generate stub. After that press F9 to run as normal.
I tried your package and everything is fine.
Hope this helps
Gwen
Dalecase.gif


#5

The reason you get this message is probably that you don’t have privileges to run package sys.dbms_utility. Actually, in the ‘execution console’ there is an option to generate exception block, but unfortunately it doesn’t work and the generated stub always contains the exception block. This is already fixed for the next build. So all you have to do in the next build is make sure this option is off. For now, you have to delete/comment the line manually. Sorry for the inconvenience.

Roman


#6

I have turned server output off and on and off and on and off and on, no difference.
Here is the results in the output window from Run on the bottom of the package attached (Just in case I am doing this all wrong).

I get:
7:25:58 Procedure/function insert_something executed in 0.047 sec
7:25:58 End Procedure Execution

Run it from a stub and I get (after commenting the line and adding the /)
7:29:47 Start Script Execution …
7:29:47 **** SCRIPT STARTED 6/3/2008 7:29:47 ****
7:29:47 DECLARE
7:29:47 BEGIN
7:29:47 …
7:29:47 PL/SQL block executed
I am inserting: DALE WAS HERE
7:29:47 **** SCRIPT ENDED 6/3/2008 7:29:47 ****
7:29:47 End Script Execution

And the entry is actually in the table.
runpackage.jpeg


#7

In that case, the problem is probably what Roman posted above. Please check the next Beta release to make sure this problem has been addressed. Much appreciated!
Gwen


#8

Works correctly! Thanks…