Re: SELECT col INTO :sqlplus_bind_vars.

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 23 Dec 2003 04:10:56 -0800
Message-ID: <1a75df45.0312230410.42bdc0b0@posting.google.com>

farmer_at_netnet.net (Mark A Framness) wrote

> I am trying to something along the lines of
>
> variable a_bind_var DATE
>
> select sysdate
> into :a_bind_var
> from dual;
>
> Is this possible in SQL+? I see it is possible to use bind variables
> to hold returns from functions and procedures. This is not the only
> way I know of to do what I want to do, but if possible it is my first
> choice.

Yes and no.

No it is not as SQL*Plus does not support DATE as a bind variable data type.

SQL> help var

 VARIABLE

---

 Declares a bind variable that can be referenced in PL/SQL, or  lists the current display characteristics for a single variable  or all variables.

 VAR[IABLE] [variable [NUMBER | CHAR | CHAR (n [CHAR|BYTE]) | NCHAR

            | NCHAR (n) | VARCHAR2 (n [CHAR|BYTE]) | NVARCHAR2 (n)
            | CLOB | NCLOB | REFCURSOR]]

Yes. You can use bind variables in SQL*Plus and it is highly desirable you do. Use varchar2 instead in absense of date. E.g.

SQL> var d varchar2(20);
SQL> exec :d := TO_CHAR( sysdate, 'yyyy-mm-dd hh24:mi:ss');

PL/SQL procedure successfully completed.

SQL> print d

D

---

2003-12-23 14:09:37

--
Billy

Received on Tue Dec 23 2003 - 06:10:56 CST