OraFAQ Forum: SQL & PL/SQL » is there any way to convert number into integer?

Home » SQL & PL/SQL » SQL & PL/SQL » is there any way to convert number into integer? (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

is there any way to convert number into integer? [message #639025]

Tue, 30 June 2015 00:26

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi All,

Is there any way to convert number field into integer??

i tried below code but still it returing as number in my .Net application.

---code start

create or replace procedure p_integer(v1 out int)
as
iv_doc int;
begin
select 1 into iv_doc from dual;
v1:=iv_doc;
end;
/

---code end

Report message to a moderator

Re: is there any way to convert number into integer? [message #639027 is a reply to message #639025]

Tue, 30 June 2015 00:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://community.oracle.com/thread/3756826

Report message to a moderator

Re: is there any way to convert number into integer? [message #639028 is a reply to message #639025]

Tue, 30 June 2015 00:34

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SQL> SET serveroutput ON
SQL> DECLARE
  2    iv_doc INT;
  3  BEGIN
  4    SELECT 1.234 INTO iv_doc FROM dual;
  5    dbms_output.put_line(iv_doc);
  6  END;
  7  /
1

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: is there any way to convert number into integer? [message #639029 is a reply to message #639027]

Tue, 30 June 2015 00:39

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Thanks Blackswan,

But problem is i need to return integer to .Net application.But in My column has type number in table.
If i create column as Integer type ,,it working.
But in production i can't do this.
So could you please help me on this.

Table :

create table test(emp number);

create or replace procedure p_integer(v1 out int)
as
iv_doc int;
begin
select emp into iv_doc from test;
v1:=iv_doc;
end;
/

But when i check in .Net application it is showing as Number type.They need type as Integer only

Regards,
Rajesh

Report message to a moderator

Re: is there any way to convert number into integer? [message #639030 is a reply to message #639028]

Tue, 30 June 2015 00:39

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Thanks ,

But problem is i need to return integer to .Net application.But in My column has type number in table.
If i create column as Integer type ,,it working.
But in production i can't do this.
So could you please help me on this.

Table :

create table test(emp number);

create or replace procedure p_integer(v1 out int)
as
iv_doc int;
begin
select emp into iv_doc from test;
v1:=iv_doc;
end;
/

But when i check in .Net application it is showing as Number type.They need type as Integer only

Regards,
Rajesh

Report message to a moderator

Re: is there any way to convert number into integer? [message #639031 is a reply to message #639030]

Tue, 30 June 2015 00:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

mvrkr44 wrote on Mon, 29 June 2015 22:39

Thanks ,

But problem is i need to return integer to .Net application.But in My column has type number in table.
If i create column as Integer type ,,it working.
But in production i can't do this.
So could you please help me on this.

Table :

create table test(emp number);

create or replace procedure p_integer(v1 out int)
as
iv_doc int;
begin
select emp into iv_doc from test;
v1:=iv_doc;
end;
/

But when i check in .Net application it is showing as Number type.They need type as Integer only

Regards,
Rajesh

So how is this an Oracle problem?

Report message to a moderator

Re: is there any way to convert number into integer? [message #639032 is a reply to message #639030]

Tue, 30 June 2015 00:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mvrkr44 wrote on Tue, 30 June 2015 11:09

They need type as Integer only

Then "They" just need to declare the data type as INTEGER. When a number is passed to an integer, it would hold only the whole number.

Report message to a moderator

Re: is there any way to convert number into integer? [message #639033 is a reply to message #639032]

Tue, 30 June 2015 00:47

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Thanks Lalit,

So column type need to change as INTeger in database (or) .net application need to handle this one?

Regards,
Rajesh

Report message to a moderator

Re: is there any way to convert number into integer? [message #639034 is a reply to message #639033]

Tue, 30 June 2015 00:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mvrkr44 wrote on Tue, 30 June 2015 11:17

Thanks Lalit,

So column type need to change as INTeger in database (or) .net application need to handle this one?

There is nothing to do at database side, your application should handle it. Pass the number from database to the client as it is, in the client side hold it as INT.

Report message to a moderator

Re: is there any way to convert number into integer? [message #639037 is a reply to message #639025]

Tue, 30 June 2015 01:36

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you want to be sure your query returns a specific data type then use CAST:

SQL> select cast(1.2345 as integer) v from dual;
         V
----------
         1

Report message to a moderator

Re: is there any way to convert number into integer? [message #639038 is a reply to message #639037]

Tue, 30 June 2015 01:41

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Michel,

Thanks.

I tried the same solution..But while returning from procedure its giving as Number type only..

Regards,
Rajesh

Report message to a moderator

Re: is there any way to convert number into integer? [message #639039 is a reply to message #639025]

Tue, 30 June 2015 01:42

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

In the Oracle daabase, an integer is not a data type, it is just a number with no decimals:

orclz>
orclz> create table t1(c1 int);

Table created.

orclz> desc t1
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- -----------------
 C1                                                                   NUMBER(38)

orclz>

you'll have to do any conversion in your client code.

Report message to a moderator

Re: is there any way to convert number into integer? [message #639040 is a reply to message #639038]

Tue, 30 June 2015 01:47

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oracle has only one number data type: NUMBER, all other ones are derived from this one:

  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)
  subtype "DOUBLE PRECISION" is FLOAT;
  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;
  subtype SMALLINT is NUMBER(38,0);
  subtype DECIMAL is NUMBER(38,0);
  subtype NUMERIC is DECIMAL;
  subtype DEC is DECIMAL;

Report message to a moderator

Re: is there any way to convert number into integer? [message #639042 is a reply to message #639039]

Tue, 30 June 2015 01:56

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

i created same,

SQL>create table t1(c1 int);

sql>desc t1;
Name Type Nullable Default comments
---- ----- -------- ------ ------
c1 INTEGER Y

Report message to a moderator

Re: is there any way to convert number into integer? [message #639043 is a reply to message #639042]

Tue, 30 June 2015 02:03

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I used copy/paste to show my demonstration. You did not. What do you do?

Report message to a moderator

Re: is there any way to convert number into integer? [message #639044 is a reply to message #639042]

Tue, 30 June 2015 02:06

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mvrkr44 wrote on Tue, 30 June 2015 12:26

SQL>create table t1(c1 int);

sql>desc t1;
Name Type Nullable Default comments
---- ----- -------- ------ ------
c1 INTEGER Y

What's the matter with the SQL> prompt?

Report message to a moderator

Re: is there any way to convert number into integer? [message #639045 is a reply to message #639044]

Tue, 30 June 2015 02:14

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I change mine to show the username/SID of the connection so I dont get lost in a maze of terminals, thus avoiding those wrong terminal moments. Maybe OP does the same.

Report message to a moderator

Previous Topic:	Trigger on table to update other table
Next Topic:	Optimize PL/SQL code and get rid of one by one If then else statement check

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:51:15 CDT 2024