Cursor out parameter example request.

[francescm]

I would really appreciate an example with a cursor out parameter in a stored procedure.

I am not able to translate to working elixir code the test fragment at: test/jamdb_oracle_SUITE.erl with_input_and_output_params.

If a procedure has signature:

   procedure myprocedure(someData varchar2, aSqlErrM out varchar, aRows out sys_refcursor )

what code is needed?

    conn_opts = Application.get_env(:myapp, :conn_opts)
    {:ok, pid} = Jamdb.Oracle.connect(conn_opts)

    sql = "BEGIN MYPKG.MYPROCEDURE(:1, :2, :3); END;"
           |> to_charlist
    data = "my_input_value"
         |> to_charlist
    Jamdb.Oracle.query(pid, sql, %{
      :"1" => {:in, data},
      :"2" => {:out, "0"},
      :"3" => {:out, :cursor}
    })

{:error,
 [
   {:jamdb_oracle_tns_decoder, :decode_ub4, 1,
    [file: 'src/jamdb_oracle_tns_decoder.erl', line: 481]},
   {:jamdb_oracle_tns_decoder, :decode_token, 2,
    [file: 'src/jamdb_oracle_tns_decoder.erl', line: 80]},
   {:jamdb_oracle_tns_decoder, :decode_token, 3,
    [file: 'src/jamdb_oracle_tns_decoder.erl', line: 126]},
   {:jamdb_oracle_tns_decoder, :decode_token, 3,
    [file: 'src/jamdb_oracle_tns_decoder.erl', line: 111]},
   {:jamdb_oracle_tns_decoder, :decode_token, 3,
    [file: 'src/jamdb_oracle_tns_decoder.erl', line: 169]},
   {:jamdb_oracle_conn, :handle_resp, 4,
    [file: 'src/jamdb_oracle_conn.erl', line: 232]},
   {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 43]},
   {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}
 ], #PID<0.327.0>}

[vstavskyi]

Thank you for finding the issue! Please check out commit + commit and new test with_multi_output_params.

It's also possible to use keywords number, varchar, nvarchar, date, etc for declaring output parameters. commit and updated test with_output_params.

[francescm]

Super! That's perfect. Tested the output params binding with keyword. It works too. Thank you so much.

[francescm]

There can be a exiting/closing issue?

I can execute the stored procedure with Ecto one time for every worker in the connection pool. Then it fails with:

(DBConnection.ConnectionError) [{:lists, :zip, [[], [{:format, "SAMBANTPASSWORD", :in, 1, 80, 0, 873}, {:format, "UIDNUMBER", :in, 2, 22, 0, 0}, {:format, "TYPE", :in, 1, 20, 0, 873}, {:format, "PWDPOLICYSUBENTRY", :in, 1, 128, 0, 873}, {:format, "UNIMOREIDTESSERASANITARIA", :in, 1, 40, 0, 873}]], [file: 'lists.erl', line: 387]}, {:lists, :zip, 2, [file: 'lists.erl', line: 387]}, {:lists, :zip, 2, [file: 'lists.erl', line: 387]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 165]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 234]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 43]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}]
    (jamdb_oracle) lib/jamdb_oracle_ecto.ex:176: Ecto.Adapters.Jamdb.Oracle.Connection.error!/1
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6

Of course supervisor starts again the pool worker and then it works one time, then fails, and so on.

This pattern shows up only with stored procedures. Other queries do work as expected.

[vstavskyi]

Could you give me a little more info?

My working example:

CREATE OR REPLACE PROCEDURE "UA"."ISSUE"
(ID IN NUMBER,RET OUT VARCHAR2)
AS
EX EXCEPTION;
BEGIN
  if ID > 0 then
    RET := ID;
  else 
    raise EX;  
  end if;
EXCEPTION
  when others then null;
END;

  def issue(id) do
    Jamdb.Repo.query("begin issue(:1, :2); end;", [id, out: :varchar])
  end

iex(2)> Jamdb.issue(2)

11:36:53.715 [debug] QUERY OK db=0.0ms
begin issue(:1, :2); end; [2, {:out, :varchar}]
{:ok, %{num_rows: 1, rows: [["2"]]}}

iex(3)> Jamdb.issue(-2)

11:36:58.483 [debug] QUERY OK db=0.0ms
begin issue(:1, :2); end; [-2, {:out, :varchar}]
{:ok, %{num_rows: 1, rows: [[nil]]}}

[francescm]

Yes, thank you for the answer.

It is a issue related to cursors, in my opinion:

create or replace package body PKG_ISSUE is

PROCEDURE UA
(ID IN NUMBER, RET OUT sys_refcursor)
AS
EX EXCEPTION;
BEGIN
  if ID > 0 then
    open RET for
          select id as "MYKEY" from dual; 
  else 
    raise EX;
  end if;
EXCEPTION
  when others then null;
END;

end PKG_ISSUE;

def ua(number) do
     query(
       "BEGIN PKG_ISSUE.UA(:1, :2); END;",
       [number, out: :cursor]
     )
 end

Now, with a pool of size 3:

Interactive Elixir (1.7.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> Orasia.CesiaRepo.ua(2)
{:ok, %{columns: ["MYKEY"], num_rows: 1, rows: [[2]]}}
iex(2)> Orasia.CesiaRepo.ua(2)
{:ok, %{columns: ["MYKEY"], num_rows: 1, rows: [[2]]}}
iex(3)> Orasia.CesiaRepo.ua(2)
{:ok, %{columns: ["MYKEY"], num_rows: 1, rows: [[2]]}}
iex(4)> Orasia.CesiaRepo.ua(2)
** (DBConnection.ConnectionError) [{:lists, :zip, [[16], []], [file: 'lists.erl', line: 387]}, {:lists, :zip, 2, [file: 'lists.erl', line: 387]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 165]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 234]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 43]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]
    (jamdb_oracle) lib/jamdb_oracle_ecto.ex:176: Ecto.Adapters.Jamdb.Oracle.Connection.error!/1
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
iex(4)> Orasia.CesiaRepo.ua(2)
** (DBConnection.ConnectionError) [{:lists, :zip, [[16], []], [file: 'lists.erl', line: 387]}, {:lists, :zip, 2, [file: 'lists.erl', line: 387]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 165]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 234]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 43]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]
    (jamdb_oracle) lib/jamdb_oracle_ecto.ex:176: Ecto.Adapters.Jamdb.Oracle.Connection.error!/1
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
iex(4)> Orasia.CesiaRepo.ua(2)
** (DBConnection.ConnectionError) [{:lists, :zip, [[16], []], [file: 'lists.erl', line: 387]}, {:lists, :zip, 2, [file: 'lists.erl', line: 387]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 165]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 234]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 43]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]
    (jamdb_oracle) lib/jamdb_oracle_ecto.ex:176: Ecto.Adapters.Jamdb.Oracle.Connection.error!/1
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
iex(4)> Orasia.CesiaRepo.ua(2)
{:ok, %{columns: ["MYKEY"], num_rows: 1, rows: [[2]]}}

It looks I didn't use properly the curso so now the connection is stale. But Ecto then restart the connection, the query works (once) and so on.

[vstavskyi]

With this commit, the error is gone, but only for one output cursor parameter and many input parameters. Support for many ouput parameters additionally to cursor ouput parameter is not implemented yet.

[francescm]

Awesome. Works great. Thank you for the clarification about lacking support of multiple out params. Actually there are quite often workarounds not to use them!

[vstavskyi]

I understood that cursor remained open and its closure had been required. So, I had reverted that quick fix and made the big commit

[francescm]

hi again. I was upgrading to {:jamdb_oracle, "0.3.0"}. Stored procedures with cursor parameters, which do really work in commit #0afe407908dd2f3425fbdf330759a5881d98086b, seem having troubles:

iex(2)> Orasia.EctoTest.myusername %DBConnection.ConnectionError{ message: "[{:jamdb_oracle_tns_decoder, :decode_data, [<<11, 105, 110, 102, 111, 97, 105, 97, 50, 48, 48, 51, 38, 123, 83, 83, 72, 65, 125, 101, 120, 100, 52, 77, 116, 82, 122, 86, 76, 75, 111, 56, 83, 99, 65, 111, 97, 117, 67, 108, 82, 114, 100, 101, 76, ...>>, 79], [file: 'src/jamdb_oracle_tns_decoder.erl', line: 393]}, {:jamdb_oracle_tns_decoder, :decode_rxd, 6, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 336]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 181]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 254]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 51]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]" } iex(3)> 10:17:31.214 [error] Jamdb.Oracle (#PID<0.272.0>) disconnected: ** (DBConnection.ConnectionError) [{:jamdb_oracle_tns_decoder, :decode_data, [<<11, 105, 110, 102, 111, 97, 105, 97, 50, 48, 48, 51, 38, 123, 83, 83, 72, 65, 125, 101, 120, 100, 52, 77, 116, 82, 122, 86, 76, 75, 111, 56, 83, 99, 65, 111, 97, 117, 67, 108, 82, 114, 100, 101, 76, ...>>, 79], [file: 'src/jamdb_oracle_tns_decoder.erl', line: 393]}, {:jamdb_oracle_tns_decoder, :decode_rxd, 6, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 336]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 181]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 254]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 51]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]

Do you need more details?

[vstavskyi]

Yes, please describe procedure parameters types and output cursors types.

[francescm]

Thank you so much for your time.

create or replace package body pkg_issue is
procedure usernames(aId varchar2, aRows out sys_refcursor ) is
   begin
     open aRows for
          select p.username,
                 p.userpassword,
                 [...]
            from 
                 tablespace.passwd p                  
           where (p.codicefiscale = aId); 
   end; 
end pkg_issue;

defmodule Orasia.Repo do
  use Ecto.Repo,
      otp_app: :orasia,
      adapter: Ecto.Adapters.Jamdb.Oracle

  def username(id) do
    varchar = id |> to_charlist
    query(
      "BEGIN PKG_ISSUE.USERNAMES(:1, :2); END;",
      [varchar, out: :cursor]
    )
  end
end

defmodule Orasia.EctoTest do

  def username(id) do
    Orasia.Repo.username(id)
  end

end

iex(1)> Orasia.EctoTest.username("NOT_FOUND")
{:ok,
 %{
   columns: ["USERNAME",  ... many more fields ... ],
   num_rows: 0,
   rows: []
 }}
Orasia.EctoTest.username("EXISTING_ID")
%DBConnection.ConnectionError{
  message: "[{:jamdb_oracle_tns_decoder, :decode_data, [<<16, 99, 111, 110, 102, 108, 105, 99, 116, 100, 105, 97, 108, 111, 103, 117, 101, 38, 123, 83, 83, 72, 65, 125, 74, 119, 81, 49, 108, 83, 49, 103, 111, 101, 110, 82, 57, 105, 82, 56, 79, 67, 57, 120, 73, ...>>, 79], [file: 'src/jamdb_oracle_tns_decoder.erl', line: 393]}, {:jamdb_oracle_tns_decoder, :decode_rxd, 6, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 336]}, {:jamdb_oracle_tns_decoder, :decode_token, 3, [file: 'src/jamdb_oracle_tns_decoder.erl', line: 181]}, {:jamdb_oracle_conn, :handle_resp, 4, [file: 'src/jamdb_oracle_conn.erl', line: 254]}, {:jamdb_oracle, :handle_call, 3, [file: 'src/jamdb_oracle.erl', line: 51]}, {:gen_server, :try_handle_call, 4, [file: 'gen_server.erl', line: 661]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 690]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 249]}]"

Please note [99, 111, 110, 102, 108, 105, 99, 116, 100, 105, 97, 108, 111, 103, 117, 101, 38, 123, 83, 83, 72, 65, 125, 74, 119, 81, 49, 108, 83, 49, 103, 111, 101, 110, 82, 57, 105, 82, 56, 79, 67, 57, 120, 73] is: "conflictdialogue&{SSHA}JwQ1lS1goenR9iR8OC9xI" which is correct: the raw charlist is good. It actually includes a username (plus other) as the query result.

[vstavskyi]

I didn't know about ORA-01405 error when driver attempt to fetch NULL value. quick fix

[francescm]

thank you, we are really getting closer. Now it's a DBConnection.ConnectionError

Orasia.EctoTest.username("MY_ID")
%DBConnection.ConnectionError{
  message: "[['malvezzi', [...], :null]]"
}

[vstavskyi]

Now you can substitute bad NULL values with NVL functions.

[francescm]

Oh yes, that works. Thank you so much.