Home » SQL & PL/SQL » SQL & PL/SQL » A simple function (can not call)
A simple function [message #311959] Mon, 07 April 2008 09:08 Go to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
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 #311994 is a reply to message #311959] Mon, 07 April 2008 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>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.
Re: A simple function [message #311997 is a reply to message #311990] Mon, 07 April 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the greates bug you can have in your code:
EXCEPTION WHEN OTHERS THEN ROLLBACK;

Regards
Michel
Re: A simple function [message #312005 is a reply to message #311994] Mon, 07 April 2008 13:57 Go to previous messageGo to next message
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 #312012 is a reply to message #311959] Mon, 07 April 2008 14:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>when I am trying to call the function it says invalid function
Then you are doing it wrong; what ever it may be.
Since you refuse to show us EXACTLY what you are doing, You're On Your Own (YOYO)!
Re: A simple function [message #312013 is a reply to message #312005] Mon, 07 April 2008 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you successfully create this function? If you aren't sure, please, post such an output (copy and paste what you did, just like I am):
SQL> create or replace function fun_test return number is
  2    retval number;
  3  begin
  4    select count(*) from dept;
  5    return (retval);
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FUN_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>

See? SHOW ERRORS will tell you (and us) what happened with the code.

Re: A simple function [message #312024 is a reply to message #311959] Mon, 07 April 2008 18:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
my friend anecedent is in a bad mood tonight it seems, but he is correct when he points out that you have not shown us your actual call.

show us the select statment or exec statement that trys to call your function along with the error that is produced. You can reproduce this via sqlplus right?

Once you show us that, there will be many of us ready to blast you with a solution.

Kevin
Re: A simple function [message #312052 is a reply to message #311959] Mon, 07 April 2008 20:35 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Try this:

EXCEPTION WHEN OTHERS THEN ROLLBACK;
RETURN (STATUS);



instead of

RETURN STATUS;
EXCEPTION WHEN OTHERS THEN ROLLBACK;


[Updated on: Mon, 07 April 2008 22:49]

Report message to a moderator

Re: A simple function [message #312153 is a reply to message #312052] Tue, 08 April 2008 02:06 Go to previous messageGo to next message
pablolee
Messages: 2836
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 #312155 is a reply to message #312052] Tue, 08 April 2008 02:11 Go to previous messageGo to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
hope this helps:
  • Attachment: ORA-06576.JPG
    (Size: 169.00KB, Downloaded 86 times)
Re: A simple function [message #312156 is a reply to message #312052] Tue, 08 April 2008 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@bibsdash
AWFULLY WRONG!
Read carefully what you wrote.
You wrote that for any error that can raise (ANY ONE) you undo all the work that have be done.
What if the caller made hours of work and don't want YOU erase it, just because your nose itches?

Regards
Michel
Re: A simple function [message #312167 is a reply to message #312156] Tue, 08 April 2008 02:27 Go to previous messageGo to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
can someone stick with the initial question?
Re: A simple function [message #312169 is a reply to message #312167] Tue, 08 April 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the session.

Regards
Michel
Re: A simple function [message #312173 is a reply to message #312169] Tue, 08 April 2008 02:37 Go to previous messageGo to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
whats wrong with Toad?

here is sqlplus:

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.

[Updated on: Tue, 08 April 2008 02:44]

Report message to a moderator

Re: A simple function [message #312182 is a reply to message #312173] Tue, 08 April 2008 02:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Close, what is actually required is that you go into SQL Plus and try to compile your Function, Then enter show errors.
Re: A simple function [message #312186 is a reply to message #312182] Tue, 08 April 2008 02:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #312192 is a reply to message #312187] Tue, 08 April 2008 02:59 Go to previous messageGo to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
great, thank you flyboy this is what I wanted Smile
cheers
Re: A simple function [message #312195 is a reply to message #311959] Tue, 08 April 2008 03:12 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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 Smile

Thanks Michel.
Previous Topic: to display top ten and bottom ten student as per their grade
Next Topic: How to store image files in database
Goto Forum:
  


Current Time: Sun Dec 11 06:18:42 CST 2016

Total time taken to generate the page: 0.09002 seconds