CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
user2084446
user2084446

Reputation: 119

Calling a stored procedure with a select

I have a query and i have to put it in a stored procedure but when I call the stored procedure it doesn´t return anything;

This is my query:

select * from mdc_cat_parametros where param_llave='SMTP_SERVER';

When I execute the query it returns one row but the stored procedure doesn´t.

This is my stored procedure:

CREATE OR REPLACE PROCEDURE MDC_UTIL_PROCEDURE
AS
    RT MDC_CAT_PARAMETROS%ROWTYPE;
BEGIN
    SELECT * INTO RT FROM MDC_CAT_PARAMETROS WHERE PARAM_LLAVE='SMTP_SERVER';
END MDC_UTIL_PROCEDURE;

I call the stored procedure:

EXECUTE MDC_UTIL_PROCEDURE;

Upvotes: 1

Views: 192

Answers (2)

OldProgrammer
OldProgrammer

Reputation: 12179

Your procedure is just fetching the result set into a local variable in the procedure. When the procedure exits, the variable is no longer in scope. If you want to return data from a select, you need to use a ref cursor. Here is a link to some good examples

Upvotes: 0

Samuel Neff
Samuel Neff

Reputation: 74949

The stored procedure is populating RT but you then need to select out of it:

CREATE OR REPLACE PROCEDURE MDC_UTIL_PROCEDURE (results OUT SYS_REFCURSOR)
AS
    RT MDC_CAT_PARAMETROS%ROWTYPE;
BEGIN
    SELECT * INTO RT FROM MDC_CAT_PARAMETROS WHERE PARAM_LLAVE='SMTP_SERVER';
    OPEN results FOR SELECT * FROM RT;
END MDC_UTIL_PROCEDURE;

or you could simplify it to get rid of the RT variable:

CREATE OR REPLACE PROCEDURE MDC_UTIL_PROCEDURE (results OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN results FOR 
    SELECT * FROM MDC_CAT_PARAMETROS WHERE PARAM_LLAVE='SMTP_SERVER';
END MDC_UTIL_PROCEDURE;

Upvotes: 2

Related Questions