RE: [sqr-users] using a user defined pl/sql function

["Kevin Laroche" <bydesign@magma.ca>]

I think you are trying to use a user defined function which is located in
the oracle database?

This can be done this way.  It usually helps to initialize the variables first 
before
//filling them
//in an SQR REPORT

BEGIN-SQL ON-ERROR=get_error
BEGIN

CMS.RETURN_DATES($Vstart, $vend);;

END;; 
END-SQL

//Oracle database user function  v_start and v_end are initialized elsewhere in 
the 
//database but the idea is the same.  Notice the use of OUT parameters

PROCEDURE RETURN_DATES(vv_start OUT DATE, vv_end OUT DATE) IS
--return the dates set up in the BUILD_DATES procedure--

BEGIN
v_error := v_error||' RETURN_DATES';
IF v_start IS NOT NULL AND v_end IS NOT NULL THEN
  vv_start := v_start;
   vv_end  := v_end;
ELSE
   RAISE NO_DATA_FOUND;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
vv_start := sysdate;
vv_end   := sysdate;
WHEN OTHERS THEN
v_error :=' RETURN_DATES';
RECORD_ERRORS;
END RETURN_DATES;
///
Kevin LaRoche
Ottawa,Ontario

On Nov 24, <R.Kaushik@iflexsolutions.com> wrote:
> 
> But how will I get a variable out when I use it in begin-sql procedure?
> The user defined function was primarily designed to give a output value
> 
> -----Original Message-----
> From: the dragon [mailto:ceprn@hotmail.com] 
> Sent: Friday, November 24, 2006 6:14 PM
> To: sqr-users@sqrug.org
> Cc: R Kaushik-IC
> Subject: RE: [sqr-users] using a user defined pl/sql function
> 
> slap it in a begin-sql procedure and see what happens....
> 
> peace,
> clark 'the dragon' willis
> 
> 
> 
> PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
> services for 40 hours a week, not purchasing your soul. Your time is the 
> only real finite asset that you have, and once used it can never be 
> recovered, so don't waste it by giving it away.
> 
> I work to live; I don't live to work.
> 
> "Time is the coin of your life. It is the only coin you have, and only you 
> can determine how it will be spent. Be careful lest you let other people 
> spend it for you."
> 
> Carl Sandburg
> (1878 - 1967)
> 
> It is impossible to defeat an ignorant man in argument.
> 
> William G. McAdoo
> 
> ----Original Message Follows----
> 
> Hi all,
> 
>          I am trying to use a user defined function to return a value
> 
> Select ud_getparent('PROJECT','DIVISION') from dual
> 
> Returns the division for the project.
> 
> This works fine as a adhoc sql.
> 
> Begin-select
> 
> ...
> 
> .
> 
> ...
> 
> ...
> 
> ...
> 
> ....
> 
> ud_getparent(&PROJECT,'DIVISION') &DIVISION
> 
>              show &DIVISION
> 
> from
> 
> ...
> 
> ...
> 
> Where
> 
> ..
> 
> ...
> 
> ..
> 
> End-procedure
> 
> This doesn't work.doesnt give any error. Returns a null though a return
> value is available.
> 
> 
> Any help ?
> 
> Database: Oracle 9i2
> 
> SQR: SQR for Peoplesoft.
> 
> Thanks & Regards.
> 
> Kaushik
> 
> _________________________________________________________________
> All-in-one security and maintenance for your PC.  Get a free 90-day trial! 
> <a href='http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?href=<a 
href='http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?href=<a 
href='http://www.windowsonecare.com/?sc_cid=msn_hotmail'>http://www.windowsonecare.com/?
sc_cid=msn_hotmail</a>'>http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?
href=<a href='http://www.windowsonecare.com/?
sc_cid=msn_hotmail'>http://www.windowsonecare.com/?
sc_cid=msn_hotmail</a></a>'>http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?
href=<a 
href='http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?href=<a 
href='http://www.windowsonecare.com/?sc_cid=msn_hotmail'>http://www.windowsonecare.com/?
sc_cid=msn_hotmail</a>'>http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?
href=<a href='http://www.windowsonecare.com/?
sc_cid=msn_hotmail'>http://www.windowsonecare.com/?sc_cid=msn_hotmail</a></a></a>
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> <a href='http://www.sqrug.org/mailman/listinfo/sqr-
users'>http://www.sqrug.org/mailman/listinfo/sqr-users</a>
> 
> 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users