get data from a procedure and show in a table-CodePudding

these are the facts:

this procedure is an example


     CREATE OR replace PROCEDURE Demo_test(
                v_1 IN NUMBER DEFAULT NULL,
                v_2 IN DATE DEFAULT NULL,
                v_n IN VARCHAR2 DEFAULT NULL,
                v_c OUT SYS_REFCURSOR
            )
            AS
            BEGIN       
        open v_c for
        select dat_1, dat_2, dat_n from(
             select vw.od1, xt.od2, xt.odn   
             from xview vw
             join xtbl xt
             order by vw.od1 asc
             FETCH FIRST 100 ROWS ONLY
             ) fidata;        
        END demo_test;

> - and i have this other bad procedure, i need to fix:

CREATE OR replace PROCEDURE Demo_test2(
    v_1 IN NUMBER DEFAULT NULL,
    v_2 IN DATE DEFAULT NULL,
    v_n IN VARCHAR2 DEFAULT NULL,
    v_c OUT SYS_REFCURSOR
)
as
begin
execute immediate ('create table xtbl (val1 varchar2, val2 date, val2 number)');

END demo_test2;

i need to fill this table xtbl(Demo_test2) with the Demo_test data

and show the result

CodePudding user response：

In pseudo-code

declare
  rc sys_refcursor;
  v1, v2, v3  number;  -- ie, the datatypes of dat1,dat2,...,datn
begin 
  Demo_test(in_parameters, rc);

  loop
    fetch rc into v1,v2,v3;
    exit when rc%notfound;
    
    execute immediate 'insert into xtbl values (:1,:2,:3)' using v1,v,v3;
  end loop;
end;

The 'execute imediate' is because we can't reference 'xtbl' yet because it is does not exist at compile time.

But I'd be having a look at the requirements here - we generally dont want to be dynamically creating tables on the fly.