Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 474 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Please help me. I am getting error while running the following Oracle 11.2g SQL code:

DECLARE
x NUMBER := 0;
iSalary Number;
iAllowance Number;
BEGIN
iSalary := 585.90;
iAllowance := 58.59;
LOOP
x := x+1;
INSERT INTO EmployeeTab (BasicSalary)
select CASE WHEN GL = 'L'||x THEN 23 + (iSalary*(x-1)) END
FROM EmployeeTab;
INSERT INTO EmployeeTab (BasicAllowance)
select CASE WHEN GL = 'M'||x THEN 32 + (iAllowance*(x-1)) END
FROM EmployeeTab;
IF x > 14 THEN
EXIT;
END IF;
END LOOP;
END;
Best Answer
-
I'm guessing that you're actually trying to update the EmployeeTab table rather than really wanting to Insert records to it. By inserting individual columns like that, it's likely you're not providing important other information to the inserted row, such as the required primary key or suchlike.
As an update, you're probably looking for something like...
update EmployeeTab set BasicSalary = case when gl like 'L%' then 23+(585.90*(to_number(substr(gl,2))-1)) else BasicSalary end ,BasicAllowance = case when gl like 'M%' then 32+(58.59*(to_number(substr(gl,2))-1)) else BasicAllowance end where regexp_like(gl, '^(L|M)(1|2|3|4|5|6|7|8|9|10|11|12|13|14)$')
That will update all existing rows that have a GL value that is L1, L2 ... L14, or M1, M2 ... M14 and then update the BasicSalary or BasicAllowance column based on the numeric value against the L or M gl value.
Answers
-
Hi, @User_6TYJG
Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. (In this case, it looks like the table may be empty when you run the code, so you may not need any INSERT statements; just the CREATE TABLE statement.) Always post your complete Oracle version (e.g. 18.4.0.0.0).
I am getting error while running the following Oracle 11.2g SQL code:
What is the error? Error messages are designed to help people locate and fix the mistakes. Don't hide that valuable information from the people who want to help you. Post the complete error message (or messages; PL/SQL errors often occur in groups), including line numbers
What you posted is PL/SQL code, not SQL code. SQL and PL/SQL are two different languages, like Latin and Pig Latin.
-
When i execute the code it stuck while processing...
I am using SQL Developer and Oracle APEX.
-
I'm guessing that you're actually trying to update the EmployeeTab table rather than really wanting to Insert records to it. By inserting individual columns like that, it's likely you're not providing important other information to the inserted row, such as the required primary key or suchlike.
As an update, you're probably looking for something like...
update EmployeeTab set BasicSalary = case when gl like 'L%' then 23+(585.90*(to_number(substr(gl,2))-1)) else BasicSalary end ,BasicAllowance = case when gl like 'M%' then 32+(58.59*(to_number(substr(gl,2))-1)) else BasicAllowance end where regexp_like(gl, '^(L|M)(1|2|3|4|5|6|7|8|9|10|11|12|13|14)$')
That will update all existing rows that have a GL value that is L1, L2 ... L14, or M1, M2 ... M14 and then update the BasicSalary or BasicAllowance column based on the numeric value against the L or M gl value.
-
Hi, @User_6TYJG
When i execute the code it stuck while processing...
So you're not getting any error; is that right?
In any event, post a little sample data, the results you want from that data, and explain how you get those results from the given data. In the case of a DML issue (such as INSERT or UPDATE), the INSERT statements you post should show the state of the tables before the DML starts, and the results will be what the changed table looks like after the DML is finished.
-
I never though of that..
I really appreciate.
Thank you.