OraCall is a package for calling Oracle stored procedures with gRPC. As OCI does not allow using PL/SQL record types, this causes some difficulty.
Of course you'll need a working Oracle environment. For instructions, see godror.
As OraCall uses gRPC for the interface, you'll need protoc
, the
Protocol Buffers
code generator and a Go code generator,
such as protoc-gen-gofast.
But these two is downloaded by go generate
.
go get github.com/tgulacsi/oracall
go generate github.com/tgulacsi/oracall # this will download protoc and protoc-gen-gofast
Then you can use it:
oracall -db-out ./pkg/db -pb-out ./pkg/pb -connect 'user/passw@sid' 'MY_PKG.%'
This will generate
my_pkg_functions.go
with the calling machinery,my_pkg.proto
with the Protocol Buffers messages and RPC service definitions,protoco-gen-gofast
with my_pkg.proto
, which will generate
my_pkg.pb.go
with the Protocol Buffers (un)marshal code.First, it reads the functions, procedures' names and their arguments' types from the given database with the following query:
SELECT object_id, subprogram_id, package_name, object_name,
data_level, sequence, argument_name, in_out,
data_type, data_precision, data_scale, character_set_name,
pls_type, char_length, type_owner, type_name, type_subname, type_link
FROM user_arguments
ORDER BY object_id, subprogram_id, SEQUENCE;
So the argument's type must be readable in user_arguments
!
For a SYS_REFCURSOR
(returning a cursor, a query's result set in Oracle parlance),
you have to specialize the type for the returned columns -- see below.
Supported types:
If you have a package with mixed content, you can force oracall to ignore them either by
-private pkg.func_to_be_ignored,pkg2.other_private
--oracall:private func_to_be_ignored
to the package header.The latter can be used to implement two other hacks:
--oracall:replace non_compliant_complex => xml_replacement
and this will create the non_compliant_complex
function's call type in the protobuf file
(so this will look like the original complex function), but will call the xml_replacement
function with the protobuf serialized to XML, and deserialized from the returned XML.For example for
FUNCTION ret_cur(p_id IN INTEGER) RETURN SYS_REFCURSOR IS
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
SELECT state, amount FROM table;
RETURN v_cur;
END;
You'll have to define the type in the package HEAD:
TYPE state_amount_rec_typ IS RECORD (state table.state%TYPE, amount table.amount%TYPE);
TYPE state_amount_cur_typ IS REF CURSOR RETURN state_amount_rec_typ;
Then only change the type in the BODY:
FUNCTION ret_cur(p_id IN INTEGER) RETURN state_amount_cur_typ IS
v_cur state_amount_cur_typ;
BEGIN
OPEN v_cur FOR
SELECT state, amount FROM table;
RETURN v_cur;
END;
TL;DR; oracall needs "strongly typed" REF CURSOR - see http://www.dba-oracle.com/plsql/t_plsql_cursor_variables.htm for example!
Minimal is a minimal example using OraCall: a simple main package which connects to the database and calls the function specified on the command line. Args are specified using JSON.
This must be compiled with your oracall output - such as
oracall <one.csv >examples/minimal/generated_functions.go \
&& go fmt ./examples/minimal/ \
&& go build ./examples/minimal/ \
&& ./minimal DB_web.sendpreoffer_31101
This calls oracall with one.csv as stdin, and redirects its output to examples/minimal/generated_functions.go. Then formats the output (good for safety check, too) after this it builds the "minimal" binary (into the current dir). If all this was successful, than calls the resulting binary to call the specified function.
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。