David, Thanks.... Don't get me wrong I understand where you're coming from. I'm still trying to get all the parts to build the statement properly and am only receiving a vague scenario.
I will go through both your examples; thanks. v/r Roy On Apr 28, 3:29 pm, ddf <[email protected]> wrote: > On Apr 28, 3:14 pm, Roy Arellano <[email protected]> wrote: > > > > > > > I would like make it a stored procedure so I can use an external tool > > to call it and process it accordingly. Also, I would like multiple > > rows to be inserted into table B; not just one; so I assume that there > > would be the need to use variables. Any ideas? > > > v/r > > Roy > > > On Apr 28, 3:04 pm, ddf <[email protected]> wrote: > > > > On Apr 28, 2:38 pm, Roy Arellano <[email protected]> wrote: > > > > > I am trying to create a stored procedure in which I take a select > > > > statement for multiple rows out of one table and insert them into > > > > another table when calling this stored procedure. Any suggestions? > > > > > Goal: > > > > 1. Create Stored Procedure > > > > 2. Select Distinct data columns from table A (many rows) > > > > 3. Insert result set into table B > > > > > Thanks. > > > > Why is it 'necessary' to make this a stored procedure? Unless this > > > is homework a simple insert into ... select ... would be much faster > > > and easier to implement. > > > > David Fitzjarrell- Hide quoted text - > > > - Show quoted text - > > I feel you don't understand the power of a fairly simple insert > statement: > > SQL> insert into lotsa_objects > 2 select * from dba_objects; > > 69314 rows created. > > SQL> > > That is far more than one row. > > Obviously you're of the mind a stored proedure is the only way to > accomplish this; so be it. You'd best learn the most efficient way to > process such inserts using BULK COLLECT and FORALL: > > SQL> CREATE OR REPLACE PROCEDURE fast_way IS > 2 > 3 TYPE myarray IS TABLE OF lotsa_objects%ROWTYPE; > 4 l_data myarray; > 5 > 6 CURSOR r IS > 7 SELECT * > 8 FROM user_objects; > 9 > 10 BEGIN > 11 OPEN r; > 12 LOOP > 13 FETCH r BULK COLLECT INTO l_data LIMIT 1000; > 14 > 15 FORALL i IN 1..l_data.COUNT > 16 INSERT INTO lotsa_objects VALUES l_data(i); > 17 > 18 EXIT WHEN r%NOTFOUND; > 19 END LOOP; > 20 COMMIT; > 21 CLOSE r; > 22 END fast_way; > 23 / > > Procedure created. > > SQL> > SQL> exec fast_way > > PL/SQL procedure successfully completed. > > SQL> select count(*) > 2 from lotsa_objects; > > COUNT(*) > ---------- > 300 > > SQL> > > David Fitzjarrell- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
