Home » SQL & PL/SQL » SQL & PL/SQL » Error in Inserting and Updating Data on LONG Datatype Column
Error in Inserting and Updating Data on LONG Datatype Column [message #392425] Tue, 17 March 2009 23:39 Go to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hi,

I am getting the below error when I am trying to compile/execute the package body which does insert and update the data into a table thru stored procedures.<<the table has LONG Data Type Column>>

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got LONG.

I understood that its is becoz of that LONG datatype only as when I comment out that particluar column....there are no compilation errors.

I cannot change or modify the column to any datatype such as CLOB..it is not possible due to some restrictions.

Please let me know how to overcome this issue.

Thanks,
Ash

Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392426 is a reply to message #392425] Tue, 17 March 2009 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.

Why do you expect assistance while with holding meaningful details?
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392428 is a reply to message #392426] Wed, 18 March 2009 00:06 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Ok!!!
Below are the codes which I had.

Table Name <<Head(l_Comment LONG,..and other columns>>

I have a package which has Head_insert & Head_update stored procedures deined in the package specification.
<<please exclude the syntax as it is just a sample code>>

<<Package Specification Head_Pk>>

Procedure Head_insert(v_comment Head.l_Comment%TYPE,...other input params...);

Procedure Head)Update(v_comment Head.l_Comment%Type...other params);

<<Package Body Head_pk>>

Procedure Head_Insert(v_comment Head.l_Comment%TYPE,...other input params...)
IS
Begin
Insert INTO HEAD
(l_Comment,....other table columns..)
Values
(v_comment,...other input params...);
End;
End Head_Insert;

Procedure Head_Update(v_comment Head.l_Comment%TYPE,...other input params...)
IS
Begin
Update HEAD
SET
l_Comment = v_comment,
...other input params...)
Some Where Condition;
End;
End Head_Update;

END HEAD_PK;

Now Can you plese help me???

Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392440 is a reply to message #392425] Wed, 18 March 2009 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Now Can you plese help me???
No

how can I actually/really/truly reproduce error message?
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392444 is a reply to message #392440] Wed, 18 March 2009 00:43 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
>Now Can you plese help me???
>No
Fine Thanks!!!!!

>how can I actually/really/truly reproduce error message?
For your information the error I got is mentioned in my first post...
How can you expect me to copy paste the whole bunch of code?
Rather I just explained my issue with a sample code.
If you can help or give any suggestions..you r welcome otherwise..it is upto you...
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392507 is a reply to message #392444] Wed, 18 March 2009 04:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is that you haven't given us any additional information with your second post.

We knew from the first post that you had a package that did DML, and at the end of the second post, that's still all we know.

We need the structure of the table, and the SQL statement that is failing. If the SQL statement is too long to post, then remove as much of it as you can while still getting the error.

I would guess that you're passing a LONG into a function to get this error, but that is just a guess.
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392555 is a reply to message #392507] Wed, 18 March 2009 07:02 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
I am getting the error when I am trying to compile the Package body with the structure as mentioned in my previous post.

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I am trying to update the table Header which has l_comment column defined as LONG.
I have restrictions for using the same names as that of column names in the table as input params and also from changing the LONG data type column to CLOB.

Insert procedure is working fine.
Update Procedure is causing the problem.

Code for update procedure:
Header_Update(Id Header.Id%TYPE,
l_comment Header.l_comment%Type,
other input params...)
IS
v_comment Header.l_comment%Type;
v_Id Header.Id%TYPE;
BEGIN
v_comment := l_comment;
v_Id := Id;

Update Header
SET l_comment = NVL(v_comment,l_comment,<<here is where I am showing error>>
other columns to be updated based on the input params
WHERE Id = v_Id;
END Header_Update;

Help me in overcoming this issue.
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392562 is a reply to message #392425] Wed, 18 March 2009 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well first off you might want to rename the parameter l_comment so that it has different name to column you're using it to update.
Secondly - I'm not sure nvl takes LONG as an argument.
Thirdly - oracle has desurported LONG so you should probably look at changing the datatype.
Fourthly - your code would be a lot easier to read if you used code tags - the orafaq forum guide tells you how to do so.
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392563 is a reply to message #392555] Wed, 18 March 2009 07:38 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
NVL doesnot work with long datatype.
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392567 is a reply to message #392563] Wed, 18 March 2009 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Let's not over specify here - with the exception of TO_LOB, I don't think any function will work on LONGs of an arbitrary length.

You really, really need to move from LONGs to LOBs
Re: Error in Inserting and Updating Data on LONG Datatype Column [message #392571 is a reply to message #392425] Wed, 18 March 2009 08:05 Go to previous message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hey Guz,

Thanks for your inputs.
Yeah NVL doesn't work with LONG.

I have written separate PLSQL block inorder to handle NULL of input parameter.

Just a simple code:

<<Update Procedure with l_comment & Id as input params>>

BEGIN
v_comment := l_comment;
v_Id := Id;

IF v_comment IS NULL
THEN
Select l_comment
INTO v_comment
From header
where Id = v_id;
END IF;

UPDATE HEADER
SET l_comment = v_comment,
......
WHERE Id = v_id;
END;

As of now the package body gets compiled without any errors.
Hope it works fine.

Correct me if I am wrong.




Previous Topic: Validate option
Next Topic: DB_LINK question
Goto Forum:
  


Current Time: Sun Dec 11 02:11:30 CST 2016

Total time taken to generate the page: 0.08764 seconds