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)
--declare the variables for the SELECT INTO statements
--select the customer no, cust name, and comments for the customer segment and the comment field in the LONG data type
--open the cursor
OPEN Comnt_Detail FOR
SELECT 'C ', vbc.user_cust_no, vbc.cust_name, csc.comnt_txt
INTO note_type, user_cust_no, cust_name, comnt
FROM V_BKLG_CUST vbc,
WHERE vbc.cust_no = csc.cust_no
AND vbc.seg_no = csc.seg_no
AND csc.print_cd = 'I'
AND vbc.user_ord_no = nOrderNo
-- union this with the results from the part comment segment
----select the part no, and comments for the customer segment and the comment field in the LONG data type
SELECT 'P ', vbp.user_part_no, '', psc.comnt_txt
FROM V_BKLG_PART vbp,
WHERE vbp.part_no = psc.part_no
AND vbp.seg_no = psc.seg_no
AND psc.print_cd = 'I'
AND psc.comnt_type_cd = 'Mfg'
AND vbp.user_ord_no = nOrderNo;
--end stored procedure
My SSRS Data Sourse is called OracleDS, with a Type of Oracle. My Server Name is correct, and the login info works and I have checked Save my password. I can test my connection and it works. I have my credentials set with a valid user name and password. MY Dataset uses a dataset embedded in my report, using my Oracle Data Source, with a Query type of Stored Procedure and I have the correct Stored Procedure selected.
Becuase it would not auto populate the feidls for this, I have set them manually, there are 4. Options and Filters are set to defaults and I have manually set my Paramenter, NORDERNO = @ORderNumber.
It looks like it should work and I can not figure out why it doesn't. When I preview the report and pass it a valid Order number I get the following error:
Query execution failed for dataset 'MyDataSet'. ORA-06564: object GETWOTRV_CMNTS_ORL does not existORA-06512: at "SYS.DBMS_UTILITY", line 114ORA-06512: at line 1
So I am tossing up a flare to the more experianced DBAs out there. Help... what am I doing wrong?
Thanks for you help, Ed