Calling a Stored Procedure in Oracle with in and Out Parameters

How to call stored procedure with only OUT parameter?

There's no problem with the procedure body. You can call like this :

SQL> var nmr number;
SQL> exec greetings(:nmr);

PL/SQL procedure successfully completed
nmr
------------------------------------------
306 -- > <a numeric value returns in string format> 

Oracle doesn't care assigning a numeric value to a string. The execution prints the result directly, but whenever you want you can recall that value of variable(nmr) again, and print as

SQL> print nmr
nmr
---------
306

Oracle Run Procedure with one in parameter and multiple out parameter

This is the correct syntax

Declare 
x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
GET_EMPLOYEE(1, x, y, z);
DBMS_OUTPUT.PUT_LINE(x);
End;

calling stored procedure with cursor and out parameter

To answer your original question, print rc is a SQL*Plus command, so it needs to be outside the PL/SQL block. execute is also a SQL*Plus command and is not used in PL/SQL. So your code should look like this:

set serveroutput on 
var rc refcursor;
declare
  mycount number(19);
begin
  sprocvPOP_GetvemployeeByFilter (NULL,NULL,1,10,mycount,:rc);
  dbms_output.put_line(mycount);
end;
/
print rc;

However, it turns out you are using SQL Developer not SQL*Plus client to run your code. Not many SQL*Plus commands are natively supported in SQL Developer. The list is here.

The latest versions of the tool come with a Command Line interface which is very neat. Find out more.

Alternatively, use the built-in Run PL/SQL functionality as described in this tutorial

How to select stored procedure output parameters as a table in oracle sql developer

To me, the most straightforward option is to create a function - it is supposed to return a value:

create or replace function proc (p1 IN varchar2, p2 IN varchar2)
  return varchar2
AS
BEGIN
  return p1 || ' ' || p2;
END proc;

Then you'd call it as e.g.

select proc('a', 'b') from dual;

---

Saying that you "don't have permission to create ... functions" - well, if you are granted to create a PROCEDURE, then you are also granted to create a FUNCTION. It is the same privilege.

---

If you can only use procedures that are already created, then:

SQL> create or replace PROCEDURE proc
  2  (
  3    p1 IN varchar2,
  4    p2 IN varchar2,
  5    p3 OUT varchar2
  6  ) AS
  7  BEGIN
  8    p3:= p1 || ' ' || p2;
  9  END ;
 10  /

Procedure created.

SQL> var result varchar2(20)
SQL>
SQL> exec proc('a', 'b', :result);

PL/SQL procedure successfully completed.

SQL> print :result

RESULT
--------------------------------
a b
    
SQL> select :result from dual;

:RESULT
--------------------------------
a b

SQL>

Call Oracle StoredProcedure with multiple output parameters

Spring boot JPA starter calls StoredProcedureJpaQuery class on initialization.
That class contains a property called useNamedParameters.

Calling your stored procedure like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName();

means that spring JPA will map your IN and OUT parameters by position
To avoid this, you have to use the annotation @Param in your repository, like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName(@Param("PO_VALUE1") String value1);

using the @Param annotation will set the useNamedParameters property to true and Spring JPA will not start looking by position instead by name. But, I don't have any IN parameters in my query. So I changed the method to INOUT like this ...

@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "PO_VALUE1", type = String.class)

and passing an empty string when I call the repository ...

Map<String, Object> spNameMap = tableNameRepository.spName("");

That solution works for me, but I'm open to see a better solution for this.

How can I call stored procedure within a procedure and print the output in same line?

You can create a Private Procedure to achieve this. See below:

CREATE OR REPLACE PROCEDURE p1 (fn IN  VARCHAR)
AS
   v_nam varchar2(100):='Bob'; 
   --private Procedure
   PROCEDURE p2 (LN IN OUT VARCHAR)
   IS
   BEGIN
      null;
   END;

BEGIN
   p2(lN => v_nam);
   DBMS_OUTPUT.put_line (fn ||' '||v_nam);
END;

Execution:

SQL>  exec p1('Alex');

Alex Bob

PL/SQL procedure successfully completed.

---

Related Topics