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
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 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
- 468 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
I want to insert variable values into a table

I have tried but not able insert the values into the summary table. Please assume all the tables are created.
Declare tot_records number(10); records_loaded number(10); process_start_time timestamp; lv_count number(10); begin tot_records := select count(1) into lv_count from employees; insert into department select * from employees; records_loaded := sql%rowcount; process_start_time := current_timestamp; insert into summary(total,tot_loads,start_time) values(tot_records,records_loaded,process_start_time); end;
Best Answer
-
You don't assign SELECTed data to a variable via assignment statement. You use INTO clause:
Declare tot_records number(10); records_loaded number(10); process_start_time timestamp; begin select count(1) into tot_records from employees; insert into department select * from employees; records_loaded := sql%rowcount; process_start_time := current_timestamp; insert into summary(total,tot_loads,start_time) values(tot_records,records_loaded,process_start_time); end;
Also why are you inserting employee data into department table???
SY.
Answers
-
Hi, @Albert Chao
Please assume all the tables are created.
No; whenever you have a question, actually post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from that data. For DML questions (such as INSERT) the results will be what the changed table looks like after the DML is completed.
-
You don't assign SELECTed data to a variable via assignment statement. You use INTO clause:
Declare tot_records number(10); records_loaded number(10); process_start_time timestamp; begin select count(1) into tot_records from employees; insert into department select * from employees; records_loaded := sql%rowcount; process_start_time := current_timestamp; insert into summary(total,tot_loads,start_time) values(tot_records,records_loaded,process_start_time); end;
Also why are you inserting employee data into department table???
SY.
-
Hi, @Albert Chao
tot_records := select count(1) into lv_count from employees;
won't work in PL/SQL; you can't have SELECT immediately after :=.
Do something like this instead:
select count (*) into tot_records from employees;
-
I have tried but not able insert the values into the summary table.
In this case, the mistake was so obvious that we didn't need more information from you. But in general, if something "doesn't work", you won't get a lot of help if you just say "it doesn't work". You need to tell us exactly what is not working. Does compilation fail? (It would, in this case - with an error message pointing exactly to the place in the code where the error is.) Does the compilation succeed, but then you get an error thrown when you execute the block? Does execution go through, but you get the wrong result? Etc.
Please keep this in mind next time you ask a question (here or on any other forum). Also, make sure to always state your database version - in many cases (even though not in your problem here), that is the key piece of information.