SQL Oracle get the all columns of a cursor to use in a insert into

Question

I want to create a procedure in which I use a cursor to select certain lines and then insert them in another table. I wonder if there is a notation to write it faster. For instance here is the complete procedure

create or replace procedure myProc as
  Cursor lines is
    select * from table1 where c = '2';
   begin
      for line in lines loop
        insert into table2 values(line.a, line.b, line.c, line.d ....);
      end loop;
   end;
/

I want to know if I can replace the 'insert into' line by something like

insert into table2 values(line.something);

or

insert into tables2 values(something(line));

(I think a view could be more effective but it's not the question here.)

Gordon Linoff · Accepted Answer · 2017-06-09 11:56:48Z

2

Absolutely:

create or replace procedure myProc as
begin
  insert into table2( . . .)
    select a, b, c, d, . . 
    from table1
    where c = '2';
end;
/

You should list the columns in table2 as well. That is what the table2( . . . ) means.

answered Jun 9, 2017 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ccile Over a year ago

I didn't know this way... So there is nothing with the cursor ? Why should I list the columns in table2 ?

Gordon Linoff Over a year ago

@Ccile . . . You should avoid cursors. Period. Sometimes they are necessary. In that case, use them. You should list the columns so you are sure that the right values are inserted into the right columns.

Ccile Over a year ago

Why ? Ok for the columns.

abrittaf · Accepted Answer · 2017-06-09 14:44:34Z

0

Despite the following code should work in case structure of both tables were identical...

INSERT INTO target_table
  SELECT * FROM source_table;

... you should avoid this way of programming because any column addition to source or target table will end in SQL became invalid.

answered Jun 9, 2017 at 14:44

abrittaf

5475 silver badges12 bronze badges

4 Comments

Ccile Over a year ago

The use of * was only to say that I select a lot of columns and I don't want to write line.name1, line.name2 etc. I know I have to write it at least once (twice with the cursor) and my question was about how to use cursor and only write one time the names.

abrittaf Over a year ago

You can use a dynamic cursor and query ALL_TAB_COLUMNS in order to produce the column list for both INSERT and SELECT.

Ccile Over a year ago

ALL_TAB_COLUMNS is for * ? If I want all columns except one ?

abrittaf Over a year ago

you simply add "WHERE COLUMN_NAME != column_I_dont_want

Rachcha · Accepted Answer · 2017-06-09 14:58:08Z

Looks like you're looking for a short-cut in order to avoid qualifying column names in an insert statement.

Well, although I do not recommend this, it can be done bug it's a little tedious. Here is an example:

if you have a table employee (employee_id, employee_name, designation). You'll need to create two types in the database-

a. An object type which is similar to employee-

    create type employee_obj as object  (employee_id   number(28,0),
                                         employee_name varchar2(100),
                                         designation   varchar2(30));

b. Create the set type of this record-

    create type employee_obj_set is table of employee_obj;

c. In your PL/SQL procedure you can use something like:

    DECLARE
        empset employee_obj_set;
        -- More variables here
    BEGIN
        -- other operations
        -- populate your empset
        -- other operations
        INSERT INTO employee
            SELECT * FROM table(empset);
        -- other operations
    END;
    /

Allen · Accepted Answer · 2017-06-10 08:31:02Z

0

For faster inserts use bulk collect/FORALL insert instead of singular inserts.Find below sample...

DECLARE
    CURSOR lines is select * from table1 where c = '2';
    type lines_ty is table of table2%rowtype;
    l_lines_t2 lines_ty;
BEGIN
    OPEN lines;
    LOOP
    FETCH lines BULK COLLECT INTO l_lines_t2 LIMIT 500;
    FORALL i IN 1..l_lines_t2.COUNT
        INSERT INTO table2 VALUES l_lines_t2(i);
        EXIT WHEN lines%NOTFOUND;
    END LOOP;
    CLOSE lines;
END;

answered Jun 10, 2017 at 8:31

Allen

481 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQL Oracle get the all columns of a cursor to use in a insert into

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related