Re: PL/SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/10
Message-ID: <34b97e43.35542757_at_inet16>#1/1

Right, you are executing an anonymous PL/SQL block (but not a SQL statement -- a PL/SQL statement).

You cannot call pr_get_state_text_name from a SELECT statement for example because it is a procedure, not a function. You might be able to create a function:

create function f_get_state_text_name( p_state in varchar2 ) is

   l_return_string varchar2(255);
   l_rc number;
begin

   pr_get_state_text_name( p_state, l_return_string, l_rc );    if ( l_rc <> 0 ) then

      raise_application_error( -20001, 'Rc = ' || l_rc );    end if;
   return l_return_string;
end;

and then,

SQL> select f_get_state_text_name( 'TX' ) C1 from dual;

C1

---

Texas

On Sat, 10 Jan 1998 04:22:13 GMT, theslays_at_mindspring.com (Alan Slay) wrote:

>I read the replies that said you can only call a function. If so,
>please explain to me what is happening internally when I do something
>like the following many times a day from the SQL*PLUS command line (U
>actually use internal procedures to handle the dbms_output stuff):
>
>set serveroutput on;
>var v_rc number;
>var v_return_string vchar2(25);
>
>begin
> pr_get_state_text_name('TX', :v_return_string, :v_rc);
>end;
>
>print v_rc;
>
>'Texas'
>
>
>Amoda Kulkarni <amodk_at_bellsouth.net> wrote:
>
>>I want to know how to call a PL/SQL stored procedure from a SQL
>>statement.
>>
>>Thanks in advance
>>Amod Kulkarni
>>email: amodk_at_juno.com
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 10 1998 - 00:00:00 CET