Home » SQL & PL/SQL » SQL & PL/SQL » Calling member procedure from member function
Calling member procedure from member function [message #291263] Thu, 03 January 2008 09:52 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hello,

I am getting some strange errors while trying to compile the object type function TST. The situation is this. I have an Object type POLISAS.PolisasObj, that has been stripped down just to represent the problem:
Header:
CREATE OR REPLACE TYPE POLISAS.PolisasObj 
AS OBJECT
(
nPARD_VIEN_VER_ID    NUMBER, 
  MEMBER FUNCTION GetPardVienVerID RETURN NUMBER ,
  MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ),
  MEMBER FUNCTION TST RETURN NUMBER
  );

Body:
CREATE OR REPLACE TYPE BODY POLISAS.PolisasObj AS
   
  --PARD_VIEN_VER_ID---    
  MEMBER FUNCTION GetPardVienVerID RETURN NUMBER IS
  BEGIN
    RETURN nPARD_VIEN_VER_ID;
  END;
  -----  
  MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ) IS
  BEGIN
    nPARD_VIEN_VER_ID := nPardVienVerIDParm;
  END;
  
  -------------  
  MEMBER FUNCTION TST RETURN NUMBER 
  IS
  BEGIN
    --POLISAS.PolisasObj.SetPardVienVerID(1); /* 1st row */
    --SetPardVienVerID(1);                    /* 2nd row */
    RETURN 1;
   END;
  
END;


As it is given above, PolisasObj compiles without errors.
Here are the problems:
1. If I uncomment the 1st row, I receive the error "PLS-00306: Wrong number or types of arguments in call to SetPardVienVerID". For me that makes no sense. 1 is a number as far as I know. And it is the only parameter... Wink
2. If I uncomment the 2nd row, I receive the error "PLS-00363: expression 'SELF' cannnot be used as assignement target". Well, maybe it makes sense, I dont know. I tried googling on this error, but found nothing that could help me to compile this line.
A hint please!
3. If I make TST not a function, but a procedure, the 1st row gives the same error as mentioned before, but the 2nd row compiles OK! The bad news are that I need a function... Now I am totally confused. I have noticed no compilation differences between functions and procedures before. What are they? Shocked
4. What is essentially the difference between the 1st and 2nd line? I think it is all the same, because as if it was with packages, I could call the other procedures and functions of the same package not using long notation <schema>.<package_name>.<procedure_name>, but a short one: <procedure_name>... Confused
Re: Calling member procedure from member function [message #291318 is a reply to message #291263] Thu, 03 January 2008 18:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I used the scott schema for testing. Adding a parameter (SELF IN OUT PolisasObj) to the TST function seems to solve the problem.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE SCOTT.PolisasObj
  2  AS OBJECT
  3  (
  4  nPARD_VIEN_VER_ID	  NUMBER,
  5    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER ,
  6    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ),
  7    MEMBER FUNCTION TST (SELF IN OUT PolisasObj) RETURN NUMBER
  8    );
  9  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY SCOTT.PolisasObj AS
  2  
  3    --PARD_VIEN_VER_ID---
  4    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER IS
  5    BEGIN
  6  	 RETURN nPARD_VIEN_VER_ID;
  7    END;
  8    -----
  9    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ) IS
 10    BEGIN
 11  	 nPARD_VIEN_VER_ID := nPardVienVerIDParm;
 12    END;
 13  
 14    -------------
 15    MEMBER FUNCTION TST (SELF IN OUT PolisasObj) RETURN NUMBER
 16    IS
 17    BEGIN
 18  	 SetPardVienVerID(1);
 19  	 RETURN 1;
 20    END;
 21  
 22  END;
 23  /

Type body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 

Re: Calling member procedure from member function [message #291319 is a reply to message #291263] Thu, 03 January 2008 18:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
The following takes it a step further, adding a parameter to the TST function to pass to the SetPardVienVerID procedure and testing it, just guessing at what you might be trying to do.


 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE SCOTT.PolisasObj
  2  AS OBJECT
  3  (
  4  nPARD_VIEN_VER_ID	  NUMBER,
  5    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER ,
  6    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ),
  7    MEMBER FUNCTION TST (SELF IN OUT PolisasObj, p_num IN NUMBER) RETURN NUMBER
  8    );
  9  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY SCOTT.PolisasObj AS
  2  
  3    --PARD_VIEN_VER_ID---
  4    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER IS
  5    BEGIN
  6  	 RETURN nPARD_VIEN_VER_ID;
  7    END;
  8    -----
  9    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ) IS
 10    BEGIN
 11  	 nPARD_VIEN_VER_ID := nPardVienVerIDParm;
 12    END;
 13  
 14    -------------
 15    MEMBER FUNCTION TST (SELF IN OUT PolisasObj, p_num IN NUMBER) RETURN NUMBER
 16    IS
 17    BEGIN
 18  	 SetPardVienVerID(p_num);
 19  	 RETURN 1;
 20    END;
 21  
 22  END;
 23  /

Type body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> DECLARE
  2    v_test PolisasObj := PolisasObj (2);
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('intial value:  ' || v_test.nPARD_VIEN_VER_ID);
  5    DBMS_OUTPUT.PUT_LINE ('return value:  ' || PolisasObj.TST (v_test, 3));
  6    DBMS_OUTPUT.PUT_LINE ('set value:  ' || v_test.nPARD_VIEN_VER_ID);
  7  END;
  8  /
intial value:  2
return value:  1
set value:  3

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: Calling member procedure from member function [message #291389 is a reply to message #291319] Fri, 04 January 2008 01:21 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
Quote:

SELF IN OUT PolisasObj

This seem to solve the problem! Thanks.

[Updated on: Fri, 04 January 2008 01:26]

Report message to a moderator

Re: Calling member procedure from member function [message #291394 is a reply to message #291389] Fri, 04 January 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you just try to execute it in your database before answering?
BZFD01> CREATE OR REPLACE TYPE PolisasObj
  2  AS OBJECT
  3  (
  4    nPARD_VIEN_VER_ID    NUMBER,
  5    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER ,
  6    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ),
  7    MEMBER FUNCTION TST (SELF IN OUT PolisasObj, p_num IN NUMBER) RETURN NUMBER
  8    );
  9  /

Type created.

BZFD01> CREATE OR REPLACE TYPE BODY PolisasObj AS
  2
  3    --PARD_VIEN_VER_ID---
  4    MEMBER FUNCTION GetPardVienVerID RETURN NUMBER IS
  5    BEGIN
  6      RETURN nPARD_VIEN_VER_ID;
  7    END;
  8    -----
  9    MEMBER PROCEDURE SetPardVienVerID ( nPardVienVerIDParm IN NUMBER ) IS
 10    BEGIN
 11      nPARD_VIEN_VER_ID := nPardVienVerIDParm;
 12    END;
 13
 14    -------------
 15    MEMBER FUNCTION TST (SELF IN OUT PolisasObj, p_num IN NUMBER) RETURN NUMBER
 16
 17    IS
 18    BEGIN
 19      SetPardVienVerID(p_num);
 20      RETURN 1;
 21     END;
 22
 23  END;
 24  /

Type body created.

BZFD01> DECLARE
  2    v_test PolisasObj := PolisasObj (2);
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('intial value:  ' || v_test.nPARD_VIEN_VER_ID);
  5    DBMS_OUTPUT.PUT_LINE ('return value:  ' || PolisasObj.TST (v_test, 3));
  6    DBMS_OUTPUT.PUT_LINE ('set value:  ' || v_test.nPARD_VIEN_VER_ID);
  7  END;
  8  /
intial value:  2
return value:  1
set value:  3

PL/SQL procedure successfully completed.

BZFD01> @v

Version Oracle : 9.2.0.6.0

Regards
Michel
Re: Calling member procedure from member function [message #291395 is a reply to message #291389] Fri, 04 January 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh! Don't erase and change completly your post.
Now my previous answer seems completly silly.

Previous OP post was somthing like:
"SCOTT@orcl_11g>" are you working in 11g?
We are working in 9i, this seems to be the problem.

Regards
Michel
Re: Calling member procedure from member function [message #291398 is a reply to message #291395] Fri, 04 January 2008 01:40 Go to previous message
Buchas
Messages: 83
Registered: March 2006
Member
Smile
OK, sorry. I thought I changed it in a moment before anybody could read it and did not notice you had already answered to it Wink
Previous Topic: Stored Proceudure
Next Topic: in operator
Goto Forum:
  


Current Time: Tue Dec 06 12:15:48 CST 2016

Total time taken to generate the page: 0.07481 seconds