Actually, because Jos� mentioned he was doing an insert in his function, it cannot be called from SQL, but only from an anonymous PL/SQL block. In any instance, it is *always* faster to execute a PL/SQL function from an anonymous PL/SQL block than from a SELECT.
In terms of the throughput you are seeing (8000 rows in 7 minutes), that does seem slow, but it could be one of many things. Are there any triggers on the table(s) you are inserting into ? Have you tried tracing the session, and running the trace file through tkprof ? If you're not sure how to do this, in your Perl code, you need to execute 'alter session set sql_trace = true'. You then run your script, and ask your DBA where trace files end up. It will be wherever the Oracle parameter USER_DUMP_DEST is pointing to. Note that this is on the database server machine which may not be the same as your client machine. Once you have located your trace file, you use tkprof to format it into something useful - eg 'tkprof tracefile outfile'. If you just type 'tkprof<return>' on the command line, you will see all the options. Hope this helps, Steve -----Original Message----- From: Michael A Chase [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 19 November 2002 4:00 AM To: Jeff Seger Cc: [EMAIL PROTECTED] Subject: Re: Execute an Oracle Function (not a Procedure) On 18 Nov 2002 11:31:56 -0500 Jeff Seger <[EMAIL PROTECTED]> wrote: > Maybe I am missing something herre (It is Monday after all) but > shouldn't a function be called in a select statement: > > "select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual" > > or something similar? > > On Mon, 2002-11-18 at 10:43, Michael A Chase wrote: >> You are not providing anything to receive the value returned by the >> function. You will need either a PL/SQL variable that will be >> discarded or a bind variable as shown in the examples in >> http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and >> http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . Look at the function examples in the URIs I cited. PL/SQL functions can often be used in a SELECT, but not always. Unless you are getting arguments for the function from a table, there is no advantage to using a SELECT instead of an anonymous block and there may be some additional overhead. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
