Oracle exception ora 06550



Handling exceptions. ORA-06550.

I’m using 11g Express Edition 11.2.0.2.0.

The code below runs fine. It takes a parameter (student_id in this instance), and returns some test data including ‘pass’ or ‘fail’ etc.

At the moment i’m struggling with creating some exceptions that will handle things such as the parameter not being an integer, the parameter being null etc.

Replicating these errors returns the error code ORA-06550, which i understand from researching this forum, cant be handled as its a compilation time error and not an execution time error.

Is it possible to alter my code somehow, so that i can still get the same outcome, but also write some exceptions based on the parameter input.

Any help is much appreciated.

Answers

You could change your parameter into a varchar, and then translate it using cast.

This way as long as they do input something extremely exotic you could then trap the conversion error as most things can be varchar.

So when I declare the cursor, I make the parameter p_student varchar instead.

How would I then use the case statement. Sorry its a bit beyond me!

Thanks for your help

Make your p_student varchar2

Then change all of your instances after the parameters from p_student to v_student.

And as a first action; —

Which implicitly changes your varchar2 to number, if this fails it will error at that point and you can explicitly capture the errors that occur.

That introduces more work, and more potential bugs. If it is expected to be a number, defining the parameter as NUMBER is the right decision.

For the OP, Are you really running this as an anonymous PL/SQL block, or are you creating a stored procedure? If your stored procedure is called with the wrong data type (passing a character to a number parameter), then the call to that procedure will fail with ORA-06502 PL/SQL numeric or value error, which raises the VALUE_ERROR exception. Whatever calls the procedure can trap that.

ORA-06550 is a symptom of some other issue. There is no need to handle it directly.

I’m not sure why you started a new thread, since this seems to be related to your other question?

Источник

Oracle exception ora 06550

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

We are currently experiencing issues trying to connect to Oracle stored procedures from Microsoft SQL Server Reporting Services 2014, receiving the following error anytime we try and run any stored procedure via a report connecting to an Oracle stored procedure:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘ ‘ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

These stored procedures return an “OUT” SYS_REFCURSOR parameter as required to return a data set to SSRS, and it seems to be that parameter that is causing the issue for all our reports.

Читайте также:  Visual studio error c1075

We are running SSRS on a Windows Server 2012 R2 Standard OS, 64 bit, and SSRS is running under a 64 bit configuration. We have installed the ODAC 64 bit components (64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64) for Windows (downloaded from https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html) and also registered the ODP.NET DLLs on the server into the GAC as our reports use this connection method.

From SSRS on the server, we can successfully make a connection to the Oracle datasource using the SSRS Report Manager. And from our development machines, where we installed the 32 bit ODAC / ODT Tools for Visual Studio (ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.1)) (because Visual Studio uses the 32-bit ODAC components), we can successfully connect to the Oracle database and execute the reports without the error we are receiving on the server.

We have already validated that we have the correct parameters in the report, and we have validated that we can connect and execute the stored procedures successfully via SQL Plus and also on our local development machines from the SSRS report.

We are trying to connect to an Oracle 11.2.0.4 database.

We have already tried following the advice and procedures from a number of articles, including those listed in other posts on this site such as «https://social.technet.microsoft.com/Forums/en-US/424f750e-7f58-49e3-bd4a-51e0acdd99a4/not-able-to-use-oracle-sp-in-ssrsgetting-an-error?forum=sqlreportingservices» and «https://social.technet.microsoft.com/Forums/en-US/626c9c6c-1c99-4718-9cb1-054a102701cd/ssrs-calling-a-stored-procedure-error-pls00306-wrong-number-or-types-of-arg?forum=sqlreportingservices&ppud=4». But as far as we can tell, the ODAC version we have installed on the server (12.2c Release 1) can connect to an Oracle database version 10g or higher (according to https://www.oracle.com/technetwork/topics/dotnet/install122010-3711118.html), and our database is 11.2.0.4 so we should be good, correct? Or is the Oracle documentation wrong, and in order to connect to an Oracle 11.x database we need the ODAC 11.2.0.3.0 components on the server (even though the ODAC 12.2c components installed on our development machines allow us to run the reports successfully from Visual Studio)?

Anyone have any thoughts?

Thanks in advance.

All replies

According to your description , seems you could check in the following aspects.

  • Do not use the stored procedure directly, try to use a simple query check if the query would runs ok in ssrs. If the simple query runs correct , seems it is an issue about the query (stored procedure ), if not seems it is an issue about the connection provider driver.
  • For the query problem .
    1. Check if you have the correct parameter type.
    2. Do you have enough permission to access the stored procedure and the correspond temp table space.
    3. Check your stored procedure again.
    4. Any custom datatype or just mistype.
  • For the provider driver.
    1. Make sure you have correct install the correspond provider driver .(both 32 bit and 64 bit ,and both user level and system level)
    2. The multiple connection driver ‘s crash , try to make sure you have a clean environment .see: Connection error after upgrading from ODAC 9i to 11.2
Читайте также:  Enlisted fatal error freeze detected call stack 5 frames

You could also offer the correspond ssrs log or the oracle log information to us for more further research.

Hope it can help you.

Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

Thanks for the response. Following your suggestions, I took the (very simple) PL-SQL out of the stored procedure, and built a new SSRS report to run directly against the SQL, and as expected this worked both on my development machine, and from the server.

However, I don’t necessarily agree that this points to an issue with the stored procedure. I say that because I can successfully use the stored procedure, using the same database and user and connection string, from my development machine and have it work. If there was an issue with the stored proc, then it shouldn’t work anywhere I try and use it, correct?

And the PL-SQL / stored procedure we are trying to get working is extremely simple. Here is the PL-SQL:

WHERE TRUNC (DATECOLUMN) =

TRUNC (TO_DATE (‘2018-01-01’, ‘yyyy-mm-dd’))

And as a stored procedure, it is pretty much just as simple, except that we have defined a single input parameter for the date, and the necessary output parameter to hold the dataset to pass back to the report:

CREATE OR REPLACE PROCEDURE BLAH.spParameterTest (

param1 IN VARCHAR,

Results OUT SYS_REFCURSOR)

OPEN Results FOR

WHERE TRUNC (DATECOLUMN) =

TRUNC (TO_DATE (param1, ‘yyyy-mm-dd’));

I have double (and triple :)) checked the stored procedure, it’s parameters and data types, and permissions, and all seem good (again, we can successfully use it from our development machines). I had actually seen that first article you reference previously and validated all of that.

Regarding the second article — I don’t believe we have that issue either, as we also thought this could be an issue and removed all ODAC installations on the server, then installed the singular ODAC 64 bit components (64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64) for Windows component. One item you mentioned peaked my interest though, and that was:

  1. Make sure you have correct install the correspond provider driver .(both 32 bit and 64 bit ,and both user level and system level)

Is the driver not automatically installed where necessary by the ODAC installation? And why would I need the 32 bit driver since I’m using all 64 bit software and OS? And how do I install at a user vs. system level?

Finally, regarding your suggestion to post the related SSRS log, here is an excerpt that we get when we attempt to run the report from the SSRS Report Manager:

  • An error has occurred during report processing. (rsProcessingAborted)
    • Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
      • ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘BLAH’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Читайте также:  Error no supported proxies

And in the SSRS log file we get (sorry for the length :)):

processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: i INFO: DataPrefetch abort handler called for Report with Aborting data sources .
processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.OnDemandProcessingContext.AbortHelper.ThrowAbortException(String uniqueName)
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.FetchData()
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.PrefetchData(ReportInstance reportInstance, ParameterInfoCollection parameters, Boolean mergeTran)
at Microsoft.ReportingServices.OnDemandProcessing.Merge.FetchData(ReportInstance reportInstance, Boolean mergeTransaction)
at Microsoft.ReportingServices.ReportProcessing.Execution.ProcessReportOdpInitial.PreProcessSnapshot(OnDemandProcessingContext odpContext, Merge
webserver!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.OnDemandProcessingContext.AbortHelper.ThrowAbortException(String uniqueName)
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.FetchData()
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.PrefetchData(ReportInstance reportInstance, ParameterInfoCollection parameters, Boolean mergeTran)
at Microsoft.ReportingServices.OnDemandProcessing.Merge.FetchData(ReportInstance reportInstance, Boolean mergeTransaction)
at Microsoft.ReportingServic

So, does any of the above give you any clues that might be wrong?

Источник

Оцените статью
toolgir.ru
Adblock
detector