Home » SQL & PL/SQL » SQL & PL/SQL » Compiling in Debug Mode (Oracle 11g / SQL Developer)
Compiling in Debug Mode [message #659266] Tue, 10 January 2017 14:35 Go to next message
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 #659267 is a reply to message #659266] Tue, 10 January 2017 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
do not do in PL/SQL that which can be done in plain SQL.

What results when you invoke procedure via sqlplus?
Re: Compiling in Debug Mode [message #659283 is a reply to message #659267] Wed, 11 January 2017 08:01 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
In SQLPlus, it ran fine:
ITATP @ rvi2t2 > exec itatp.increase_salary2BC (20, .85)
PL/SQL procedure successfully completed.

Then I ran this in SQL Developer from an untitled SQL window:
OPTION 1
begin
exec itatp.increase_salary2BC (20, .85)
end;

OPTION 2
begin
execute immediate itatp.increase_salary2BC (20, .85)
end;

Results on OPTION 1 & 2 were the same:
I received the same message described in my original post.


Re: Compiling in Debug Mode [message #659284 is a reply to message #659283] Wed, 11 January 2017 08:04 Go to previous messageGo to next message
Littlefoot
Messages: 21016
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
begin exec?

Are you sure?
Re: Compiling in Debug Mode [message #659285 is a reply to message #659283] Wed, 11 January 2017 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
>Then I ran this in SQL Developer from an untitled SQL window:
>OPTION 1
>begin
>exec itatp.increase_salary2BC (20, .85)
>end;

not as above but as below


begin
itatp.increase_salary2BC (20, .85)
end;
Re: Compiling in Debug Mode [message #659286 is a reply to message #659285] Wed, 11 January 2017 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 12550
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 #659287 is a reply to message #659286] Wed, 11 January 2017 10:24 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
(New to SQL Plus) sorry for what looks to be elementary errors....
Re: Compiling in Debug Mode [message #659288 is a reply to message #659287] Wed, 11 January 2017 10:28 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Running this in SQL Developer:

begin
itatp.increase_salary2BC (20, .85)
end;

Yielded this error:
Error starting at line 1 in command:
begin
itatp.increase_salary2BC (20, .85)
end;
Error report:
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

:= . ( % ;
The symbol ";" was substituted for "END" to continue.
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Re: Compiling in Debug Mode [message #659290 is a reply to message #659288] Wed, 11 January 2017 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
semicolon required below!

begin
itatp.increase_salary2BC (20, .85); --on this line
end;
Re: Compiling in Debug Mode [message #659291 is a reply to message #659290] Wed, 11 January 2017 10:57 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
I took your post verbatim; (the only semi was after "end")

>>not as above but as below
begin
itatp.increase_salary2BC (20, .85)
end; <<
(sorry BlackSwan, my bad)

It ran fine after I added the semi at the end of the package name...thank you. Smile

While I am grateful for the help here in the forum, I realize many of my questions/errors right now are attributed to a possible need for more structured learning. I'm sort of going at it piecemeal right now which might not be the best way.
What might be better is a more structured approach.
In years past when I did Visual Basic programming, I have used Microsoft's Step-By-Step book series as a learning tool and found that structured approach to be very helpful for my learning style. That said, I have looked around at some, but wanted to ask if there are some good step-by-step tutorial sites you have worked through and would recommend? Thanks.
Re: Compiling in Debug Mode [message #659292 is a reply to message #659291] Wed, 11 January 2017 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
consider learning GOOGLE before starting on PL/SQL

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=learn+pl%2Fsql+in+21+days+pdf
Re: Compiling in Debug Mode [message #659293 is a reply to message #659292] Wed, 11 January 2017 11:06 Go to previous messageGo to next message
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 #659294 is a reply to message #659293] Wed, 11 January 2017 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
On a scale of 1 (can't spell SQL) to 10 (SQL guru) where would you rate yourself on basic SQL competency?
Which other RDBMS have you used ("regularly")?
Re: Compiling in Debug Mode [message #659296 is a reply to message #659294] Wed, 11 January 2017 11:22 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
PL/SQL: 2 to 3
SQL Server: about a 3
MS-Access SQL: 5 to 6 (I was a VBA programmer for 14 years linking custom designed Access Front ends to Oracle and SQL Server back ends for the telecomm industry. (this might not matter so much here)


Re: Compiling in Debug Mode [message #659297 is a reply to message #659296] Wed, 11 January 2017 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
SQL != PL/SQL
I asked about SQL; not PL/SQL.
Each is separate & distinct language with their own distinct syntax rules.
What is valid in one may not be valid in the other.
Oracle is substantially different from SQL Server in some fundamental ways; yet they both contain SQL engines.
FWIW, PL/SQL is loosely based upon ADA.
https://en.wikipedia.org/wiki/Ada_(programming_language)
the whole Oracle documentation set can be found at URL below
https://docs.oracle.com/database/121/nav/portal_booklist.htm
Re: Compiling in Debug Mode [message #659298 is a reply to message #659297] Wed, 11 January 2017 12:03 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
I am familiar with general SQL and realize there are specific syntax rules unique to PL/SQL, SQL Server SQL and MS-Access SQL.

Thank you.
Re: Compiling in Debug Mode [message #659299 is a reply to message #659298] Wed, 11 January 2017 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25225
Registered: January 2009
Location: SoCal
Senior Member
2 fine Manuals worth reading

https://docs.oracle.com/database/121/CNCPT/toc.htm

https://docs.oracle.com/database/121/LNPLS/toc.htm
Re: Compiling in Debug Mode [message #659300 is a reply to message #659299] Wed, 11 January 2017 12:26 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
I will have a look at them thank you.
Re: Compiling in Debug Mode [message #659302 is a reply to message #659300] Wed, 11 January 2017 14:52 Go to previous messageGo to next message
Littlefoot
Messages: 21016
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 Go to previous message
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.
Previous Topic: Recursive SQL Insert
Next Topic: add a sequential number to a query
Goto Forum:
  


Current Time: Thu Feb 23 15:06:42 CST 2017

Total time taken to generate the page: 0.12391 seconds