0

I am trying to run PLSql procedure in Software AG Designer application, while running it as a service, it doesn't ask for input. The Stored procedure is as follows:

CREATE OR REPLACE PROCEDURE getProductDetails(productName VARCHAR, productResult SYS_REFCURSOR) AS

BEGIN
  OPEN productResult FOR
  SELECT product_count from product WHERE product_name = productName;
END;

And the I call the procedure as follows:

SET SERVEROUTPUT ON
VAR rs REFCURSOR;

BEGIN
  getProductDetails('pendrive', :rs);
END;

print rs;

Am I doing anything wrong with the Stored Procdure?

Please Help. Thanks.

Panther Coder
  • 1,058
  • 1
  • 16
  • 43
  • 1
    I don't know "Software AG Designer" (never heard of it, actually). Code you posted should work in **SQL Plus**. But, what "input" are you talking about? Nothing in your code suggests that you should be asked for any "input" ... Procedure accepts one IN parameter and you provided it (`'pendrive'`, right?). – Littlefoot May 25 '21 at 06:15

1 Answers1

1

It should not ask for input. (However, you do need a / on a newline at the end of the PL/SQL block):

SET SERVEROUTPUT ON
VAR rs REFCURSOR;

BEGIN
  getProductDetails('pendrive', :rs);
END;
/

print rs;
  • Line 1 is just telling Oracle that the client expects to receive the data which is output to the buffer (rather than the default when Oracle does not share the output with the client).
  • Line 2 is declaring a cursor bind variable.
  • Line 4 starts an anonymous PL/SQL block.
  • Line 5 calls your procedure passing the IN parameters of your productName as the string literal 'pendrive' and productResult as the rs bind variable.
  • Line 6 ends the anonymous PL/SQL block.
  • Line 9 prints the value of the bind variable.

None of those require any input from the user.

However, if you are asking about not getting any output; then it is probably because you want the productResult to be an OUT parameter (rather than IN):

CREATE OR REPLACE PROCEDURE getProductDetails(
  productName   IN  VARCHAR,
  productResult OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN productResult FOR
    SELECT product_count
    FROM   product
    WHERE  product_name = productName;
END;
/
MT0
  • 143,790
  • 11
  • 59
  • 117