Home » SQL & PL/SQL » SQL & PL/SQL » Insert inside PL/SQL Procedure
Insert inside PL/SQL Procedure [message #366039] Wed, 11 October 2000 18:09 Go to next message
Sunil Shah
Messages: 1
Registered: October 2000
Junior Member
I am writting following Insert statement in PL/SQL Procedure.

create or replace procedure Test1 as
begin
insert into emp1
SELECT Seq_emp.nextval,
name,
city
FROM emp
ORDER BY city ASC;

end;
end Test1;

This procedure does not get complied in Oracel8i. It gives following error message:
Error Text = PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:

. , @ ; return returning <an identifier>
<a double-quoted delimited-identifier> group having intersect minus partition start subpartition union where connect SAMPLE_

Error Text = PLS-00103: Encountered the symbol "END"

I have two table (EMP and EMP1 ) and one sequence (SEQ_EMP) in my schema. I have 4 rows entered in EMP and I want to enter those rows in EMP1 in ordered fashion with new ID created for each row. EMP and EMP1 has foloowing fields:
ID number(10)
Name varchar2(50)
City varchar2(20)

Thanks,
Sunil
Re: Insert inside PL/SQL Procedure [message #366040 is a reply to message #366039] Thu, 12 October 2000 01:12 Go to previous messageGo to next message
sandeep
Messages: 110
Registered: October 2000
Senior Member
You can't have insert into a table from select with order by clause.Either use sleect w/o order
or insert through cursor.

Regards,
Sandeep
Re: Insert inside PL/SQL Procedure - try this trick [message #366062 is a reply to message #366040] Tue, 24 October 2000 13:18 Go to previous message
Andrew
Messages: 144
Registered: March 1999
Senior Member
To make the result of a query/subquery ordered, try the following trick:
SELECT a.col1, ROWNUM ordered_rownum
FROM TAB1 a, dual b
WHERE a.col1 = b.Dummy (+)

You will see that the data in table TAB1 is sorted on col1. You should be able to apply this to your situation too. Ora 8.1.6 does support ordering in subqueries as a new feature - see this too.
Previous Topic: Selecting data from nested tables
Next Topic: Urgent : Error Message problem
Goto Forum:
  


Current Time: Fri Mar 29 05:35:00 CDT 2024