Home » SQL & PL/SQL » SQL & PL/SQL » check the pl/sql function (multiple merges)
check the pl/sql function (multiple merges) [message #308443] Mon, 24 March 2008 05:56 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

can any one pls check the following code and modify if any changes needed.
create or replace FuNCTION TIER_WH (message IN OUT ORDERDESC)
 return boolean
IS 
CURSOR chk_loc IS
SELECT store FROM loc
  WHERE virtual_wh=message.OrderDt(i);
  rec chk_loc%type;
BEGIN
  OPEN chk_loc;
  FETCH chk_loc INTO rec;
   if chk_loc%FOUND then
   message.XOrderDt(i):=rec;
   end if;
  CLOSE chk_loc;
 return true;
 end  TIER_WH;

Here I am getting the following errors
1.identifier "i" must be declared.

if i define I number(10) then am getting the error as
"the declaration of the type of this expression is incomplete or malformed".

Please check my code and give me idea about modifications.can u pls look into this

Thank you

[Updated on: Mon, 24 March 2008 05:58]

Report message to a moderator

Re: check the pl/sql function [message #308454 is a reply to message #308443] Mon, 24 March 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Use SQL*Plus and copy and paste your session
2/ Don't use IM/SMS speak
3/ You (syntaxically) understand the error, now what is I?
4/ You don,'t post your second code, how could we know what you made wrong?

Regards
Michel
executing the function [message #308479 is a reply to message #308443] Mon, 24 March 2008 08:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
I have created one function as
function funs(desc in out XORDERS)
...
...
end;

where XORDERS is craeted as
create or replace type XORDERS is
(customer  number(10),
order number(10),
loc number(10)
);

Now please tell me how to execute the function. I need to execute the function for "loc".

Thank you.

[Updated on: Mon, 24 March 2008 08:32]

Report message to a moderator

Re: executing the function [message #308480 is a reply to message #308479] Mon, 24 March 2008 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The same way as you invoke the USER,SYSDATE or any other function.
Re: executing the function [message #308482 is a reply to message #308480] Mon, 24 March 2008 08:49 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

select package_name.function_name(....?)

Here I am getting the doubt. How to give this syntax.Please let me know.
Re: executing the function [message #308486 is a reply to message #308482] Mon, 24 March 2008 09:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What does your function return?
Re: executing the function [message #308547 is a reply to message #308479] Mon, 24 March 2008 15:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It is not possible to execute a function with OUT parameters form sql; you will get this error:

ORA-06572: Function F1 has out arguments

Kevin
Re: check the pl/sql function [message #308548 is a reply to message #308443] Mon, 24 March 2008 15:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
all the Michel said.

Your problem is i. Look in your code. You see there is a index called i being used in two places. Where is this variable declared?

Hence why you get the error

i must be declared.


Your second error is likely because of one of these two things:

1) you forgot a semi-colon in you declaration of i
2) one of your column names/table names is spelled wrong

Kevin
Re: check the pl/sql function [message #308553 is a reply to message #308548] Mon, 24 March 2008 17:07 Go to previous messageGo to next message
usmanqamar
Messages: 6
Registered: January 2007
Junior Member
Hi,
Well, there is no logic for the variable 'i', its possible that you were trying to use collections. Also its recommended not use OUT parameter in function, as function always returns value.
Regards
Usman

[Updated on: Tue, 25 March 2008 00:58] by Moderator

Report message to a moderator

Re: executing the function [message #308554 is a reply to message #308547] Mon, 24 March 2008 17:19 Go to previous messageGo to next message
usmanqamar
Messages: 6
Registered: January 2007
Junior Member
Hi,
Function always returns a value so its always recommended not use OUT parameter. There are ways to use OUT parameter but in special circumstance.... for a moment you better forget that OUT parameter is used from fuction.

e.g
select my_function_name(value) from dual;
or if i follow your code
select funs(10) from dual;

Regards
Usman

[Updated on: Tue, 25 March 2008 00:59] by Moderator

Report message to a moderator

Re: executing the function [message #308557 is a reply to message #308479] Mon, 24 March 2008 20:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to avoid using Oracle reserved words like ORDER and DESC for column names. You can execute your function within a pl/sql block. However, you need to provide more complete explanation as to what you are trying to achieve, as "execute the function for loc" is meaningless. You should provide your business requirements as such a type and function with an out parameter may not even be appropriate, although you can (... indicates missing information):

create or replace type xorders is ...
  (customer  number (10),
   orders    number (10),
   loc       number (10));
/
show errors
create or replace function funs
  (descrip in out XORDERS)
  ...
end funs;
/
show errors
declare
  out_parameter  xorders;
  return_value   ...;
begin
  out_parameter := xorders (..., ..., ...);
  return_value := funs (out_parameter);
end;
/



executing the function [message #308586 is a reply to message #308557] Tue, 25 March 2008 00:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created function as follows
create or replace FuNCTION TIER_WH (message IN OUT ORDERDESC)
return boolean
IS 
CURSOR chk_loc(v_location Number) IS
SELECT store FROM loc
WHERE virtual_wh=v_location;
rec chk_loc%type;
BEGIN
For i in 1..message.count
Loop
OPEN chk_loc(message.OrderDt(i).location);
FETCH chk_loc INTO rec;
if chk_loc%FOUND then
message.XOrderDt(i).location:=rec;
end if;
CLOSE chk_loc;
End loop;
return true;
end TIER_WH;

but OERDERDESC is defined as
craete or repalce type ORDERDESC is
(Orders number(10),
XOrderDT  EXorderdt_mtl,
customer varchar2(20)
);

Now I want to execute the above function .Can you please give me idea to execute this function...

Thank you.

[Updated on: Tue, 25 March 2008 00:11]

Report message to a moderator

Re: executing the function [message #308592 is a reply to message #308586] Tue, 25 March 2008 00:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
YOU created this function; you decided it had to be a function returning a boolean; you decided the parameter had to be IN OUT.
Then what trouble do you have calling it?
If you don't know how to call a function in general, then you should start at the basics and leave the objects-stuff for a while!
Re: executing the function [message #308593 is a reply to message #308592] Tue, 25 March 2008 00:29 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Frank,
I am calling the function as follows
variable X number;
exec :x:=Function_name(.....( Here I don't know which parameter have to mention. That's the problem.);

I f am giving "meesage in paramer list" am getting error when passing the value.
can u pls look into this.

Thank u
Re: executing the function [message #308596 is a reply to message #308593] Tue, 25 March 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't call it in SQL*Plus which is unable to declare of value of YOUR type.

Regards
Michel
Re: executing the function [message #308597 is a reply to message #308593] Tue, 25 March 2008 00:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First do it the easy way: try to call it from an anonymous pl/sql block.
Re: executing the function [message #308625 is a reply to message #308597] Tue, 25 March 2008 01:42 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All i have craeted package "ORDERS".In this I have craeted two functions.
 FuNCTION TIER_WH (message IN OUT ORDERDESC)
return boolean
IS 
CURSOR chk_loc(v_location Number) IS
SELECT store FROM loc
WHERE virtual_wh=v_location;
rec chk_loc%type;
BEGIN
For i in 1..message.count
Loop
OPEN chk_loc(message.OrderDt(i).location);
FETCH chk_loc INTO rec;
if chk_loc%FOUND then
message.XOrderDt(i).location:=rec;
end if;
CLOSE chk_loc;
End loop;
return true;
end TIER_WH;

other function is
 FuNCTION Mesg (message IN OUT ORDERDESC,
                errors varchar2)
return boolean
IS 
BEGIN
if not valid_mesg(....)
...
For i in 1..message Loop
.....
end if;
End loop;
return true;
end Mesg;

NOw I want to call TIER_WH in MESG. How to write the TIER_WH function in MESG. Pls let me know.
note: My query here exceeds the forum limit.Pls excuse
Thank you.
Re: executing the function [message #308629 is a reply to message #308625] Tue, 25 March 2008 01:54 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I got it. am able call one function from othe rfunction..

Thank you very much for giving support and great suggestions.

Thank u
Re: executing the function [message #308647 is a reply to message #308629] Tue, 25 March 2008 02:26 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi
In the above mentioned function TIER_WH and MESG i have passed a parameter "Message IN OUT ORDERDESC".
But ORDERDESC is created as
create or replace type ORDERDESC
( customer number(10),
orderdtl  EXOrderdtl,
num      number(10)
Err      Varchar2(20)
);

How to initialize these columns in the Join of two functions(i.e I have called TIER1_WH in MESG Function).
Please let me know.

Thank you.
calling function [message #308700 is a reply to message #308443] Tue, 25 March 2008 04:16 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

hi ,
I have created one package.In this I have created one private function.
function messg (mesg in out xorders)
return boolean

I am calling this function in other function
function tier(err varchar2,
              mesg in out xorders,
              orders number,
              loc in number);
for i in mesg.Xdtl(i).first... mesg.Xdtl(i).last loop
 if not Messg(mesg.XOrders(i)) then
return false;

when I am executing this am getting the following error
.
PLS-00306: wrong number or types of arguments in call to 'Messg'

can u plese look into this and give me any suggestion.

Thank you

[Updated on: Tue, 25 March 2008 04:21]

Report message to a moderator

Re: calling function [message #308702 is a reply to message #308700] Tue, 25 March 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please don't start a new topic, continue on the previous one.

Regards
Michel
Re: calling function [message #308706 is a reply to message #308702] Tue, 25 March 2008 04:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

OK.Michel .
Please give me idea and where I have done mistake...and where I have to do changes.. Pls tell me.
Re: calling function [message #308707 is a reply to message #308706] Tue, 25 March 2008 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
messg (mesg in out xorders)

Quote:
function tier(... mesg in out xorders ...

Quote:
Messg(mesg.XOrders(i))

So parameter is of wrong type unless XOrders(i) is also "xorders".

Regards
Michel
Re: calling function [message #308710 is a reply to message #308707] Tue, 25 March 2008 04:43 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

 for i in message.XOrder.first .. message.XOrder.last loop
      if NOT TIER_WH(message.XOrder(i).location) then
         return FALSE;
      end if;
   end loop;


for this I have mentioned
Function tier_wh(message in out orderdesc)
return boolean

then am getting the error what i have mentioned above...

Thank you

[Updated on: Tue, 25 March 2008 04:44]

Report message to a moderator

Re: calling function [message #308719 is a reply to message #308710] Tue, 25 March 2008 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for this I have mentioned...

Wrong this is not the same thing.
Post the ACTUAL and FULL code as long as with the FULL session.

Regards
Michel
Re: calling function [message #308733 is a reply to message #308719] Tue, 25 March 2008 05:33 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

FUNCTION MESSAGE(error_message   IN OUT   ERRORS.TEXT%TYPE,
                 messg  IN OUT   ORDERDESC,
                 mesg_type    IN       VARCHAR2)
   RETURN BOOLEAN IS
    
L_program VARCHAR2(50) := 'XORDER.MESSAGE';--XORDER is a package
     
BEGIN
   
   if NOT ORDER_NO(error_message,
                   messg,
                   mesg_type) then
      return FALSE;
   end if;

-- here i want ot use TIER function
for i in ....
    if not TIER(  ....) then

Function TIER is defined as
function TIER( messg in out  XORDERDESC)
return boolean;

Here I want to use TIER function also. BUt i am getting error.
Michel pls give me idea..

Thank you.

[Updated on: Tue, 25 March 2008 05:37]

Report message to a moderator

Re: calling function [message #308744 is a reply to message #308733] Tue, 25 March 2008 05:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why did you choose to use types here?
Why not pl/sql recordtypes or even scalars?
You know too little about PL/SQL to grasp this.
Re: calling function [message #308747 is a reply to message #308733] Tue, 25 March 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post the ACTUAL and FULL code as long as with the FULL session

As long as you don't post what is requested you can't have help.

Regards
Michel
Run a function in package [message #309037 is a reply to message #308443] Wed, 26 March 2008 04:58 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ALL,
I have a function in a packagebody. Now I want to execute this function.Please let me know how to execute...
FUNCTION MESSAGE(error_message   IN OUT   TEXT%TYPE,
                 IO_message      IN OUT   XORDERDESC,
                 message_type    IN       VARCHAR2)
   RETURN BOOLEAN IS
   program        VARCHAR2(50) := 'VALIDATE.MESSAGE';
   
BEGIN
      
   if NOT ORDER_NO(         O_error_message,
                            IO_message.order_no,
                            message_type) then
      return FALSE;
   end if;
  
   if I_message_type in(XORDER.dtl_cre_type,
                        XORDER.dtl_mod_type,
                        XORDER.lP_cre_type ) then
      if NOT TIER_WH(IO_message,
                     error_message) then
         return FALSE;
      end if;
   end if;                      

   

EXCEPTION
   when OTHERS then
      error_message := LIBrary.CREATE_MSG('PACKAGE_ERROR',
                                            SQLERRM,
                                            program,
                                            to_char(SQLCODE));
      return FALSE;
END MESSAGE;
END pack_body;


Then how to execute this function...
please give me idea regarding this..

Thank you.
Re: Run a function in package [message #309049 is a reply to message #309037] Wed, 26 March 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't repost the same thing ABOVE ALL in a wrong forum.
Continue the previous topic.

Regards
Michel
Run a function in package [message #309061 is a reply to message #308443] Wed, 26 March 2008 06:09 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ALL,
I have a function in a packagebody. Now I want to execute this function.Please let me know how to execute...
FUNCTION MESSAGE(error_message   IN OUT   TEXT%TYPE,
                 IO_message      IN OUT   XORDERDESC,
                 message_type    IN       VARCHAR2)
   RETURN BOOLEAN IS
   program        VARCHAR2(50) := 'VALIDATE.MESSAGE';
   
BEGIN
      
   if NOT ORDER_NO(         O_error_message,
                            IO_message.order_no,
                            message_type) then
      return FALSE;
   end if;
  
   if I_message_type in(XORDER.dtl_cre_type,
                        XORDER.dtl_mod_type,
                        XORDER.lP_cre_type ) then
      if NOT TIER_WH(IO_message,
                     error_message) then
         return FALSE;
      end if;
   end if;                      

   

EXCEPTION
   when OTHERS then
      error_message := LIBrary.CREATE_MSG('PACKAGE_ERROR',
                                            SQLERRM,
                                            program,
                                            to_char(SQLCODE));
      return FALSE;
END MESSAGE;
END pack_body;


Then how to execute this function...
please give me idea regarding this..

Thank you.
Re: Run a function in package [message #309068 is a reply to message #309061] Wed, 26 March 2008 06:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Using PL/SQL Packages
Re: Run a function in package [message #309070 is a reply to message #309068] Wed, 26 March 2008 06:33 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thomas,
I want to write a anonymous block to execute this..

I wrote the code as follows

declare
ret_val varchar2(20);
v_error_message xorder%type;
v_message varchar2(20);
begin
...
...
ret_val:=validate.message(...,...,'my message');
...
...
end ;

still i am getting the error.
So pls tell me is there any other way to write Anonymous blockkk

Thank you
Re: Run a function in package [message #309071 is a reply to message #309061] Wed, 26 March 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't restart once more this one.

Regards
Michel
Re: Run a function in package [message #309072 is a reply to message #309070] Wed, 26 March 2008 06:35 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
- You are not posting the actual session
- You are not posting the actual error

You're on your own.
Previous Topic: To store PDF template in a oracle
Next Topic: Time from Date field
Goto Forum:
  


Current Time: Thu Dec 08 10:47:44 CST 2016

Total time taken to generate the page: 0.12824 seconds