Insert inside PL/SQL Procedure [message #366039] |
Wed, 11 October 2000 18:09  |
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 - try this trick [message #366062 is a reply to message #366040] |
Tue, 24 October 2000 13:18  |
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.
|
|
|