Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Dec 18, 2018 3:54 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Returning values from Oracle Procedures
PostPosted: Fri Mar 22, 2013 6:41 pm 
Offline

Joined: Fri Mar 22, 2013 6:31 pm
Posts: 3
Hi !
I need some help on oracle procedures:
example:
I want to pass one parameter and to receive one on real basic.
Oracle Procedure on database:

myprocedure(ORDER IN VARCHAR2, RETORNO OUT VARCHAR2) IS
BEGIN
INSERT INTO ...... VALUES ( ORDER ); -- this line works !

RETORNO:="OK - RETURNING TO REALBASIC"

EXCEPTION
WHEN OTHERS THEN
RETORNO:="ERROR - RETURNING TO REALBASIC"
END;

I want to receive ( RETORNO ) into a variable in realbasic

dim zorder,retvalue as string
zorder = "1000"
retvalue = ""
db.sqlexecute("begin myprocedure(" + zorder + "," + retvalue + "); end;")
i want retvalue with value returning from oracle procedure, i can pass it to procedure, how do i receive the
value returning into retvalue variable ?
Please help me.
Thanks
Mauricio


END;


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning values from Oracle Procedures
PostPosted: Mon Mar 25, 2013 2:35 pm 
Offline
Site Admin
User avatar

Joined: Fri Sep 30, 2005 9:35 am
Posts: 987
Location: South Portland, Maine
Real Studio cannot get OUT values back from stored procedure calls. It can only get data back by calling SQLSelect and looking at the resulting RecordSet.

Perhaps you could create an anonymous block that does the procedure call, saves the value and then returns it as a cursor? I've not tried this; it's just a suggestion.

_________________
Paul Lefebvre
Developer Evangelist
Xojo, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning values from Oracle Procedures
PostPosted: Fri Mar 29, 2013 8:05 am 
Offline

Joined: Sun Jan 29, 2012 5:02 am
Posts: 3
Location: Sweden / Germany
With Oracle the easiest way is to use "select from table(...)". I give you a short example; you can contact me by mail if you have any specific questions.

create or replace
package xxx as
type xxx_row_type is record (
id varchar2,
some_text varchar2,
some_number number
);
type xxx_table_type is table of xxx_row_type;
function get_xxx(
a_id varchar2)
return xxx_table_type
pipelined;
end xxx;

create or replace
package body xxx as
function get_xxx(
a_id varchar2)
return xxx_table_type
pipelined
as
v_row xxx_row_type;
begin
for r in (select some_text, some_number from my_table where id = a_id order by upper(some_text))
loop
v_row_row.id := r.id;
v_row.some_text := r.some_text;
v_row.some_number := r.some_number;
pipe row (v_row);
end loop;
end get_xxx;
end xxx;


In RB, you can use this with:

Dim vSQL As String
Dim vRS As RecordSet
vSQL = "select * from table (xxx.get_xxx('" + <the id> + '))"
vRS = gDB.SQLSelect(vSQL)
... and so on


You can use as many parameters as you wish, and inside the stored function you can do nearly everything. In my example I have made the row type look the same as the table, but that's not necessary. And you don't have to use a cursor in the function, you can pipe just one result row.

Cheers,

Thomas


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group