Home » SQL & PL/SQL » SQL & PL/SQL » SELECT <id> INTO KEY_VAR fails
SELECT <id> INTO KEY_VAR fails [message #258797] Mon, 13 August 2007 10:40 Go to next message
wbmoore
Messages: 3
Registered: August 2007
Junior Member
I am trying to determine why my select into statement is failing.
When I do a select manually, it works.
here is the local table description:
===========
desc dd_usermessage ;
Name Null Type
-------------- -------- ------------
USERMESSAGEID NOT NULL VARCHAR2(10)
COUNTRYID VARCHAR2(3)
ABBREV VARCHAR2(52)
TEXTID VARCHAR2(6)
VALIDATION NUMBER(2)
TRACKINGNUMBER NUMBER(10)
LEVEL_ID VARCHAR2(4)
===========

Here is the remote table description:
===========
desc oradba.dd_usermessage ;
Name Null Type
USERMESSAGEID NOT NULL CHAR(10)
COUNTRYID VARCHAR2(3)
ABBREV VARCHAR2(52)
TEXTID VARCHAR2(Cool
VALIDATION NUMBER(2)
TRACKINGNUMBER NUMBER(10)
LEVEL_ID VARCHAR2(4)
===========

data locally:

select * from dd_usermessage where UserMessageId='FOA0001';
========
USERMESSAGEID COUNTRYID ABBREV TEXTID VALIDATION TRACKINGNUMBER LEVEL_ID
------------- --------- ---------------------------------------------------- -------- ---------------------- ---------------------- --------
FOA0001 FO FOUA1 FOT001 1 77

1 rows selected

==========

Data remotely:
==========
USERMESSAGEID COUNTRYID ABBREV TEXTID VALIDATION TRACKINGNUMBER LEVEL_ID
------------- --------- ---------------------------------------------------- -------- ---------------------- ---------------------- --------
FOA0001 FO FOUA1 FOT001 1 77

1 rows selected
==========


Below is the stored procedure.
===========
create or replace PROCEDURE testLoad (
in_countryid IN VARCHAR2,
in_trackingnumber IN LONG)
IS

ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
KEY_VAR VARCHAR2(500);

CURSOR usermessage_cursor IS SELECT UsermessageId, CountryId, Abbrev, TextId, LEVEL_ID, Validation, trackingnumber FROM DD_USERMESSAGE WHERE trackingnumber = in_trackingnumber ORDER BY UserMessageId;

BEGIN

DBMS_OUTPUT.PUT_LINE('PROCESSING DD_Usermessage TRACKING NUMBER ='|| in_trackingnumber);
/* DD_Usermessage process */

FOR USERMESSAGE_REC IN usermessage_cursor
LOOP

/* Insert or Update, Concatenating fields to input in the declared variable */
BEGIN
DBMS_OUTPUT.PUT_LINE('PROCESSING USERMESSAGE_REC.UserMessageId='||USERMESSAGE_REC.UserMessageId||
' USERMESSAGE_REC.CountryId=' ||USERMESSAGE_REC.CountryId||
' USERMESSAGE_RECAbbrev='||USERMESSAGE_REC.Abbrev||
' USERMESSAGE_REC.TextId='||USERMESSAGE_REC.TextId||
' USERMESSAGE_REC.LEVEL_ID='||USERMESSAGE_REC.LEVEL_ID||
' USERMESSAGE_REC.Validation='||USERMESSAGE_REC.Validation||
' USERMESSAGE_REC.trackingnumber='||USERMESSAGE_REC.trackingnumber);

SELECT UserMessageId INTO KEY_VAR FROM ORADBA.DD_USERMESSAGE@GTA1_LINK.RMTC.FEDEX.COM
WHERE UserMessageId = USERMESSAGE_REC.UserMessageId;

DBMS_OUTPUT.PUT_LINE('Past select USERMESSAGE_REC.UserMessageId='||USERMESSAGE_REC.UserMessageId);

UPDATE ORADBA.DD_USERMESSAGE@GTA1_LINK.RMTC.FEDEX.COM
SET CountryId = USERMESSAGE_REC.CountryId,
Abbrev = USERMESSAGE_REC.Abbrev,
TextId = USERMESSAGE_REC.TextId,
LEVEL_ID = USERMESSAGE_REC.LEVEL_ID,
Validation = USERMESSAGE_REC.Validation,
trackingnumber = in_trackingnumber
WHERE UserMessageId = USERMESSAGE_REC.UserMessageId;
DBMS_OUTPUT.PUT_LINE('Past UPDATE USERMESSAGE_REC.UserMessageId='||USERMESSAGE_REC.UserMessageId);

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND PROCESSING USERMESSAGE_REC.UserMessageId='||USERMESSAGE_REC.UserMessageId);
INSERT INTO ORADBA.DD_USERMESSAGE@GTA1_LINK.RMTC.FEDEX.COM
(UsermessageId, CountryId, Abbrev, TextId, LEVEL_ID,
Validation, trackingnumber)
VALUES (USERMESSAGE_REC.UserMessageId, USERMESSAGE_REC.CountryId,
USERMESSAGE_REC.Abbrev, USERMESSAGE_REC.TextId,
USERMESSAGE_REC.LEVEL_ID,
USERMESSAGE_REC.Validation, in_trackingnumber);
DBMS_OUTPUT.PUT_LINE('Past insert USERMESSAGE_REC.UserMessageId='||USERMESSAGE_REC.UserMessageId);
END;

END LOOP;

COMMIT;

EXCEPTION -- exception handlers begin
WHEN OTHERS THEN -- handles all other errors
ROLLBACK;
ERR_MSG := SUBSTR(SQLERRM,1,100);
ERR_CDE := SQLCODE;

DBMS_OUTPUT.PUT_LINE('ERRORS ENCOUNTERED ERROR CODE = '||ERR_CDE||' ERROR MESSAGE = '||ERR_MSG);
RAISE;

END TESTLOAD;
==========

Even though the data exists, the stored procedure ALWAYS goes to the 'EXCEPTION WHEN NO_DATA_FOUND THEN' statement, which then does the insert and blows up with unique constraint error.

I realize the usermessageid in the remote table is of type char(10) as opposed to varchar2(10). Would this cause my problem?
Re: SELECT <id> INTO KEY_VAR fails [message #258803 is a reply to message #258797] Mon, 13 August 2007 10:53 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The clue is in your data types:

USERMESSAGEID NOT NULL VARCHAR2(10)

USERMESSAGEID NOT NULL CHAR(10)

CHAR is padded with spaces, VARCHAR2 is not. Hence they don't match.
Re: SELECT <id> INTO KEY_VAR fails [message #258807 is a reply to message #258803] Mon, 13 August 2007 10:57 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I would advise against ever using CHAR for anything. It's nothing but trouble.
Re: SELECT <id> INTO KEY_VAR fails [message #258809 is a reply to message #258797] Mon, 13 August 2007 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For your next post:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: SELECT <id> INTO KEY_VAR fails [message #258813 is a reply to message #258803] Mon, 13 August 2007 11:02 Go to previous messageGo to next message
wbmoore
Messages: 3
Registered: August 2007
Junior Member
thank you. Once I added trim, it worked fine.

I need to 'speak' to my DBA, as these two schemas were supposed to be the same, and are elsewhere. sigh...
Re: SELECT <id> INTO KEY_VAR fails [message #258814 is a reply to message #258809] Mon, 13 August 2007 11:03 Go to previous messageGo to next message
wbmoore
Messages: 3
Registered: August 2007
Junior Member
Michel Cadot wrote on Mon, 13 August 2007 11:00

Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).



quite right. I appologize.

Walter
Re: SELECT <id> INTO KEY_VAR fails [message #258872 is a reply to message #258814] Mon, 13 August 2007 12:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since we all have our favorites, I will drop mine here:

Get rid of that last exception handler. It does not do anything but obscuring the location of the error.
Furthermore, you don't want all those dbms_output calls in your final production code.
Re: SELECT <id> INTO KEY_VAR fails [message #258878 is a reply to message #258872] Mon, 13 August 2007 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Currently the exception block also rolls back, so it is useful.
But one of my favorite is that no procedure should commit or roll back.
Transaction is a logical/functional notion, so only application knows if the current treatment should commit or rollback not the procedure.
What if I just want to test the procedure, call it and try to roll back after? Impossible.
Only me, the caller, knows if I want to commit changes or roll back them.

Regards
Michel

Re: SELECT <id> INTO KEY_VAR fails [message #258900 is a reply to message #258797] Mon, 13 August 2007 14:32 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Not totally true about rollback. I have written procedures (when needed) that established a savepoint and then rolled back to the savepoint if an error occurred.
Re: SELECT <id> INTO KEY_VAR fails [message #258908 is a reply to message #258900] Mon, 13 August 2007 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, I was talking about rolling back the transaction.
Of course a procedure can roll back its own work using savepoint and rollback to savepoint, more I tend to say this is unfortunatly underused.

Regards
Michel
Re: SELECT <id> INTO KEY_VAR fails [message #258969 is a reply to message #258878] Mon, 13 August 2007 23:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 13 August 2007 19:51
Currently the exception block also rolls back, so it is useful.
But one of my favorite is that no procedure should commit or roll back.
Transaction is a logical/functional notion, so only application knows if the current treatment should commit or rollback not the procedure.
What if I just want to test the procedure, call it and try to roll back after? Impossible.
Only me, the caller, knows if I want to commit changes or roll back them.

Regards
Michel



Hence my point: The exception block is NOT useful. Furthermore, the rollback is the default action if I'd choose not to handle the exception in the calling program. If I do handle the exception there (the need for that has not changed, since the exception is reraised) I have to decide THERE what to do. (just like you say in the rest of your reply).
Previous Topic: Transposing Hierarchical Data
Next Topic: bind variable
Goto Forum:
  


Current Time: Mon Dec 05 07:15:16 CST 2016

Total time taken to generate the page: 0.08383 seconds