May 6, 2011 at 6:51 am
Hi all!
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
BEGIN
--declare the variables for the SELECT INTO statements
DECLARE
note_type VARCHAR2(2);
user_cust_no VARCHAR2(35);
cust_name VARCHAR2(35);
comnt LONG(8000);
--select the customer no, cust name, and comments for the customer segment and the comment field in the LONG data type
BEGIN
--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,
CUST_SEG_COMNT csc
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
UNION ALL
----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,
PART_SEG_COMNT psc
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 statement
END;
--end stored procedure
END GETWOTRV_CMNTS_ORL;
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
May 6, 2011 at 7:06 am
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.May 6, 2011 at 7:15 am
I changed the name to MyStoredProcedure for security reasons forgetting that I ahd already printed it in the PROD.
So that was an old ID-10-T erro on my part. 🙂
On your side of it, I am running TOAD 10.1.1.8 as my portal to Oracle. When I run your statment I get an Invalid SQL Statement error from TOAD. I even changed it to match my variable and that did not work:
variable Comnt_Detail refcursor;
exec PROD.GETWOTRV_CMNTS_ORL (166218, :Comnt_Detail);
print Comnt_Detail;
So if my issue is my Oracle PROD I can believe that, but I do not see where it is wrong.
Thanks for looking, any help you can provide would be a big help!
Ed
May 6, 2011 at 7:26 am
Toad is not executing statements as sqlplus.
Either connect thru sqlplus - you have it in your local ORACLE_HOME/bin or research how to get Toad to exec statements as sqlplus.
_____________________________________
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.May 6, 2011 at 7:55 am
Well, short of that, what I can tell you is that we have a bunch of Oracle PROD pushing out data to Crystal Reports and they all have cursors like this. So I am confident that it is not the PROD, I genuinely believe it is something in SSRS that is not set up correctly. Heck, I could be wrong. I cannot find any more info on this on line and everything I find tells me that I am doing this right. So I am missing something small somewhere.
May 6, 2011 at 8:35 am
Check all objects getting ORA-06564 then...
1- Be sure object exist.
2- Be sure the Oracle account logging into Oracle during the process do have execute privilege granted on it.
_____________________________________
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.May 6, 2011 at 11:10 am
Hey, nevermind on this one guys. Turns out I have a permission error that fixing my longin permission has corrected the issue. Thanks!
Ed
October 1, 2013 at 6:44 am
Hi, any people.
here two solution:
Frist: qualify the store pocedure in SSRS.
Second: Create synonym (private or public) of store procedure in account or scheme that it is configuration in the datasourse of SSRS.
My recomendation is create synonym private.
Regards,
your server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply