| 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: INSERT in PL/SQL
A copy of this was sent to "Anton Eskov" <anton_at_msoft.ru>
(if that email address didn't require changing)
On Wed, 6 May 1998 10:56:07 +0400, you wrote:
>Hi
>
>I have discovered the following behaviour in PL/SQL. Is it possible to
>explain it?
>
Yes it is.
Function Y is visible ONLY to the package, only routines in the package can call it. You are trying to call Y from the SQL statement -- Consider all INSERT/UPDATE/DELETE/SELECT etc type statements to be procedure calls outside of your package. It is as if INSERT was a procedure you were calling. when you called it, Y went out of scope -- it isn't visible to INSERT. That also explains why when you moved it into the SPEC, you still needed to call test.y(1), not just y(1). When you called INSERT, you were no longer in the test package but some other scope and had to qualify the reference to y with test.
>Let's consider the following package:
>
>create or replace package test as
> procedure x;
>end test;
>/
>
>create or replace package body test as
> function y(param1 in number) return number;
> procedure x is
> begin
>
>-- T is a table with one and only numeric field.
>-- Try to insert a new row into t. Value to be inserted should be
>returned by function y.
>
> INSERT INTO T VALUES( y(1) );
> end x;
>
> function y(param1 in number) return number is
> begin
> return (param1 * 2);
> end y;
>end test;
>/
>
>The package compiles without any problem, but when a call to procedure x is
>made,ORA-904 "invalid column name" error is generated in INSERT statement.
>
>It starts to work when function y is declared in package specification (with
>appropriate pragma) and package qualifier is used in the call in the package
>body (i.e. the INSERT statement looks like following:
>
>INSERT INTO T VALUES ( test.y(1) );
>
>SY
>Anton
>
>P.S. Configuration:
>Oracle7 Server Release 7.3.3.0.0 - Production Release
>PL/SQL Release 2.3.3.0.0 - Production
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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 Wed May 06 1998 - 09:39:48 CDT
 |
 |