Re: how to use bind value in date type?

From: Tom Dyess <tdyess_at_dyessindustries.com>
Date: Fri, 25 Jan 2002 16:44:49 GMT
Message-ID: <5Yf48.121543$_w.16862399@typhoon.tampabay.rr.com>

Here is an example - you don't need the preceeding colon unless you are using something like Delphi to call the database with parameters. This is using the SCOTT database as an example. DMBS_OUTPUT.PUTLINE(y) prints the variable y to the server output, thus you need set serveroutput on for this to display.

Tom
www.oraclepower.com

SQL> set serveroutput on
SQL> declare

  2     x number;
  3     y varchar2(50);

  4 begin
  5 x := 7902;
  6 select ename into y from emp where empno = x;   7 dbms_output.put_line(y);
  8 end;
  9 /
FORD PL/SQL procedure successfully completed.

SQL> "Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1011974823.1024353027@[64.94.198.252]...
> i am learning how to use bind value. I have a test table with with
> only two rows
> Name Null? Type
> ------ -------- ------
> COL1 NUMBER
> COL2 DATE
>
> SQL> variable x number
> SQL> begin
> 2 :x:=1;
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
> SQL> select * from test where col1 = :x;
>
> COL1 COL2
> ---------- ---------
> 1 24-JAN-02
>
> 1 row selected.
>
> But I do not know how to use bind value with date type
>
> SQL> variable x date
> Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n) | VARCHAR2(n) |
> NCHAR | NCHAR (n) | NVARCHAR2 (n) |
> CLOB | NCLOB | REFCURSOR ] ]
>
> SQL> variable x varchar2(60);
> SQL> begin
> 2 :x:=to_date('2002/01/24 23:59:59','yyyy/mm/dd hh24:mi:ss');
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select * from test where col2=:x;
> no rows selected
>
> but there are two rows in the table
> SQL> select col1,to_char(col2,'yyyy/mm/dd hh24:mi:ss') from test;
>
> COL1 TO_CHAR(COL2,'YYYY/
> ---------- -------------------
> 1 2002/01/24 23:59:59
> 2 2002/01/25 23:59:59
>
> 2 rows selected.
>
> What i did was wrong?
>
> Thanks for you help
>
>
>
>
>
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
>
Received on Fri Jan 25 2002 - 10:44:49 CST