Home » SQL & PL/SQL » SQL & PL/SQL » inserting data from one table to another in a different format
inserting data from one table to another in a different format [message #37117] Fri, 18 January 2002 12:20 Go to next message
Rama
Messages: 17
Registered: October 2001
Junior Member
Hello,

I would like to know what I am doing wrong with the following code when I write a data from one another to another table in a different format. I am now learning PL/SQL and am using this as
a test problem.

I have a table called students which has the following columns:
SQL> desc student
Name Null? Type
----------- ----------------STUDENT_ID NUMBER(10)
COURSE VARCHAR2(10)
CREDITS NUMBER(10)

I have created another table called new_student which has the following structure:
SQL> desc new_student
Name Null? Type
----------------------------------------- -------- STUDENT_ID NUMBER(10)
COURSE VARCHAR2(30)
CREDITS VARCHAR2(10)
The data in the student table is:
SQL> select * from student;

STUDENT_ID COURSE CREDITS
---------- ---------- ----------
1 Science 4
1 History 3
2 Science 4
2 History 3
2 Math 4
3 Sociology 3
3 Psycology 3

I want to store the STUDENT table data in NEW_STUDENT table as:
STUDENT ID COURSES CREDITS
1 Science/History 4/3
2 Science/History/Math 4/3/4
3 Sociology/Psycology 3/3

This is my code:
DECLARE
-- Get all the student IDs
CURSOR studentlist_cursor IS select distinct student_id from student ORDER BY student_id;
-- Get the information for each student ID
CURSOR courses_cursor(nstudentid number) is select course, credits from student where student_id=nstudentid;
nIndex INTEGER;
pltcourses DBMS_SQL.VARCHAR2_TABLE;
pltcredits DBMS_SQL.NUMBER_TABLE;
newcourses varchar2(50);
newcredits varchar2(10);

BEGIN
FOR student_rec IN studentlist_cursor
LOOP
nIndex:=0;
-- Loop through each student ID from student_rec to get the
--courses and creditsstudent ID
FOR courses_rec in courses_cursor(student_rec.student_id)
LOOP
nIndex:=nIndex+1;
pltcourses(nIndex):=courses_rec.course;
-- Store the course data for each student ID in newcourses
-- while doing so check if it is null or put a '/' between each course
loop
if newcourses is null then
newcourses:=pltcourses(nIndex);
else
newcourses:=newcourses||'/'||pltcourses(nIndex);
end if;
end loop;

pltcredits(nIndex):=courses_rec.credits;
--Store credits data in variable newcredits
--If the newcredits is not null then put a '/' between each credits
loop
if newcredits is null then
newcredits:=pltcredits(nIndex);
else
newcredits:=newcredits||'/'||pltcredits(nIndex);
end if;
end loop;
-- Insert student id, newcourses, newcredits into NEW_STUDENT
INSERT INTO NEW_STUDENT
(student_id ,
course,
credits
) VALUES
(student_rec.student_id,
newcourses,
newcredits
);
COMMIT;
-- come out of the student id record and go to the next student --id record
END LOOP;
-- come out of the loop
END LOOP;
END;

While doing so I get an error message:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 23.
The error is at the line:
newcourses:=newcourses||'/'||pltcourses(nIndex);

Can anyone help me by pointing out where the mistake is?

Thank you for reading this long mail.

Thank you.
Rama.
Re: inserting data from one table to another in a different format [message #37118 is a reply to message #37117] Fri, 18 January 2002 13:04 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare
cursor c1 is select distinct(student_id) student_id from student ;
cursor c2(sno number) is select * from student where student_id=sno;
l_course varchar2(30);
l_credits varchar2(10);
begin
for crec in c1 loop
  for crec1 in c2(crec.student_id) loop
    if c2%rowcount=1 then
     l_course := crec1.course   ;
     l_credits := crec1.credits;
    else
     l_course := l_course||'/'||crec1.course;
      l_credits := l_credits||'/'||crec1.credits;
    end if;
  end loop;
insert into new_student values(crec.student_id,l_course,l_credits);
end loop;
commit;
end;
Previous Topic: SQL* Plus 3.3
Next Topic: How to hide input
Goto Forum:
  


Current Time: Tue Aug 11 08:47:52 CDT 2020