Home » SQL & PL/SQL » SQL & PL/SQL » understanding types
understanding types [message #380784] Tue, 13 January 2009 05:55 Go to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hi,

I want to develop a function that return a user data type. To test that, I developed the code list below (is only an example, is not my real case). The code is well compiled, procedure showTime works properly but showTime1 and showTime2 doesn't work properly. Execute well, without errors, but doesn't show any values.

Can anyone explain me why? It's possible, in ORACLE, return values by a user data type?

Thanks in advanced, MBAlves



create type timetype as object(
    hours number(2), 
    minutes number(2),
    seconds number(2));
/


create or replace function returnTime1 return timetype is
   secondsAfterMidnight number;
   retVar timetype ;
begin
   select to_char(sysdate, 'SSSSS') into secondsAfterMidnight from dual;
   retVar.hours := trunc(secondsAfterMidnight/ (60*60));
   secondsAfterMidnight := secondsAfterMidnight mod (60*60); 
   retVar.minutes := trunc(secondsAfterMidnight/60);
   retVar.seconds := secondsAfterMidnight mod 60; 

  return retVar;
end;
/


create or replace procedure returnTime2 (retVar out timetype) is
   secondsAfterMidnight number;

begin
   select to_char(sysdate, 'SSSSS') into secondsAfterMidnight from dual;
   retVar.hours := trunc(secondsAfterMidnight/ (60*60));
   secondsAfterMidnight := secondsAfterMidnight mod (60*60); 
   retVar.minutes := trunc(secondsAfterMidnight/60);
   retVar.seconds := secondsAfterMidnight mod 60; 

end;
/

create or replace procedure showTime is
   secondsAfterMidnight number;
    currentTime timetype ;
begin
   select to_char(sysdate, 'SSSSS') into secondsAfterMidnight from dual;
   currentTime.hours := trunc(secondsAfterMidnight/ (60*60));
   secondsAfterMidnight := secondsAfterMidnight mod (60*60); 
   currentTime.minutes := trunc(secondsAfterMidnight/60);
   currentTime.seconds := secondsAfterMidnight mod 60; 

    dbms_output.put_line('Its ' || currentTime.hours || ' hours ' || 
currentTime.minutes || ' minutes ' || currentTime.seconds || ' seconds'); 
   
end;
/

create or replace procedure showTime1 is
   secondsAfterMidnight number;
    currentTime timetype ;
begin
    currentTime := returnTime1();
    dbms_output.put_line('Its ' || currentTime.hours || ' hours ' || 
currentTime.minutes || ' minutes ' || currentTime.seconds || ' seconds'); 
   
end;
/

create or replace procedure showTime2 is
   secondsAfterMidnight number;
    currentTime timetype ;
begin
    returnTime2(currentTime);
    dbms_output.put_line('Its ' || currentTime.hours || ' hours ' || 
currentTime.minutes || ' minutes ' || currentTime.seconds || ' seconds'); 
   
end;
/


set serveroutput on;
exec showTime;
exec showTime1;
exec showTime2;


[Updated on: Tue, 13 January 2009 08:33] by Moderator

Report message to a moderator

Re: understanding types [message #380788 is a reply to message #380784] Tue, 13 January 2009 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but doesn't show any values.

set serveroutput on

Regards
Michel
Re: understanding types [message #380821 is a reply to message #380788] Tue, 13 January 2009 08:26 Go to previous messageGo to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hi Michel,

Thanks for your answer.

As you can see in my script, I had put "set serveroutput on" (four lines from the end). And, as I said in my topic, one of the procedures works properly (showTime) but the others didn´t work (showTime1 and showTime2). In resume, the procedure showTime1 calls a function (returnTime1) thats return a user data type and the procedure showTime2 calls a procedure (returnTime2) with a variable of the same user data type passed as parameter. This parameter is defined as out parameter.Both procedures didn´t show the espected values. The procedure showTime do the processing of the function returnTime1 and the procedure returnTime2 and works properly. My main doubt is if I can use user data type as return parameter. Compiling the code don't return any kind of error.

MBAlves
Re: understanding types [message #380823 is a reply to message #380821] Tue, 13 January 2009 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As you can see in my script, I had put "set serveroutput on" (four lines from the end). And, as I said in my topic, one of the procedures works properly (showTime) but the others didn´t work (showTime1 and showTime2).

This is what happens when you just say and don't copy and paste the actual SQL*Plus session.
So do it and keep your lines in 80 characters width.

Regards
Michel

[Updated on: Tue, 13 January 2009 08:35]

Report message to a moderator

Re: understanding types [message #380827 is a reply to message #380821] Tue, 13 January 2009 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> exec showTime;
Its 15 hours 34 minutes 58 seconds

PL/SQL procedure successfully completed.

SQL> exec showTime1;
Its  hours  minutes  seconds

PL/SQL procedure successfully completed.

SQL> exec showTime2;
Its  hours  minutes  seconds

PL/SQL procedure successfully completed.

For first function you have to allocate the variable:
SQL> create or replace function returnTime1 return timetype is
  2     secondsAfterMidnight number;
  3     retVar timetype :=  timetype(null,null,null);
  4  begin
  5     select to_char(sysdate, 'SSSSS') into secondsAfterMidnight from dual;
  6     retVar.hours := trunc(secondsAfterMidnight/ (60*60));
  7     secondsAfterMidnight := secondsAfterMidnight mod (60*60); 
  8     retVar.minutes := trunc(secondsAfterMidnight/60);
  9     retVar.seconds := secondsAfterMidnight mod 60; 
 10  
 11    return retVar;
 12  end;
 13  /

Function created.

SQL> exec showTime1;
Its 15 hours 37 minutes 7 seconds

PL/SQL procedure successfully completed.

For second case, you have to change your parameter to IN OUT and allocate the variable in the caller:
SQL> create or replace procedure returnTime2 (retVar in out timetype) is
  2     secondsAfterMidnight number;
  3  
  4  begin
  5     select to_char(sysdate, 'SSSSS') into secondsAfterMidnight from dual;
  6     retVar.hours := trunc(secondsAfterMidnight/ (60*60));
  7     secondsAfterMidnight := secondsAfterMidnight mod (60*60); 
  8     retVar.minutes := trunc(secondsAfterMidnight/60);
  9     retVar.seconds := secondsAfterMidnight mod 60; 
 10  
 11  end;
 12  /

Procedure created.

SQL> create or replace procedure showTime2 is
  2     secondsAfterMidnight number;
  3      currentTime timetype := timetype(null,null,null);
  4  begin
  5      returnTime2(currentTime);
  6      dbms_output.put_line('Its ' || currentTime.hours || ' hours ' || 
  7  currentTime.minutes || ' minutes ' || currentTime.seconds || ' seconds'); 
  8     
  9  end;
 10  /

Procedure created.

SQL> exec showTime2;
Its 15 hours 40 minutes 55 seconds

PL/SQL procedure successfully completed.

Regards
Michel
Re: understanding types [message #380830 is a reply to message #380823] Tue, 13 January 2009 08:52 Go to previous messageGo to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Ok,

here is the output

(from the SQL developer)

Connecting to the database connAula.
showTime -> Its 14 hours 46 minutes 37 seconds
showTime1 -> Its hours minutes seconds
showTime2 -> Its hours minutes seconds
Process exited.
Disconnecting from the database connAula.

(from the Oracle XE prompt)
./fa/5581/0/
  • Attachment: screen1.jpg
    (Size: 26.97KB, Downloaded 200 times)
Re: understanding types [message #380832 is a reply to message #380827] Tue, 13 January 2009 08:57 Go to previous messageGo to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hi Michel,

Thanks for your answer. My last topic was after I read your answr.

regards, MBAlves
Re: understanding types [message #380835 is a reply to message #380832] Tue, 13 January 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, copy and paste in TEXT format (as I did it), some of us can't download files and/or images.

Regards
Michel
Re: understanding types [message #380838 is a reply to message #380784] Tue, 13 January 2009 09:18 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I thought I'd try your code so I cut and paste into my 9i environment and got this

Procedure created
 
begin showTime; end;
 
ORA-06530: Reference to uninitialized composite
ORA-06512: at "SDDDBA.SHOWTIME", line 6
ORA-06512: at line 2
 
begin showTime1; end;
 
ORA-06530: Reference to uninitialized composite
ORA-06512: at "SDDDBA.RETURNTIME1", line 6
ORA-06512: at "SDDDBA.SHOWTIME1", line 5
ORA-06512: at line 2
 
begin showTime2; end;
 
ORA-06530: Reference to uninitialized composite
ORA-06512: at "SDDDBA.RETURNTIME2", line 6
ORA-06512: at "SDDDBA.SHOWTIME2", line 5
ORA-06512: at line 2
 
SQL> 


Using it as a learning experience (I haven't had cause to use types yet) I tracked down ORA-06530 and found that the object needs to be initialized. Following details on <a href="http://www.dbasupport.com/forums/archive/index.php/t-33739.html" target="_blank">dbasupport</a> I added
currentTime := timetype(NULL,NULL,NULL);
and
retVar := timetype(NULL,NULL,NULL);
just after the relevant begin statement, re-tried and everything worked ok.

However when you posted your example I tried again but with a 10g environment and got the same as you
Procedure created
 
Its 15 hours 8 minutes 38 seconds
 
PL/SQL procedure successfully completed
 
Its  hours  minutes  seconds
 
PL/SQL procedure successfully completed
 
Its  hours  minutes  seconds
 
PL/SQL procedure successfully completed
 


I added the previous fixes and it worked

Procedure created
 
Its 15 hours 3 minutes 9 seconds
 
PL/SQL procedure successfully completed
 
Its 15 hours 3 minutes 9 seconds
 
PL/SQL procedure successfully completed
 
Its 15 hours 3 minutes 9 seconds
 
PL/SQL procedure successfully completed


May be an 'issue' with 10g?

[Updated on: Tue, 13 January 2009 09:19]

Report message to a moderator

Previous Topic: How many "SUNDAY" in a Month
Next Topic: "instead of triggers"
Goto Forum:
  


Current Time: Thu Dec 08 12:32:12 CST 2016

Total time taken to generate the page: 0.08527 seconds