CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle-sqldeveloper
Mr. Boy
Mr. Boy

Reputation: 63728

How to execute a stored procedure which has an output parameter

This question answers about calling an Oracle stored procedure: How to execute an oracle stored procedure?

However I have a procedure which takes one IN and one OUT parameter. What should my test syntax in Oracle SQL Developer look like to define a variable, run a stored procedure, and output the result of that variable?

I'm trying this but declaring the variable gives me an error:

begin
xyz MY_TABLE.EMAIL_ADDRESS%TYPE := NULL;
myPackage.GetEmailForId('12345',xyz);
end;

Upvotes: 0

Views: 8986

Answers (2)

MT0
MT0

Reputation: 167981

What should my test syntax in Oracle SQL Developer look like to define a variable, run a stored procedure, and output the result of that variable?

Create a unit test.

  1. Go to Tools > Unit Test > Select Current Repository and follow the prompts to set up a unit test repository.
  2. Go to View > Unit Test to open the Unit Testing view.
  3. Right-click on tests and choose Create test.
  4. Choose the connection and then pick the procedure you want to test.
  5. Specify the test name and select Create single with dummy implementation then click Next.
  6. Click 'Next' (you don't need any startup).
  7. Specify the values for the parameters then click Finish.
  8. The test should then appear in the Unit Test view and you can right-click and select Run Test.

Oracle's documentation for unit tests is here.

Upvotes: 0

Shannon Severance
Shannon Severance

Reputation: 18410

declare
    xyz MY_TABLE.EMAIL_ADDRESS%TYPE; 
begin 
    myPackage.GetEmailForId('12345',xyz);
    dbms_output.put_line(xyz);
end;

Upvotes: 3

Related Questions