Compiling in Debug Mode [message #659266] |
Tue, 10 January 2017 14:35 |
|
Irish88
Messages: 36 Registered: August 2016 Location: Plains of Kansas
|
Member |
|
|
I am getting this error when trying to run my script in debug mode with a breakpoint so that I can step through the code via F8 line by line.
I'm not sure what is wrong, actually.
error:
The symbol "<an identifier> was inserted before "PROCEDURE" to continue.
ORA-06512: at line 58.
There are only 56 lines of code in this script according to SQL Developer;
Not sure where the "58" is coming from.
Here is the code:
create or replace
PROCEDURE Increase_Salary2BC (pDEPT_ID IN RLNEmployees.DEPT_ID%TYPE,
pincrease_pct IN NUMBER)
AS
-- Columns from table: (EmpID, EmpFName, EmpMName,
-- EmpLName, Salary, DEPT_ID)
-- ==================================
-- To run this Procedure from an anonymous block:
-- BEGIN
-- --Increases Salary by multiplying current Salary by .10
-- --for all employees in Dept 20.
-- Increase_Salary2BC (20, .10);
-- END;
-- ==================================
TYPE employee_ids_t IS TABLE OF RLNEmployees.EmpID%TYPE
INDEX BY PLS_INTEGER;
all_employee_ids employee_ids_t;
all_eligible_ids employee_ids_t;
bln_eligible BOOLEAN;
BEGIN
SELECT EmpID
BULK COLLECT INTO all_employee_ids
FROM RLNEmployees
WHERE dept_id = increase_salary2BC.pDEPT_ID;
FOR indx IN 1 .. all_employee_ids.COUNT
LOOP
if all_employee_ids (indx) >= 103 then
bln_eligible := true;
else
bln_eligible := false;
end if;
IF bln_eligible
THEN
all_eligible_ids (all_eligible_ids.COUNT + 1) :=
all_employee_ids (indx);
dbms_output.put_line( '#1b--in the For/Next Looper now! all_employee_ids (indx): '
|| all_employee_ids (indx) );
END IF;
END LOOP;
FORALL indx IN 1 .. all_eligible_ids.COUNT
UPDATE RLNEmployees emp
SET emp.salary =
emp.salary
+ emp.salary * increase_salary2BC.pIncrease_PCT
WHERE emp.Empid = all_eligible_ids (indx);
dbms_output.put_line('#2--increase_salary2BC.pIncrease_PCT is: '
|| increase_salary2BC.pIncrease_PCT);
commit;
END Increase_Salary2BC;
Thank you for your assistance.
|
|
|
|
|
|
|
Re: Compiling in Debug Mode [message #659286 is a reply to message #659285] |
Wed, 11 January 2017 08:38 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
exec is a sqlplus command. It means take following procedure call and run it in a BEGIN/END block.
Your option 1 would have errored out in sqlplus as well.
execute immediate is for dynamic SQL. It needs to be followed by a string (the code you want to run surrounded by quotes) or a variable that contains that string.
You can't follow it with a normal procedure call and expect it to work.
|
|
|
|
|
|
|
|
Re: Compiling in Debug Mode [message #659293 is a reply to message #659292] |
Wed, 11 January 2017 11:06 |
|
Irish88
Messages: 36 Registered: August 2016 Location: Plains of Kansas
|
Member |
|
|
Yes, I am familiar with Google. That is not what I am talking about.
I was merely asking (respectfully) if there were some sites that people have *actually used* for their own education, that they enjoyed using and would recommend to others. (see the last line of my previous post)
[Updated on: Wed, 11 January 2017 11:08] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Compiling in Debug Mode [message #659302 is a reply to message #659300] |
Wed, 11 January 2017 14:52 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I know, you're on Oracle 11g. However, I still love 10g's documentation page as it contains two useful sections: "Getting started" and "Most popular".
As you aren't going to be a DBA (yet?), I'd suggest you to read the Concepts book (just as BlackSwan has told you), SQL Reference (so that you'd get used to Oracle SQL), PL/SQL User's Guide and Reference (which is, basically, the second book BlackSwan mentioned) and - perhaps the most interesting for you - Application Developer's Guide - Fundamentals (which doesn't work in HTML format; shame on you, Oracle!, but it will open a PDF file). Of course, feel free to read the same documents which cover the 11g version.
|
|
|
Re: Compiling in Debug Mode [message #659310 is a reply to message #659302] |
Thu, 12 January 2017 07:23 |
|
Irish88
Messages: 36 Registered: August 2016 Location: Plains of Kansas
|
Member |
|
|
Thanks Littlefoot, I appreciate your response. I got a PL/SQL book (O'Reilly Books) that I am reviewing also.
My background is software development with Visual Basic and VBA Programming over in the Microsoft arena. While some things are similar with Oracle (basic SQL), there are many differences too. I am confident that continued repetition will drill the points home as I work with Oracle more in depth. Right now I am doing PL/SQL development in Oracle and I also have the opportunity to delve into SQL Server at the same time. Learning the differences side by side is educational.
|
|
|