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 285: cur_err := DBMS_SQL.OPEN_CURSOR;

281: l_dir_name := Get_Directory_Name;
282: l_file := UTL_FILE.FOPEN(l_dir_name,p_file_name,'W',g_max_linesize);
283:
284: /* Retrieve the records */
285: cur_err := DBMS_SQL.OPEN_CURSOR;
286: DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);
287: FOR col_indx IN 1 .. l_column_count
288: LOOP
289: IF is_string(l_column_type,col_indx)

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

282: l_file := UTL_FILE.FOPEN(l_dir_name,p_file_name,'W',g_max_linesize);
283:
284: /* Retrieve the records */
285: cur_err := DBMS_SQL.OPEN_CURSOR;
286: DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);
287: FOR col_indx IN 1 .. l_column_count
288: LOOP
289: IF is_string(l_column_type,col_indx)
290: THEN

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

287: FOR col_indx IN 1 .. l_column_count
288: LOOP
289: IF is_string(l_column_type,col_indx)
290: THEN
291: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);
292: ELSIF is_number(l_column_type,col_indx)
293: THEN
294: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
295: ELSIF is_date (l_column_type,col_indx)

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

290: THEN
291: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);
292: ELSIF is_number(l_column_type,col_indx)
293: THEN
294: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
295: ELSIF is_date (l_column_type,col_indx)
296: THEN
297: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
298: END IF;

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

293: THEN
294: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
295: ELSIF is_date (l_column_type,col_indx)
296: THEN
297: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
298: END IF;
299: END LOOP;
300: l_return_value := DBMS_SQL.EXECUTE(cur_err);
301:

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

296: THEN
297: DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
298: END IF;
299: END LOOP;
300: l_return_value := DBMS_SQL.EXECUTE(cur_err);
301:
302: LOOP
303: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
304: EXIT WHEN l_return_value = 0;

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

299: END LOOP;
300: l_return_value := DBMS_SQL.EXECUTE(cur_err);
301:
302: LOOP
303: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
304: EXIT WHEN l_return_value = 0;
305:
306: IF DBMS_SQL.last_row_count = 1
307: THEN

Line 306: IF DBMS_SQL.last_row_count = 1

302: LOOP
303: l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
304: EXIT WHEN l_return_value = 0;
305:
306: IF DBMS_SQL.last_row_count = 1
307: THEN
308: /* Write the column header line in file */
309: UTL_FILE.PUT_LINE(l_file,l_hdr_line);
310: END IF;

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

313: FOR col_indx IN 1 .. l_column_count
314: LOOP
315: IF is_string(l_column_type,col_indx)
316: THEN
317: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);
318: ELSIF is_number(l_column_type,col_indx)
319: THEN
320: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
321: l_string_value := TO_CHAR (l_number_value);

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

316: THEN
317: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);
318: ELSIF is_number(l_column_type,col_indx)
319: THEN
320: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
321: l_string_value := TO_CHAR (l_number_value);
322: ELSIF is_date(l_column_type,col_indx)
323: THEN
324: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);

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

320: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
321: l_string_value := TO_CHAR (l_number_value);
322: ELSIF is_date(l_column_type,col_indx)
323: THEN
324: DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);
325: l_string_value := TO_CHAR(l_date_value,'YYYY/MM/DD');
326: END IF;
327:
328: l_line := l_line || g_delimeter_char || l_string_value;

Line 335: DBMS_SQL.CLOSE_CURSOR(cur_err);

331:
332: /* Write the line to the file */
333: UTL_FILE.PUT_LINE(l_file,l_line);
334: END LOOP;
335: DBMS_SQL.CLOSE_CURSOR(cur_err);
336:
337: UTL_FILE.FCLOSE(l_file);
338: EXCEPTION
339: WHEN OTHERS

Line 345: IF DBMS_SQL.IS_OPEN(cur_err)

341: IF UTL_FILE.IS_OPEN(l_file)
342: THEN
343: UTL_FILE.FCLOSE(l_file);
344: END IF;
345: IF DBMS_SQL.IS_OPEN(cur_err)
346: THEN
347: DBMS_SQL.CLOSE_CURSOR(cur_err);
348: END IF;
349: Raise_Error(SQLERRM);

Line 347: DBMS_SQL.CLOSE_CURSOR(cur_err);

343: UTL_FILE.FCLOSE(l_file);
344: END IF;
345: IF DBMS_SQL.IS_OPEN(cur_err)
346: THEN
347: DBMS_SQL.CLOSE_CURSOR(cur_err);
348: END IF;
349: Raise_Error(SQLERRM);
350: END Export_Data;
351:

Line 405: cur_err_upd := DBMS_SQL.OPEN_CURSOR;

401: l_dir_name := Get_Directory_Name;
402: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
403:
404: /* Read and Process all records from the file */
405: cur_err_upd := DBMS_SQL.OPEN_CURSOR;
406: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
407: l_rec_count := 0;
408: LOOP
409: BEGIN

Line 406: cur_err_ins := DBMS_SQL.OPEN_CURSOR;

402: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
403:
404: /* Read and Process all records from the file */
405: cur_err_upd := DBMS_SQL.OPEN_CURSOR;
406: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
407: l_rec_count := 0;
408: LOOP
409: BEGIN
410: UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);

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

444: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
445: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
446: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
447:
448: DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
449: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
450: ELSE
451: Get_File_Column_Details(l_line,l_file_column_value,l_count);
452:

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

445: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
446: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
447:
448: DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
449: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
450: ELSE
451: Get_File_Column_Details(l_line,l_file_column_value,l_count);
452:
453: /* BUG# 13809784 - Import based on target table type -Start*/

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

470: LOOP
471: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
472: THEN
473: l_string_value := l_file_column_value(indx);
474: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);
475: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
476: THEN
477: l_number_value := TO_NUMBER(l_file_column_value(indx));
478: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);

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

474: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);
475: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
476: THEN
477: l_number_value := TO_NUMBER(l_file_column_value(indx));
478: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);
479: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
480: THEN
481: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
482: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);

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

478: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);
479: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
480: THEN
481: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
482: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);
483: END IF;
484: END LOOP;
485: l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
486:

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

481: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
482: DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);
483: END IF;
484: END LOOP;
485: l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
486:
487: /* Insert the record into table if Update fails */
488: IF l_update_count = 0
489: THEN

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

491: LOOP
492: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
493: THEN
494: l_string_value := l_file_column_value(indx);
495: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
496: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
497: THEN
498: l_number_value := TO_NUMBER(l_file_column_value(indx));
499: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

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

495: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
496: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
497: THEN
498: l_number_value := TO_NUMBER(l_file_column_value(indx));
499: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
500: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
501: THEN
502: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
503: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

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

499: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
500: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
501: THEN
502: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
503: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
504: END IF;
505: END LOOP;
506: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
507: END IF;

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

502: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
503: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
504: END IF;
505: END LOOP;
506: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
507: END IF;
508: END IF;
509: END IF;
510:

Line 519: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);

515: THEN EXIT;
516: END;
517: END LOOP;
518:
519: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
520: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
521: UTL_FILE.FCLOSE(l_file);
522:
523: COMMIT;

Line 520: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

516: END;
517: END LOOP;
518:
519: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
520: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
521: UTL_FILE.FCLOSE(l_file);
522:
523: COMMIT;
524: EXCEPTION

Line 531: IF DBMS_SQL.IS_OPEN(cur_err_upd)

527: IF UTL_FILE.IS_OPEN(l_file)
528: THEN
529: UTL_FILE.FCLOSE(l_file);
530: END IF;
531: IF DBMS_SQL.IS_OPEN(cur_err_upd)
532: THEN
533: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
534: END IF;
535: IF DBMS_SQL.IS_OPEN(cur_err_ins)

Line 533: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);

529: UTL_FILE.FCLOSE(l_file);
530: END IF;
531: IF DBMS_SQL.IS_OPEN(cur_err_upd)
532: THEN
533: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
534: END IF;
535: IF DBMS_SQL.IS_OPEN(cur_err_ins)
536: THEN
537: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

Line 535: IF DBMS_SQL.IS_OPEN(cur_err_ins)

531: IF DBMS_SQL.IS_OPEN(cur_err_upd)
532: THEN
533: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
534: END IF;
535: IF DBMS_SQL.IS_OPEN(cur_err_ins)
536: THEN
537: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
538: END IF;
539: Raise_Error(SQLERRM);

Line 537: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

533: DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
534: END IF;
535: IF DBMS_SQL.IS_OPEN(cur_err_ins)
536: THEN
537: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
538: END IF;
539: Raise_Error(SQLERRM);
540: END Import_Error;
541:

Line 608: cur_err_ins := DBMS_SQL.OPEN_CURSOR;

604: l_dir_name := Get_Directory_Name;
605: l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
606:
607: /* Read and Process all records from the file */
608: cur_err_ins := DBMS_SQL.OPEN_CURSOR;
609: l_rec_count := 0;
610: LOOP
611: BEGIN
612: UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);

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

628: l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
629: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
630: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
631:
632: DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
633: ELSE
634: Get_File_Column_Details(l_line,l_file_column_value,l_count);
635:
636: /* Insert the record into table */

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

638: LOOP
639: IF is_string(l_col_type_by_name(l_file_column_name(indx)))
640: THEN
641: l_string_value := l_file_column_value(indx);
642: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
643: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
644: THEN
645: l_number_value := TO_NUMBER(l_file_column_value(indx));
646: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);

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

642: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
643: ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
644: THEN
645: l_number_value := TO_NUMBER(l_file_column_value(indx));
646: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
647: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
648: THEN
649: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
650: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);

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

646: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
647: ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
648: THEN
649: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
650: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
651: END IF;
652: END LOOP;
653: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
654:

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

649: l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
650: DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
651: END IF;
652: END LOOP;
653: l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
654:
655: END IF;
656:
657: EXCEPTION

Line 663: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

659: THEN EXIT;
660: END;
661: END LOOP;
662:
663: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
664: UTL_FILE.FCLOSE(l_file);
665:
666: COMMIT;
667: EXCEPTION

Line 674: IF DBMS_SQL.IS_OPEN(cur_err_ins)

670: IF UTL_FILE.IS_OPEN(l_file)
671: THEN
672: UTL_FILE.FCLOSE(l_file);
673: END IF;
674: IF DBMS_SQL.IS_OPEN(cur_err_ins)
675: THEN
676: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
677: END IF;
678: Raise_Error(SQLERRM);

Line 676: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);

672: UTL_FILE.FCLOSE(l_file);
673: END IF;
674: IF DBMS_SQL.IS_OPEN(cur_err_ins)
675: THEN
676: DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
677: END IF;
678: Raise_Error(SQLERRM);
679: END Import_Data;
680:

Line 765: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;

761: l_rest_where_clause := l_rest_where_clause || ' AND SRC_IDNT_FLAG = ''' || l_tgt_table_type || '''';
762: END IF;
763: l_select_stmt := l_select_stmt || l_where_clause;
764:
765: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;
766: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;
767:
768: /* Build the Update and Insert Statements and Parse them */
769: l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';

Line 766: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;

762: END IF;
763: l_select_stmt := l_select_stmt || l_where_clause;
764:
765: cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;
766: cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;
767:
768: /* Build the Update and Insert Statements and Parse them */
769: l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';
770: l_insert_clause := 'INSERT INTO ' || l_tgt_table_name ||'(';

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

788: l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
789: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
790: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
791:
792: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
793: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
794:
795: /* Retrieve the records from Source table */
796: cur_src := DBMS_SQL.OPEN_CURSOR;

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

789: l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
790: l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
791:
792: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
793: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
794:
795: /* Retrieve the records from Source table */
796: cur_src := DBMS_SQL.OPEN_CURSOR;
797: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);

Line 796: cur_src := DBMS_SQL.OPEN_CURSOR;

792: DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
793: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
794:
795: /* Retrieve the records from Source table */
796: cur_src := DBMS_SQL.OPEN_CURSOR;
797: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
798: FOR col_indx IN 1 .. l_src_column_count
799: LOOP
800: IF is_string(l_src_column_type,col_indx)

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

793: DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
794:
795: /* Retrieve the records from Source table */
796: cur_src := DBMS_SQL.OPEN_CURSOR;
797: DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
798: FOR col_indx IN 1 .. l_src_column_count
799: LOOP
800: IF is_string(l_src_column_type,col_indx)
801: THEN

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

798: FOR col_indx IN 1 .. l_src_column_count
799: LOOP
800: IF is_string(l_src_column_type,col_indx)
801: THEN
802: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);
803: ELSIF is_number(l_src_column_type,col_indx)
804: THEN
805: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
806: ELSIF is_date (l_src_column_type,col_indx)

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

801: THEN
802: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);
803: ELSIF is_number(l_src_column_type,col_indx)
804: THEN
805: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
806: ELSIF is_date (l_src_column_type,col_indx)
807: THEN
808: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
809: END IF;

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

804: THEN
805: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
806: ELSIF is_date (l_src_column_type,col_indx)
807: THEN
808: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
809: END IF;
810: END LOOP;
811:
812: l_return_value := DBMS_SQL.EXECUTE(cur_src);

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

808: DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
809: END IF;
810: END LOOP;
811:
812: l_return_value := DBMS_SQL.EXECUTE(cur_src);
813: LOOP
814: l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);
815: EXIT WHEN l_return_value = 0;
816:

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

810: END LOOP;
811:
812: l_return_value := DBMS_SQL.EXECUTE(cur_src);
813: LOOP
814: l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);
815: EXIT WHEN l_return_value = 0;
816:
817: FOR col_indx IN 1 .. l_src_column_count
818: LOOP

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

819: IF Column_Exists(l_tgt_column_name,l_src_column_name(col_indx))
820: THEN
821: IF is_string(l_src_column_type,col_indx)
822: THEN
823: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
824: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
825: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN

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

820: THEN
821: IF is_string(l_src_column_type,col_indx)
822: THEN
823: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
824: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
825: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN
828: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);

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

821: IF is_string(l_src_column_type,col_indx)
822: THEN
823: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
824: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
825: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN
828: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
829: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);

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

824: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
825: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN
828: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
829: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
830: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN

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

825: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN
828: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
829: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
830: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN
833: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);

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

826: ELSIF is_number(l_src_column_type,col_indx)
827: THEN
828: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
829: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
830: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN
833: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
834: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);

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

829: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
830: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN
833: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
834: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
835: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
836: END IF;
837: END IF;

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

830: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN
833: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
834: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
835: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
836: END IF;
837: END IF;
838: END LOOP;

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

831: ELSIF is_date(l_src_column_type,col_indx)
832: THEN
833: DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
834: DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
835: DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
836: END IF;
837: END IF;
838: END LOOP;
839:

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

837: END IF;
838: END LOOP;
839:
840: /* Execute the DML statement against the Target table */
841: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_upd);
842:
843: /* Insert the record into table if Update fails */
844: IF l_update_count = 0
845: THEN

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

842:
843: /* Insert the record into table if Update fails */
844: IF l_update_count = 0
845: THEN
846: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
847: END IF;
848: END LOOP;
849:
850: DBMS_SQL.CLOSE_CURSOR(cur_src);

Line 850: DBMS_SQL.CLOSE_CURSOR(cur_src);

846: l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
847: END IF;
848: END LOOP;
849:
850: DBMS_SQL.CLOSE_CURSOR(cur_src);
851: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
852: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
853:
854: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */

Line 851: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

847: END IF;
848: END LOOP;
849:
850: DBMS_SQL.CLOSE_CURSOR(cur_src);
851: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
852: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
853:
854: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
855: l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;

Line 852: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

848: END LOOP;
849:
850: DBMS_SQL.CLOSE_CURSOR(cur_src);
851: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
852: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
853:
854: /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
855: l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;
856: l_delete_stmt := l_delete_stmt || ' WHERE ACTION_FLAG = ''D'' ' || l_rest_where_clause || ')';

Line 868: IF DBMS_SQL.IS_OPEN(cur_src)

864: COMMIT;
865: EXCEPTION
866: WHEN OTHERS
867: THEN
868: IF DBMS_SQL.IS_OPEN(cur_src)
869: THEN
870: DBMS_SQL.CLOSE_CURSOR(cur_src);
871: END IF;
872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)

Line 870: DBMS_SQL.CLOSE_CURSOR(cur_src);

866: WHEN OTHERS
867: THEN
868: IF DBMS_SQL.IS_OPEN(cur_src)
869: THEN
870: DBMS_SQL.CLOSE_CURSOR(cur_src);
871: END IF;
872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
873: THEN
874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

Line 872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)

868: IF DBMS_SQL.IS_OPEN(cur_src)
869: THEN
870: DBMS_SQL.CLOSE_CURSOR(cur_src);
871: END IF;
872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
873: THEN
874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
875: END IF;
876: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)

Line 874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);

870: DBMS_SQL.CLOSE_CURSOR(cur_src);
871: END IF;
872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
873: THEN
874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
875: END IF;
876: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
877: THEN
878: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

Line 876: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)

872: IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
873: THEN
874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
875: END IF;
876: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
877: THEN
878: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
879: END IF;
880: Raise_Error(SQLERRM);

Line 878: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);

874: DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
875: END IF;
876: IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
877: THEN
878: DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
879: END IF;
880: Raise_Error(SQLERRM);
881: END Transfer_Data;
882: