Home » SQL & PL/SQL » SQL & PL/SQL » Call a procedure
icon5.gif  Call a procedure [message #241492] Tue, 29 May 2007 15:38 Go to next message
Annee
Messages: 6
Registered: May 2007
Junior Member
Hi all.
I have created a procedure called addTel:
ALTER TYPE user_typ ADD MEMBER PROCEDURE addTel(tel number)
CASCADE;

create or replace type body user_typ is
member procedure addTel(tel number) is
begin
dbms_output.put_line('Hello world');
...
END;
end;
/

Now, I need to call this procedure but I don't know how. Can someone help me?. I think that I have to used PL/SQL code but I don't know how to do it.
Thanks Very Happy
Re: Call a procedure [message #241493 is a reply to message #241492] Tue, 29 May 2007 15:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
EXECUTE procedurename (arguments);
Re: Call a procedure [message #241497 is a reply to message #241492] Tue, 29 May 2007 15:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Declare a variable of type user_typ, say A, and call A.addTel(value), in PL/SQL of course.

Regards
Michel
Re: Call a procedure [message #241505 is a reply to message #241497] Tue, 29 May 2007 16:21 Go to previous messageGo to next message
Annee
Messages: 6
Registered: May 2007
Junior Member
Hi.
I have written the next code:

DECLARE

u user_typ;
BEGIN
u.addtel('123123123');
END;

but it's wrong... Sad

ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at line 1

Thanks all.
Re: Call a procedure [message #241507 is a reply to message #241505] Tue, 29 May 2007 16:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not an expert in those things but try a static procedure instead of member procedure.

Regards
Michel
icon14.gif  Re: Call a procedure [message #241511 is a reply to message #241507] Tue, 29 May 2007 16:58 Go to previous messageGo to next message
Annee
Messages: 6
Registered: May 2007
Junior Member
Thank you very much Very Happy

The solution is:
To create this procedure like static and to do:
BEGIN
user_typ.addtel('123123123');
END;

Bye
Re: Call a procedure [message #241558 is a reply to message #241511] Wed, 30 May 2007 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, that is not the solution.
As the name of your procedure suggest, you add a telephone number to the instance of your type.
The solution is not to make the procedure static (which means that you do not need an instance of the type), but to create an instance prior to calling your procedure.
Otherwise, the call to the procedure is useless.
SQL> create or replace type user_typ as object
  2  ( telephone number(10)
  3  , constructor function user_typ return self as result
  4  , member procedure addtel(tel in number)
  5  , member function gettel return number
  6  )
  7  /

Type created.

SQL> create or replace type body user_typ
  2  as
  3    constructor function user_typ return self as result
  4    is
  5    begin
  6      self.telephone := null; --dummy code
  7      return;
  8    end user_typ;
  9
 10    member procedure addtel(tel in number)
 11    is
 12    begin
 13      self.telephone := tel;
 14      dbms_output.put_line('Telno set to '||to_char(tel));
 15    end addtel;
 16
 17    member function gettel return number
 18    is
 19    begin
 20      return self.telephone;
 21    end gettel;
 22  end;
 23  /

Type body created.

SQL>
SQL> -- Now call it
SQL> declare
  2    l_user_typ user_typ := user_typ(); -- Create an instance by calling the empty constructor
  3  begin
  4    dbms_output.put_line('Before setting telno: '||l_user_typ.gettel);
  5    l_user_typ.addtel(12345);
  6    dbms_output.put_line('After setting telno: '||l_user_typ.gettel);
  7  end;
  8  /
Before setting telno:
Telno set to 12345
After setting telno: 12345
Re: Call a procedure [message #241561 is a reply to message #241558] Wed, 30 May 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks Frank.

Regards
Michel
icon14.gif  Re: Call a procedure [message #241632 is a reply to message #241558] Wed, 30 May 2007 04:07 Go to previous message
Annee
Messages: 6
Registered: May 2007
Junior Member
Thanks Frank!!!! Very Happy
Previous Topic: Date conversion function
Next Topic: ref cursor
Goto Forum:
  


Current Time: Mon Dec 05 04:47:30 CST 2016

Total time taken to generate the page: 0.10716 seconds