Skip to Main Content
  • Questions
  • Execute procedure in anonymous block returns ORA-06550 & PLS-00222

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Krastio.

Asked: April 10, 2019 - 9:45 am UTC

Last updated: April 11, 2019 - 3:55 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi, I created these objects:
-------------------------------
create table mwallet.tb_test
(test_id number GENERATED ALWAYS AS IDENTITY,
 test_name varchar2(50)
);
 

-------------------------------
create or replace procedure test_user.pr_test_input_only
  (vr_test_name in varchar2
  )
as
begin
  insert into
    test_user.tb_test(test_name)
  values (vr_test_name);

  COMMIT;
end;
 

-------------------------------
I tried to execute the procedure as "test_user" with all privilegies and code:

declare
  vr_Return number;
begin
  vr_Return := mwallet.pr_test_input_only
  (vr_test_name => 'pr_test_input_only'
  );
end;
 


and it returns me:

Error report -
ORA-06550: line 4, column 16:
PLS-00222: no function with name 'PR_TEST_INPUT_ONLY' exists in this scope
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
-------------------------------

Any ideas why it doesn't works ?

with LiveSQL Test Case:

and Chris said...

Because you've created a procedure. Not a function.

Procedures don't have return values. Functions do.

If you want to "return" a value from a procedure, you need to use an out parameter. For more discussion on (vs a function), see:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=procedure-having-out-parameter-vs-function

Also...

The procedure exists in the user TEST_USER. But your code is looking for a function in the user MWALLET. I'm assuming this is a copy-paste error...

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you !

Krastio, April 10, 2019 - 6:37 pm UTC

Thank you very much for the answer.
I'm new in Oracle, my background is SQL Server where procedures also return status.
About second comment - it was copy-paste error.
Connor McDonald
April 11, 2019 - 3:55 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library