Home » SQL & PL/SQL » SQL & PL/SQL » How to execute The Procedure Inside the Function? (Oracle 10 g)
How to execute The Procedure Inside the Function? [message #311805] Sun, 06 April 2008 22:50 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
How to Excute The Procedure from function ?

I have tried with Excute immediate option Inside function It
says Invalid Sql Stamt?


Can you help?
Re: How to execute The Procedure Inside the Function? [message #311806 is a reply to message #311805] Sun, 06 April 2008 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>says Invalid Sql Stamt?
The solution is to use only VALID SQL

>Can you help?
You post no code so how or why do you expect any help.

Follow posting guideline as stated in the Gold/Yellow Sticky post at top of this forum.
Re: How to execute The Procedure Inside the Function? [message #311807 is a reply to message #311806] Sun, 06 April 2008 22:58 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
create or replace function report_profile_latest_fn
return varchar2 is
l_instance int;
c1 varchar2(2000);
begin
c3 := 'exec report_profile_latest';
execute immediate(c3);
end;

SQL> /

Function created

SQL> select REPORT_PROFILE_LATEST_FN1 from dual;

select REPORT_PROFILE_LATEST_FN1 from dual

ORA-00900: invalid SQL statement
Re: How to execute The Procedure Inside the Function? [message #311809 is a reply to message #311805] Sun, 06 April 2008 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Follow posting guideline as stated in the URL above

Use valid SQL within PL/SQL & you won't get errors.

Do you know the difference between SQL & PL/SQL?

While a rose by any other name may smell as sweet,
objects with different spellings are different objects when it comes to Oracle.

report_profile_latest_fn <> report_profile_latest <> REPORT_PROFILE_LATEST_FN1


So which exactly is the correct name?

You should post your code, the whole code & nothing but the code;
while using <code tags> is specified in Posting Guidelines.

[Updated on: Sun, 06 April 2008 23:28] by Moderator

Report message to a moderator

Re: How to execute The Procedure Inside the Function? [message #311884 is a reply to message #311807] Mon, 07 April 2008 04:39 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
There are ceratin restrictions on functions that can be called from select statement.Search for those restrictions.

Why are you using execute immediate in it??

procedure can be called directly without use of dynamic sql.

post your code for report_profile_latest procedure with table structures that are used by the procedure.

Cheers,
Rajat Ratewal
Previous Topic: hi I WANT 2 SEND MAIL WITH ATTACHMENTS USING PL/SQL
Next Topic: Using ANSI joins
Goto Forum:
  


Current Time: Wed Dec 07 20:49:41 CST 2016

Total time taken to generate the page: 0.13061 seconds