Home » SQL & PL/SQL » SQL & PL/SQL » Executing procedure from SQL server via linked server
Executing procedure from SQL server via linked server [message #328328] Thu, 19 June 2008 15:16 Go to next message
Cattrah
Messages: 3
Registered: June 2008
Junior Member
I am trying to execute an oracle 9i procedure from sql server 2005 over a linked server, and I swear this is working fine, except that it's not. Here's how it looks:

DECLARE @AssetNo nvarchar(15),
@Requestor NVarChar(50),
@ProbDesc nvarchar(10),
@WorkType nvarchar(1),
@WorkDesc nvarchar(10),
@Craft int,
@Crew int,
@Persons int,
@Hours float,
@Priority nvarchar(2),
@DateReq nvarchar(9),
@Planner nvarchar(9),
@Shutdown nvarchar(20),
@PlanType nvarchar(20),
@LastModBy nvarchar(9),
@WOAStatus int,

@WONumber nvarchar(15),
@PassFail nvarchar(2),
@Error nvarchar(20),

@PTMID int

SET @WONumber = NULL
SET @PassFail = NULL
SET @Error=NULL

set @PTMID = 28

SELECT @Requestor = Requestor, @ProbDesc=ProblemDescription, @AssetNo=AssetNumber,
@WOAStatus=WOAssetStatus, @WorkType=WorkType, @WorkDesc=WorkDescription, @Craft=Craft,
@Crew=Crew, @Persons=Persons, @Hours=Hours, @Priority=Priority, @DateReq=REPLACE(CONVERT(VARCHAR(9), DateRequired, 6), ' ', '-'),
@Planner=Planner, @Shutdown=Shutdowncode, @PlanType=PlanningType FROM EMPAC_WOStaging
WHERE PTMID=@PTMID


execute('begin EMPAC.INT_PTM.CreateWorkOrder(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); commit; end;', @PTMID, @AssetNo, @Requestor,
@ProbDesc, @WOAStatus, @Priority, @DateReq, '000000001', @WorkDesc, @Crew, @Craft, @Shutdown, @WorkType, '000000001', @Hours,
@Persons, @WONumber output, @PassFail output, @Error output) AT PTM_TO_EMPAC;

And this does infact run, it does run the procedure (Sql tells me it executes successfully and I do see some result of the procedure running on the oracle side). I don't have access to the package to look at the code for it, but at some point it's erroring and telling me:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

which sounds like a simple enough thing to fix, but I've checked and double checked every field the oracle procedure uses with the variable sizes and types that I am using and they match, none are larger, infact for good measure I manually entered the data instead of via the parameters and it still gives me this error. When the same data is entered and the procedure run from the oracle database manually, the procedure doesn't give the same output error as I'm getting when it's executed from sql. So what's going on here? Any ideas?
Re: Executing procedure from SQL server via linked server [message #328330 is a reply to message #328328] Thu, 19 June 2008 15:18 Go to previous messageGo to next message
sunitaorc
Messages: 2
Registered: June 2008
Location: INDIA
Junior Member
OK
Re: Executing procedure from SQL server via linked server [message #328333 is a reply to message #328328] Thu, 19 June 2008 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
This error results from 1 of 2 causes:
1) trying to assign a non-numeric to a numeric field.
2) trying to assign a string to a variable which is too small to hold it.

Keep in mind that if there are differences in charactersets or NLS settings, the physical length of strings my be different in 1 DB vs. the other.

By the way, Oracle is too dumb to lie or misreport this error.

If you get desperate, you could revert to trial & error using the binary search technique.
Start by passing only the top half of the variables.
If this succeeeds, then the problem is in the lower half of the variables.
If it fails, then cut the list in half again.
Repeat process until you KNOW which variable is throwing the error.
Re: Executing procedure from SQL server via linked server [message #328337 is a reply to message #328328] Thu, 19 June 2008 15:38 Go to previous messageGo to next message
Cattrah
Messages: 3
Registered: June 2008
Junior Member
hmm, well I thought the binary search was the type of thing I was attempting to do to pass all the values manually instead of through the parameter. So this also fails in the same way:

execute('begin EMPAC.INT_PTM.CreateWorkOrder(''28'',''000000000005008'',''test'',''test work'',170,''2'',''20-Jun-08'',''000000001'',''test more'',12343,10992,''L'',''FP'',''000000001'',6,2,?,?,?); end;',
@WONumber output, @PassFail output, @Error output) AT PTM_TO_EMPAC;

I didn't know how I could manually send an output variable, but it completely fails to execute if I exclude them. At this point I can clearly see that the value being sent is within the valid length of all the expected parameters of the procedure. The only thing I can think is somewhere in the translation sql may be adding white space or extra characters that's causing the problem on the oracle side. I suppose I'll have to wait for the oracle dba to come back from vacation so he can tell me what is actually being received. Thanks for the suggestions. I'll keep at it.
Re: Executing procedure from SQL server via linked server [message #328957 is a reply to message #328328] Mon, 23 June 2008 09:37 Go to previous message
Cattrah
Messages: 3
Registered: June 2008
Junior Member
just for the record, we were able to figure this out, and it was the output parameters that were causing the problem. So we took those out from both sides and after a few more tweaks it was working just fine. I tried to send in the output parameters being declared another way and now it still executes the procedure, but it causes SQL server to crap out, I suppose when trying to get the output parameters back. I haven't been able to find anything online anywhere where someone successfully returns values from the oracle procedure using the execute command like I am. I guess no one does that!?
Previous Topic: A cursor problem
Next Topic: Summing Results of Other Columns
Goto Forum:
  


Current Time: Sat Dec 03 01:33:42 CST 2016

Total time taken to generate the page: 0.14133 seconds