| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Allocate memory for IN OUT varchar2 parameter in PL/SQL
A copy of this was sent to fchen_at_aisystem.com
(if that email address didn't require changing)
On Wed, 14 Apr 1999 20:31:32 GMT, you wrote:
>Hi,
>
>Does anyone know how to allocate the memeory for a IN OUT varchar2 parameter
>on fly?
>
>Here is my PL/SQL procedure:
>
>CREATE OR REPLACE PROCEDURE pr_get_something (io_paramer1 IN OUT VARCHAR2)
>IS
> v_var1 VARCHAR2(9);
>BEGIN
> v_var1 := '123456789';
> io_paramer1 := v_var1;
>END pr_get_something;
>
>From calling program, I passed in a string "adb" as io_paramer1 to
>pr_get_something()
>
>
>The problem is: ORA-6502: numeric or value error.
>
>I know the problem is that io_parameter has 3 characters length space, and it
>cannot accept 9 characters length value. I do not want the calling routine to
>handle the allocation.
>
If the calling routine only sends a varchar2(3), you will not be able to put 9 characters into it. You could use a function instead --
create function pr_get_something( io_paramer1 in varchar2 ) return varchar2
as
v_var1 varchar2(9);
begin
v_var1 := '123456789';
return v_var1;
end;
The functions return value can be of any size (but if you assign it to a varchar2(3) it'll fail again of course)
>
>Thanks for any help,
>
>Fei
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |