| Please help! Oracle can't find my function [message #733] |
Thu, 28 February 2002 11:28  |
Dmitry
Messages: 7
Registered: December 2001
|
Junior Member |
|
|
I wrote the following function to check how many users are logged into a database under a certain username:
CREATE OR REPLACE FUNCTION "SYS"."COUNT_JEMS_SESSSIONS"
return number is
session_count number;
begin
select count(*) into session_count from V_$SESSION where username = 'JEMS';
return (session_count);
end;
However when I try to run
"call SYS.COUNT_JEMS_SESSIONS()" or
"call COUNT_JEMS_SESSIONS()" or
"call SYS.COUNT_JEMS_SESSIONS" or
"call COUNT_JEMS_SESSIONS"
from sql plus worksheet
I get the following error:
ERROR at line 1:
ORA-06576: not a valid function or procedure name
Please help!
Thank,
Dmitry
|
|
|
|
| Re: Please help! Oracle can't find my function [message #734 is a reply to message #733] |
Thu, 28 February 2002 11:56  |
monir
Messages: 22
Registered: February 2002
|
Junior Member |
|
|
i have tried your function and oracle does not accept your function's name form
i changed the name and i solve your problem
follow this:
-try the sql statment in sql*plus
if it is good
-try to create the function in sql*plus
and see what the problem
|
|
|
|
| Re: Please help! Oracle still can't find my function [message #735 is a reply to message #734] |
Thu, 28 February 2002 12:08 |
Dmitry
Messages: 7
Registered: December 2001
|
Junior Member |
|
|
Monir, thanks for the advice but it still doesn't work.
I am pasting exactly what I'm doing perhaps this will help. I type this into SQL* Plus Worksheet
CREATE OR REPLACE FUNCTION COUNTJEMS return number
is
session_count number;
begin
select count(*) into session_count from V_$SESSION where username = 'JEMS';
return (session_count);
end;
>Connected.
>Function created.
Connected.
call SYS.COUNTJEMS
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
Connected.
call COUNTJEMS
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
As you can see I tried all different combinations with and without "sys." prefix and nothing works.
Thank a lot in advance for your help,
Dmitry
|
|
|
|
| Re: Please help! Oracle can't find my function [message #736 is a reply to message #733] |
Thu, 28 February 2002 13:28 |
raji
Messages: 30
Registered: February 2002
|
Member |
|
|
CREATE OR REPLACE FUNCTION "SYS"."COUNT_JEMS_SESSSIONS"
Don't use " quotes for function name,use simply as
SYS.COUNT_JEMS_SESSSIONS.
Did you get a message from sql*plus saying that function is created.
After getting that if you do
set server output on;
declare
v_num number;
begin
v_num := SYS.COUNT_JEMS_SESSSIONS();
dbms_output.put_line('number of users is'||v_num);
end;
you will get the result.
I have not heard about the "call function".Where did you get that?
|
|
|
|
|
|
| Re: Please help! Oracle still can't find my function [message #744 is a reply to message #734] |
Fri, 01 March 2002 03:32 |
monir
Messages: 22
Registered: February 2002
|
Junior Member |
|
|
hi dmitry,
i remark that you call your function wrongly!
what i know that your function return a number that means you must affect it to a number
try this code for exemple
set serveroutput on
declare
v_num number;
begin
v_num := COUNTJEMS;
dbms_output.put_line(v_num);
end;
/
good luck
monir
|
|
|
|
| Re: Please help! Oracle can't find my function [message #759 is a reply to message #733] |
Fri, 01 March 2002 23:41 |
Sushmita Chakraborty
Messages: 3
Registered: February 2002
|
Junior Member |
|
|
hi I tried the code in this manner and it worked.
SQL> CREATE OR REPLACE FUNCTION COUNJEMS return number
2 is
3 session_count number;
4 begin
5 select count(*) into session_count from V_$SESSION;
6 return (session_count);
7 end;
8 /
Function created.
SQL> set serveroutput on
SQL> declare
2 lv_session number;
3 begin
4 lv_session:=counjems;
5 dbms_output.put_line(lv_session);
6 end;
7 /
9
Since the function is returning some number,you will have to write the PL/SQL code.
|
|
|
|
|
|
| Re: Please help again! Permissions problem? Oracle still can't find my function [message #782 is a reply to message #734] |
Mon, 04 March 2002 06:59 |
Dmitry
Messages: 7
Registered: December 2001
|
Junior Member |
|
|
Hi,
I tried to do as you say and i still get the same thing back. Here is what I'm trying
declare
lv_session number;
begin
lv_session:=SYS.COUNTJEMS;
dbms_output.put_line(lv_session);
end;
I tried all different combination of countjems, sys.countjems, etc. Here is what I'm getting back.
lv_session:=countjems;
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'COUNTJEMS' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
lv_session:=countjems();
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'COUNTJEMS' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
lv_session:=sys.countjems();
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'SYS.COUNTJEMS' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
lv_session:=SYS.COUNTJEMS();
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'SYS.COUNTJEMS' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
lv_session:=SYS.COUNTJEMS;
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00201: identifier 'SYS.COUNTJEMS' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
here is the function countjems btw
CREATE OR REPLACE FUNCTION COUNTJEMS return number
is
session_count number;
begin
select count(*) into session_count from V_$SESSION;
return (session_count);
end;
THanks a lot for your help already, I am getting kind of frustrated but hopefully someone will be able to figure this out.
-Dmitry
|
|
|
|
| Re: Please help! Oracle can't find my function [message #792 is a reply to message #733] |
Mon, 04 March 2002 19:52 |
sushmita
Messages: 2
Registered: March 2002
|
Junior Member |
|
|
I didnot get what u are trying to say.Please specify it clearly.The discussion forum is to clear your
doubts and not to discourage others ,next time
please specifi it clearly what u r trying to say
and don't post or mail such nonsesse replies
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
