Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Unanswered: Decimal problem in select Query

    Hi folks,

    When i select a value from oracle table into a variable in SP the decimal is getting truncated.
    I tried allocating and initializing the vriable with decimal no use..

    I am using select into statement .........

    Thanx.............
    Sree

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Not enough information

    Please give more information so that we can provide meaningful ideas.

    Give us sample data from the table, relevant segments of code and example of how the SP (stored procedure?) messes up the data. Then somebody might be able to help.

    In general, when using variables that refer to table columns, if you declare them as table.column%TYPE, you should not see datatype mismatch or truncation problems.

    Ravi

  3. #3
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Red face Decimal Problem

    Hi Ravi,

    My proble is as i said in Stored Procedure.

    I have variable

    Q1 Number;

    My query is..
    Select StockQty into Q1 from StoneStock where ParcelId=MvParcelId;

    The actual value in table is 11.5
    and i get 11 in the variable;

    I tried declaring the variable as

    Q1 Number(13,3);

    Thanx
    Last edited by sreeraj; 06-02-04 at 01:48. Reason: Mistake in code
    Sree

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, but how did you find out that Q1 = 11 instead of 11.5? What does DBMS_OUTPUT.PUT_LINE(Q1) and Q1 * 2 (if the column value = 11.5, then 11.5 * 2 = 23, right? If Q1 = 11, then the result will be 22) say? Did you perhaps set numformat 999 or something like that so that output fools you?

  5. #5
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Decimalssssssssssssss

    Hi ,

    In fact i came to know the value is 11.5 through sql query executed outside the Stored Procedure.

    Only in Sp it is giving me the problem.

    Thanx
    Sree

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    I don't see a problem

    I did the following:

    HTML Code:
     CREATE TABLE stock (
       stock_id   NUMBER(4) Not Null,
       stock_Qty  NUMBER(12,3) Not Null,
       Parcel_Id  NUMBER(4) Not Null
    );
    INSERT INTO stock VALUES (1, 11.5, 1);
    CREATE OR REPLACE PROCEDURE p1 AS
       v_stock_qty   stock.stock_qty%TYPE;
    BEGIN
       SELECT stock_qty 
       INTO   v_stock_qty
       FROM   stock
       WHERE  parcel_id = 1;
       
       DBMS_OUTPUT.PUT_LINE('The stock quantity is ' ||v_stock_qty);
    END p1;
    /
    set serveroutput on size 100000
    exec p1;
    And this is the output from the SQL

    HTML Code:
    SQL> exec p1;
    The stock quantity is 11.5
    I have no idea what you are doing, but please check your work. You should not be having this problem.

    Ravi

  7. #7
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Decimal

    Hi Ravi,

    That was exaclty what i was doing ,only difference is that i dint use %type for declaring the variable .

    May be that was the reason..

    Thanx now it is working......


    Regards
    Sree

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •