Home » SQL & PL/SQL » SQL & PL/SQL » how to get the out value from function
| how to get the out value from function [message #6858] |
Thu, 08 May 2003 19:24  |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
I create a package and function .with the function ,i use "v_accessId OUt table1.col1%type"
to get one value of table1.col1 (type is char(32)).when it coded, developped with pl/sql developer, I test it ,and the function work well ,but when call it in proc using host variable "char accessId[[32]]" , as following :
v_accessId => :accessId;
the function run error 1405 :fetch NULL into . But the function dont do it.
how to get the out value from function ; thanks in advance !
I once try other idea ,adding a gobal variable "m_accessId char(32)"in the package,and when the function work it ,do :accessId := certify.m_accessId ;
(certify is my package name). but it cant work .
can you help me or give me some advance . thanks
|
|
|
|
| Re: how to get the out value from function [message #6871 is a reply to message #6858] |
Fri, 09 May 2003 02:46  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am wondering why you want the out value, rather than the return value, or since this is the newbies forum, whether or not you understand the difference. It sounds like you are probably doing things the hard way, however this sounded like an interesting exercise, so I have provided an example of what you asked for below, although I suspect you could use something simpler. Although you could use a host variable in an anonymous pl/sql block, any such host variable must be passed to a stored procedure as a parameter. In the example below, two host variables are passed to a standalone stored procedure, which executes a packaged function. The procedure passes the variables to the function and receives them back, with one of them populated as the function's out value, which is selected from the table, and the other one populated with the function's return value. Those values are ultimately printed. Like I said, it was an interesting exercise, but I suspect that all you probably need is a return value, not an out value. Functions are generally used for return values and procedures for out values, when there is more than one value to return. If this is not what you want, please post your complete code and any additional explanation.
SQL> DESC table1
Name Null? Type
----------------------- -------- ----------------
COL1 CHAR(32)
SQL> SELECT * FROM table1
2 /
COL1
--------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF
SQL> CREATE OR REPLACE PACKAGE certify
2 AS
3 FUNCTION test_out
4 (v_accessId OUT table1.col1%TYPE)
5 RETURN CHAR;
6 END certify;
7 /
Package created.
SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY certify
2 AS
3 FUNCTION test_out
4 (v_accessId OUT table1.col1%TYPE)
5 RETURN CHAR
6 IS
7 BEGIN
8 SELECT MIN (col1) INTO v_accessId FROM table1;
9 RETURN 'THIS IS THE RETURN VALUE........';
10 END test_out;
11 END certify;
12 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PROCEDURE test_proc
2 (p_value1 IN OUT CHAR,
3 p_value2 IN OUT CHAR)
4 AS
5 BEGIN
6 p_value2 := certify.test_out (v_accessId => p_value1);
7 END test_proc;
8 /
Procedure created.
SQL> SHOW ERRORS
No errors.
SQL> VARIABLE accessId CHAR(32)
SQL> VARIABLE g_return CHAR(32)
SQL> EXEC test_proc (:accessId, :g_return)
PL/SQL procedure successfully completed.
SQL> PRINT accessId
ACCESSID
--------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF
SQL> PRINT g_return
G_RETURN
--------------------------------
THIS IS THE RETURN VALUE........
|
|
|
|
| Re: how to get the out value from function [message #6884 is a reply to message #6871] |
Fri, 09 May 2003 20:51 |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
thank Barbara Boehmer for your excellent explain!
yes the my real need is the return value ,but
I usually use the function return as the execution result code (type is NUMBER ) and use the OUT paramenter return my real value .:);
I dont explain my question clearly,sorry.
after tested ,i find the core question that cause my error is :
the host variable (type is char[[32]]) cant directly get the value from pl/sql
as :resource_list := certify.g_accessId,although there are the same type (all char(32)).
how to transfer the value of Pl/sql variable (type is char(32)) into the host variable (type is char[[32]])?
|
|
|
|
| Re: how to get the out value from function [message #6891 is a reply to message #6884] |
Sat, 10 May 2003 03:24 |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
your explain indirectly give me another way to make it run.
and after recoded,it run ok in sql*plus .
but when i call it in proc , run .
there is an error !
in the sql*plus ,test code is following :
variable v_list char(32);
variable v_result int;
begin
certify.g_name :='kworld';
certify.list_resource(v_appid => 1, v_resourcelist => :v_list, v_returnnum => :v_num2);
end;
/
print v_list;
print v_num2;
PL/SQL procedure execute okĄŁ
V_LIST
--------------------------------
101010101
V_result
----------
1
in my proc ,only use host variable char v_list[[32]] ,and int v_result replace them :
EXEC SQL BEGIN DECLARE SECTION;
char login[[10]] ="yzwg/yzwg";
int result2 = -1;
char resource_list[[32]];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
memset(resource_list,0,32);
EXEC SQL CONNECT :login;
EXEC SQL EXECUTE
BEGIN
certify.g_name := 'kworld' ;
certify.list_resource(v_appid => 1, v_resourcelist => :resource_list, v_returnnum => :result2);
END;
END-EXEC;
EXEC SQL commit WORK RELEASE;
the error code is -1405 :
fetched column value is NULL
why ?
thanks in advance!
|
|
|
|
|
|
| Re: how to get the out value from function [message #6896 is a reply to message #6894] |
Sat, 10 May 2003 05:56 |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
perhaps ,i should rewrite the title "how to get the out value from function and procedure".
i should make you know clearly my question ,I know the procedure and function .I confuse why I can test in the pl/sql developer and result ok.and run in the sql*puls. but cant run under the proc .why
?
the host variable :accessid char[[32]];
in the test proc
begin
:accessid := certify.g_accessid --type is char(32);
end;
and my another question in the third post.
but thanks very much
|
|
|
|
| Re: how to get the out value from function [message #6897 is a reply to message #6896] |
Sat, 10 May 2003 06:22 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
Are you trying to set the value of a package variable to the value of a host variable or are you trying to set the value of a host variable to the value of a package variable. Why are you trying to use a package variable to begin with? You need to post the latest version of your complete code including any packages, procedures, functions, variable declarations, and method of execution, and resulting error messages, in one post. You have too many scattered pieces of various versions on various forums in various posts, using various variables. It is not clear what you are trying to do. If we could see the whole code, we could probably correct it for you. Although you say that you know the difference between a function and a procedure, if you are attempting to execute a function in the manner that you posted in your previous post, then you are doing it wrong. You might want to take another look at my last post.
|
|
|
|
| Re: how to get the out value from function [message #6900 is a reply to message #6896] |
Sat, 10 May 2003 07:46 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I will make one last attempt at providing an example. I took a look at your other post with the procedure code. I gather that you have a procedure and a function in your package and that the procedure calls the function. In the example below, I have provided such a package. I have also provided host variables and an anonymous pl/sql block that passes those host variables to the packaged procedure, which calls the function. This should be similar to what you put in your sections of EXEC SQL. If this does not help, then please post everything as I have previously requested. I need to see code that works completely, like what I have provided, from SQL*Plus, and confirm that it works, before I can begin to determine why it might not work in some other environment.
SQL> CREATE TABLE table1
2 (col1 CHAR(32))
3 /
Table created.
SQL> INSERT INTO table1 (col1)
2 VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF')
3 /
1 row created.
SQL> CREATE TABLE groupinfo
2 (groupid NUMBER,
3 logid NUMBER,
4 accessid NUMBER)
5 /
Table created.
SQL> INSERT INTO groupinfo (groupid, logid, accessid)
2 VALUES (1, 1, 1)
3 /
1 row created.
SQL> CREATE TABLE logininfo
2 (loginid NUMBER,
3 appid NUMBER)
4 /
Table created.
SQL> INSERT INTO logininfo (loginid, appid)
2 VALUES (1, 1)
3 /
1 row created.
SQL> CREATE TABLE useraccessinfo
2 (userid VARCHAR2 (30),
3 groupid NUMBER)
4 /
Table created.
SQL> INSERT INTO useraccessinfo (userid, groupid)
2 VALUES ('test', 1)
3 /
1 row created.
SQL> DESC table1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 CHAR(32)
SQL> SELECT * FROM table1
2 /
COL1
--------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF
SQL> CREATE OR REPLACE PACKAGE certify
2 AS
3 FUNCTION current_loginId
4 (v_resource_list IN OUT table1.col1%TYPE)
5 RETURN NUMBER;
6 PROCEDURE list_resource
7 (v_appId IN NUMBER,
8 v_resourceList IN OUT CHAR,
9 v_returnNum IN OUT NUMBER,
10 v_name IN VARCHAR2);
11 END certify;
12 /
Package created.
SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY certify
2 AS
3 FUNCTION current_loginId
4 (v_resource_list IN OUT table1.col1%TYPE)
5 RETURN NUMBER
6 IS
7 v_returnnum INTEGER;
8 BEGIN
9 SELECT MIN (col1) INTO v_resource_list FROM table1;
10 v_returnnum := 1;
11 RETURN v_returnnum;
12 END current_loginId;
13 PROCEDURE list_resource
14 (v_appId IN NUMBER,
15 v_resourceList IN OUT CHAR,
16 v_returnNum IN OUT NUMBER,
17 v_name IN VARCHAR2)
18 IS
19 m_group groupinfo.groupid%TYPE;
20 m_loginId logininfo.loginid%TYPE;
21 CURSOR c_group IS
22 SELECT groupId
23 FROM useraccessinfo
24 WHERE userid = v_name;
25 BEGIN
26 SELECT loginid INTO m_loginId FROM logininfo WHERE appid = v_appId;
27 FOR current_group IN c_group
28 LOOP
29 FOR current_loginId IN
30 (SELECT logId, accessId FROM groupinfo WHERE groupid = current_group.groupId)
31 LOOP
32 IF current_loginId.logId = m_loginid THEN
33 v_returnNum := certify.current_loginId (v_resourceList);
34 END IF;
35 END LOOP;
36 END LOOP;
37 v_returnNum := 1;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 v_returnNum := 5;
41 WHEN COLLECTION_IS_NULL THEN
42 v_returnNum := 6;
43 END list_resource;
44 END certify;
45 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL> VARIABLE accessid NUMBER
SQL> VARIABLE v_list CHAR(32)
SQL> VARIABLE v_result NUMBER
SQL> BEGIN
2 :accessid := 1;
3 certify.list_resource
4 (v_appId => :accessid,
5 v_resourceList => :v_list,
6 v_returnNum => :v_result,
7 v_name => 'test');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> PRINT v_list
V_LIST
--------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF
SQL> PRINT v_result
V_RESULT
----------
1
SQL> PRINT accessid
ACCESSID
----------
1 |
|
|
|
| Re: how to get the out value from function [message #6916 is a reply to message #6897] |
Sun, 11 May 2003 08:41 |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
The following is my original code :
before your reading ,please let me make a simple introduce for the functions:
the first, we will use three tables ,the construct is following:
table 1: userAccessInfo;
userAccessInfo
-------------------
userId varchar(24);
groupId varchar(24);
--one userId can relate to one or more groupId.
table 2: groupInfo ;
groupInfo
---------------------
groupId varchar(24);
loginId long;
accessId char(32);
description varchar(255);
table 3: loginInfo;
loginInfo
---------------------
loginId long;
appId long;
loginName varchar(24);
function listResource(v_appId IN long) return NUMBER;
According to IN parameter v_appId we get the loginId (m_loginid In program) from loginInfo table ,
their relation is one to one.And from the gobal variable g_name, we get the squence of
groupId (current_loginId In program) from table userAccessInfo .
we can based on the loginId and groupId, get the accessId that current user can.
the way is that we do "g_accessId = current_lgoinId.accessId" when the current_loginId.loginId = m_loginid
then In my proc ,we can set host variable resource_list char[[32]] ,then get the value from certify.g_accessId;
when we execute my proc,if we not sentence ":resource_list := certify.g_accessId",it do it well.
I dont know what happened ,but the return result is good .
if we add sentence ":resource_list := certify.g_accessId" ,meet error
I try use procedure List_resource(v_appId in long ,v_resourceList out char, v_returnNum out int);
in procedure ,there are some difference but the function is same.
I once test ,my procedure can work well in sql*plus ,but when in my proc ,there are error :1405
I dont know what happend?
they are all tested under the pl/sql developer 5.0
I try much way ,including that make function return char then get it,but no one can work well under the environment of my pro program.
please help me!
thanks !
ONE PACKAGE HEAD;
CREATE OR REPLACE PACKAGE "CERTIFY" is
--VARIABLE
g_name userInfo.userId%type;
g_accessId char(32); --groupinfo.accessid%type; --type is char(32);
--FUNCTION AND PROCUDURE
function listResource(v_appId IN long, v_resourceList out char) return NUMBER;
procedure List_resource(v_appId in long ,v_resourceList out char, v_returnNum out int);
end certify;
TWO PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY "CERTIFY" is
-- the function to return resource list
-- edit time :2003-5-6
-- version :1.0 a
function listResource(v_appId IN long, v_resourceList out char) return NUMBER
is
m_group groupinfo.groupid%type ;
m_loginId logininfo.loginid%type ;
cursor c_group is select groupId
from useraccessinfo where userid = g_name;
m_result BINARY_INTEGER :=-1 ;
begin
select loginid into m_loginid from logininfo where appid = v_appId ;
for current_group IN c_group --get the group
loop
for current_loginId IN (select loginId,accessId from groupinfo where groupid = current_group.groupId)
loop
if current_loginId.loginId = m_loginid then
g_accessId := current_loginId.accessId ;
goto back;
end if;
end loop ;
end loop ;
<<back>>
m_result := 1; --execute ok ,return 1Ł»
return m_result ;
exception
when NO_DATA_FOUND then
m_result :=5 ; --there are no loginId to relate to this appid,
return m_result ;
when collection_is_null then
m_result :=6 ; --
return m_result;
when others then
m_result :=7 ; --meet other errors
return m_result;
end ;
procedure List_resource(v_appId in long ,v_resourceList out char, v_returnNum out int)
is
m_group groupinfo.groupid%type ;
m_loginId logininfo.loginid%type ;
cursor c_group is select groupId
from useraccessinfo where userid = g_name;
begin
select loginid into m_loginid from logininfo where appid = v_appId ;
open c_group ;
loop
fetch c_group into m_group;
exit when not c_group %found;
declare
m_temploginId groupinfo.logid%type ;
cursor c_login is select loginId
from groupinfo where groupid = m_group ;
bexit BOOLEAN := false;
begin
open c_login;
loop
fetch c_login into m_TemploginId ;
exit when bexit or not c_login %found ;
if m_temploginid = m_loginid then
select accessId into v_resourceList from groupinfo
where groupid = m_group and logId = m_loginId;
bexit := true;
end if;
end loop ;
end;
end loop ;
<<back>>
v_returnNum := 1;
exception
when NO_DATA_FOUND then
v_returnNum :=5 ;
when collection_is_null then
v_returnNum :=6 ;
when others then
v_returnNum :=7 ;
end ;
end CERTIFY;
THREE my proc program :
int test(char * resource)
{
assert(resource != NULL);
EXEC SQL BEGIN DECLARE SECTION;
char login[[10]] ="yzwg/yzwg";
int result1 = -1;
char resource_list[[32]];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
memset(resource_list,0,32);
EXEC SQL CONNECT :login;
EXEC SQL EXECUTE
BEGIN
certify.g_name := 'kworld';
certify.list_resource(v_appid => 1, v_resourcelist => :resource_list, v_returnnum => :result1);
/* the second way is :
certify.g_name := 'kworld';
:result1 := certify.listResource(v_appid =>1);
:resource_list := g.accessId;
*/
END;
END-EXEC;
EXEC SQL commit WORK RELEASE;
return result1;
}
|
|
|
|
| Re: how to get the out value from function [message #6941 is a reply to message #6916] |
Tue, 13 May 2003 02:52 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Let us see if we can simplify this whole thing. You don't need any package variables and a function is not appropriate. All you should need is a packaged procedure. Please see if you can adapt the example below. I have used %TYPE for all the input and output parameters. That is the recommended method so that, if you change your datatypes sometimes later, you don't have to change all your procedures. Instead of using OUT, I have used IN OUT, because some old versions of Oracle or Developer or other calling environments require IN OUT, instead of just OUT. I have initialized the two IN OUT variables, p_resourceList and p_returnNum to 'EMPTY' and -1, in order to eliminate any possibility of returning null values that might cause a problem. I have simplified the processing so that it uses two cursor for loops, one nested within the other, similar to your former function. The section at the bottom, with the variable declarations and the anonymous pl/sql block from begin to end corresponds to what you would use in your EXEC SQL. Notice how I have passed in the parameters. Please test what I have provided in SQL*Plus first, as I have done. If that doesn't work for you, then please cut and paste the run of the code and errors. If that works, then try your EXEC SQL. If that doesn't work, then, once again, cut and paste the run and errors.
SQL> CREATE TABLE userAccessInfo
2 (userId VARCHAR (24),
3 groupId VARCHAR (24))
4 /
Table created.
SQL> INSERT INTO userAccessInfo (userId, groupId)
2 VALUES ('kworld', 'test_groupId')
3 /
1 row created.
SQL> CREATE TABLE groupInfo
2 (groupId VARCHAR (24),
3 loginId LONG,
4 accessId CHAR (32),
5 description VARCHAR (255))
6 /
Table created.
SQL> INSERT INTO groupInfo (groupId, loginId, accessId, description)
2 VALUES ('test_groupId', 'test_loginId', 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF', 'test_desc')
3 /
1 row created.
SQL> CREATE TABLE loginInfo
2 (loginId LONG,
3 appId NUMBER,
4 loginName VARCHAR (24))
5 /
Table created.
SQL> INSERT INTO loginInfo (loginId, appId, loginName)
2 VALUES ('test_loginId', 1, 'test_loginName')
3 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> CREATE OR REPLACE PACKAGE certify
2 AS
3 PROCEDURE list_resource
4 (p_name IN userAccessInfo.userId%TYPE,
5 p_appId IN loginInfo.appId%TYPE,
6 p_resourceList IN OUT groupInfo.accessId%TYPE,
7 p_returnNum IN OUT NUMBER);
8 END certify;
9 /
Package created.
SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY certify
2 AS
3 PROCEDURE list_resource
4 (p_name IN userAccessInfo.userId%TYPE,
5 p_appId IN loginInfo.appId%TYPE,
6 p_resourceList IN OUT groupInfo.accessId%TYPE,
7 p_returnNum IN OUT NUMBER)
8 IS
9 v_loginId loginInfo.loginId%TYPE;
10 BEGIN
11 p_resourceList := 'EMPTY';
12 p_returnNum := -1;
13 SELECT loginId into v_loginId FROM loginInfo where appId = p_appId;
14 FOR current_group IN
15 (SELECT groupId FROM userAccessInfo WHERE userId = p_name)
16 LOOP
17 FOR current_loginId IN
18 (SELECT loginId, accessId FROM groupInfo WHERE groupId = current_group.groupId)
19 LOOP
20 IF current_loginId.loginId = v_loginId THEN
21 p_resourceList := current_loginId.accessId;
22 END IF;
23 END LOOP;
24 END LOOP;
25 p_returnNum := 1;
26 EXCEPTION
27 WHEN NO_DATA_FOUND THEN
28 p_returnNum := 5;
29 WHEN COLLECTION_IS_NULL THEN
30 p_returnNum := 6;
31 WHEN OTHERS THEN
32 p_returnNum := 7;
33 END list_resource;
34 END certify;
35 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL> VARIABLE result1 NUMBER
SQL> VARIABLE resource_list CHAR (32)
SQL> BEGIN
2 certify.list_resource
3 (p_name => 'kworld',
4 p_appId => 1,
5 p_resourcelist => :resource_list,
6 p_returnnum => :result1);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> PRINT result1
RESULT1
----------
1
SQL> PRINT resource_list
RESOURCE_LIST
--------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEF
|
|
|
|
| Re: how to get the out value from function [message #6999 is a reply to message #6941] |
Wed, 14 May 2003 18:56 |
kworld
Messages: 23
Registered: February 2003
|
Junior Member |
|
|
my proc can work now ,thank you !
the problem is type char(32) in oracle ,the real size is 33 bytes(i think maybe it include the last ' '.so that you must define the host variable type is char[[33]] in your proc program .
this size is not same as variable defined in sql*plus( the size is 32).
your advice give me some additional good idea.
thanks
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 01:05:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
