Re: Oracle Stored Proc problem

From: <fitzjarrell_at_cox.net>
Date: 6 Sep 2006 12:21:26 -0700
Message-ID: <1157570486.711593.229850@i3g2000cwc.googlegroups.com>

KK wrote:
> Is there any way to declare a variable in stored procedure that should
> hold --- length of text which is not known at design time.
>
> For ex:
>
> PROCEDURE MUpdate(
> inFKey in varchar,
> inSubFKey in varchar
> )
> IS
> vCount NUMBER NOT NULL DEFAULT 0;
> vDupTxt ---
> BEGIN
> vDupTxt := "---"
>
> END MUpdate;
>
> In the above example If I use vDupTxt VARCHAR(---), I have to specify
> length (1.. 32767)
>
> While executing the stored procedure at runtime, some times I may want
> to assign longer text.
>
> Appreciate your help!
>
> Thanks

SQL> create or replace procedure deftest(inLen in number default 4000)   2 is
  3 vTxt varchar2(inLen);
  4 begin

  5     dbms_output.put_line('Length is: '||inLen);
  6     vTxt := rpad(vTxt, 'X', inLen);
  7     dbms_output.put_line(vTxt);

  8 end;
  9 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE DEFTEST:

LINE/COL ERROR

---

---

3/18 PLS-00491: numeric literal required SQL> 32767 is as long as it gets with PL/SQL variables. It appears you'll simply need to declare the maximum length for the variable as 'dynamic' sizing won't work.

David Fitzjarrell Received on Wed Sep 06 2006 - 14:21:26 CDT