Usually a pl sql compilation error

Hi there,

I’m comming crazy guys. I need help. I am making a call to a DB Procedure (which exists; its correct written) from a block properties, so when I execute the query from the application, it returns the data from a cursor into the form. That should be it, but an ORA-6550 pops-up with no data return at the Execute Query Trigger, at Form Level. This is it:

BLOCK’S PROPERTIES:
— The block is a DB block.
— Query Data Source Type: Procedure
— Query Data Source Name: PackageName.ID_QUERY
— Query Data Source Columns: 9 columns, put in the same order than as called from the DB Procedure (which has a cursor on it as I will show); I declare the Type of each column with its length as at the DB Table is. NO SPACES after the name of each Column Name!!! After the last columns, I have checked there wasn’t spaces either in the next place to put another Column.
— Query Data Source Arguments: 2 argument names: a cursor (type: RefCursor, Type Name: PackageName.B_CURSOR, Mode: in out) and the parameter the user inserts to make the query (Type: Number, Mode: In; Value: :block.itemName). At the place of the third argument, nothing, no spaces.

DB Procedure:

PROCEDURE ID_QUERY (CUR IN OUT B_CURSOR, PARAM IN NUMBER) IS
BEGIN

OPEN CUR FOR
SELECT A.NUM_RET,
B.NOM_NOM, B.NOM_APE1, B.NOM_APE2,
A.COD_DES,
A.COD_ULT,
C.TIP_NIV,
A.NUM_TIME
FROM AP_1 A, AP_2 B, AP_3 C
WHERE A.COD_AFE = B.COD_PER
AND A.COD_ULT = C.COD_SIT
AND A.NUM_RET = PARAM;

EXCEPTION
WHEN OTHERS THEN
OPEN CUR FOR
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM AP_RET A
WHERE A.NUM_RET = PARAM;

END ID_QUERY;

I have tryed the Query. It works!!!

The thing is SYMPLE!!! But i have the error ORA-6550, and… I don’t get it!!!

I need some help. Somedy please tell me something to try.

What I’ve tried until now is:
— To change the Procedure’s name, at DB and at Block’s Properties Query Data Source Name.
— To create a new Block, with all this stuff again.
— To erase possible spaces left at the Column Names.
— To put the same order the DB Cursor Query Columns has at Block’s Query Data Source Columns.
— I have checked all the Columns Types, and All the Columns Lengths types.

Please, tell me something to try. I’ve been 3h now with this error…

Thank you!!!

Let’s Develop in Oracle

ORA-06550: line n, column n

ORA-06550: line string, column string: string
Cause: Usually a PL/SQL compilation error.
Action: none

ORA-06550 is a very simple exception, and occurs when we try to execute a invalid pl/sql block like stored procedure. ORA-06550 is basically a PL/SQL compilation error. Lets check the following example to generate ORA-06550:

Here we create a stored procedure «myproc» which has some compilation errors and when we tried to execute it, ORA-06550 was thrown by the Oracle database. To debug ORA-06550 we can use «show error» statement as:

Now we know variable SAL is not defined and must be written as c.sal. So we will need to make corrections in «myproc» as

Hi every one!
Can anyone give me some idea about PRAGMA INLINE?

Pragma inline is compiler directive to replace its call with its definition, like we have #define in C language
check this link out: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/inline_pragma.htm

i have created the package
create or replace package maths
as
procedure addition(a in number, b in number,c in out number);
function subtraction(a in number,b in number,c out number) return number;
procedure multiplication(a in number,b in number,c out number);
function division(a in number,b in number,c out number) return number;
end maths;
And i created package body,
create or replace package body maths
as
procedure addition(a in number,b in number,c in out number)
is
begin
c:=a+b;
end addition;
function subtraction(a in number,b in number,c out number) return number
is
begin
c:=a-b;
return c;
end subtraction;
procedure multiplication(a in number,b in number,c out number)
is
begin
c:=a*b;
end multiplication;
function division(a in number,b in number,c out number) return number
is
begin
c:=a/b;
return c;
end division;
end maths;
And then i called the procedure by using the code
set serveroutput on
declare
x number;
y number;
z number;
begin
x:=10;
y:=20;
addition(x,y,z);
dbms_output.put_line(z);
end;
but i am getting the below error:

Error starting at line 148 in command:
declare
x number;
y number;
z number;
begin
x:=10;
y:=20;
addition(x,y,z);
dbms_output.put_line(z);
end;
Error report:
ORA-06550: line 8, column 1:
PLS-00905: object SATYA.ADDITION is invalid
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
06550. 00000 — «line %s, column %s:n%s»
*Cause: Usually a PL/SQL compilation error.
*Action:

HOW CAN I RESOLVE THIS ERROR CAN ANY ONE PLZ HELP ME:

Источник

Accessing TABLE From READ ONLY DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905 (Doc ID 358697.1)

Last updated on JANUARY 29, 2022

Applies to:

Symptoms

Accessing TABLE From READ ONLY (STANDBY) DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905

To reproduce in remote read only Database:

In local database:

drop database link ora102;
create database link ora102 using ‘ora102’;

declare
i number;
begin
select count(*) into i from x@ora102;
end;
/

If local and remote database have version between Oracle9i 9.2.0 to Oracle 11.2 or later it fails with:

If local database have versions between Oracle8 8.0.6 to Oracle8i 8.1.7 it fails with :

SVRMGR> declare
2> i number;
3> begin
4> select count(*) into i from x@ora817;
5> end;
6> /
select count(*) into i from x@ora817;

*
ORA-06550: line 4, column 33:
PLS-00905: object SCOTT.X@ORA817.WORLD is invalid
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

If Local Database has a version higher than Oracle9i 9.2.0 and remote Database a version lower than Oracle9i 9.0.1 then it could fails with ORA-00600 [17069]

Work OK When Accessing TABLE Using SQL From READ ONLY (STANDBY) DATABASE Using DATABASE LINK

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-06550 and PLS-00201 identifier

must be declared

I am attempting to execute a sql script in SQLplus, but I am getting this error:
ORA-06550: line 18, column 7
PLS-00201: identifier ‘IPFMODHISTORY.ALLUSERSALLTABLES’ must be declared
PL/SQL: statement ignored

I don’t understand what the problem is. I have granted EXECUTE privileges on the package to the schema owner. I also tried granting EXECUTE privileges on the package to the user. I know the package is there and the ALLUSERSALLTABLES function exists.

Maybe the allusersalltables function is only declared in the package body, not in the specification of ipfmodhistory package. This way the function cannot be called from outside.

I do have the function declared in the spec as well.

I tried something else now. I tried preceeding the package name with the name of the schema, such as: SCHEMA_NAME.ipfModHistory.AllUsersAllTables,
I get this error: ORA-00942: table or view does not exist

what does following query return?

select * from all_objects where object_name = ‘IPFMODHISTORY’;

Why do you have same package under different schemas? It is not recommended to have your own objects under SYS schema.

From which user was the EXECUTE grant given?

Since there is no package body under SYS schema, I presume it would be for PANTOS schema only?

I don’t know why the package is listed under both the SYS schema and the PANTOS schema. And yes, it is for the PANTOS schema only.
Logged in as the user who is trying to execute this package, I did a:
SELECT * FROM USER_TAB_PRIVS_RECD;
and found that PANTOS granted the user EXECUTE privileges on the package.

Thank you,
Laura

Message was edited by:
The Fabulous LB

I tried creating a synonym, but that didn’t help either. I still get the same error message.

Logged in as PANTOS, I tried your example, CREATE SYNONYM ipfModHistory FOR PANTOS.ipfModHistory, and got this error:
ORA-01471: cannot create a synonym with same name as object

So I tried this:
CREATE SYNONYM modhistory FOR PANTOS.ipfModHistory, and the synonym was created.

Logged in as PANTOS, EXECUTE privileges were granted to the user who will actually be executing the SQL script:
GRANT EXECUTE on ipfModHistory TO user2;
Grant succeeded.

Then I modified the SQL script to call the package & function with the synonym. Maybe this is where I am going wrong? And I get the same type of error as before:
PLS-00201: identifier ‘MODHISTORY.ALLUSERSALLTABLES’ must be declared.
PLS-00201: identifier ‘MODHISTORY.ALLUSERSSINGLETABLE’ must be declared.
. and so on.

What am I missing here?

Can you successfully run ipfmodhistory.allusersalltables as the pantos user? From the error message, it appears that the function is trying to access a table that does not exist, or that pantos does not have directly granted privileges on. If any of the tables used in the function are not owned by pantos, you will either need to make a private synonym in the pantos schema, or prefix the table name wih the table owners name.

When i try to execute this script logged in as PANTOS, I get this error:
ORA-00942: table or view does not exist
ORA-06512: at «PANTOS.IPFMODHISTORY», line 73

I am trying to do a select statement inside my function that selects from
v$xml_audit_trail view and the audit_actions table.

So I verified who the owner of this view/table are:
select owner, table_name from dba_tables where table_name = ‘AUDIT_ACTIONS’;
select owner, object_name from all_objects where object_name = ‘V$XML_AUDIT_TRAIL’;

Источник

Ora 06550 error in sql

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.

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.

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

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

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?

Источник

ORA-06550: line string, column string: string
Cause: Usually a PL/SQL compilation error.
Action: none

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e4100.htm

ORA-06550 is a very simple exception, and occurs when we try to execute a invalid pl/sql block like stored procedure. ORA-06550 is basically a PL/SQL compilation error. Lets check the following example to generate ORA-06550:

SQL> create or replace procedure myproc
  2  as
  3  begin
  4     for c in (select * from scott.emp)
  5     loop
  6             dbms_output.put_line(c.empno || ' ' || c.ename || ' ' || sal);
  7     end loop;
  8  end;
  9  /
Warning: Procedure created with compilation errors.

SQL> exec myproc
BEGIN myproc; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object MYUSER.MYPROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here we create a stored procedure «myproc» which has some compilation errors and when we tried to execute it, ORA-06550 was thrown by the Oracle database. To debug ORA-06550 we can use «show error» statement as:

SQL> show error procedure myproc
Errors for PROCEDURE MYPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: Statement ignored
6/60     PLS-00201: identifier 'SAL' must be declared

Now we know variable SAL is not defined and must be written as c.sal. So we will need to make corrections in «myproc» as

SQL> create or replace procedure myproc
  2  as
  3  begin
  4     for c in (select * from scott.emp)
  5     loop
  6             dbms_output.put_line(c.empno || ' ' || c.ename || ' ' || c.sal);
  7     end loop;
  8  end;
  9  /
Procedure created.

SQL> set serveroutput on

SQL> exec myproc
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
PL/SQL procedure successfully completed.

Related Posts:
— ORA-00936 missing expression
— ORA-00911: invalid character
— ORA-01722: invalid number
— ORA-00904: invalid identifier
— ORA-06502: PL/SQL: numeric or value errorstring

The PLS-00302: component must be declared error occurs when the component of an object is used in the PL/SQL code but not declared in the oracle database object. The data base objects such as type, package, table, cursor may contain member components. If the components are not declared in the respective database objects but referenced in the oracle PL/SQL code, the error PLS-00302: component must be declared will be thrown.

The oracle data base package contains components such as functions, procedures, triggers, variables. The package components are referenced by the name of the type or package. If the package components are not declared either in package declaration or package body but used in the PL/SQL code, the error PLS-00302: component must be declared will be thrown.

Exception

The stack trace for the exception would look like this. The exception would be thrown if the component is not declared in the database object. When the component reference is used in Oracle PL/SQL language, an error would be thrown.

Error report -
ORA-06550: line 4, column 17:
PLS-00302: component 'PUT_LINE1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Cause

The database objects such as type, package, table, cursor can contain multiple components. The database container objects are used to identify these member elements. If you refer to a member component that isn’t declared inside the object but you attempt to call it, an exception would be thrown. The member component is not available. As a result, the database object was unable to provide the member component.

Solution 1

The component is not declared, which is why this exception is thrown. If an error happens when trying to call some system functions. This is most likely due to a misspelled function, procedure, or trigger name. This error would be fixed if the spelling in the code is corrected.

declare
    empname varchar2(10) :='yawin';
begin
    dbms_output.put_line1(empname);
end;

Exception

Error report -
ORA-06550: line 4, column 17:
PLS-00302: component 'PUT_LINE1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

Solution

declare
    empname varchar2(10) :='yawin';
begin
    dbms_output.put_line(empname);
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 2

If you call a member component that isn’t specified in the database type, the type object won’t know what to do with the member variable’s value. The member variable is not declared in the code or the member variable name is misspelled. If a member variable isn’t declared, it can’t be used. declare the variable as a type variable. If the variable name is misspelled, make the appropriate changes.

create or replace type student is object
(
name varchar2(100)
);

set serveroutput on
declare
 st student;
begin
    st := student(1,'Yawin');
    dbms_output.put_line(st.id);
end;

Exception

Error report -
ORA-06550: line 4, column 11:
PLS-00306: wrong number or types of arguments in call to 'STUDENT'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 29:
PLS-00302: component 'ID' must be declared
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"

Solution

create or replace type student is object
(
id numeric(5),
name varchar2(100)
);

set serveroutput on
declare
 st student;
begin
    st := student(1,'Yawin');
    dbms_output.put_line(st.id);
end;

Output

1
PL/SQL procedure successfully completed.

Solution 3

If package members such as function, procedure, and triggers are not declared and used in PL/SQL code, an exception would be thrown. Add a member function, procedure, or trigger definition if it is absent or the declaration isn’t available. If the spelling of a member’s name is incorrect, change it.

CREATE PACKAGE MYPACKAGE AS 
   PROCEDURE PRINTNAME; 
END MYPACKAGE;

CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS  
   PROCEDURE PRINTNAME IS     
   BEGIN 
      dbms_output.put_line('Yawin'); 
   END PRINTNAME; 
END MYPACKAGE; 

DECLARE  
BEGIN 
   MYPACKAGE.PRINTNAME1; 
END; 

Exception

Error report -
ORA-06550: line 3, column 14:
PLS-00302: component 'PRINTNAME1' must be declared
ORA-06550: line 3, column 4:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.

Solution

DECLARE  
BEGIN 
   MYPACKAGE.PRINTNAME; 
END; 

Output

Yawin
PL/SQL procedure successfully completed.

Solution 4

If the variable is declared as an array or list, the elements of the array or list should be invoked. If the array variable is used as a single object variable, the exception will be thrown. The index can be used to refer to the value in the array variable. The error may be fixed by indexing the variable.

create or replace type student is object
(
id numeric(5),
name varchar2(100)
);
declare
 TYPE studentarr IS TABLE OF student INDEX BY PLS_INTEGER;
 st studentarr;
begin
    st := student(1,'Yawin');
   dbms_output.put_line(st.id);
end;

Exception

Error report -
ORA-06550: line 5, column 11:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
ORA-06550: line 6, column 28:
PLS-00302: component 'ID' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored

Solution

set serveroutput on
declare
 TYPE studentarr IS TABLE OF student INDEX BY PLS_INTEGER;
 st studentarr;
begin
    st(1) := student(1,'Yawin');
   dbms_output.put_line(st(1).id);
end;

Output

1
PL/SQL procedure successfully completed.

Solution 5

There’s a chance you don’t have enough permission to execute the objects. If the permission isn’t available, you’ll have to give it to the objects. You may need to contact the database administrator to get this permission. You can access the components if you have the database object permission.

grant execute on MYPACKAGE.PRINTNAME to yawin;

A reader asked me to investigate why he was getting the error being discussed whenever he tried to execute the following PL/SQL block:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
--VALS := WARRAY(null, null);
--LLISTA := WARRAY(null);
--LLISTA := VALS;
dbms_output.put_line(number);
loop
 exit when LLISTA(number)%NOTFOUND;
LLISTA(number) := VALS(number);
Number:= Number + 1;
end loop;
end;
Error report:
ORA-06550: line 0, column 0:
PLS-00707: unsupported construct or internal error [2704]
ORA-06550: line 12, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:



  


                       
  

There are several things worth noting in this tiny PL/SQL snippet, but let’s start off with the main issue.
PLS-00707 is raised because Oracle doesn’t support the %NOTFOUND construct with collections as it is meant to be used with cursors. I must guess that the user got confused with the EXISTS method that allows to check for the presence of non-initiatilized elements in collections (not to be confused with null elements!).

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd');
--LLISTA := VALS;
loop
--exit when LLISTA(number)%NOTFOUND;
exit when not LLISTA.EXISTS(number);
LLISTA(number) := VALS(number);
number:= number + 1;
end loop;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number  - 1));
end;

If the intention of this procedure was to copy the elements of collection VALS into LLISTA, then it must be noted that if both varrays are exactly of the same type and dimension, then we could get rid of the loop altogether:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;

Entire collections can be copied as if they were simple variables when the aforementioned conditions are met.
Note also that the third element of LLISTA (‘e’) has disappeared after the copy, so, don’t expect that Oracle copies only the subset of the collection containing the initialized elements, you’ll need to do it yourself (see later on).
Let’s see what happens when the type looks equal but is not, as in the following example where i duplicated they custom type WARRAY:

declare
type WARRAY is varray(324) OF varchar2(200);
type WARRAY2 is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY2;
number pls_integer := 1;
begin
VALS := WARRAY2('a', 'b');
LLISTA := WARRAY('c', 'd');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;
Error report:
ORA-06550: line 10, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The collection assignment doesn’t work anymore because even if WARRAY and WARRAY2 appear to be perfectly equivalent, they are not the same type, strictly speaking.

Finally, let’s go back to the previous step, the working one, and assume that we want to copy only a subset of the elements of VALS. I guess that for some reason VALS contains either updated or saved values that at a certain point we want to restore in LLISTA, but without erasing the elements of LLISTA that have no corresponding subscript in VALS:

declare
LLISTA string_table_type;
VALS   string_table_type;
BKP    string_table_type;
n      pls_integer := 1;
begin
VALS := string_table_type('a','b');
LLISTA := string_table_type('c','d','e','f');
BKP    := LLISTA;
n      := VALS.count;
select c
bulk collect into LLISTA
from (
 select column_value as c
   from table(VALS)
 union all
 select c
   from (select rownum r, column_value as c
                 from table(BKP))
   where r > n
);
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;
4
a
b
e
f

Again, some remarks: first of all i had to use an externally defined collection type (string_table_type), not a locally defined collection type because PL/SQL doesn’t allow me to use local collections in SQL statements:

create or replace TYPE "STRING_TABLE_TYPE"                                                                                                                                                                                                                                                                                                     as table
 of varchar2(200)

Secondly, i had to create a third collection named BKP where i staged the data of LLISTA because otherwise i’d get an empty subset. Oracle is implicitly erasing LLISTA before performing BULK COLLECT so i cannot access its elements while performing the query.

Is this memory wasting approach faster than manually copying each and every element from VALS to LLISTA?

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS   WARRAY;
n      pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e', 'f');
loop
exit when not VALS.EXISTS(n);
LLISTA(n) := VALS(n);
n:= n + 1;
end loop;
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
 dbms_output.put_line(LLISTA(i));
end loop;
end;
4
a
b
e
f

The performance comparison test is left to the reader as exercise, as some professors say when they are running late for lunch…

See message translations for PLS-00707 and search additional resources.