the.eviled209.40k (5/6/2011)
I am having an issue retreiving data from a Stored Procedure in Oracle to SSRS. First, I have to keep this Stored Procedure in Oracle because it is passing data of the LONG data type to SSRS and I can not seem to find a way to get a SQL Storded Procedure to get this data without truncating it. I am using a REF CURSOR in the Oracle PROD to pas this data and I have an Oracle Data Source set up in SSRS and tested my connection and it works. However, despite having the cursor set up correctly, and from what I can tell having the report set up correct to retreive the data, I am still getting an error. While I connent to the PROD, I can not refresh my fields. It is telling me that it can not updated a list of fields for the query.In any case, here is my Oracle PROD, is this correct?
CREATE OR REPLACE PROCEDURE PROD.GETWOTRV_CMNTS_ORL (
nOrderNo IN VARCHAR2,
Comnt_Detail OUT cursorpkg.c_any)
AS
...
Query execution failed for dataset 'MyDataSet'. ORA-06564: object MyStoredProcedure does not existORA-06512: at "SYS.DBMS_UTILITY", line 114ORA-06512: at line 1
This may not solve all the issues but will point you in the right direction.
1- Error suggests Oracle thinks you are callling a storedproc called "MyStoredProcedure" when procedure appears to be named GETWOTRV_CMNTS_ORL. Be sure you are calling the right one, be sure the user login into Oracle has execute privilege on PROD.GETWOTRV_CMNTS_ORL
2- I would start by being sure storedproc works as expected on Oracle side, to do so I would...
2.a) Log into Oracle instance as PROD
2.b) execute...
variable my_cursor refcursor;
exec PROD.GETWOTRV_CMNTS_ORL ('value-of-external-parameter', :my_cursor);
print my_cursor;
2.c) Once you are certain code works as expected on Oracle side then any remaining issue will be related to SQL Server interaction and you can focus there knowing issue is really there.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.