OraFAQ Forum: SQL & PL/SQL » returning text at sql prompt from a procedure

Home » SQL & PL/SQL » SQL & PL/SQL » returning text at sql prompt from a procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

returning text at sql prompt from a procedure [message #7612]

Wed, 25 June 2003 00:48

Suman Chattopadhyay
Messages: 9
Registered: June 2003

Junior Member

I have one procedure which returns data(e.g a line of text) of varchar2 data type through an out parameter.
I have declared a variable at the sql prompt as follows-

var ret varchar2

and calling the procedure( suppose "myproc") as follows-

exec myproc(:ret)

it is giving numeric or value error. I think it is because a line of text is being returned.Can any body help me to perform the same thing?

Thanks in advance

Suman

Report message to a moderator

Re: returning text at sql prompt from a procedure [message #7613 is a reply to message #7612]

Wed, 25 June 2003 01:38

jigar
Messages: 74
Registered: July 2002

Member

May be following code can help you,
create or replace procedure outdata (a out varchar2)
is
begin
a:='Hello';
end;

--------------

declare
a varchar2(10);
begin
outdata (a);
dbms_output.put_line(a);
end;
/

Report message to a moderator

Re: returning text at sql prompt from a procedure [message #7633 is a reply to message #7612]

Wed, 25 June 2003 18:42

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You need to give your ret variable a size and make it big enough to hold the output. See the test below, that shows your error, when no size is given to ret, and executes without error, when a sufficient size is given to ret.

SQL> CREATE OR REPLACE PROCEDURE myproc
  2    (p_var2 IN OUT VARCHAR2)
  3  AS
  4  BEGIN
  5    p_var2 := 'test';
  6  END myproc;
  7  /

Procedure created.

SQL> SHOW ERRORS
No errors.
SQL> --
SQL> -- produces error, due to no size or insufficient size:
SQL> VARIABLE ret VARCHAR2
SQL> EXEC myproc (:ret)
BEGIN myproc (:ret); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "EICAP.MYPROC", line 5
ORA-06512: at line 1

SQL> PRINT g_var2

G_VAR2
--------------------------------
test

SQL> --
SQL> -- executes without error, with sufficient size
SQL> VARIABLE ret VARCHAR2 (10)
SQL> EXEC myproc (:ret)

PL/SQL procedure successfully completed.

SQL> PRINT g_var2

G_VAR2
--------------------------------
test

Report message to a moderator

Previous Topic:	Question on using Data Dictionary Views:
Next Topic:	System functions for checking permissions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 01:07:55 CDT 2024