Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: bind variable

Re: bind variable

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 05 Aug 2004 22:34:41 -0700
Message-ID: <1091770518.405440@yasure>


steve wrote:
> | No version
>
> oracle 9i release 9.0.1.0.0 - production
>
> | No platform
>
> jserver release 9.0.1.0.0 - production
>
> | No operating system
>
> red hat enterprise
>
> | No code
>
> "update foo set bar = bar + 1 where col1 = 'a' and col2 = 'b' returning bar
> into :foo_bar"
>
> (select w/ same criteria returns only one record)
>
> | No error message
>
> sp2-0552: bind variable "foo_bar" not declared.

Looks to me like you didn't declare the variable just as it said.

Try this instead:

SQL> variable foo_bar VARCHAR2(50);

UPDATE foo
SET bar = bar+1
WHERE col1 = 'a'
AND col2 = 'b'
RETURNING bar INTO :foo_bar;

PRINT foo_bar

Or my demo which I think more realistic:

CREATE TABLE t AS
SELECT *
FROM all_tables;

set serveroutput on

DECLARE r urowid;

BEGIN
   DELETE FROM t
   WHERE rownum = 1
   RETURNING rowid INTO r;

   dbms_output.put_line(r);
END;
/

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Aug 06 2004 - 00:34:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US