1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
| CREATE OR REPLACE PROCEDURE "INSERT_UPDATE_TABLE" (p_LIB_TAB IN Varchar2,
p_LIB_VAL IN Varchar2,
p_champ1 IN Varchar2 DEFAULT NULL,
p_champ2 IN Varchar2 DEFAULT NULL,
p_champ3 IN Varchar2 DEFAULT NULL,
p_champ4 IN Varchar2 DEFAULT NULL,
p_champ5 IN Varchar2 DEFAULT NULL,
p_champ6 IN Varchar2 DEFAULT NULL,
p_champ7 IN Varchar2 DEFAULT NULL,
p_champ8 IN Varchar2 DEFAULT NULL,
p_champ9 IN Varchar2 DEFAULT NULL,
p_champ10 IN Varchar2 DEFAULT NULL,
p_champ11 IN Varchar2 DEFAULT NULL,
p_champ12 IN Varchar2 DEFAULT NULL,
p_champ13 IN Varchar2 DEFAULT NULL,
p_champ14 IN Varchar2 DEFAULT NULL,
p_champ15 IN Varchar2 DEFAULT NULL,
p_champ16 IN Varchar2 DEFAULT NULL,
p_champ17 IN Varchar2 DEFAULT NULL,
p_champ18 IN Varchar2 DEFAULT NULL,
p_champ19 IN Varchar2 DEFAULT NULL,
p_champ20 IN Varchar2 DEFAULT NULL,
p_champ21 IN Varchar2 DEFAULT NULL,
p_champ22 IN Varchar2 DEFAULT NULL,
p_champ23 IN Varchar2 DEFAULT NULL,
p_champ24 IN Varchar2 DEFAULT NULL,
p_champ25 IN Varchar2 DEFAULT NULL,
p_champ26 IN Varchar2 DEFAULT NULL,
p_champ27 IN Varchar2 DEFAULT NULL,
p_champ28 IN Varchar2 DEFAULT NULL,
p_champ29 IN Varchar2 DEFAULT NULL,
p_champ30 IN Varchar2 DEFAULT NULL,
p_champ31 IN Varchar2 DEFAULT NULL,
p_champ32 IN Varchar2 DEFAULT NULL,
p_champ33 IN Varchar2 DEFAULT NULL,
p_champ34 IN Varchar2 DEFAULT NULL,
p_champ35 IN Varchar2 DEFAULT NULL,
p_champ36 IN Varchar2 DEFAULT NULL,
p_champ37 IN Varchar2 DEFAULT NULL,
p_champ38 IN Varchar2 DEFAULT NULL,
p_champ39 IN Varchar2 DEFAULT NULL,
p_champ40 IN Varchar2 DEFAULT NULL,
p_champ41 IN Varchar2 DEFAULT NULL,
p_champ42 IN Varchar2 DEFAULT NULL,
p_champ43 IN Varchar2 DEFAULT NULL,
p_champ44 IN Varchar2 DEFAULT NULL,
p_champ45 IN Varchar2 DEFAULT NULL,
p_champ46 IN Varchar2 DEFAULT NULL,
p_champ48 IN Varchar2 DEFAULT NULL,
p_champ49 IN Varchar2 DEFAULT NULL,
p_champ50 IN Varchar2 DEFAULT NULL,
p_champ51 IN Varchar2 DEFAULT NULL,
p_champ52 IN Varchar2 DEFAULT NULL,
p_champ53 IN Varchar2 DEFAULT NULL,
p_champ54 IN Varchar2 DEFAULT NULL,
p_champ55 IN Varchar2 DEFAULT NULL,
p_champ56 IN Varchar2 DEFAULT NULL,
p_champ57 IN Varchar2 DEFAULT NULL,
p_champ58 IN Varchar2 DEFAULT NULL,
p_champ59 IN Varchar2 DEFAULT NULL,
p_champ60 IN Varchar2 DEFAULT NULL,
p_champ61 IN Varchar2 DEFAULT NULL,
p_champ62 IN Varchar2 DEFAULT NULL,
p_champ63 IN Varchar2 DEFAULT NULL,
p_champ64 IN Varchar2 DEFAULT NULL,
p_champ65 IN Varchar2 DEFAULT NULL,
p_champ66 IN Varchar2 DEFAULT NULL,
p_champ67 IN Varchar2 DEFAULT NULL,
p_champ68 IN Varchar2 DEFAULT NULL,
p_champ69 IN Varchar2 DEFAULT NULL,
p_champ70 IN Varchar2 DEFAULT NULL,
p_champ71 IN Varchar2 DEFAULT NULL,
p_champ72 IN Varchar2 DEFAULT NULL,
p_champ73 IN Varchar2 DEFAULT NULL,
p_champ74 IN Varchar2 DEFAULT NULL,
p_champ75 IN Varchar2 DEFAULT NULL,
p_champ76 IN Varchar2 DEFAULT NULL,
p_champ77 IN Varchar2 DEFAULT NULL,
p_champ78 IN Varchar2 DEFAULT NULL,
p_champ79 IN Varchar2 DEFAULT NULL,
p_champ80 IN Varchar2 DEFAULT NULL,
p_champ81 IN Varchar2 DEFAULT NULL,
p_champ82 IN Varchar2 DEFAULT NULL,
p_champ83 IN Varchar2 DEFAULT NULL,
p_champ84 IN Varchar2 DEFAULT NULL,
p_champ85 IN Varchar2 DEFAULT NULL,
p_champ86 IN Varchar2 DEFAULT NULL,
p_champ87 IN Varchar2 DEFAULT NULL,
p_champ88 IN Varchar2 DEFAULT NULL,
p_champ89 IN Varchar2 DEFAULT NULL,
p_champ90 IN Varchar2 DEFAULT NULL,
p_champ91 IN Varchar2 DEFAULT NULL,
p_champ92 IN Varchar2 DEFAULT NULL,
p_champ93 IN Varchar2 DEFAULT NULL,
p_champ94 IN Varchar2 DEFAULT NULL,
p_champ95 IN Varchar2 DEFAULT NULL,
p_champ96 IN Varchar2 DEFAULT NULL,
p_champ97 IN Varchar2 DEFAULT NULL,
p_champ98 IN Varchar2 DEFAULT NULL,
p_champ99 IN Varchar2 DEFAULT NULL,
p_champ100 IN Varchar2 DEFAULT NULL
)
IS
TYPE TAB_CHAMP is table of varchar2(200)index by binary_integer ;
TAB1 TAB_CHAMP ;
val_param varchar2(50) ;
-- chaine varchar2(900) ;
chaine2 varchar2(900) ;
chaine3 varchar2(900) ;
val_champ varchar2(100) ;
ret varchar2(100) ;
i pls_integer := 0 ;
j pls_integer := 1 ;
Begin
Loop
i := i + 1 ;
val_param := 'p_champ'||i;
--DBMS_OUTPUT.PUT_LINE (' val_param: '||val_param);
--execute immediate 'declare p_champ1 varchar2(100) := :1; begin :2 := '||p_champ1||'; end;' USING IN val_param, OUT ret;
-- execute immediate 'declare p_champ1 varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;
-- execute immediate 'declare p_champ'||i||' varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;
execute immediate 'declare p_champ'||i||' varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;
DBMS_OUTPUT.put_line('val_param= '||val_param ||'. retour:'|| ret);
exit;
IF ret is null THEN
EXIT;
END IF;
TAB1(i):= ret;
End loop ;
commit;
chaine2:= TAB1(j);
FOR j IN 2 .. i LOOP
chaine2:= chaine2 ||','|| TAB1(j);
END LOOP;
COMMIT;
chaine3:= 'INSERT INTO '||p_LIB_TAB||' ('||p_LIB_VAL||') SELECT '||chaine2|| ' FROM DUAL';
EXECUTE IMMEDIATE chaine3;
COMMIT;
End ; |
Partager