1

I have a table (base-info) with some columnslike(name, family ,gen, city ,age, job, id) that have data; Now I want to build a new table by base-info data's randomly and Ofcourse I want to use for loop & insert into valuse sysntax; I wrote the commands but I faced ora_06550 error;

 Declare
  c1 number(3):=0;
  c2 number(3):=0;
 begin 
 for c1 in 1..200 loop
 for c2 in 1..200 loop 
    insert into person p 
    (
     id, 
     name, 
     family,
     gen,
     job,
     city, 
     age
    )
 values 
   ( 
   seq_person_id,
   (select b.name from base_info b where b.id=c1) ,
   ( SELECT * FROM  (SELECT b.family  FROM   base_info b ORDER BY DBMS_RANDOM.RANDOM)  WHERE  rownum=1;) ,
   (select b.gen from base_info b where b.id=c1) ,
   (SELECT * FROM  (SELECT b.job  FROM   base_info b ORDER BY DBMS_RANDOM.RANDOM) WHERE  rownum=1;) ,
   (SELECT * FROM  (SELECT b.city  FROM   base_info b ORDER BY DBMS_RANDOM.RANDOM) WHERE  rownum=1;) , 
   (SELECT * FROM  (SELECT b.age  FROM   base_info b ORDER BY DBMS_RANDOM.RANDOM) WHERE  rownum=1;)
   ); 
  end loop;
  end loop; 
end; 


 select t.*, t.rowid from PERSON t ;

I want to run it in PL/SQL . it is error :

ORA-06550: line 7, column 6: PL/SQL: ORA-00921: unexpected end of SQL command ORA-06550: line 7, column 6: PL/SQL: SQL Statement ignored ORA-06550: line 18, column 112: PLS-00103: Encountered the symbol ")" when expecting one of the following:

( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array ORA-06550: line 20, column 106: PLS-00103: Encountered the symbol ";" when expecting one of the following:

) * & - + / at mod remainder rem with <an exponent (**)> and or group having intersect minus order start union where connect || multiset The symbol ";" was ignored. ORA-06550: line 21, column 107: PLS-00103: Encountered the symbol ";" when expecting one of the following:

) * & - + / at mod remainder rem with <an exponent (**)> and or group having intersect minus order start union where connect || multiset The symbol ";" was ignored. ORA-06550: line 22, column 106: PLS-00103: Encountered the symbol ";" when expecting one of the following:

) * & - + / at mod remainder rem with <an exponent (**)> and or group having intersect minus order start union where connect || multiset

Hossein GH
  • 11
  • 3
  • 1
    Please edit your question to include the full error stack - if you're running all of that as a script you're missing a `/` after the anonymous block, or depending on how you're running it you might need to make the query completely separate, but look for other error messages - and maybe explain why you want to do this in PL/SQL. ('Of course' and 'wrong' aren't very helpful...) – Alex Poole Jan 26 '23 at 11:45
  • 1
    You have semicolons in your subqueries, which is wrong, as the errors are saying; remove all of those and see what is complains about after that. – Alex Poole Jan 26 '23 at 12:33
  • i did but didnt answerd. as you know in the orginal "INSERT INTO" sysntax there is simicolon ... – Hossein GH Jan 28 '23 at 18:53

0 Answers0