Re: PL/SQL help needed (newbie)

From: Mark Gumbs <mgumbs_at_nospam.hotmail.com>
Date: Wed, 26 May 1999 13:26:28 +0100
Message-ID: <374be63f.0@145.227.194.253>

First of all, the declaration of varchar(2) in the procedure should return quite a 'wide' value so that shouldn't be a problem.

Ok, time to get heavy! I'l put in my code Thomas Kyte style! I tried...

SQL> select to_char(233.3,'9,999') from dual;

TO_CHA

---

   233

Then...

SQL> select to_char(233) from dual;

TO_
---
233

Note that the result of the first has leading spaces.

Altering your original procedure...

   create or replace procedure myproc(amt OUT varchar2) AS

   BEGIN
         select ltrim(to_char(233.3,'9,999')) into amt from dual;    END myproc;
   /

Then testing

SQL> declare
  2 v_amt varchar2(5);
  3 begin

  4       myproc(v_amt);
  5       dbms_output.put_line(v_amt);

  6 end;
  7 /
233

PL/SQL procedure successfully completed.

This seems to work!

Hope this helps.

Mark.

---

Paul Wiles wrote in message <374bdf93_at_newsread3.dircon.co.uk>...
>I'm calling it from a DBI perl routine:
>
>my %sth = $dbh->prepare(q{
> BEGIN
> myproc(totalamt => :totalamt);
> END;
> });
>
>$sth->bind_param_inout(":totalamt",\$totalamt,10);
>
>The problem occurs within the proc when it sets the OUT variable to a
>character string with a length greater than 1. Of couse, I can't declare
>the OUT variable to be a varchar2(n), only a varchar2.
>
>So how does the OUT variable accept a value with more than 1 character.
I'm
>going round in circles and am very confused
>
>Help appreciated
Received on Wed May 26 1999 - 07:26:28 CDT