BEGIN-END issue with pong function

[stoch]

Using oranif for

BEGIN
:VARCHAR2_OUT_RESULT := pong(:VARCHAR2_IN_PING);
END;

returns

{error,function_clause}

whereas using oci for

BEGIN
:SQLT_CHR_OUT_RESULT := pong(:SQLT_CHR_IN_PING);
END;

returns

ORA-06550: Zeile 2, Spalte 29: PLS-00201: Bezeichner 'PONG' muss deklariert werden ORA-06550: Zeile 2, Spalte 1: PL/SQL: Statement ignored

Note: after creating the PONG function, it properly works for oci:

CREATE OR REPLACE
FUNCTION PONG (
p_ping in Varchar2
) Return VARCHAR2
Is
Begin
RETURN p_ping;
End;
/

but not for odpi (no response, no error message).

[KarlKeiser]

Can't reproduce, I tried following escript:

#!/user/bin/escript
%% -*- erlang -*-
%%! -pa _build/default/lib/oranif/ebin

-include_lib("eunit/include/eunit.hrl").
-define(DPI_MAJOR_VERSION, 3).
-define(DPI_MINOR_VERSION, 0).
-define(TNS, <<>>).
main([]) ->
	dpi:load_unsafe(),
	Context = dpi:context_create(?DPI_MAJOR_VERSION, ?DPI_MINOR_VERSION),
    Conn = dpi:conn_create(
        Context, <<"foo">>, <<"bar">>, ?TNS,
        #{encoding => "AL32UTF8", nencoding => "AL32UTF8"}, #{}
    ),
    Sql = <<"BEGIN
              :VARCHAR2_OUT_RESULT := pong(:VARCHAR2_IN_PING);
             END;">>,
    Stmt = dpi:conn_prepareStmt(Conn, false, Sql, <<>>),
    [ok, ok] = [begin
    #{var := VarCol} = dpi:conn_newVar(
        Conn, 'DPI_ORACLE_TYPE_VARCHAR', 'DPI_NATIVE_TYPE_BYTES', 10,
        10, true, false, null
    ),
    dpi:stmt_bindByName(Stmt, X, VarCol)
    end || X <- [<<"VARCHAR2_OUT_RESULT">>, <<"VARCHAR2_IN_PING">>]],

    ?assertException(error, {error, _File, _Line,
        #{message :=  "ORA-06550: line 2, column 39:\n"
                      "PLS-00201: identifier 'PONG' must be declared\n"
                      "ORA-06550: line 2, column 15:\n"
                      "PL/SQL: Statement ignored"}},
        dpi:stmt_execute(Stmt, [])
    ),
    	io:format("Test successful.~n"),
    halt(1).

[KarlKeiser]

ok, I think I understand the problem. It's not an oranif issue, but a dderl issue. If oranif returns an error in a certain place, the error message is discarded. I created a fix for the issue in dderl.

As for the second part, what should the result be? As it is currently, the internal type of the statement is not a query, hence why no result is shown.

[KarlKeiser]

Looks like it's working in oranif. I'll see what the issue it in dderl.

#!/user/bin/escript
%% -*- erlang -*-
%%! -pa _build/default/lib/oranif/ebin

-include_lib("eunit/include/eunit.hrl").
-define(DPI_MAJOR_VERSION, 3).
-define(DPI_MINOR_VERSION, 0).
-define(TNS, <<>>).
main([]) ->
	dpi:load_unsafe(),
	Context = dpi:context_create(?DPI_MAJOR_VERSION, ?DPI_MINOR_VERSION),
    Conn = dpi:conn_create(
        Context, <<"foo">>, <<"bar">>, ?TNS,
        #{encoding => "AL32UTF8", nencoding => "AL32UTF8"}, #{}
    ),
    SqlCreation = <<"CREATE OR REPLACE "
                  "FUNCTION PONG ( "
                  "p_ping in Varchar2 "
                  ") Return VARCHAR2 "
                  "Is "
                  "Begin "
                  "RETURN p_ping; "
                  "End; ">>,
    StmtC = dpi:conn_prepareStmt(Conn, false, SqlCreation, <<>>),
    0 = dpi:stmt_execute(StmtC, []),
    Sql = <<"BEGIN
              :VARCHAR2_OUT_RESULT := pong(:VARCHAR2_IN_PING);
             END;">>,
    Stmt = dpi:conn_prepareStmt(Conn, false, Sql, <<>>),

    #{var := Var1, data := Datas1} = dpi:conn_newVar(
        Conn, 'DPI_ORACLE_TYPE_VARCHAR', 'DPI_NATIVE_TYPE_BYTES', 10,
        10, true, false, null
    ),
    #{var := Var2, data := Datas2} = dpi:conn_newVar(
        Conn, 'DPI_ORACLE_TYPE_VARCHAR', 'DPI_NATIVE_TYPE_BYTES', 10,
        10, true, false, null
    ),
    dpi:var_setFromBytes(Var2, 0, <<"foo">>), % assign to VARCHAR2_OUT_RESULT
    dpi:stmt_bindByName(Stmt, <<"VARCHAR2_OUT_RESULT">>, Var1),
    dpi:stmt_bindByName(Stmt, <<"VARCHAR2_IN_PING">>, Var2),
    0 = dpi:stmt_execute(Stmt, []),
    <<"foo">> = dpi:data_get(lists:nth(1, Datas1)), % read back from VARCHAR2_IN_PING
    io:format("Test successful.~n"),

    % cleanup
    [dpi:data_release(D) || D <- Datas1 ++ Datas2],
    ok = dpi:var_release(Var1),
    ok = dpi:var_release(Var2),
    ok = dpi:stmt_close(StmtC, <<>>),
    ok = dpi:stmt_close(Stmt, <<>>),
    ok = dpi:conn_close(Conn, [], <<>>),
    ok = dpi:context_destroy(Context),
    halt(1).

[KarlKeiser]

I'm still trying to figure out all the PL/SQL syntax, but I made a demo where a function is created and then called.

In sqlplus:

In oranif:

#!/user/bin/escript
%% -*- erlang -*-
%%! -pa _build/default/lib/oranif/ebin

-include_lib("eunit/include/eunit.hrl").
-define(DPI_MAJOR_VERSION, 3).
-define(DPI_MINOR_VERSION, 0).
-define(TNS, <<>>).
main([]) ->
	dpi:load_unsafe(),
	Context = dpi:context_create(?DPI_MAJOR_VERSION, ?DPI_MINOR_VERSION),
    Conn = dpi:conn_create(
        Context, <<"foo">>, <<"bar">>, ?TNS,
        #{encoding => "AL32UTF8", nencoding => "AL32UTF8"}, #{}
    ),
    SqlCreation = <<"CREATE OR REPLACE
                    FUNCTION PONG (
                    p_ping in Varchar2
                    ) Return VARCHAR2
                    Is
                    Begin
                    RETURN concat('returned-', p_ping);
                    End;">>,
    StmtC = dpi:conn_prepareStmt(Conn, false, SqlCreation, <<>>),
    0 = dpi:stmt_execute(StmtC, []),
    Sql = <<"select pong('foo') from dual">>,
    Stmt = dpi:conn_prepareStmt(Conn, false, Sql, <<>>),

    1 = dpi:stmt_execute(Stmt, []),
    dpi:stmt_fetch(Stmt),
    #{data := Result} =
        dpi:stmt_getQueryValue(Stmt, 1),
    ?assertEqual(<<"returned-foo">>, dpi:data_get(Result)),
    dpi:data_release(Result),

    % cleanup,
    ok = dpi:stmt_close(StmtC, <<>>),
    ok = dpi:stmt_close(Stmt, <<>>),
    ok = dpi:conn_close(Conn, [], <<>>),
    ok = dpi:context_destroy(Context),
    io:format("test successful.~n"),
    halt(1).

In dderl:

[stoch]

@KarlKeiser Our SQL parser does not support the create statement but DDerl still gives it to the DB to execute. Everything fine here now.