Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Record datatype (Oracle 9i)
Problem with Record datatype [message #414379] Tue, 21 July 2009 23:01 Go to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Below is the PL/SQL block that is showing errors.Can anyone please tell me whats the problem with it

declare
	   cursor c1 is select * from emp;
	   type emp1_record is record
	   (new_data emp%rowtype);
	   v_emp_records emp1_record;
begin
	   open c1;
	   LOOP
	   fetch c1 into v_emp_records;
	   exit when c1%notfound;
	   insert into emp1 values v_emp_records;
	   end LOOP;
	   close c1;
	   dbms_output.put_line('Done');
end;	


Re: Problem with Record datatype [message #414380 is a reply to message #414379] Tue, 21 July 2009 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Below is the PL/SQL block that is showing errors
My eyes must be failing me, because I don't see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Problem with Record datatype [message #414382 is a reply to message #414379] Tue, 21 July 2009 23:09 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Below is the ERRORS

ORA-06550: line 11, column 29:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 17:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
Re: Problem with Record datatype [message #414383 is a reply to message #414379] Tue, 21 July 2009 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More precisely:
Use SQL*Plus and copy and paste your session.

Also always post your Oracle version woth 4 decimals.

Regards
Michel
Re: Problem with Record datatype [message #414385 is a reply to message #414382] Tue, 21 July 2009 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v_emp_records is a record containing one field which is a record of emp row.
Fetch is expected a record of emp row.

Regards
Michel
Re: Problem with Record datatype [message #414387 is a reply to message #414379] Tue, 21 July 2009 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> type emp1_record is record
Is it a requirement of the homework assignment that TYPE be used?
Re: Problem with Record datatype [message #414389 is a reply to message #414387] Tue, 21 July 2009 23:16 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
See my intention is to copy the entire emp table data to table emp1, for this i m using record datatype.

I have created a table emp1 with same structure as emp table.

So whats wrong in my PL/SQL block that it is shoing the above errors
Re: Problem with Record datatype [message #414390 is a reply to message #414379] Tue, 21 July 2009 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
INSERT INTO EMP1 SELECT * FROM EMP;

accomplishes your goal.
Re: Problem with Record datatype [message #414410 is a reply to message #414389] Wed, 22 July 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So whats wrong in my PL/SQL block that it is shoing the above errors

Michel Cadot wrote on Wed, 22 July 2009 06:11
v_emp_records is a record containing one field which is a record of emp row.
Fetch is expected a record of emp row.

Regards
Michel


Re: Problem with Record datatype [message #414443 is a reply to message #414379] Wed, 22 July 2009 03:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't need to mess about defining you own record types for this - in fact, as your record type is defined in Pl/Sql and not Sql, you'd be doomed to failure anyway.

Just define your record type as a Cursor%rowtype, or table%rowtype is replace your declare block with one of these:
declare
  cursor c1 is select * from emp;
  v_emp_records c1%rowtype;
begin
...
or
declare
  cursor c1 is select * from emp;
  v_emp_records emp%rowtype;
begin
...

Previous Topic: How to schedule a jod to run at 10 AM, 3 PM everyday
Next Topic: bulk collect
Goto Forum:
  


Current Time: Sat Dec 03 10:16:23 CST 2016

Total time taken to generate the page: 0.09129 seconds