eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.DDR_ETL_UTIL_PKG dependencies on DBMS_SQL

Line 260: cur_err := DBMS_SQL.OPEN_CURSOR;

256: l_dir_name := Get_Directory_Name;
257: l_file := UTL_FILE.FOPEN(l_dir_name,p_file_name,'W',g_max_linesize);
258:
259: /* Retrieve the records */
260: cur_err := DBMS_SQL.OPEN_CURSOR;
261: DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);
262: FOR col_indx IN 1 .. l_column_count
263: LOOP
264: IF is_string(l_column_type,col_indx)

Line 261: DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);

257: l_file := UTL_FILE.FOPEN(l_dir_name,p_file_name,'W',g_max_linesize);
258:
259: /* Retrieve the records */
260: cur_err := DBMS_SQL.OPEN_CURSOR;
261: DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);
262: FOR col_indx IN 1 .. l_column_count
263: LOOP
264: IF is_string(l_column_type,col_indx)
265: THEN

Line 266: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);

262: FOR col_indx IN 1 .. l_column_count
263: LOOP
264: IF is_string(l_column_type,col_indx)
265: THEN
266: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);
267: ELSIF is_number(l_column_type,col_indx)
268: THEN
269: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
270: ELSIF is_date (l_column_type,col_indx)

Line 269: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);

265: THEN
266: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);
267: ELSIF is_number(l_column_type,col_indx)
268: THEN
269: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
270: ELSIF is_date (l_column_type,col_indx)
271: THEN
272: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
273: END IF;

Line 272: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);

268: THEN
269: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
270: ELSIF is_date (l_column_type,col_indx)
271: THEN
272: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
273: END IF;
274: END LOOP;
275: l_return_value := DBMS_SQL.EXECUTE(cur_err);
276:

Line 275: l_return_value := DBMS_SQL.EXECUTE(cur_err);

271: THEN
272: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
273: END IF;
274: END LOOP;
275: l_return_value := DBMS_SQL.EXECUTE(cur_err);
276:
277: LOOP
278: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
279: EXIT WHEN l_return_value = 0;

Line 278: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);

274: END LOOP;
275: l_return_value := DBMS_SQL.EXECUTE(cur_err);
276:
277: LOOP
278: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
279: EXIT WHEN l_return_value = 0;
280:
281: IF DBMS_SQL.last_row_count = 1
282: THEN

Line 281: IF DBMS_SQL.last_row_count = 1

277: LOOP
278: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
279: EXIT WHEN l_return_value = 0;
280:
281: IF DBMS_SQL.last_row_count = 1
282: THEN
283: /* Write the column header line in file */
284: UTL_FILE.PUT_LINE(l_file,l_hdr_line);
285: END IF;

Line 292: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);

288: FOR col_indx IN 1 .. l_column_count
289: LOOP
290: IF is_string(l_column_type,col_indx)
291: THEN
292: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);
293: ELSIF is_number(l_column_type,col_indx)
294: THEN
295: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
296: l_string_value := TO_CHAR (l_number_value);

Line 295: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);

291: THEN
292: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);
293: ELSIF is_number(l_column_type,col_indx)
294: THEN
295: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
296: l_string_value := TO_CHAR (l_number_value);
297: ELSIF is_date(l_column_type,col_indx)
298: THEN
299: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);

Line 299: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);

295: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
296: l_string_value := TO_CHAR (l_number_value);
297: ELSIF is_date(l_column_type,col_indx)
298: THEN
299: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);
300: l_string_value := TO_CHAR(l_date_value,'YYYY/MM/DD');
301: END IF;
302:
303: l_line := l_line || g_delimeter_char || l_string_value;

Line 310: DBMS_SQL.CLOSE_CURSOR(cur_err);

306:
307: /* Write the line to the file */
308: UTL_FILE.PUT_LINE(l_file,l_line);
309: END LOOP;
310: DBMS_SQL.CLOSE_CURSOR(cur_err);
311:
312: UTL_FILE.FCLOSE(l_file);
313: EXCEPTION
314: WHEN OTHERS

Line 320: IF DBMS_SQL.IS_OPEN(cur_err)

316: IF UTL_FILE.IS_OPEN(l_file)
317: THEN
318: UTL_FILE.FCLOSE(l_file);
319: END IF;
320: IF DBMS_SQL.IS_OPEN(cur_err)
321: THEN
322: DBMS_SQL.CLOSE_CURSOR(cur_err);
323: END IF;
324: Raise_Error(SQLERRM);

Line 322: DBMS_SQL.CLOSE_CURSOR(cur_err);

318: UTL_FILE.FCLOSE(l_file);
319: END IF;
320: IF DBMS_SQL.IS_OPEN(cur_err)
321: THEN
322: DBMS_SQL.CLOSE_CURSOR(cur_err);
323: END IF;
324: Raise_Error(SQLERRM);
325: END Export_Data;
326:

Line 375: cur_err_upd := DBMS_SQL.OPEN_CURSOR;

371: l_dir_name := Get_Directory_Name;
372: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
373:
374: /* Read and Process all records from the file */
375: cur_err_upd := DBMS_SQL.OPEN_CURSOR;
376: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
377: l_rec_count := 0;
378: LOOP
379: BEGIN

Line 376: cur_err_ins := DBMS_SQL.OPEN_CURSOR;

372: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
373:
374: /* Read and Process all records from the file */
375: cur_err_upd := DBMS_SQL.OPEN_CURSOR;
376: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
377: l_rec_count := 0;
378: LOOP
379: BEGIN
380: UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);

Line 414: DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);

410: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
411: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
412: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
413:
414: DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
415: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
416: ELSE
417: Get_File_Column_Details(l_line,l_file_column_value,l_count);
418:

Line 415: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);

411: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
412: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
413:
414: DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
415: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
416: ELSE
417: Get_File_Column_Details(l_line,l_file_column_value,l_count);
418:
419: IF l_file_column_value(l_action_column_indx) IN ('Y','D') /* i.e. ACTION_FLAG IN ('Y','D') */

Line 427: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);

423: LOOP
424: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
425: THEN
426: l_string_value := l_file_column_value(indx);
427: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);
428: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
429: THEN
430: l_number_value := TO_NUMBER(l_file_column_value(indx));
431: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);

Line 431: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);

427: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);
428: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
429: THEN
430: l_number_value := TO_NUMBER(l_file_column_value(indx));
431: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);
432: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
433: THEN
434: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
435: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);

Line 435: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);

431: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);
432: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
433: THEN
434: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
435: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);
436: END IF;
437: END LOOP;
438: l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
439:

Line 438: l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);

434: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
435: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);
436: END IF;
437: END LOOP;
438: l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
439:
440: /* Insert the record into table if Update fails */
441: IF l_update_count = 0
442: THEN

Line 448: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);

444: LOOP
445: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
446: THEN
447: l_string_value := l_file_column_value(indx);
448: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
449: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
450: THEN
451: l_number_value := TO_NUMBER(l_file_column_value(indx));
452: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

Line 452: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

448: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
449: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
450: THEN
451: l_number_value := TO_NUMBER(l_file_column_value(indx));
452: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
453: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
454: THEN
455: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
456: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

Line 456: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

452: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
453: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
454: THEN
455: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
456: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
457: END IF;
458: END LOOP;
459: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
460: END IF;

Line 459: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);

455: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
456: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
457: END IF;
458: END LOOP;
459: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
460: END IF;
461: END IF;
462:
463: END IF;

Line 471: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);

467: THEN EXIT;
468: END;
469: END LOOP;
470:
471: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
472: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
473: UTL_FILE.FCLOSE(l_file);
474:
475: COMMIT;

Line 472: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

468: END;
469: END LOOP;
470:
471: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
472: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
473: UTL_FILE.FCLOSE(l_file);
474:
475: COMMIT;
476: EXCEPTION

Line 483: IF DBMS_SQL.IS_OPEN(cur_err_upd)

479: IF UTL_FILE.IS_OPEN(l_file)
480: THEN
481: UTL_FILE.FCLOSE(l_file);
482: END IF;
483: IF DBMS_SQL.IS_OPEN(cur_err_upd)
484: THEN
485: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
486: END IF;
487: IF DBMS_SQL.IS_OPEN(cur_err_ins)

Line 485: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);

481: UTL_FILE.FCLOSE(l_file);
482: END IF;
483: IF DBMS_SQL.IS_OPEN(cur_err_upd)
484: THEN
485: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
486: END IF;
487: IF DBMS_SQL.IS_OPEN(cur_err_ins)
488: THEN
489: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

Line 487: IF DBMS_SQL.IS_OPEN(cur_err_ins)

483: IF DBMS_SQL.IS_OPEN(cur_err_upd)
484: THEN
485: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
486: END IF;
487: IF DBMS_SQL.IS_OPEN(cur_err_ins)
488: THEN
489: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
490: END IF;
491: Raise_Error(SQLERRM);

Line 489: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

485: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
486: END IF;
487: IF DBMS_SQL.IS_OPEN(cur_err_ins)
488: THEN
489: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
490: END IF;
491: Raise_Error(SQLERRM);
492: END Import_Error;
493:

Line 558: cur_err_ins := DBMS_SQL.OPEN_CURSOR;

554: l_dir_name := Get_Directory_Name;
555: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
556:
557: /* Read and Process all records from the file */
558: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
559: l_rec_count := 0;
560: LOOP
561: BEGIN
562: UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);

Line 582: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);

578: l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
579: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
580: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
581:
582: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
583: ELSE
584: Get_File_Column_Details(l_line,l_file_column_value,l_count);
585:
586: /* Insert the record into table */

Line 592: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);

588: LOOP
589: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
590: THEN
591: l_string_value := l_file_column_value(indx);
592: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
593: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
594: THEN
595: l_number_value := TO_NUMBER(l_file_column_value(indx));
596: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

Line 596: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

592: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
593: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
594: THEN
595: l_number_value := TO_NUMBER(l_file_column_value(indx));
596: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
597: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
598: THEN
599: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
600: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

Line 600: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

596: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
597: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
598: THEN
599: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
600: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
601: END IF;
602: END LOOP;
603: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
604:

Line 603: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);

599: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
600: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
601: END IF;
602: END LOOP;
603: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
604:
605: END IF;
606:
607: EXCEPTION

Line 613: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

609: THEN EXIT;
610: END;
611: END LOOP;
612:
613: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
614: UTL_FILE.FCLOSE(l_file);
615:
616: COMMIT;
617: EXCEPTION

Line 624: IF DBMS_SQL.IS_OPEN(cur_err_ins)

620: IF UTL_FILE.IS_OPEN(l_file)
621: THEN
622: UTL_FILE.FCLOSE(l_file);
623: END IF;
624: IF DBMS_SQL.IS_OPEN(cur_err_ins)
625: THEN
626: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
627: END IF;
628: Raise_Error(SQLERRM);

Line 626: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

622: UTL_FILE.FCLOSE(l_file);
623: END IF;
624: IF DBMS_SQL.IS_OPEN(cur_err_ins)
625: THEN
626: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
627: END IF;
628: Raise_Error(SQLERRM);
629: END Import_Data;
630:

Line 715: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;

711: l_rest_where_clause := l_rest_where_clause || ' AND SRC_IDNT_FLAG = ''' || l_tgt_table_type || '''';
712: END IF;
713: l_select_stmt := l_select_stmt || l_where_clause;
714:
715: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;
716: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;
717:
718: /* Build the Update and Insert Statements and Parse them */
719: l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';

Line 716: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;

712: END IF;
713: l_select_stmt := l_select_stmt || l_where_clause;
714:
715: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;
716: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;
717:
718: /* Build the Update and Insert Statements and Parse them */
719: l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';
720: l_insert_clause := 'INSERT INTO ' || l_tgt_table_name ||'(';

Line 742: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);

738: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
739: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
740: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
741:
742: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
743: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
744:
745: /* Retrieve the records from Source table */
746: cur_src := DBMS_SQL.OPEN_CURSOR;

Line 743: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);

739: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
740: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
741:
742: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
743: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
744:
745: /* Retrieve the records from Source table */
746: cur_src := DBMS_SQL.OPEN_CURSOR;
747: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);

Line 746: cur_src := DBMS_SQL.OPEN_CURSOR;

742: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
743: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
744:
745: /* Retrieve the records from Source table */
746: cur_src := DBMS_SQL.OPEN_CURSOR;
747: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
748: FOR col_indx IN 1 .. l_src_column_count
749: LOOP
750: IF is_string(l_src_column_type,col_indx)

Line 747: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);

743: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
744:
745: /* Retrieve the records from Source table */
746: cur_src := DBMS_SQL.OPEN_CURSOR;
747: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
748: FOR col_indx IN 1 .. l_src_column_count
749: LOOP
750: IF is_string(l_src_column_type,col_indx)
751: THEN

Line 752: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);

748: FOR col_indx IN 1 .. l_src_column_count
749: LOOP
750: IF is_string(l_src_column_type,col_indx)
751: THEN
752: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);
753: ELSIF is_number(l_src_column_type,col_indx)
754: THEN
755: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
756: ELSIF is_date (l_src_column_type,col_indx)

Line 755: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);

751: THEN
752: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);
753: ELSIF is_number(l_src_column_type,col_indx)
754: THEN
755: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
756: ELSIF is_date (l_src_column_type,col_indx)
757: THEN
758: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
759: END IF;

Line 758: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);

754: THEN
755: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
756: ELSIF is_date (l_src_column_type,col_indx)
757: THEN
758: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
759: END IF;
760: END LOOP;
761:
762: l_return_value := DBMS_SQL.EXECUTE(cur_src);

Line 762: l_return_value := DBMS_SQL.EXECUTE(cur_src);

758: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
759: END IF;
760: END LOOP;
761:
762: l_return_value := DBMS_SQL.EXECUTE(cur_src);
763: LOOP
764: l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);
765: EXIT WHEN l_return_value = 0;
766:

Line 764: l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);

760: END LOOP;
761:
762: l_return_value := DBMS_SQL.EXECUTE(cur_src);
763: LOOP
764: l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);
765: EXIT WHEN l_return_value = 0;
766:
767: FOR col_indx IN 1 .. l_src_column_count
768: LOOP

Line 773: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);

769: IF Column_Exists(l_tgt_column_name,l_src_column_name(col_indx))
770: THEN
771: IF is_string(l_src_column_type,col_indx)
772: THEN
773: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
774: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN

Line 774: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);

770: THEN
771: IF is_string(l_src_column_type,col_indx)
772: THEN
773: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
774: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN
778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);

Line 775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);

771: IF is_string(l_src_column_type,col_indx)
772: THEN
773: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
774: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN
778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);

Line 778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);

774: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN
778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN

Line 779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);

775: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN
778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN
783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);

Line 780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);

776: ELSIF is_number(l_src_column_type,col_indx)
777: THEN
778: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN
783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
784: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);

Line 783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);

779: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN
783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
784: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
785: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
786: END IF;
787: END IF;

Line 784: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);

780: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN
783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
784: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
785: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
786: END IF;
787: END IF;
788: END LOOP;

Line 785: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);

781: ELSIF is_date(l_src_column_type,col_indx)
782: THEN
783: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
784: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
785: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
786: END IF;
787: END IF;
788: END LOOP;
789:

Line 791: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_upd);

787: END IF;
788: END LOOP;
789:
790: /* Execute the DML statement against the Target table */
791: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_upd);
792:
793: /* Insert the record into table if Update fails */
794: IF l_update_count = 0
795: THEN

Line 796: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);

792:
793: /* Insert the record into table if Update fails */
794: IF l_update_count = 0
795: THEN
796: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
797: END IF;
798: END LOOP;
799:
800: DBMS_SQL.CLOSE_CURSOR(cur_src);

Line 800: DBMS_SQL.CLOSE_CURSOR(cur_src);

796: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
797: END IF;
798: END LOOP;
799:
800: DBMS_SQL.CLOSE_CURSOR(cur_src);
801: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
802: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
803:
804: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */

Line 801: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

797: END IF;
798: END LOOP;
799:
800: DBMS_SQL.CLOSE_CURSOR(cur_src);
801: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
802: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
803:
804: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
805: l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;

Line 802: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

798: END LOOP;
799:
800: DBMS_SQL.CLOSE_CURSOR(cur_src);
801: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
802: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
803:
804: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
805: l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;
806: l_delete_stmt := l_delete_stmt || ' WHERE ACTION_FLAG = ''D'' ' || l_rest_where_clause || ')';

Line 818: IF DBMS_SQL.IS_OPEN(cur_src)

814: COMMIT;
815: EXCEPTION
816: WHEN OTHERS
817: THEN
818: IF DBMS_SQL.IS_OPEN(cur_src)
819: THEN
820: DBMS_SQL.CLOSE_CURSOR(cur_src);
821: END IF;
822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)

Line 820: DBMS_SQL.CLOSE_CURSOR(cur_src);

816: WHEN OTHERS
817: THEN
818: IF DBMS_SQL.IS_OPEN(cur_src)
819: THEN
820: DBMS_SQL.CLOSE_CURSOR(cur_src);
821: END IF;
822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
823: THEN
824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

Line 822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)

818: IF DBMS_SQL.IS_OPEN(cur_src)
819: THEN
820: DBMS_SQL.CLOSE_CURSOR(cur_src);
821: END IF;
822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
823: THEN
824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
825: END IF;
826: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)

Line 824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

820: DBMS_SQL.CLOSE_CURSOR(cur_src);
821: END IF;
822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
823: THEN
824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
825: END IF;
826: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
827: THEN
828: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

Line 826: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)

822: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
823: THEN
824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
825: END IF;
826: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
827: THEN
828: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
829: END IF;
830: Raise_Error(SQLERRM);

Line 828: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

824: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
825: END IF;
826: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
827: THEN
828: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
829: END IF;
830: Raise_Error(SQLERRM);
831: END Transfer_Data;
832: