Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number
ORA-01722: invalid number [message #125767] Wed, 29 June 2005 03:00 Go to next message
alikazim
Messages: 6
Registered: June 2005
Junior Member
Hi everyone,

I have a stored procedure which takes a string of comma separated values as parameter and performs certain functions on it. Among the functions I'm doing is to conditionally add records in a table and delete existing records if they are not included in string value passed i.e. If I pass '6,7,8,' as pCHILD_BIZ_PARTNER_ID and 4 as pPARENT_BIZ_PARTNER_ID, it will ADD 3 records in TBL_SALES_HIERARCHY if they dont already exists.

Secondly if there exists any other CHILD_BIZ_PARTNER_ID against the PARENT_BIZ_PARTNER_ID, it should be deleted (by moving SYSDATE in END_DATE field). This is where I'm getting problem becuase I'm using IN clause and I put my comma separated string in thereand get the above mentioned error i.e. ORA-01722: invalid number

Here is the script of tables/stored procedure. Any help will be highly appreciated.

create table TBL_BIZ_PARTNER
(
BIZ_PARTNER_ID NUMBER not null,
BIZ_PARTNER_NAME VARCHAR2(100) not null,
BIZ_PARTNER_DESC VARCHAR2(255),
CREATE_DATE DATE not null,
END_DATE DATE,
SESSION_ID NUMBER not null,
CREATED_BY NUMBER not null,
AREA_ID NUMBER not null,
BIZ_PARTNER_TYPE_ID NUMBER not null,
BIZ_PARTNER_CODE VARCHAR2(100) not null,
ENDED_BY NUMBER,
BIZ_PARTNER_STATUS NUMBER not null
);

create table TBL_SALES_HIERARCHY
(
SALES_HIERARCHY_ID NUMBER(11) not null,
START_DATE DATE default SYSDATE not null,
END_DATE DATE,
SESSION_ID NUMBER(19),
CREATED_BY NUMBER(19),
ENDED_BY NUMBER(19),
PARENT_BIZ_PARTNER_ID NUMBER(11) not null,
CHILD_BIZ_PARTNER_ID NUMBER(11) not null
);

PROCEDURE PRC_SAV_SALES_HIERARCHY(pSESSION_ID IN NUMBER,
pCREATED_BY IN NUMBER,
pPARENT_BIZ_PARTNER_ID IN NUMBER,
pCHILD_BIZ_PARTNER_ID IN VARCHAR2,
pMSG_INFO OUT VARCHAR2) AS
i NUMBER := 1;
j NUMBER := 0;
vCHILD_BIZ_PARTNER_ID NUMBER;
SALES_HIERARCHY_COUNT NUMBER;
SALES_HIERARCHY_ID NUMBER;
SALES_HIERARCHY_END_DATE DATE;
pCHILD_BIZ_PARTNER_ID_TRIM VARCHAR2(100);
mySQL VARCHAR2(1000);
BEGIN
WHILE i < LENGTH(pCHILD_BIZ_PARTNER_ID) LOOP
i := INSTR(pCHILD_BIZ_PARTNER_ID, ',', i);

vCHILD_BIZ_PARTNER_ID := TRIM(',' FROM
SUBSTR(pCHILD_BIZ_PARTNER_ID, j, i - j));
SELECT COUNT(*)
INTO SALES_HIERARCHY_COUNT
FROM TBL_SALES_HIERARCHY
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID = vCHILD_BIZ_PARTNER_ID;
IF SALES_HIERARCHY_COUNT = 0 THEN
INSERT INTO TBL_SALES_HIERARCHY
(SALES_HIERARCHY_ID,
SESSION_ID,
CREATED_BY,
PARENT_BIZ_PARTNER_ID,
CHILD_BIZ_PARTNER_ID)
VALUES
(SEQ_SALES_HIERARCHY_ID.NEXTVAL,
pSESSION_ID,
pCREATED_BY,
pPARENT_BIZ_PARTNER_ID,
vCHILD_BIZ_PARTNER_ID);
ELSE
SELECT SALES_HIERARCHY_ID, END_DATE
INTO SALES_HIERARCHY_ID, SALES_HIERARCHY_END_DATE
FROM TBL_SALES_HIERARCHY
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID = vCHILD_BIZ_PARTNER_ID;
IF SALES_HIERARCHY_END_DATE <> NULL THEN
UPDATE TBL_SALES_HIERARCHY
SET END_DATE = NULL
WHERE SALES_HIERARCHY_ID = SALES_HIERARCHY_ID;
END IF;
END IF;
j := i;
i := i + 1;
END LOOP;

pMSG_INFO := 'T';

pCHILD_BIZ_PARTNER_ID_TRIM := TRIM(',' FROM pCHILD_BIZ_PARTNER_ID);
/* mySQL := 'UPDATE TBL_SALES_HIERARCHY SET END_DATE = SYSDATE WHERE PARENT_BIZ_PARTNER_ID = ' ||
TO_CHAR(pPARENT_BIZ_PARTNER_ID) ||
' AND CHILD_BIZ_PARTNER_ID NOT IN (' ||
pCHILD_BIZ_PARTNER_ID_TRIM || ')';
*/ UPDATE TBL_SALES_HIERARCHY
SET END_DATE = SYSDATE
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID NOT IN (TO_NUMBER(pCHILD_BIZ_PARTNER_ID_TRIM));

--DBMS_OUTPUT.PUT_LINE(mySQL);
--EXECUTE IMMEDIATE mySQL;
END PRC_SAV_SALES_HIERARCHY;

P.S: I've also tried using Dynamic SQL (as u can see from commented code) but that seems to hang my system. I wonder whats the problem here...

Thanks in advance.

Ali Kazim Gardezi

Report message to a moderator

Re: ORA-01722: invalid number [message #125773 is a reply to message #125767] Wed, 29 June 2005 03:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Hai Kazim,

On ging through Your code i FOUND the folloeing codes...

vCHILD_BIZ_PARTNER_ID := TRIM(',' FROM
SUBSTR(pCHILD_BIZ_PARTNER_ID, j, i - j));


here vCHILD_BIZ_PARTNER_ID is NUMBER and U r trying to assign trimmed varchar2 into that..

I suspect that may be a reason...

rajuvan

Report message to a moderator

Re: ORA-01722: invalid number [message #125777 is a reply to message #125773] Wed, 29 June 2005 03:56 Go to previous messageGo to next message
alikazim
Messages: 6
Registered: June 2005
Junior Member
Hi rajuvan,

Well... that statement is right as it will assign single numeric value to vCHILD_BIZ_PARTNER_ID. Actually if v pass '6,7,8,' in pCHILD_BIZ_PARTNER_ID, then SUBSTR(pCHILD_BIZ_PARTNER_ID, j, i - j) will return '6,' in first loop, '7,' in second loop and so on... I trim that value so now it returns '6', '7' etc. And Oracle explicitly convert taht into NUMBER type.

Proble I'm facing is in the IN clause of the UPDATE statement begining at line 61. I use that to update multiple records in TBL_SALES_HIERARCHY table. Any help with that will be highly appreciated.

Ali Kazim

Report message to a moderator

Re: ORA-01722: invalid number [message #125792 is a reply to message #125767] Wed, 29 June 2005 04:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Kasim,

I think error happens like this....

Quote:

pCHILD_BIZ_PARTNER_ID_TRIM := TRIM(',' FROM pCHILD_BIZ_PARTNER_ID);
/* mySQL := 'UPDATE TBL_SALES_HIERARCHY SET END_DATE = SYSDATE WHERE PARENT_BIZ_PARTNER_ID = ' ||
TO_CHAR(pPARENT_BIZ_PARTNER_ID) ||
' AND CHILD_BIZ_PARTNER_ID NOT IN (' ||
pCHILD_BIZ_PARTNER_ID_TRIM || ')';
*/ UPDATE TBL_SALES_HIERARCHY
SET END_DATE = SYSDATE
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID NOT IN (TO_NUMBER(pCHILD_BIZ_PARTNER_ID_TRIM));

Suppose pCHILD_BIZ_PARTNER_ID = '6,7,8'
SQL> select TRIM(',' FROM ',6,7,8,')from dual;
6,7,8
SQL> select replace(',6,7,8,',',','')from dual;
678


I thing You got the problem...

Because Update clause is getting error like this...

SQL> select to_number(TRIM(',' FROM '6,7,8')) from dual;
select to_number(TRIM(',' FROM '6,7,8')) from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


So REPLACE SHOULD BE USED INSTEAD OF TRIM...
Then,
SQL>  select to_number(replace(',6,7,8,',',',''),'999') from dual;
                                       678



Rajuvan

[Updated on: Wed, 29 June 2005 04:56]

Report message to a moderator

Re: ORA-01722: invalid number [message #125811 is a reply to message #125792] Wed, 29 June 2005 05:57 Go to previous messageGo to next message
alikazim
Messages: 6
Registered: June 2005
Junior Member
Rajuvan,

I think I'm not able to explain the issue here. Actually I want to update all such records which dont have CHILD_BIZ_PARTNER_ID of 6, 7 or 8 in TBL_SALES_HIERARCHY. So the reason I'm usingg TRIM function is just to remove any leading or traling ',' thats it. I want to run a statement like this:

UPDATE TBL_SALES_HIERARCHY
SET END_DATE = SYSDATE
WHERE PARENT_BIZ_PARTNER_ID = 4 AND
CHILD_BIZ_PARTNER_ID NOT IN (6,7,8);

Here are the sample data:

TBL_BIZ_PARTNER
---------------
BIZ_PARTNER_ID BIZ_PARTNER_NAME CREATE_DATE END_DATE BIZ_PARTNER_TYPE_ID
4 ABC Technolongies 26-JUN-2005 3
6 Sohail Computers 27-JUN-2005 4
7 XYZ Soft 27-JUN-2005 4
8 XYZ Enterprises 28-JUN-2005 5
9 Yaseen Computers 28-JUN-2005 5

TBL_SALES_HIERARCHY
-------------------
SALES_HIERARCHY_ID PARENT_BIZ_PARTNER_ID CHILD_BIZ_PARTNER_ID CREATE_DATE END_DATE
1 4 6 29-JUN-2005
2 4 9 29-JUN-2005

So now that I call PRC_SAV_SALES_HIERARCHY and pass it 4 as pPARENT_BIZ_PARTNER_ID and '6,7,8,' aspCHILD_BIZ_PARTNER_ID it should add two more records in the TBL_SALES_HIERARCHY (one for 7 and one for 8 against PARENT_BIZ_PARTNER_ID of 4) and it should update the END_DATE of remaining rows for the PARENT_BIZ_PARTNER_ID of 4 i.e. to SYSDATE. There is one such record i.e. Sales_Hierarchy_ID of 2.

So the update statement soon after the commented code is trying to achieve the above mentioned result but its throwing error: ORA-01722 as I've mentioned earlier. First part of the stored procedure which break the string and ADD rows conditionaly in the table TBL_SALES_HIERARCHY is working fine although I believe there is still room for improvement. But I have troubles with the second part.

I hope I have cleared my problem so far. Thanks for ur continued intrest in the problem.

Regards

Ali Kazim

Report message to a moderator

Re: ORA-01722: invalid number [message #125851 is a reply to message #125767] Wed, 29 June 2005 08:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I've only skimmed your code, but I think you are confusing a string containing a comma separated list from an actual comma separated list. It is a common issue. Have a look at this link, I think it will help you go in the right direction:

http://www.orafaq.com/forum/m/120939/0#msg_120932

Report message to a moderator

Re: ORA-01722: invalid number [message #125859 is a reply to message #125767] Wed, 29 June 2005 09:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Hello From India once again,

Quote:

pCHILD_BIZ_PARTNER_ID_TRIM := TRIM(',' FROM pCHILD_BIZ_PARTNER_ID);


You will get pCHILD_BIZ_PARTNER_ID_TRIM ='6,7,8'
Bcoz,
SQL>select TRIM(',' FROM '6,7,8') from dual
TRIM(','FROM'6,7,8')
--------------------
6,7,8               
1 row selected


Quote:

UPDATE TBL_SALES_HIERARCHY
SET END_DATE = SYSDATE
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID NOT IN (TO_NUMBER(pCHILD_BIZ_PARTNER_ID_TRIM));


ie , By
Quote:

CHILD_BIZ_PARTNER_ID NOT IN (TO_NUMBER(pCHILD_BIZ_PARTNER_ID_TRIM))


You will get
SQL>select to_number(TRIM(',' FROM '6,7,8')) from dual
ORA-01722: invalid number

ie,
SQL>select to_number('6,7,8') from dual
ORA-01722: invalid number


This is the same error where U R struck now....

But Your Dynamic Sql code seems to be working fine for me with almost same problem.

May the records be locked anywhere....Try again

rgds,
Rajuvan

Report message to a moderator

Re: ORA-01722: invalid number [message #126385 is a reply to message #125859] Mon, 04 July 2005 03:02 Go to previous messageGo to next message
alikazim
Messages: 6
Registered: June 2005
Junior Member
Hello Rajuvan,

By the way, I'm from ur friendly naighbourhood, Pakistan Smile

No there was no lock anywhere on this table. As far as the issue is concerned, the problem still exists even I take out the TO_NUMBER function from the following statement:

UPDATE TBL_SALES_HIERARCHY
SET END_DATE = SYSDATE
WHERE PARENT_BIZ_PARTNER_ID = pPARENT_BIZ_PARTNER_ID AND
CHILD_BIZ_PARTNER_ID NOT IN (TO_NUMBER(pCHILD_BIZ_PARTNER_ID_TRIM));

But...I was able to solve the problem using a workaround. That is, I update the records first without the second condition in where clause (CHILD_BIZ_PARTNER_ID NOT IN (...)). And then I execute the loop which conditionally insert/update records for each value in comma separated string. This I'm able to get the job done.

I still wonder whats wrong with the DYNAMIC SQL approach. Why its taking forever to execute.

Thanks anyways for ur replies.

Regards

Ali Kazim

Report message to a moderator

Re: ORA-01722: invalid number [message #126388 is a reply to message #125851] Mon, 04 July 2005 03:15 Go to previous message
alikazim
Messages: 6
Registered: June 2005
Junior Member
Thanks smartin

The link (http://www.williamrobertson.pwp.blueyonder.co.uk/documents/comma_separated.html) inside the tread u have mentioned was really helpful.

Reagrds

Ali Kazim

Report message to a moderator

Previous Topic: Plz tell me advantages and disadvantages of Cursor
Next Topic: how to display the date in date partitioning even date is null
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Aug 09 14:04:29 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.