'How to call a Oracle stored precedure with output parameter in .net 3.1 command type as text?

I have created a following oracle stored procedure to fetch the table data.

CREATE OR REPLACE PROCEDURE GetListOfEmp (
    EMPCURSOR OUT SYS_REFCURSOR
)
AS
Begin
Open EMPCURSOR For
SELECT ID, Name,Value FROM Emp;
End;

Now using c#.net core 3.1 I am trying to execute the stored procedure as below:

> DataSet ds = new DataSet("Datapoints");
>             using (var connection = new OracleConnection("ConnectionString"))
>           {
>              using (OracleCommand cmd = connection.CreateCommand())
>               {
>                   connection.Open();
>                    cmd.CommandText = "var empcur refcursor;Begin GetListOfEmp(:empcur); END;";
>                 cmd.CommandType = CommandType.Text;
>                   OracleDataAdapter da = new OracleDataAdapter(cmd);
>                  OracleCommandBuilder cb = new OracleCommandBuilder(da);
>                da.Fill(ds);
>              }
>           }

In this case getting error as "ORA-00900: invalid SQL statement". But same command text works in the Toad for oracle. Error Message



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source