A simple function [message #311959] |
Mon, 07 April 2008 09:08  |
bluerratiq
Messages: 22 Registered: March 2008 Location: Bucharest
|
Junior Member |
|
|
Hello,
I know that what I am trying to do is a very simple thing, but I cant figure out why is not working.
So, I am creating a function which checks the status of a book (its about a library administration), meaning it returns a message: book is currently at client x or book is available.
When I am calling the function it says not a valid function
This is the function:
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION STATUS_CARTE(F_NUME IN VARCHAR2)
RETURN VARCHAR2
IS
STATUS VARCHAR2(50);
V_COD_CARTE NUMBER(10);
V_COD_CLIENT NUMBER(10);
V_NUME_CARTE VARCHAR2(50);
V_NUME_CLIENT VARCHAR2(50);
CURSOR STATUS_CARTE_CURSOR IS
SELECT NUME_CARTE, C.COD_CARTE, NUME_CLIENT, CI.COD_CLIENT FROM CARTI C, CARTI_INCHIRIATE CI, CLIENT CL
WHERE C.COD_CARTE=CI.COD_CARTE AND CI.COD_CLIENT=CL.COD_CLIENT;
BEGIN
OPEN STATUS_CARTE_CURSOR;
LOOP
FETCH STATUS_CARTE_CURSOR INTO V_NUME_CARTE, V_COD_CARTE, V_NUME_CLIENT, V_COD_CLIENT;
IF V_NUME_CARTE=F_NUME THEN STATUS:='Book assigned to:'||V_NUME_CLIENT;
ELSE STATUS:='Book is available';
END IF;
EXIT WHEN STATUS_CARTE_CURSOR%NOTFOUND;
END LOOP;
CLOSE STATUS_CARTE_CURSOR;
RETURN STATUS;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END STATUS_CARTE;
-----------------------------------------------------------------
Can someone tell me what I am doing wrong?
Thanks a lot!!
[Updated on: Mon, 07 April 2008 09:10] Report message to a moderator
|
|
|
Re: A simple function [message #311961 is a reply to message #311959] |
Mon, 07 April 2008 09:12   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
1. You need to format your posts and use code tags as per the Forum Guidelines.
2. Post your session (again formatted between code tags) that raises the error.
Once you have done that, we can look at the actual problem
|
|
|
Re: A simple function [message #311990 is a reply to message #311959] |
Mon, 07 April 2008 11:44   |
bluerratiq
Messages: 22 Registered: March 2008 Location: Bucharest
|
Junior Member |
|
|
CREATE OR REPLACE FUNCTION STATUS_CARTE(F_NUME IN VARCHAR2)
RETURN VARCHAR2
IS
STATUS VARCHAR2(50);
V_COD_CARTE NUMBER(10);
V_COD_CLIENT NUMBER(10);
V_NUME_CARTE VARCHAR2(50);
V_NUME_CLIENT VARCHAR2(50);
CURSOR STATUS_CARTE_CURSOR IS
SELECT NUME_CARTE, C.COD_CARTE, NUME_CLIENT, CI.COD_CLIENT
FROM CARTI C, CARTI_INCHIRIATE CI, CLIENT CL
WHERE C.COD_CARTE=CI.COD_CARTE AND CI.COD_CLIENT=CL.COD_CLIENT;
BEGIN
OPEN STATUS_CARTE_CURSOR;
LOOP
FETCH STATUS_CARTE_CURSOR INTO V_NUME_CARTE, V_COD_CARTE, V_NUME_CLIENT, V_COD_CLIENT;
IF V_NUME_CARTE=F_NUME THEN STATUS:='Book assigned to:'||V_NUME_CLIENT;
ELSE STATUS:='Book is available';
END IF;
EXIT WHEN STATUS_CARTE_CURSOR%NOTFOUND;
END LOOP;
CLOSE STATUS_CARTE_CURSOR;
RETURN STATUS;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END STATUS_CARTE;
sorry its my first time posting here and I didnt read the rules
[Updated on: Mon, 07 April 2008 12:03] by Moderator Report message to a moderator
|
|
|
|
|
Re: A simple function [message #312005 is a reply to message #311994] |
Mon, 07 April 2008 13:57   |
bluerratiq
Messages: 22 Registered: March 2008 Location: Bucharest
|
Junior Member |
|
|
anacedent wrote on Mon, 07 April 2008 20:00 | >I cant figure out why is not working.
My car is not working.
tell me how to make my car go.
Error? What error? I don't see any error to fix.
|
when I am trying to call the function it says invalid function
|
|
|
|
|
|
|
Re: A simple function [message #312153 is a reply to message #312052] |
Tue, 08 April 2008 02:06   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
WRONG!
Do not use when others without re-raisng the exception. As has been repeatedly said in this thread, the OP MUST show us any error that occurred in the function compilation, if there were none, the the OP MUST show us the call to the function. Otherwise it is impossible to tell what has gone wrong without wild guesses.
|
|
|
|
|
|
|
|
|
Re: A simple function [message #312186 is a reply to message #312182] |
Tue, 08 April 2008 02:50   |
bluerratiq
Messages: 22 Registered: March 2008 Location: Bucharest
|
Junior Member |
|
|
SQL> CREATE OR REPLACE FUNCTION STATUS_CARTE(F_NUME IN VARCHAR2)
2 RETURN VARCHAR2 IS STATUS VARCHAR2(50);
3 V_COD_CLIENT NUMBER(20);
4 V_COD_CARTE NUMBER(20);
5 V_NUME_CARTE VARCHAR2(50);
6 V_NUME_CLIENT VARCHAR2(50);
7 CURSOR STATUS_CARTE_CURSOR IS SELECT NUME_CARTE, C.COD_CARTE, NUME_CLIENT,
8 CI.COD_CLIENT FROM CARTI C, CARTI_INCHIRIATE CI, CLIENT CL
9 WHERE C.COD_CARTE=CI.COD_CARTE AND CI.COD_CLIENT=CL.COD_CLIENT;
10 BEGIN
11 OPEN STATUS_CARTE_CURSOR;
12 LOOP
13 FETCH STATUS_CARTE_CURSOR INTO V_NUME_CARTE, V_COD_CARTE, V_NUME_CLIENT,
14 V_COD_CLIENT;
15 IF V_NUME_CARTE=F_NUME THEN STATUS:='BOOK ASSIGNED TO:'||V_NUME_CLIENT;
16 ELSE STATUS:='BOOK IS AVAILABLE';
17 END IF;
18 EXIT WHEN STATUS_CARTE_CURSOR%NOTFOUND;
19 END LOOP;
20 CLOSE STATUS_CARTE_CURSOR;
21 RETURN STATUS;
22 END STATUS_CARTE;
23 /
Function created.
SQL> call status_carte('Carte3');
call status_carte('Carte3')
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
SQL> show errors
No errors.
SQL>
[Updated on: Tue, 08 April 2008 02:51] Report message to a moderator
|
|
|
Re: A simple function [message #312187 is a reply to message #311959] |
Tue, 08 April 2008 02:51   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just a demonstration in SQL*Plus: SQL> create or replace function f1 ( p1 in varchar2 ) return varchar2 is
2 begin
3 return p1;
4 end;
5 /
Function created.
SQL> call f1( 'anything' );
call f1( 'anything' )
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
SQL> var s1 varchar2(100)
SQL> call f1( 'anything' ) into :s1;
Call completed.
SQL> print s1
S1
--------------------------------------------------------------------------------
anything
SQL> If you would have looked into documentation, found eg. online on http://tahiti.oracle.com/, you would find the CALL syntax (link is for 10gR2).
I do not know, how to create and use bind variables in TOAD, but I do not doubt it is possible to find using its help (F1 button).
|
|
|
|
Re: A simple function [message #312195 is a reply to message #311959] |
Tue, 08 April 2008 03:12   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Thank you for feedback.
Before asking another questions, look into this thread once more. Do you not think you would get help faster if you would post the content of your last two posts in the beginning? Nobody here knew how did you call the function and nobody took the effort for blind guessing (which would be most possibly in the wrong direction).
Also you shall learn that consulting the documentation is good. Now you know, where to find it.
|
|
|
Re: A simple function [message #312196 is a reply to message #312192] |
Tue, 08 April 2008 03:13   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You can also use:
SQL> create or replace function f1 ( p1 in varchar2 ) return varchar2 is
2 begin
3 return p1;
4 end;
5 /
Function created.
SQL> var s1 varchar2(100)
SQL> execute :s1 := f1 ('anything')
PL/SQL procedure successfully completed.
SQL> print s1
S1
------------------------------------------------------------------------
anything
or
SQL> begin
2 :s1 := f1 ('anything');
3 end;
4 /
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Tue, 08 April 2008 03:14] Report message to a moderator
|
|
|
Re: A simple function [message #312201 is a reply to message #312195] |
Tue, 08 April 2008 03:34  |
bluerratiq
Messages: 22 Registered: March 2008 Location: Bucharest
|
Junior Member |
|
|
flyboy wrote on Tue, 08 April 2008 11:12 | Thank you for feedback.
Before asking another questions, look into this thread once more. Do you not think you would get help faster if you would post the content of your last two posts in the beginning? Nobody here knew how did you call the function and nobody took the effort for blind guessing (which would be most possibly in the wrong direction).
Also you shall learn that consulting the documentation is good. Now you know, where to find it.
|
Yes you are right. Once again, thanks for the answer and for the tips. For sure I will bookmark that link 
Thanks Michel.
|
|
|