Oracle return the value of a variable-CodePudding

I am migrating Microsoft Sql Server Database to Oracle 19c, a query migrated is failing:

            DECLARE cardnum varchar2(100);
            BEGIN
            MyProcedure('ES', cardnum );
            SELECT cardnum;
            END;

The error returned is:

     PL/SQL: ORA-00923: FROM keyword not found where expected

I think that the error is in the line 4:

     SELECT cardnum;

The procedure sets the value of the variable, therefore I need to return the value of the variable "cardnum".

What should i use instead of select clausule?

Note --> this query is used from net core with Dapper.

Thank you!

CodePudding user response：

Well, you already have cardnum, procedure set it as its OUT parameter, you don't have to do anything about it.

SQL> create or replace procedure myprocedure(par_1 in varchar2, par_2 out varchar2) is
  2  begin
  3    par_2 := 'ABC';
  4  end;
  5  /

Procedure created.

SQL> declare
  2    cardnum varchar2(100);
  3  begin
  4    myprocedure('ES', cardnum);
  5
  6    dbms_output.put_line('Cardnum = ' || cardnum);
  7  end;
  8  /
Cardnum = ABC

PL/SQL procedure successfully completed.

SQL>

If you want to select it, you can - but PL/SQL requires an INTO clause. For example:

SQL> declare
  2    cardnum varchar2(100);
  3    l_var   varchar2(100);
  4  begin
  5    myprocedure('ES', cardnum);
  6
  7    select cardnum into l_var from dual;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

It depends on what you actually want to do with cardnum ...