Home » SQL & PL/SQL » SQL & PL/SQL » RowType In Function List
RowType In Function List [message #263151] Wed, 29 August 2007 04:06 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
hi,
I ask for help!
while i try to compile the main Function,Oracle return error code pls_00382 with message:Error: PLS-00382: expression is of wrong type.
Below is My Codes.
Main Function
F_main()
is
 v_T tab%rowtype;
begin
 update table_r set isvalid=f_getvalidvalue(v_t,Score);
 commit;
end;

Sub Function
function F_GetValidValue(p_t in tab%rowtype,p_score in number) return int
is
begin
  ......
   --return somthing here
end;

tso ,how to declare the variable in the main function? or how to change the sub function.
Re: RowType In Function List [message #263152 is a reply to message #263151] Wed, 29 August 2007 04:08 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Main function , Sub function.. !!!

I am in oracle, not in C/C++.
icon6.gif  Re: RowType In Function List [message #263159 is a reply to message #263152] Wed, 29 August 2007 04:24 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Sorry ,But i think you know what i said! That is just for the sake of Writing.
You can take the main Function as a Main Function,the Sub function as a minor function.

Re: RowType In Function List [message #263160 is a reply to message #263151] Wed, 29 August 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put you subfunction in the declaration section but this is only valid for function you want to use in PL/SQL.
If you want to use it in SQL you have to create a stored function and not a local one.

Regards
Michel

[Updated on: Wed, 29 August 2007 04:26]

Report message to a moderator

icon5.gif  Re: RowType In Function List [message #263367 is a reply to message #263159] Wed, 29 August 2007 20:51 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
sorry,Maybe you do not understand what i said.
I Means that that is a question about function rowtype parameter .
if the type of one of the parameter of a function is %rowtype,then the oracle will compile the function without any error,but while i call the function like that with a rowtype variable ,the compiler will throw the errors.
SO,My Question is that how to declare a variable to call a function with one rowtype parameter.
function f_main return int
is
  i int;
  v_T tab%rowtype;--Is this right?
  --if declare a variable like that the following statement will
  --throw errors.
begin
  F_GetValidValue(V_T); --will throw errors.
end;

function F_GetValidValue(p_t in tab%rowtype) return int
is
begin
  ..
end;
Re: RowType In Function List [message #263430 is a reply to message #263367] Thu, 30 August 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Maybe you do not understand what i said.

Maybe you don't explain your problem correctly.
SQL> create or replace package pkg is
  2    function f_main return int;
  3    function f_sub (v_t t%rowtype) return int;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg is
  2    function f_main return int is
  3      v_t t%rowtype;
  4    begin 
  5      return f_sub (v_t);
  6    end;
  7    function f_sub (v_t t%rowtype) return int is
  8    begin return 1; end;
  9  end;
 10  /

Package body created.

SQL> select pkg.f_main from dual;
    F_MAIN
----------
         1

1 row selected.

There is no problem with rowtype.

Regards
Michel

[Updated on: Fri, 31 August 2007 00:37]

Report message to a moderator

icon3.gif  Re: RowType In Function List [message #263724 is a reply to message #263430] Thu, 30 August 2007 20:35 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Thank you ,Michel.
I have tested your program,it run fine.
By the way ,the version of my oracle db is 9iR2.
If you call the f_sub in a update sql such as :
Create table tab_t(id int unique not null);
  update tab_t set id=f_sub(vt);


You will get unexpected experience.

doese anyone else find that?
Re: RowType In Function List [message #263731 is a reply to message #263151] Thu, 30 August 2007 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> doese anyone else find that?
yes, if you do it wrong you get an error.

You should always post complete CUT & PASTE including COMPLETE output/error messages.
Re: RowType In Function List [message #263734 is a reply to message #263731] Thu, 30 August 2007 21:42 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Here are the complete Codes:
Create table tab_T(id int);

create or replace package pkg_test is
 Function F_main return int;
 Function F_sub(p_t in tab%rowtype) return int;

end pkg_test;
create or replace package body pkg_test is
Function F_main return int
is
  v_t tab%rowtype;
begin
  update tab_t set id=to_char(f_sub(v_t));
  return 100;
  --return f_sub(v_t);  --this will work fine.
end;
Function F_sub(p_t in tab%rowtype) return int
is
begin
  return 100;
end;
end pkg_test;


Re: RowType In Function List [message #263779 is a reply to message #263734] Fri, 31 August 2007 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot do a to_char on a rowtype. to_char expects scalar input

Woops: misread the code. Ignore my post please

[Updated on: Fri, 31 August 2007 00:45]

Report message to a moderator

Re: RowType In Function List [message #263780 is a reply to message #263779] Fri, 31 August 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
You will get unexpected experience.

Maybe you can explain and show what results you get?
Re: RowType In Function List [message #263781 is a reply to message #263734] Fri, 31 August 2007 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many errors in your code.
The table is name "tab_t" and not "tab" as referenced in your code.
Type tab_t%rowtype is not known of SQL only PL/SQL when you "update" you switch to SQL engine so you have to transit via a PL/SQL variable:
SQL> create or replace package body pkg_test is
  2  Function F_main return int
  3  is
  4    v_t tab_t%rowtype;
  5    v_id int;
  6  begin
  7    v_id := f_sub(v_t);
  8    update tab_t set id=to_char(v_id);
  9    return 100;
 10    --return f_sub(v_t);  --this will work fine.
 11  end;
 12  Function F_sub(p_t in tab_t%rowtype) return int
 13  is
 14  begin
 15    return 100;
 16  end;
 17  end pkg_test;
 18  /

Package body created.

Regards
Michel

Re: RowType In Function List [message #263882 is a reply to message #263781] Fri, 31 August 2007 04:00 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
does that means update sqls can not call functions with rowtype parameters?
It looks like that. I Just need a confirmation.
Re: RowType In Function List [message #263898 is a reply to message #263882] Fri, 31 August 2007 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes that means that.
SQL only knows types that are defined in dictionary.

Regards
Michel
Re: RowType In Function List [message #264415 is a reply to message #263898] Mon, 03 September 2007 04:31 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Thank you very much!
I hope this would change in the future version .
Re: RowType In Function List [message #264419 is a reply to message #264415] Mon, 03 September 2007 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope this would change in the future version.

I don't think this will ever happen.

Regards
Michel
Re: RowType In Function List [message #266310 is a reply to message #264419] Mon, 10 September 2007 08:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not an unreasonable hope - after all, Sql DOES know everything it needs to know about the datatype - it just won't use it.
Previous Topic: How to "union" values in two columns from the same table?
Next Topic: Optimizing ALTER TABLE DDL statement
Goto Forum:
  


Current Time: Fri Dec 09 19:18:23 CST 2016

Total time taken to generate the page: 0.09539 seconds