exec pl/sql block

[zaz600]

Hi
How to execute and read output from pl/sql block?

r, _ := db.Exec("begin dbms_output.put_line('sdsdsdd'); end;")

[mattn]

create utility function and do select

CREATE OR REPLACE PACKAGE dbms_output_table IS
   TYPE piped_output IS RECORD (dbms_output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;
   FUNCTION display RETURN piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY dbms_output_table IS
   FUNCTION display RETURN piped_output_table PIPELINED IS
      rec piped_output;
      p_status NUMBER;
   BEGIN
      DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      WHILE p_status = 0 LOOP
         PIPE ROW(rec);
         DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      END LOOP;
      RETURN;
   END;
END;
/

rows, err := db.Query("select * from table(dbms_output_table.display);")

[zaz600]

i can't add something to production database :(

[mattn]

go-oci8 is possible to add implementation for fetch output, but database/sql doesn't provide way to set output-buffer for the query. So currently, it's not possible.

[zaz600]

okay. thx

[asaf]

@mattn

Does it make sense to support this in go-oci8 out of the database/SQL interface?
Like the supplied functionality in the competitive oracle driver?

[mattn]

It's possible like go-sqlite3's hook. https://github.com/mattn/go-sqlite3/blob/master/_example/hook/hook.go

But currently, go-oci8 doesn't support yet.

[asaf]

@mattn Thanks, should follow golang/go#18417 that aggregates some issues that would make the standard interface support SP param bindings.

[kardianos]

@mattn and @asaf I'm not familiar with this particular aspect of Oracle PL/SQL. Could someone explain this to me? The docs I found: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#BABJCAJA

What is this typically used for in production databases? How are ways you might interact with it from your API such as database/sql?

[mattn]

One of the motivation is rowid in oracle. rowid is not number, so go-oci8 doesn't work correctly for the go's LastInsertId. The second, we have to allocate/free the buffer to get result specific value for the pl/sql.

[kardianos]

Thanks @mattn,

If the goal is to return a non-numerical rowid, then I would need to look at sql.Result.LastInsertId. Can the driver receive output lines today? Is it just a matter of piping that to the end user somehow? Can multiple lines be outputted and does that happen commonly?

[MichaelS11]

Is this now good with adding of SQL out? Can this be closed?

[MichaelS11]

@mattn Close this?

[mattn]

This should be fixed. Please reopen you still have issue and it is related on go-oci8.