Page 1 of 1
show package output parameter on form
Posted: Fri 14 Jan 2011 14:31
by Ludek
hi, what is the simplest way of showing output parameters returned by a package on a form? I would prefer some simple way like binding of a field to a tdbedit.
thanks!
Posted: Fri 14 Jan 2011 15:37
by AlexP
Hello,
One of the ways to bind an output parameter to DBEdit is to use the Oracle ref cursor as an output parameter, like
CREATE OR REPLACE PACKAGE pg_alexp IS
TYPE return_cur IS REF CURSOR;
PROCEDURE get_cursor(p_return_rec OUT return_cur, p_ret_text OUT varchar2);
END pg_alexp;
/
CREATE OR REPLACE PACKAGE BODY pg_alexp IS
PROCEDURE get_cursor(p_return_rec OUT return_cur, p_ret_text OUT varchar2) IS
BEGIN
OPEN p_return_rec FOR
SELECT 1 AS num, 'text' AS txt, TRUNC(sysdate) AS dat FROM dual;
p_ret_text:= 'this is a text';
END get_cursor;
END pg_alexp;
/
OraStoredProc1.StoredProcName := 'PG_ALEXP.GET_CURSOR';
OraStoredProc1.Prepare;
OraDataSource1.DataSet:=OraStoredProc1;
DBEdit1.DataSource:=OraDataSource1;
DBEdit1.DataField:= 'num';
OraStoredProc1.Execute;
or to use the ParamByName method, like
DBEdit1.Text:=OraStoredProc1.paramByName('P_RET_TEXT').AsString;
But using REF CURSOR decreases performance.
Please specify the task in more details and I'll try to give you a more detailed answer.
Posted: Wed 19 Jan 2011 07:01
by Ludek
Thanks for a tip.
I have some package with a procedure with some input and output parameters (no complex types, typically integers) - implemented by someone else, used also with other tools (not just my application with delphi+odac). Now I need to execute the procedure and show the output parameters on delphi form. And I'm looking for some good practice for doing this.
With ms sql & stored proc i would write following to a tmsquery.sql:
Code: Select all
declare @outputpar1 int
declare @outputpar2 int
exec myprocedure 1, @outputpar1 output, @outputpar2 output
select @outputpar1 as outputpar1, @outputpar2 as outputpar2
create persistent fields, connect to datasource and bind to tdbedits. simple , straightforward, no delphi code required, no new ddl required, just short code in tmsquery and a few clicks in delphi designer.
And I'm looking for something similar with oracle+odac. Or, when nothing simliar possible, some other "good practice".
Posted: Wed 19 Jan 2011 09:02
by AlexP
Hello,
For Oracle you can use the following PL/SQL code:
DECLARE
a NUMBER;
b VARCHAR2(50);
c DATE;
BEGIN
TEST_PROCEDURE(a,b,c);
OPEN :cur FOR
SELECT a, b, c FROM dual;
END;
where 'TEST_PROCEDURE' is a stored procedure:
PROCEDURE TEST_PROCEDURE
(A_VAL1 OUT NUMBER, A_VAL2 OUT VARCHAR2, A_VAL3 OUT DATE)
AS
BEGIN
A_VAL1 := 1;
A_VAL2 := 'text';
A_VAL3 := trunc(sysdate);
END;
you should determine the ':cur' parameter as Cursor in TOraQuery and you can use it to for binding to data controls.