Home » SQL & PL/SQL » SQL & PL/SQL » small problem in calling function
small problem in calling function [message #214747] Wed, 17 January 2007 21:47 Go to next message
sanammg
Messages: 31
Registered: June 2005
Member
problem is: i written one function which should written a type of record.i need to call this function in the one plsql block(written below),i want to print the record which is returning from the function

i am getting error 'wrong type of arguments to call the function'.
and i want output as a type of record only.

ex:

--package spec
create or replace package system_change
is
type abc is record (a number,
b varchar2(10),
c number,
d varchar2(12));
function sathya return abc;
end system_change;

----pack body

create or replace package body system_change is
FUNCTION sathya RETURN abc IS
X abc;
BEGIN
X.A:=100;
X.B:='SATHYA';
X.C:=200;
X.D:='HI';
RETURN X;
END sathya;
end system_change;

---the function calling block is

declare
type xyz is record (a number,b varchar2(10),c number,d varchar2(12));
temp xyz;
begin
temp:=system_change.sathya;
dbms_output.put_line('the values are'||temp.a);
dbms_output.put_line('the values are'||temp.b);
dbms_output.put_line('the values are'||temp.c);
dbms_output.put_line('the values are'||temp.d);
end;
Re: small problem in calling function [message #214775 is a reply to message #214747] Thu, 18 January 2007 00:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try data base type instead of local record.

By
Vamsi
Re: small problem in calling function [message #214776 is a reply to message #214775] Thu, 18 January 2007 01:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Although you may have declared two alike recordstructures, the types are not the same.
You can either (like vamsi kasina suggested) declare a sql type, or refer to the publicly declared packaged type in the call to your function:
declare temp in the following way:
temp system_change.abc;
Re: small problem in calling function [message #214796 is a reply to message #214776] Thu, 18 January 2007 02:17 Go to previous messageGo to next message
sanammg
Messages: 31
Registered: June 2005
Member
OK , I WILL TRY
Re: small problem in calling function [message #214800 is a reply to message #214747] Thu, 18 January 2007 02:26 Go to previous messageGo to next message
sanammg
Messages: 31
Registered: June 2005
Member
hi vamsi and Frank
i tried in both the ways but no improvement.


1 declare
temp system_change.abc;
4 begin
5 temp:=system_change.sathya;
6 dbms_output.put_line('the values are'||temp.a);
7 dbms_output.put_line('the values are'||temp.b);
8 dbms_output.put_line('the values are'||temp.c);
9 dbms_output.put_line('the values are'||temp.d);
10* end;
SQL> /
temp:=system_change.sathya;
*
ERROR at line 5:
ORA-06550: line 5, column 21:
PLS-00306: wrong number or types of arguments in call to 'SATHYA'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored


regards
sathya
Re: small problem in calling function [message #215049 is a reply to message #214800] Fri, 19 January 2007 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> declare
  2    temp system_change.abc;
  3  begin
  4    temp:=system_change.sathya;
  5    dbms_output.put_line('the values are'||temp.a);
  6    dbms_output.put_line('the values are'||temp.b);
  7    dbms_output.put_line('the values are'||temp.c);
  8    dbms_output.put_line('the values are'||temp.d);
  9  end;
 10  /
the values are100
the values areSATHYA
the values are200
the values areHI

PL/SQL procedure successfully completed.


You either altered your packaged function (did you add a parameter?) or you did not copy-paste your call to the function.
Either one of those, or you are mixing up users.
Re: small problem in calling function [message #215559 is a reply to message #215049] Mon, 22 January 2007 22:24 Go to previous message
sanammg
Messages: 31
Registered: June 2005
Member
fine thanks frank.
Previous Topic: Reg: Latest values....
Next Topic: prob in EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Wed Dec 07 03:07:06 CST 2016

Total time taken to generate the page: 0.17860 seconds