Home » SQL & PL/SQL » SQL & PL/SQL » Executing procedure
Executing procedure [message #206075] Tue, 28 November 2006 10:10 Go to next message
c341
Messages: 2
Registered: November 2006
Junior Member
Hello,
How to execute the stored procedure with parameters.

========================
CREATE OR REPLACE PROCEDURE my_first_proc
(p_name IN VARCHAR2 := 'Lewis',
p_address IN VARCHAR2 := '123 Mockingbird Ln',
p_an_in_out_parameter IN OUT NUMBER,
p_an_out_parameter OUT DATE )
AS v_a_variable VARCHAR2(30);
BEGIN
IF p_name = 'Lewis' THEN
DBMS_OUTPUT.PUT_LINE( p_name || ': ' || p_address );
END IF;
v_a_variable := 99;
p_an_in_out_parameter := v_a_variable;
p_an_out_parameter := SYSDATE;
END;
==============================

Above is the code for the procedure.

To execute, I gave
execute my_first_proc('Lewis', '123 Mockingbird Ln', 3);

Its not working. How do I execute the procedure?

Thank You
Regards - Joseph
Re: Executing procedure [message #206078 is a reply to message #206075] Tue, 28 November 2006 10:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Its not working.


How is it not working? Does the server explode? Does a deep voice from above say "don't do this, my young padawan" ?

Oh, you get an ERROR MESSAGE like :

ORA-06550: line 2, column 9:
PLS-00306: wrong number or types of arguments in call to 'MY_FIRST_PROC'
ORA-06550: line 2, column 9:
PL/SQL: Statement ignored


which pretty much tells you that you call a procedure with 3 parameters, but the procedure needs four parameters.

And the third and fourth is an output parameter, so you problably have to wrap the call in an anonymous declare / begin / end - Block, e.g.

declare 
v_var1 number;
v_var2 DATE;
begin
my_first_proc('Lewis', '123 Mockingbird Ln', v_var1, v_var2);
END;
/



Re: Executing procedure [message #206097 is a reply to message #206078] Tue, 28 November 2006 14:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
How is it not working? Does the server explode? Does a deep voice from above say "don't do this, my young padawan" ?


Laughing Laughing Laughing
Re: Executing procedure [message #206112 is a reply to message #206075] Tue, 28 November 2006 17:27 Go to previous messageGo to next message
c341
Messages: 2
Registered: November 2006
Junior Member
Hello,

I called the procedure by below code,

====================================
DECLARE
v_employee VARCHAR2(30) := 'BillyBob';
v_number NUMBER := 22;
v_date DATE;
BEGIN my_first_proc(p_name => v_employee,p_an_in_out_parameter => v_number,p_an_out_parameter => v_date );
DBMS_OUTPUT.PUT_LINE(v_employee ||','||to_Char(v_number) || ', ' ||to_char(v_date) );
my_first_proc(p_an_in_out_parameter => v_number,p_an_out_parameter => v_date );
DBMS_OUTPUT.PUT_LINE(v_employee || ', ' || to_Char(v_number) || ', ' ||to_char(v_date) );
END;
====================================

I ran and executed successfully. But i didn't find any ouput values returned to the screen.
What should I do to get the output values.

Thank You
Regards - Joseph
Re: Executing procedure [message #206174 is a reply to message #206112] Wed, 29 November 2006 01:05 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
c341 wrote on Wed, 29 November 2006 00:27
What should I do to get the output values.



RTFM
Previous Topic: How to form this query
Next Topic: excel will read the vachar 1.10 as number when export from plsql
Goto Forum:
  


Current Time: Wed Dec 07 22:22:10 CST 2016

Total time taken to generate the page: 0.08918 seconds