Is there any way to use a function in a cursor for loop to insert value to table?

Question

Try to put a function (that populate sequence number) to a variable and use it in a cursor loop to insert it to each row of record, but error said invalid identifier to the v_refnr variable. PL/SQL: ORA-00904: "V_REFNR": invalid identifier.

The closest reference I found is this [1]Call a function in a cursor for loop

My function has no parameter. The select statement (contains other columns) I made in the cursor has no problem output the value. I proceed to use the for loop to iterate each record then insert a ref number to test if it works.

Here is my code I tried so far

Declare
 v_refnr number;
 cursor c_book IS
   --working select statement--
 r_book c_book%ROWTYPE

begin
  v_refnr := get_refnr; <-- function

  for r_book in c_book loop
  insert into some_tbl refnr
  values v_refnr;  
  end loop;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error insert record ' || SUBSTR(SQLERRM, 1, 250));
    ROLLBACK;
end;

If the above is the working code, it will insert value to the empty table.

Answer 1

You don't need a cursor loop for doing an Insert operation. Simply do an INSERT INTO SELECT *, it is far efficient than loops.

So, If you want to call the function each time, use it in the select.

BEGIN
  INSERT INTO some_tbl (refnr,col1,col2,col3)
     SELECT get_refnr,col1,col2,col3 from yoursourcetable; 
                                          --Your working select statement
    EXCEPTION
     WHEN OTHERS THEN
    dbms_output.put_line('Error insert record ' || substr(SQLERRM,1, 250));
 ROLLBACK;
END;

If you want to use the output of the function as a constant and only once then you may need a variable to store it and to it can used in the above select query in place of the function call.