38:
39: stmt:= 'SELECT dim_name from edw_dimensions_md_v@'|| l_target_link ||
40: ' where dim_long_name = :longname';
41:
42: cid := DBMS_SQL.OPEN_CURSOR;
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
39: stmt:= 'SELECT dim_name from edw_dimensions_md_v@'|| l_target_link ||
40: ' where dim_long_name = :longname';
41:
42: cid := DBMS_SQL.OPEN_CURSOR;
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
40: ' where dim_long_name = :longname';
41:
42: cid := DBMS_SQL.OPEN_CURSOR;
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48: DBMS_SQL.close_cursor(cid);
41:
42: cid := DBMS_SQL.OPEN_CURSOR;
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48: DBMS_SQL.close_cursor(cid);
49:
42: cid := DBMS_SQL.OPEN_CURSOR;
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48: DBMS_SQL.close_cursor(cid);
49:
50:
43: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48: DBMS_SQL.close_cursor(cid);
49:
50:
51: g_index := substr(g_dimension, 13 , instr(g_dimension, '_M') - 13 );
44: DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45: DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46: l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47: DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48: DBMS_SQL.close_cursor(cid);
49:
50:
51: g_index := substr(g_dimension, 13 , instr(g_dimension, '_M') - 13 );
52:
149: to_char(p_from_date,'MM/DD/YYYY HH24:MI:SS')||''','''|| 'MM/DD/YYYY HH24:MI:SS'||''''||')+1/864000) between :p_from_date and :p_to_date';
150:
151: edw_log.put_line('Insert statement is : '|| stmt);
152:
153: cid := DBMS_SQL.open_cursor;
154: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
155: edw_log.put_line('Parsed stmt');
156:
157:
150:
151: edw_log.put_line('Insert statement is : '|| stmt);
152:
153: cid := DBMS_SQL.open_cursor;
154: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
155: edw_log.put_line('Parsed stmt');
156:
157:
158: DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
154: DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
155: edw_log.put_line('Parsed stmt');
156:
157:
158: DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
159: DBMS_SQL.BIND_VARIABLE(cid, ':p_to_date', p_to_date);
160: l_dummy := DBMS_SQL.EXECUTE(cid);
161: DBMS_SQL.close_cursor(cid);
162: commit;
155: edw_log.put_line('Parsed stmt');
156:
157:
158: DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
159: DBMS_SQL.BIND_VARIABLE(cid, ':p_to_date', p_to_date);
160: l_dummy := DBMS_SQL.EXECUTE(cid);
161: DBMS_SQL.close_cursor(cid);
162: commit;
163: g_row_count := g_row_count + l_dummy;
156:
157:
158: DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
159: DBMS_SQL.BIND_VARIABLE(cid, ':p_to_date', p_to_date);
160: l_dummy := DBMS_SQL.EXECUTE(cid);
161: DBMS_SQL.close_cursor(cid);
162: commit;
163: g_row_count := g_row_count + l_dummy;
164: edw_log.put_line('Inserted '||l_dummy|| ' rows into the staging table');
157:
158: DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
159: DBMS_SQL.BIND_VARIABLE(cid, ':p_to_date', p_to_date);
160: l_dummy := DBMS_SQL.EXECUTE(cid);
161: DBMS_SQL.close_cursor(cid);
162: commit;
163: g_row_count := g_row_count + l_dummy;
164: edw_log.put_line('Inserted '||l_dummy|| ' rows into the staging table');
165: edw_log.put_line('Commiting records for EDW_FLEX_DIM'||g_index||'_L'||p_level||'_LSTG');