Re: how can i exeucte stored function in sqlplus program.

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 22 Jul 2002 14:57:39 -0700
Message-ID: <92eeeff0.0207221357.556e6b11@posting.google.com>

> > I made PL/SQL code like below and stored oracle.
> >
> > $ cat func.sql
> > create function emp_insert ( my_empno in emp.empno%type, my_ename in
> > emp.ename%type ) return boolean as
> > begin
> > insert into emp( empno, ename ) values( my_empno, my_ename );
> > return true;
> > exception
> > when others then
> > return false;
> > end;
> > /
> >
> >
> > --------------------------------------------------------------------------

>  --

> > ----
> >
> >
> > When I executed this function in sqlplus, I showed error like below.. how
> > can i execute stored function in sqlplus.
> >
> >
> > --------------------------------------------------------------------------

>  --

> > ----
> >
> >
> > SQL> call emp_insert ( 200, 'you' );
> > call emp_insert ( 200, 'you' )
> > *
> > ERROR at line 1:
> > ORA-06576: not a valid function or procedure name
> >
> > SQL> execute emp_insert( 100, 'you' );
> > BEGIN emp_insert( 100, 'you' ); END;
> >
> > *
> > ERROR at line 1:
> > ORA-06550: line 1, column 7:
> > PLS-00221: 'EMP_INSERT' is not a procedure or is undefined
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> >
> >
> > SQL> emp_insert( 100, 'you' );
> > SP2-0734: unknown command beginning "emp_insert..." - rest of line

>  ignored.

> > SQL> call emp_inert( 100, 'you' );
> > call emp_inert( 100, 'you' )
> > *
> > ERROR at line 1:
> > ORA-06576: not a valid function or procedure name
> >

You can call function in a sql statement as,

select emp_insert( 200, 'you' ) from dual;

*BUT* in your case it will not work as you are returning a BOOLEAN. If you change return type to NUMBER/VARCHAR2 and return 1/'TRUE' and 0/'FALSE' then you can use the above statement.

The other way would be to leave it the way it is and use PLSQL block,

SET SERVEROUT ON
DECLARE
   return_ BOOLEAN;
BEGIN
   return_ := emp_insert ( 200, 'you' );    IF (return_) THEN

      DBMS_OUTPUT.PUT_LINE('TRUE');
   ELSE
      DBMS_OUTPUT.PUT_LINE('FALSE');
   END IF;
END;
/ Received on Mon Jul 22 2002 - 16:57:39 CDT