do $$ declare c int; strval varchar; intval int; nrows int default 30; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)'); call dbms_sql.bind_variable(c, 'nrows', nrows); call dbms_sql.define_column(c, 1, strval); call dbms_sql.define_column(c, 2, intval); perform dbms_sql.execute(c); while dbms_sql.fetch_rows(c) > 0 loop call dbms_sql.column_value(c, 1, strval); call dbms_sql.column_value(c, 2, intval); raise notice 'c1: %, c2: %', strval, intval; end loop; call dbms_sql.close_cursor(c); end; $$; NOTICE: c1: ahoj1, c2: 1 NOTICE: c1: ahoj2, c2: 2 NOTICE: c1: ahoj3, c2: 3 NOTICE: c1: ahoj4, c2: 4 NOTICE: c1: ahoj5, c2: 5 NOTICE: c1: ahoj6, c2: 6 NOTICE: c1: ahoj7, c2: 7 NOTICE: c1: ahoj8, c2: 8 NOTICE: c1: ahoj9, c2: 9 NOTICE: c1: ahoj10, c2: 10 NOTICE: c1: ahoj11, c2: 11 NOTICE: c1: ahoj12, c2: 12 NOTICE: c1: ahoj13, c2: 13 NOTICE: c1: ahoj14, c2: 14 NOTICE: c1: ahoj15, c2: 15 NOTICE: c1: ahoj16, c2: 16 NOTICE: c1: ahoj17, c2: 17 NOTICE: c1: ahoj18, c2: 18 NOTICE: c1: ahoj19, c2: 19 NOTICE: c1: ahoj20, c2: 20 NOTICE: c1: ahoj21, c2: 21 NOTICE: c1: ahoj22, c2: 22 NOTICE: c1: ahoj23, c2: 23 NOTICE: c1: ahoj24, c2: 24 NOTICE: c1: ahoj25, c2: 25 NOTICE: c1: ahoj26, c2: 26 NOTICE: c1: ahoj27, c2: 27 NOTICE: c1: ahoj28, c2: 28 NOTICE: c1: ahoj29, c2: 29 NOTICE: c1: ahoj30, c2: 30 do $$ declare c int; strval varchar; intval int; nrows int default 30; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)'); call dbms_sql.bind_variable(c, 'nrows', nrows); call dbms_sql.define_column(c, 1, strval); call dbms_sql.define_column(c, 2, intval); perform dbms_sql.execute(c); while dbms_sql.fetch_rows(c) > 0 loop strval := dbms_sql.column_value_f(c, 1, strval); intval := dbms_sql.column_value_f(c, 2, intval); raise notice 'c1: %, c2: %', strval, intval; end loop; call dbms_sql.close_cursor(c); end; $$; NOTICE: c1: ahoj1, c2: 1 NOTICE: c1: ahoj2, c2: 2 NOTICE: c1: ahoj3, c2: 3 NOTICE: c1: ahoj4, c2: 4 NOTICE: c1: ahoj5, c2: 5 NOTICE: c1: ahoj6, c2: 6 NOTICE: c1: ahoj7, c2: 7 NOTICE: c1: ahoj8, c2: 8 NOTICE: c1: ahoj9, c2: 9 NOTICE: c1: ahoj10, c2: 10 NOTICE: c1: ahoj11, c2: 11 NOTICE: c1: ahoj12, c2: 12 NOTICE: c1: ahoj13, c2: 13 NOTICE: c1: ahoj14, c2: 14 NOTICE: c1: ahoj15, c2: 15 NOTICE: c1: ahoj16, c2: 16 NOTICE: c1: ahoj17, c2: 17 NOTICE: c1: ahoj18, c2: 18 NOTICE: c1: ahoj19, c2: 19 NOTICE: c1: ahoj20, c2: 20 NOTICE: c1: ahoj21, c2: 21 NOTICE: c1: ahoj22, c2: 22 NOTICE: c1: ahoj23, c2: 23 NOTICE: c1: ahoj24, c2: 24 NOTICE: c1: ahoj25, c2: 25 NOTICE: c1: ahoj26, c2: 26 NOTICE: c1: ahoj27, c2: 27 NOTICE: c1: ahoj28, c2: 28 NOTICE: c1: ahoj29, c2: 29 NOTICE: c1: ahoj30, c2: 30 drop table if exists foo; create table foo(a int, b varchar, c numeric); do $$ declare c int; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)'); for i in 1..100 loop call dbms_sql.bind_variable(c, 'a', i); call dbms_sql.bind_variable(c, 'b', 'Ahoj ' || i); call dbms_sql.bind_variable(c, 'c', i + 0.033); perform dbms_sql.execute(c); end loop; end; $$; select * from foo; a | b | c -----+----------+--------- 1 | Ahoj 1 | 1.033 2 | Ahoj 2 | 2.033 3 | Ahoj 3 | 3.033 4 | Ahoj 4 | 4.033 5 | Ahoj 5 | 5.033 6 | Ahoj 6 | 6.033 7 | Ahoj 7 | 7.033 8 | Ahoj 8 | 8.033 9 | Ahoj 9 | 9.033 10 | Ahoj 10 | 10.033 11 | Ahoj 11 | 11.033 12 | Ahoj 12 | 12.033 13 | Ahoj 13 | 13.033 14 | Ahoj 14 | 14.033 15 | Ahoj 15 | 15.033 16 | Ahoj 16 | 16.033 17 | Ahoj 17 | 17.033 18 | Ahoj 18 | 18.033 19 | Ahoj 19 | 19.033 20 | Ahoj 20 | 20.033 21 | Ahoj 21 | 21.033 22 | Ahoj 22 | 22.033 23 | Ahoj 23 | 23.033 24 | Ahoj 24 | 24.033 25 | Ahoj 25 | 25.033 26 | Ahoj 26 | 26.033 27 | Ahoj 27 | 27.033 28 | Ahoj 28 | 28.033 29 | Ahoj 29 | 29.033 30 | Ahoj 30 | 30.033 31 | Ahoj 31 | 31.033 32 | Ahoj 32 | 32.033 33 | Ahoj 33 | 33.033 34 | Ahoj 34 | 34.033 35 | Ahoj 35 | 35.033 36 | Ahoj 36 | 36.033 37 | Ahoj 37 | 37.033 38 | Ahoj 38 | 38.033 39 | Ahoj 39 | 39.033 40 | Ahoj 40 | 40.033 41 | Ahoj 41 | 41.033 42 | Ahoj 42 | 42.033 43 | Ahoj 43 | 43.033 44 | Ahoj 44 | 44.033 45 | Ahoj 45 | 45.033 46 | Ahoj 46 | 46.033 47 | Ahoj 47 | 47.033 48 | Ahoj 48 | 48.033 49 | Ahoj 49 | 49.033 50 | Ahoj 50 | 50.033 51 | Ahoj 51 | 51.033 52 | Ahoj 52 | 52.033 53 | Ahoj 53 | 53.033 54 | Ahoj 54 | 54.033 55 | Ahoj 55 | 55.033 56 | Ahoj 56 | 56.033 57 | Ahoj 57 | 57.033 58 | Ahoj 58 | 58.033 59 | Ahoj 59 | 59.033 60 | Ahoj 60 | 60.033 61 | Ahoj 61 | 61.033 62 | Ahoj 62 | 62.033 63 | Ahoj 63 | 63.033 64 | Ahoj 64 | 64.033 65 | Ahoj 65 | 65.033 66 | Ahoj 66 | 66.033 67 | Ahoj 67 | 67.033 68 | Ahoj 68 | 68.033 69 | Ahoj 69 | 69.033 70 | Ahoj 70 | 70.033 71 | Ahoj 71 | 71.033 72 | Ahoj 72 | 72.033 73 | Ahoj 73 | 73.033 74 | Ahoj 74 | 74.033 75 | Ahoj 75 | 75.033 76 | Ahoj 76 | 76.033 77 | Ahoj 77 | 77.033 78 | Ahoj 78 | 78.033 79 | Ahoj 79 | 79.033 80 | Ahoj 80 | 80.033 81 | Ahoj 81 | 81.033 82 | Ahoj 82 | 82.033 83 | Ahoj 83 | 83.033 84 | Ahoj 84 | 84.033 85 | Ahoj 85 | 85.033 86 | Ahoj 86 | 86.033 87 | Ahoj 87 | 87.033 88 | Ahoj 88 | 88.033 89 | Ahoj 89 | 89.033 90 | Ahoj 90 | 90.033 91 | Ahoj 91 | 91.033 92 | Ahoj 92 | 92.033 93 | Ahoj 93 | 93.033 94 | Ahoj 94 | 94.033 95 | Ahoj 95 | 95.033 96 | Ahoj 96 | 96.033 97 | Ahoj 97 | 97.033 98 | Ahoj 98 | 98.033 99 | Ahoj 99 | 99.033 100 | Ahoj 100 | 100.033 (100 rows) truncate foo; do $$ declare c int; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)'); for i in 1..100 loop perform dbms_sql.bind_variable_f(c, 'a', i); perform dbms_sql.bind_variable_f(c, 'b', 'Ahoj ' || i); perform dbms_sql.bind_variable_f(c, 'c', i + 0.033); perform dbms_sql.execute(c); end loop; end; $$; select * from foo; a | b | c -----+----------+--------- 1 | Ahoj 1 | 1.033 2 | Ahoj 2 | 2.033 3 | Ahoj 3 | 3.033 4 | Ahoj 4 | 4.033 5 | Ahoj 5 | 5.033 6 | Ahoj 6 | 6.033 7 | Ahoj 7 | 7.033 8 | Ahoj 8 | 8.033 9 | Ahoj 9 | 9.033 10 | Ahoj 10 | 10.033 11 | Ahoj 11 | 11.033 12 | Ahoj 12 | 12.033 13 | Ahoj 13 | 13.033 14 | Ahoj 14 | 14.033 15 | Ahoj 15 | 15.033 16 | Ahoj 16 | 16.033 17 | Ahoj 17 | 17.033 18 | Ahoj 18 | 18.033 19 | Ahoj 19 | 19.033 20 | Ahoj 20 | 20.033 21 | Ahoj 21 | 21.033 22 | Ahoj 22 | 22.033 23 | Ahoj 23 | 23.033 24 | Ahoj 24 | 24.033 25 | Ahoj 25 | 25.033 26 | Ahoj 26 | 26.033 27 | Ahoj 27 | 27.033 28 | Ahoj 28 | 28.033 29 | Ahoj 29 | 29.033 30 | Ahoj 30 | 30.033 31 | Ahoj 31 | 31.033 32 | Ahoj 32 | 32.033 33 | Ahoj 33 | 33.033 34 | Ahoj 34 | 34.033 35 | Ahoj 35 | 35.033 36 | Ahoj 36 | 36.033 37 | Ahoj 37 | 37.033 38 | Ahoj 38 | 38.033 39 | Ahoj 39 | 39.033 40 | Ahoj 40 | 40.033 41 | Ahoj 41 | 41.033 42 | Ahoj 42 | 42.033 43 | Ahoj 43 | 43.033 44 | Ahoj 44 | 44.033 45 | Ahoj 45 | 45.033 46 | Ahoj 46 | 46.033 47 | Ahoj 47 | 47.033 48 | Ahoj 48 | 48.033 49 | Ahoj 49 | 49.033 50 | Ahoj 50 | 50.033 51 | Ahoj 51 | 51.033 52 | Ahoj 52 | 52.033 53 | Ahoj 53 | 53.033 54 | Ahoj 54 | 54.033 55 | Ahoj 55 | 55.033 56 | Ahoj 56 | 56.033 57 | Ahoj 57 | 57.033 58 | Ahoj 58 | 58.033 59 | Ahoj 59 | 59.033 60 | Ahoj 60 | 60.033 61 | Ahoj 61 | 61.033 62 | Ahoj 62 | 62.033 63 | Ahoj 63 | 63.033 64 | Ahoj 64 | 64.033 65 | Ahoj 65 | 65.033 66 | Ahoj 66 | 66.033 67 | Ahoj 67 | 67.033 68 | Ahoj 68 | 68.033 69 | Ahoj 69 | 69.033 70 | Ahoj 70 | 70.033 71 | Ahoj 71 | 71.033 72 | Ahoj 72 | 72.033 73 | Ahoj 73 | 73.033 74 | Ahoj 74 | 74.033 75 | Ahoj 75 | 75.033 76 | Ahoj 76 | 76.033 77 | Ahoj 77 | 77.033 78 | Ahoj 78 | 78.033 79 | Ahoj 79 | 79.033 80 | Ahoj 80 | 80.033 81 | Ahoj 81 | 81.033 82 | Ahoj 82 | 82.033 83 | Ahoj 83 | 83.033 84 | Ahoj 84 | 84.033 85 | Ahoj 85 | 85.033 86 | Ahoj 86 | 86.033 87 | Ahoj 87 | 87.033 88 | Ahoj 88 | 88.033 89 | Ahoj 89 | 89.033 90 | Ahoj 90 | 90.033 91 | Ahoj 91 | 91.033 92 | Ahoj 92 | 92.033 93 | Ahoj 93 | 93.033 94 | Ahoj 94 | 94.033 95 | Ahoj 95 | 95.033 96 | Ahoj 96 | 96.033 97 | Ahoj 97 | 97.033 98 | Ahoj 98 | 98.033 99 | Ahoj 99 | 99.033 100 | Ahoj 100 | 100.033 (100 rows) truncate foo; do $$ declare c int; a int[]; b varchar[]; ca numeric[]; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)'); a := ARRAY[1, 2, 3, 4, 5]; b := ARRAY['Ahoj', 'Nazdar', 'Bazar']; ca := ARRAY[3.14, 2.22, 3.8, 4]; call dbms_sql.bind_array(c, 'a', a); call dbms_sql.bind_array(c, 'b', b); call dbms_sql.bind_array(c, 'c', ca); raise notice 'inserted rows %d', dbms_sql.execute(c); end; $$; NOTICE: inserted rows 3d select * from foo; a | b | c ---+--------+------ 1 | Ahoj | 3.14 2 | Nazdar | 2.22 3 | Bazar | 3.8 (3 rows) truncate foo; do $$ declare c int; a int[]; b varchar[]; ca numeric[]; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)'); a := ARRAY[1, 2, 3, 4, 5]; b := ARRAY['Ahoj', 'Nazdar', 'Bazar']; ca := ARRAY[3.14, 2.22, 3.8, 4]; call dbms_sql.bind_array(c, 'a', a, 2, 3); call dbms_sql.bind_array(c, 'b', b, 3, 4); call dbms_sql.bind_array(c, 'c', ca); raise notice 'inserted rows %d', dbms_sql.execute(c); end; $$; NOTICE: inserted rows 1d select * from foo; a | b | c ---+-------+----- 3 | Bazar | 3.8 (1 row) truncate foo; do $$ declare c int; a int[]; b varchar[]; ca numeric[]; begin c := dbms_sql.open_cursor(); call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)'); call dbms_sql.define_array(c, 1, a, 10, 1); call dbms_sql.define_array(c, 2, b, 10, 1); call dbms_sql.define_array(c, 3, ca, 10, 1); perform dbms_sql.execute(c); while dbms_sql.fetch_rows(c) > 0 loop call dbms_sql.column_value(c, 1, a); call dbms_sql.column_value(c, 2, b); call dbms_sql.column_value(c, 3, ca); raise notice 'a = %', a; raise notice 'b = %', b; raise notice 'c = %', ca; end loop; call dbms_sql.close_cursor(c); end; $$; NOTICE: a = {1,2,3,4,5,6,7,8,9,10} NOTICE: b = {Ahoj1,Ahoj2,Ahoj3,Ahoj4,Ahoj5,Ahoj6,Ahoj7,Ahoj8,Ahoj9,Ahoj10} NOTICE: c = {1.003,2.003,3.003,4.003,5.003,6.003,7.003,8.003,9.003,10.003} NOTICE: a = {11,12,13,14,15,16,17,18,19,20} NOTICE: b = {Ahoj11,Ahoj12,Ahoj13,Ahoj14,Ahoj15,Ahoj16,Ahoj17,Ahoj18,Ahoj19,Ahoj20} NOTICE: c = {11.003,12.003,13.003,14.003,15.003,16.003,17.003,18.003,19.003,20.003} NOTICE: a = {21,22,23,24,25,26,27,28,29,30} NOTICE: b = {Ahoj21,Ahoj22,Ahoj23,Ahoj24,Ahoj25,Ahoj26,Ahoj27,Ahoj28,Ahoj29,Ahoj30} NOTICE: c = {21.003,22.003,23.003,24.003,25.003,26.003,27.003,28.003,29.003,30.003} NOTICE: a = {31,32,33,34,35} NOTICE: b = {Ahoj31,Ahoj32,Ahoj33,Ahoj34,Ahoj35} NOTICE: c = {31.003,32.003,33.003,34.003,35.003} drop table foo;