Statement ignored oracle ошибка - Не ошибается лишь тот, кто ничего не делает!

I’m doing tutorial from website http://www.plsqltutorial.com/plsql-procedure/. I have run the code on apex:

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
    UPDATE EMPLOYEES
    SET salary = salary + salary * in_percent / 100
    WHERE employee_id = in_employee_id;
END;

but I got error:

Error at line 6: PL/SQL: SQL Statement ignored

4. ) IS
5. BEGIN
6.  UPDATE EMPLOYEES
7.  SET salary = salary + salary * in_percent / 100
8.  WHERE employee_id = in_employee_id;

I have checked and table employees is there. What is the problem and how to fix it?

ZygD

21.4k39 gold badges74 silver badges99 bronze badges

asked Jan 15, 2012 at 16:06

WHERE employee_id = in_employee_id;

in_employee_id is not declared, neither is it a parameter. The function definition says the parameter is in_employee so your code block should be

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
    UPDATE EMPLOYEES
    SET salary = salary + salary * in_percent / 100
    WHERE employee_id = in_employee;
END;

Looking at the article, I see that you’ve made a typo while creating the function, the function declaration as per the article is

 CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,

So, if you change your code to the above, no changes are required to the update statement.

answered Jan 15, 2012 at 16:14

Sathyajith BhatSathyajith Bhat

21.2k21 gold badges94 silver badges134 bronze badges

To avoid such typos, it is better to use Dot Notation (or namespaces) instead of the prefixes. In the context of a procedure, this is the name of the procedure.

Check out the following code:

create or replace procedure adjust_salary(
    employee_id hr.employees.employee_id%type, percent number) is
begin
    update hr.employees set 
        salary = salary + salary * percent / 100
    where employee_id = adjust_salary.employee_id;
end;
/
Procedure ADJUST_SALARY compiled

answered Oct 9, 2020 at 11:46

0xdb0xdb

3,5291 gold badge19 silver badges36 bronze badges

The parameter is in_employee but you’re using in_employee_id in your update. Change to:

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
    UPDATE EMPLOYEES
    SET salary = salary + salary * in_percent / 100
    WHERE employee_id = in_employee;
END;

answered Jan 15, 2012 at 16:14

John DoyleJohn Doyle

7,4055 gold badges33 silver badges40 bronze badges

The parameter name «in_employee» is different while you are using different variable name «in_employee_id» in the query

CREATE OR REPLACE PROCEDURE adjust_salary(
in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
in_percent IN NUMBER

) IS
BEGIN
UPDATE EMPLOYEES
SET salary = salary + salary * in_percent / 100
WHERE employee_id = in_employee;
END;

answered Aug 26, 2020 at 6:28

In my case (Oracle SQL Developer 19.2, Oracle version 12c), I just had to save the procedure and the error was gone.

E.g., enter some key, delete it (the procedure wasn’t changed, but now you can save it using Ctrl+s). After the save the error disappeared and I was able to run the procedure.

answered Jul 13, 2021 at 12:54

ZygDZygD

21.4k39 gold badges74 silver badges99 bronze badges

Источник

Learn the cause and how to resolve the ORA-06550 error message in Oracle.

Description

When you encounter an ORA-06550 error, the following error message will appear:

ORA-06550: line num, column num: str

Cause

You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Refer to the line and column numbers (in the error message) to find the compilation error and correct it. Then try recompiling your code.

Let’s look at an example of how to resolve an ORA-06550 error. For example, if you created a procedure called TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    vnum number;
  4  BEGIN
  5    vnum := vAnotherNum;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

This procedure was created with compilation errors. So if we try to execute this procedure, we will get an ORA-06550 error as follows:

SQL> execute TestProc();
BEGIN TestProc(); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object EXAMPLE.TESTPROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

You can run the SHOW ERROR command to view the errors as follows:

SQL> show error procedure TestProc;
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1	 PL/SQL: Statement ignored
5/9	 PLS-00201: identifier 'VANOTHERNUM' must be declared

As you can see, the error is caused by the variable called VANOTHERNUM not being declared. To resolve this error, we can modify our TestProc procedure to declare the variable as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc
  2  AS
  3    vnum number;
  4    vAnotherNumber number;
  5  BEGIN
  6    vAnotherNum := 999;
  7    vnum := vAnotherNum;
  8  END;
  9  /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06550 error has been resolved.

SQL> execute TestProc();

PL/SQL procedure successfully completed.

Источник

Вы почти все сделали нормально. Немного о процедуре,

Выполните приведенную ниже команду (возможно, вы пропустили ошибку)

ALTER PROCEDURE student_grade COMPILE;
Warning: Procedure altered with compilation errors

вам не нужно делать p_average параметр как IN OUT, OUT должно быть достаточно, так как вы рассчитываете его внутри.

чтобы присвоить какое-либо значение параметру OUT, вам не нужно использовать SET. Допускается только присваивание с использованием оператора присваивания. Смотри ниже

CREATE OR REPLACE PROCEDURE student_grade
(
   p_school_no IN lessons.school_number%TYPE
  ,p_lesson    OUT lessons.lesson_name%TYPE
  ,p_midterm_1 OUT lessons.midterm_notu_1%TYPE
  ,p_midterm_2 OUT lessons.midterm_notu_2%TYPE
  ,p_final     OUT lessons.final_notu%TYPE
  ,p_average   OUT NUMBER
) IS
BEGIN
   SELECT d.lesson
         ,d.midterm_notu_1
         ,d.midterm_notu_2
         ,d.final_notu
   INTO   p_lesson
         ,p_midterm_1
         ,p_midterm_2
         ,p_final
   FROM   lessons d
   WHERE  d.shool_number = p_school_no;
   --assign to the output variable for average
   p_average := (((d.midterm_notu_1 * 25) / 100) + ((d.midterm_notu_2 * 30) / 100) + ((d.final_notu * 45) / 100));
END;
/

Я считаю, что из-за ошибки в процедуре вы не смогли протестировать, после внесения вышеуказанных изменений она должна работать.

Вы также можете протестировать его с помощью анонимного блока PL/SQL вместо окна команд SQL, что было бы проще. Например

DECLARE
    --assign the input directly here in the declare section
    v_school_no lessons.school_number%type := 10;
    v_lesson lessons.lesson_name%type;
    v_midterm_1 lessons.midterm_notu_1%type;
    v_midterm_2  lessons.midterm_notu_2%type;
    v_final lessons.final_notu%type;
    v_average NUMBER;
BEGIN
    -- call the procedure
    student_grade(  
        v_lesson,
        v_midterm_1,
        v_midterm_2  ,
        v_final,
        v_average );
    DBMS_OUTPUT.put_line ('Student Grade');
    DBMS_OUTPUT.put_line ('School Number: ' ||v_school_no);
    DBMS_OUTPUT.put_line ('Midterm 1: ' || v_midterm_1);
    DBMS_OUTPUT.put_line ('Midterm 2: ' || v_midterm_2  );
    DBMS_OUTPUT.put_line ('Final: ' || v_final);
    DBMS_OUTPUT.put_line ('Average: ' || v_average );
END;
/

Сообщите мне, если это решит вашу проблему;

ПЕРЕСМОТРЕННЫЙ ОТВЕТ О КОНКРЕТНОЙ ПРОБЛЕМЕ ИСПОЛЬЗОВАНИЯ И ИСПОЛЬЗОВАНИЯ ИНСТРУМЕНТА

Я настоятельно рекомендую / предлагаю вам изучить документацию PL/SQL перед тем, как продолжить выполнение следующего задания. Это поможет вам понять возникающие ошибки и исправить их. Также есть много видеороликов об инструменте SQL Developer, как их эффективно использовать. Проверьте их тоже.

Возвращаясь к проблеме, которую я пробовал на своей машине, в скрипте есть много проблем, которые мне приходилось исправлять одно за другим, просматривая сообщения об ошибках. Пожалуйста, найдите пункты и окончательное решение, которые должны работать, иначе я закончу.

Проблемы: есть отличия в названиях столбцов в таблице от написанного вами кода

WHERE d.shool_number = p_school_no; -> shool_number не существующий столбец, вероятно, d.school_number
v_lesson lessons.lesson_name%type; -> фактический столбец lesson и нет lesson_name. Я могу сказать это из вашего пункта select в процедуре
p_lesson OUT lessons.lesson_name%type, -> то же, что и в пункте 2
p_average := (((d.midterm_notu_1 * 25)/100) + ((d.midterm_notu_2 * 30)/100) + ((d.final_notu * 45)/100)); — нельзя ссылаться на столбцы таким образом, что это значит с "d." где код не знает, к чему он относится. d вы использовали в запросе выбора как псевдоним таблицы lessons и с помощью оператора select заканчивается область действия dзаканчивает там сам. Поскольку вы уже перенесли значения в выходные переменные, такие какp_midterm_1, p_midterm_2, p_finalиспользуйте их вместо них.
Кроме того, убедитесь, что оператор select возвращает только одну строку для каждого school_number=20201754 иначе вы закончите с ошибкой ORA-01422: exact fetch returns more than requested number of rowsи есть другие способы справиться с этим. (пока ничего не скажу по этому поводу)
Заключительный момент, когда вы пытаетесь протестировать процедуру, например student_grade( v_lesson, v_midterm_1, v_midterm_2 , v_final, v_average ); -> вы передаете неправильное количество аргументов в процедуру, не включая v_school_no в качестве первого параметра.

Однако я создал свою собственную настройку и соответственно изменил процедуру и тест, см. Ниже.

--table definition
create table lessons (school_number number,lesson varchar2(100),midterm_notu_1 number,midterm_notu_2 number,final_notu number);
--inserting unique rows per school_number
insert into lessons values(20201754,'Maths',35,55,85);
insert into lessons values(20201755,'Science',45,65,95);

-- to enable the dbms_output
SET SERVEROUTPUT ON;

--procedure definition
CREATE OR REPLACE PROCEDURE student_grade(
        p_school_no IN lessons.school_number%type,
        p_lesson OUT lessons.lesson%type,
        p_midterm_1 OUT lessons.midterm_notu_1%type,
        p_midterm_2  OUT lessons.midterm_notu_2%type,
        p_final OUT lessons.final_notu%type,
        p_average OUT NUMBER
    )
IS
BEGIN
    SELECT
    d.lesson,
    d.midterm_notu_1,
    d.midterm_notu_2,
    d.final_notu
    INTO
        p_lesson,
        p_midterm_1,
        p_midterm_2,
        p_final
    FROM lessons d
    WHERE d.school_number = p_school_no;
    p_average := (((p_midterm_1 * 25)/100) + ((p_midterm_2 * 30)/100) + ((p_final * 45)/100));
END student_grade;
/

--testing the procedure    
DECLARE
    v_school_no lessons.school_number%type := 20201754;
    v_lesson lessons.lesson%type;
    v_midterm_1 lessons.midterm_notu_1%type;
    v_midterm_2  lessons.midterm_notu_2%type;
    v_final lessons.final_notu%type;
    v_average NUMBER;
BEGIN
    student_grade(
        v_school_no,
        v_lesson,
        v_midterm_1,
        v_midterm_2  ,
        v_final,
        v_average );
    DBMS_OUTPUT.put_line ('Student Grade');
    DBMS_OUTPUT.put_line ('School Number: ' ||v_school_no);
    DBMS_OUTPUT.put_line ('Midterm 1: ' || v_midterm_1);
    DBMS_OUTPUT.put_line ('Midterm 2: ' || v_midterm_2  );
    DBMS_OUTPUT.put_line ('Final: ' || v_final);
    DBMS_OUTPUT.put_line ('Average: ' || v_average );
END;
/

Источник

Содержание

Let’s Develop in Oracle
ORA-06550: line n, column n
6 comments:
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)
Applies to:
Symptoms
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!
ORA-06550 and PLS-00201 identifier
Comments
Ora 06550 error in sql
Asked by:
Question
All replies

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.

Источник

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:

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?

Источник

Are you getting the ORA-06550 error when running an SQL statement? Learn the cause of this error and the solution in this article.

Demonstration of the Error

To demonstrate this error, I can run this code that creates a stored procedure:

CREATE OR REPLACE PROCEDURE testProcedure AS
  textValue VARCHAR2(3);
BEGIN
  textValue := someOtherValue;
END;

If I compile this procedure, I get this message:

Procedure TESTPROCEDURE compiled
Errors: check compiler log

Now, I can run this procedure:

EXEC testProcedure;

Error starting at line : 8 in command -
EXEC testProcedure
Error report -
ORA-06550: line 1, column 7:
PLS-00905: object INTRO_USER.TESTPROCEDURE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

As you can see, I get the error code ORA-06550: line X column X.

What caused this error?

ORA-06550 Cause

ORA-06550 is caused by a PL/SQL compilation error – an error in your PL/SQL code.

The cause of the error is actually dependent on the error it is linked to.

It’s a bit of a decoy. It says “Hey, I’ve found an error, but your actual error is over there!”

As you might notice, when you get an ORA-06550 error, it is accompanied by a second error message just below it.

You should be able to see this if you’re running SQL Developer or Toad (or many other IDEs).

Go to View > Log, and a Log window should appear.

If not, you can run the SHOW ERRORS command.

SHOW ERRORS;

Errors for PROCEDURE INTRO_USER.TESTPROCEDURE:
LINE/COL ERROR
-------- -------------------------------------------------------
4/3      PL/SQL: Statement ignored
4/16     PLS-00201: identifier 'SOMEOTHERVALUE' must be declared

This will show the error message that causes this error to appear, which could be one of many errors.

To resolve the ORA-06550 error, first fix the error that comes with it.

The error that comes with it is also more descriptive of the actual issue.

For example, in our example above, I also got a PLS-00201 error.

This was because I had mentioned a variable called someOtherValue but this was not declared anywhere.

To fix this, I would need to declare the variable, correct a typo if I spelt it wrong, or use another value.

Once you have fixed the error, recompile the code.

For example:

CREATE OR REPLACE PROCEDURE testProcedure AS
  textValue VARCHAR2(3);
BEGIN
  textValue := 'ABC';
END;

Procedure TESTPROCEDURE compiled

EXEC testProcedure;

PL/SQL procedure successfully completed.

Hopefully this article has helped you resolve the ORA-06550 error message.

Источник

Description

Cause

Resolution

Option #1

ПЕРЕСМОТРЕННЫЙ ОТВЕТ О КОНКРЕТНОЙ ПРОБЛЕМЕ ИСПОЛЬЗОВАНИЯ И ИСПОЛЬЗОВАНИЯ ИНСТРУМЕНТА

Let’s Develop in Oracle

ORA-06550: line n, column n

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)

Applies to:

Symptoms

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!

ORA-06550 and PLS-00201 identifier

Ora 06550 error in sql

<img decoding="async" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Asked by:

Question

Demonstration of the Error

ORA-06550 Cause