REPOST: 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: <5$--$%%%_$$%%%$-$$@news.noc.cabal.int>

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
>

• WAS CANCELLED BY =======: From: "Tom Dyess" <tdyess_at_dyessindustries.com> Control: cancel <5Yf48.121543$_w.16862399_at_typhoon.tampabay.rr.com> Subject: cmsg cancel <5Yf48.121543$_w.16862399_at_typhoon.tampabay.rr.com> Date: Mon, 28 Jan 2002 02:29:17 GMT Message-ID: <cancel.5Yf48.121543$_w.16862399_at_typhoon.tampabay.rr.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!fu-berlin.de!news.mailgate.org!news2.euro.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716325

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 10:44:49 CST