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
1
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
I have the following small function that does not compile:
function f_query_01 Return interval Day to second is
start_time timestamp(3);
end_time timestamp(3);
time_diff interval Day to second;
c_query_number number;
begin
start_time := systimestamp;
select count(*) into c_query_number from wg; <--This is the line that errors out
end_time := systimestamp;
time_diff := start_time - end_time;
return time_diff;
end f_query_01;
The compiler gives me the following errors:
Error(29,3): PL/SQL: SQL Statement ignored
Error(29,44): PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here
What is causing this error and how can I fix it?
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.
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.
Этот код SQL выдает мне ошибку «Ошибка в строке 2: PL / SQL: инструкция проигнорирована». Я работаю над приложением SQL oracle express / APEX: я пробовал все, что мог придумать, и каждый раз это вызывает у меня разные проблемы.
CREATE or replace TRIGGER remove_artista
instead of delete on V_ARTISTA
REFERENCING old AS orow
FOR EACH ROW
BEGIN
if exists(select * from Utilizadores where pessoaID = orow.pessoaID) then
delete from Pessoas where pessoaID = orow.pessoaID;
ELSE
delete from Artistas where pessoaID = orow.pessoaID;
delete from Pessoas where pessoaID = orow.pessoaID;
end if;
END;
Вид:
create or replace view v_artista as
select
pessoaID, nome_p, sexo, data_nasc, nome_art, biografica
from Pessoas natural inner join Artistas;
РЕДАКТИРОВАТЬ: исправлена небольшая опечатка в коде.
2 ответа
Лучший ответ
Полная ошибка, которую я получаю от вашего триггера, выглядит следующим образом:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: Statement ignored
2/4 PLS-00204: function or pseudo-column 'EXISTS' may be used inside
a SQL statement only
По сути, проблема в том, что вы не можете сказать if exists(...)
, как вы это делаете. Oracle не позволяет вам.
Вместо этого попробуйте выбрать количество совпадающих строк в таблице Utilizadores
в локальной переменной, а затем использовать это в своем операторе if
:
CREATE or replace TRIGGER remove_artista
instead of delete on V_ARTISTA
REFERENCING old AS orow
FOR EACH ROW
DECLARE
l_count INTEGER;
BEGIN
select count(*)
into l_count
from Utilizadores
where pessoaID = :orow.pessoaID;
if l_count > 0 then
delete from Pessoas where pessoaID = :orow.pessoaID;
ELSE
delete from Artistas where pessoaID = :orow.pessoaID;
delete from Pessoas where pessoaID = :orow.pessoaID;
end if;
END;
Мне также нужно было заменить orow
на :orow
. После внесения этого изменения ваш триггер скомпилирован для меня.
4
Luke Woodward
24 Май 2014 в 00:06
Я не думаю, что вы можете использовать конструкцию IF EXISTS, чтобы проверить, существует ли строка. Вы можете использовать SELECT COUNT(*) INTO <a variable>
. Однако вам может не понадобиться проверять, существует ли строка. Следующий код, вероятно, подойдет:
CREATE OR REPLACE TRIGGER remove_artista
INSTEAD OF DELETE ON V_ARTISTA
FOR EACH ROW
BEGIN
DELETE FROM PESSOAS
WHERE PESSOAID = :OLD.PESSOAID;
DELETE FROM Artistas
WHERE PESSOAID = :OLD.PESSOAID
AND NOT EXISTS (SELECT 1 FROM UTILIZADORES WHERE PESSOAID = :OLD.PESSOAID);
END;
Строка из PESSOAS
в любом случае будет удалена. Строка из ARTISTAS
будет удалена, только если PESSOAID
не существует в UTILIZADORES
.
Ссылки :
Проверить, существует ли запись на форуме OTN
2
Joseph B
24 Май 2014 в 00:11
SummaryAfter |
Applies toBizagi |
SymptomsAfter ORA-06550: line 1, column 7: The |
CauseThe |
Solution1. grant execute on In 2. 3. |
Yesterday, while learning oracle stored procedures, I wrote a demo of stored procedures. The statement is as follows:
CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20)) AS psssal TESTDELETE.TESTID%TYPE; BEGIN SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME; UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME; DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||' After the raise'||(psssal+1000)); end; /
The idea is to find the number type field through the varchar(20) type field of the table, and then change the number type field. The table structure is as follows:
create table TESTDELETE ( TESTID NUMBER, TESTNAME VARCHAR2(20) )
Call the stored procedure, and the result is as follows:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20)) 2 AS 3 psssal TESTDELETE.TESTID%TYPE; 4 BEGIN 5 SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME; UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME; DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||' After the raise'||(psssal+1000)); 8 end; 9 / Warning: Procedure created with compilation errors. SET SERVEROUTPUT ON; BEGIN RAISESALARY('name2091'); COMMIT; END; SQL> 2 3 4 5 / RAISESALARY('name2091'); * ERROR at line 2: ORA-06550: line 2, column 5: PLS-00905: object TEST.RAISESALARY is invalid ORA-06550: line 2, column 5: PL/SQL: Statement ignored SQL>
An error occurred: the stored procedure is not valid.
what? The stored procedure that Mingming just established. Then I turn to the above sentence after creation, Warning: Procedure created with compilation errors.
That is to say, there is an error in creating the stored procedure. OK, look for the stored procedure to see what is wrong.
emmmm, looking back for a long time, just a few lines, I can’t see what’s wrong, baidu, google.
Later, I saw this in StackOverFlow. The original link is as follows:
https://stackoverflow.com/questions/48497140/oracle-sql-stored-procedure-object-invalid
The landlord found a saying in it:
You can’t give a size or precision restriction for the data type of a formal parameter to a function or procedure, so NUMBER(10,0) should just be NUMBER;
That is, you cannot specify the size or precision of the data for the parameters of functions and stored procedures. OK, looking back at my parameter, I see that varchar2(20) clearly specifies the precision for the parameter type. You need to change to varchar2.
Or write the table name. Field% TYPE directly.
After the change, run as follows:
CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR) 2 AS 3 psssal TESTDELETE.TESTID%TYPE; 4 BEGIN 5 SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME; UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME; DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||' After the raise'||(psssal+1000)); 8 end; 9 / Procedure created. BEGIN RAISESALARY('name2091'); 3 COMMIT; 4 END; 5 / The original salary2091 After the raise3091 PL/SQL procedure successfully completed. SQL>
After the modification, it is clear that there is no warning. The Procedure created appears
Run successfully! Problem solving.
I try to create one test procedure but it gets
Error(4,1): PL/SQL: SQL Statement ignored ** and ** Error(12,20): PL/SQL: ORA-00942: table or view does not exist when I try to compile. Then I test select statement in next session, it run successfully. Could you please help me.
create or replace PROCEDURE TEST_1
AS
BEGIN
SELECT
A.EMPLOYEE_ID AS "Employee ID",
A.FIRST_NAME||' '||A.LAST_NAME AS "Name",
B.DEPARTMENT_NAME AS "Department Name",
A.EMAIL AS "Email Address",
A.PHONE_NUMBER AS "Contact Number",
C.STREET_ADDRESS||', '||C.CITY||', '||NVL(C.STATE_PROVINCE,C.CITY) AS "Address"
FROM HR.EMPLOYEES A
LEFT OUTER JOIN HR.DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
LEFT OUTER JOIN HR.LOCATIONS C
ON B.LOCATION_ID = C.LOCATION_ID;
END;
asked Mar 28, 2018 at 6:16
Looks like a right problem. Do you realy need «HR.» ?
Try
create PROCEDURE TEST_1 AS BEGIN
CURRENT_USER
SELECT A.EMPLOYEE_ID AS "Employee ID",
A.FIRST_NAME || ' ' || A.LAST_NAME AS "Name",
B.DEPARTMENT_NAME AS "Department Name",
A.EMAIL AS "Email Address",
A.PHONE_NUMBER AS "Contact Number",
C.STREET_ADDRESS || ', ' || C.CITY || ', ' ||
NVL(C.STATE_PROVINCE, C.CITY) AS "Address"
FROM EMPLOYEES A
LEFT OUTER JOIN DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS C
ON B.LOCATION_ID = C.LOCATION_ID;
END;
And have a look at GRANT (for example)
GRANT EXECUTE ON PROCEDURE <user>.test_1 TO <role>
answered Mar 28, 2018 at 7:28
5