RE: procedure/function error
function cannot be called with EXEC. u have to call it within a
procedure...
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of purushottam krishna
hegdeSent: Friday, June 06, 2003 5:05 PMTo: Multiple
recipients of list ORACLE-LSubject: procedure/function
error
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return
varchar2 is 2 resex varchar2(3); 3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex); 6 END; 7 /
Function created.
SQL EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END;
*ERROR at line 1:ORA-06550: line
1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is
undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
Do you Yahoo!?Free online
calendar with sync to Outlook(TM).
RE: procedure/function error
Hi
purushotam,
since
it is a function that you are creating , its return value needs to be
collected.
hence
after BEGIN clause give a statement like
v_ret:=EMP_SEL('gali'); where v_ret
is a local variable of type varchar2
Thiswill work.
Regards
Kesh
-Original Message-From: purushottam krishna hegde
[mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003
5:05 PMTo: Multiple recipients of list ORACLE-LSubject:
procedure/function error
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return
varchar2 is 2 resex varchar2(3); 3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex); 6 END; 7 /
Function created.
SQL EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END;
*ERROR at line 1:ORA-06550: line
1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is
undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
Do you Yahoo!?Free online
calendar with sync to Outlook(TM).
This e-mail and any files transmitted with it are for the sole use of the intended
recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or
copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.
Visit us at http://www.cognizant.com
Re: procedure/function error
The error message tells you all: EMP_SEL is not a procedure, it's a function.
You cannot execute functions like that. Functions are called and not executed.
try something like
declare
ret varchar2(3);
begin
ret:=emp_sel('gali');
dbms_output.put_line('RET:'||ret);
end;
/
On 2003.06.06 07:35 purushottam krishna hegde wrote:
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2 is
2 resex varchar2(3);
3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex);
6 END;
7 /
Function created.
SQL EXECUTE EMP_SEL('gali');
BEGIN EMP_SEL('gali'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'EMP_SEL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
-
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: procedure/function error
declarea varchar2(20); b:=varchar2(20):='gali'; begin a:=EMP_SEL(b); dbms_output.put_line(a); end;
You cannot call the functions like this, only procedures can be.Venkat
- Original Message -
DATE: Fri, 06 Jun 2003 03:35:11
From: purushottam krishna hegde [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Cc:
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2 is 2 resex varchar2(3); 3 Begin 4 SELECT sex into resex from emp where name=ename; 5 return(resex); 6 END; 7 /
Function created.
SQL EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END;
*ERROR at line 1:ORA-06550: line 1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
Do you Yahoo!?Free online calendar with sync to Outlook(TM).Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!Login To Lycos Mail
Re: procedure/function error
exec only works with procedures. you can execute a function by calling to from sql as
such
select emp_sel('gali')
from dual;
or with a pl/sql block
declare
resex varchar2(3);
begin
resex := emp_sel('gali');
end;
From: purushottam krishna hegde [EMAIL PROTECTED]
Date: 2003/06/06 Fri AM 07:35:11 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: procedure/function error
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2 is
2 resex varchar2(3);
3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex);
6 END;
7 /
Function created.
SQL EXECUTE EMP_SEL('gali');
BEGIN EMP_SEL('gali'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'EMP_SEL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
-
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2 is 2 resex varchar2(3); 3 Begin 4 SELECT sex into resex from emp where name=ename; 5 return(resex); 6 END; 7 /
Function created.
SQL EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END;
*ERROR at line 1:ORA-06550: line 1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
RE: procedure/function error
you
need to accept the return value of the function in a variable. the correct way
to do is
SQL var a varchar2(1000)
sql execute :a := emp_sel('gali')
SQL print a
OR
SQLdeclare
SQLa varchar2(1000);
SQLbegin
SQLa := emp_sel('gali');
SQLDBMS_OUTPUT.PUT_LINE(a);
SQLend;
SQL/
Regards
Naveen
-Original Message-From: purushottam krishna hegde
[mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003
5:05 PMTo: Multiple recipients of list ORACLE-LSubject:
procedure/function error
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return
varchar2 is 2 resex varchar2(3); 3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex); 6 END; 7 /
Function created.
SQL EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END;
*ERROR at line 1:ORA-06550: line
1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is
undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
Do you Yahoo!?Free online
calendar with sync to Outlook(TM).DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
RE: procedure/function error
Functions CAN be called from SQL*Plus like this ...
create the function
---
create or replace function emp_sel(e IN VARCHAR2) RETURN VARCHAR2 IS
cName VARCHAR2(32);
BEGIN
SELECT INITCAP(ename) INTO cName FROM EMP WHERE ename = e;
RETURN (cName);
END;
/
in SQL*Plus do the following
-
SQLvariable x varchar2(32);
SQL:x := emp_sel('SCOTT');
PL/SQL procedure successfully completed.
SQLprint X;
X
Scott
HTH ...
-Original Message-
Sent: Friday, June 06, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L
declare
a varchar2(20);
b:=varchar2(20):='gali';
begin
a:=EMP_SEL(b);
dbms_output.put_line(a);
end;
You cannot call the functions like this, only procedures can be.
Venkat
- Original Message -
DATE: Fri, 06 Jun 2003 03:35:11
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Cc:
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2
is
2 resex varchar2(3);
3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex);
6 END;
7 /
Function created.
SQL EXECUTE EMP_SEL('gali');
BEGIN EMP_SEL('gali'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'EMP_SEL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
_
Do you Yahoo!?
Free online http://us.rd.yahoo.com/mail_us/tag/*http://calendar.yahoo.com
calendar with sync to Outlook(TM).
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
Login To Lycos http://login.mail.lycos.com/r/referral?aid=27005 Mail
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: procedure/function error
slight correction
SQL:x := emp_sel('SCOTT');
has to be changed to :-
SQL exec :x := emp_sel('SCOTT');
sorry ... cutpaste problem from sqlplus output !!!
-Original Message-
Sent: Friday, June 06, 2003 9:51 AM
To: '[EMAIL PROTECTED]'
Functions CAN be called from SQL*Plus like this ...
create the function
---
create or replace function emp_sel(e IN VARCHAR2) RETURN VARCHAR2 IS
cName VARCHAR2(32);
BEGIN
SELECT INITCAP(ename) INTO cName FROM EMP WHERE ename = e;
RETURN (cName);
END;
/
in SQL*Plus do the following
-
SQLvariable x varchar2(32);
SQL exec :x := emp_sel('SCOTT');
PL/SQL procedure successfully completed.
SQLprint X;
X
Scott
HTH ...
-Original Message-
Sent: Friday, June 06, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L
declare
a varchar2(20);
b:=varchar2(20):='gali';
begin
a:=EMP_SEL(b);
dbms_output.put_line(a);
end;
You cannot call the functions like this, only procedures can be.
Venkat
- Original Message -
DATE: Fri, 06 Jun 2003 03:35:11
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Cc:
hi all,
this is purushottam hegde from Bangalore(IND)
i am relatively new to oracle and so to this group.
iam having a problem with function...
it goes like this.
SQL CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2
is
2 resex varchar2(3);
3 Begin
4 SELECT sex into resex from emp where name=ename;
5 return(resex);
6 END;
7 /
Function created.
SQL EXECUTE EMP_SEL('gali');
BEGIN EMP_SEL('gali'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'EMP_SEL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
pl help me in this regard
thanking all of u
purushottam hegde
_
Do you Yahoo!?
Free online http://us.rd.yahoo.com/mail_us/tag/*http://calendar.yahoo.com
calendar with sync to Outlook(TM).
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
Login To Lycos http://login.mail.lycos.com/r/referral?aid=27005 Mail
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
