Oracle, Web, Script, SQLserver, Tips & Trick

May 19, 2011

print via store procedure

Filed under: oracle — sikathabis @ 9:33 am
Tags:
DECLARE
	v_file         VARCHAR2(200);
	v_nm           VARCHAR2(10);
	f_out_file     Text_IO.File_Type;
	v_err_msg      VARCHAR2(240);
	f_err_file     Text_IO.File_Type;
	v_eof          BOOLEAN := FALSE;
	v_b_return     BOOLEAN;
	v_v2_data      VARCHAR2(200);
	v_all_rec      NUMBER := 0;
	v_rec_ok       NUMBER := 0;
	v_rec_not_ok   NUMBER := 0;
	v_all2_rec     NUMBER;
	v_rec2_ok      NUMBER;
	v_rec2_not_ok  NUMBER;
	osname         VARCHAR2(20);
	v_err_file     VARCHAR2(200);
	v_ext          VARCHAR2(3);

BEGIN
		   osname := get_application_property(operating_system);
/*
 		     if osname = 'WIN32COMMON' then
            host('cmd.exe /c move '||:Upload.Filename||'\SJ*.*  C:\sap2000\log\arsip\sj',no_prompt);
		     end if;
*/

		IF :upload.Filename IS NULL THEN
 			 Message_util.Show_Message('Error','Directory must be filled !');
			 go_item('upload.filename');
		ELSIF message_util.ask_user_2(p_title=>'Confirmation',
  				p_message => 'Are you sure want to upload ?') = ALERT_BUTTON1 THEN
--      host('dir /b /o '||:UPLOAD.FILENAME||'\ > C:\loadfaktur.txt');

        if osname in ('MSWINDOWS', 'MSWINDOWS32' ) then
	         host('command /c dir /b /o'||:Upload.Filename||'\ > c:\loadfak.txt',no_prompt);
        elsif osname in ('WIN32COMMON' ) then
	         host('cmd /c dir /b '||:Upload.Filename||' > c:\loadfak.txt',no_prompt);
	      end if;   

      IF GRD$FILE_UTIL.F_OPEN_File(p_out_file_id  => f_out_file
	   	  		  	 			,p_in_path_filename => 'C:\loadfak.txt'
					 						,p_in_mode => 'r'
					 						,p_out_error_msg  => v_err_msg) THEN

--				  v_err_file := :UPLOAD.FILENAME||'\loadfaktur_'||TO_CHAR(SYSDATE,'DDMMRRRR')||'.txt';
				  v_err_file := :UPLOAD.FILENAME||'\loadfak_'||TO_CHAR(SYSDATE,'DDMMRRRR')||'.txt';				  

				  v_b_return := GRD$FILE_UTIL.F_OPEN_File(p_out_file_id  => f_err_file
	   	  		  	 			,p_in_path_filename => v_err_file
					 						,p_in_mode => 'w'
					 						,p_out_error_msg  => v_err_msg);

    	    GO_BLOCK('TMP');
    	    CLEAR_BLOCK;

				WHILE NOT v_eof LOOP
				   GRD$FILE_UTIL.P_GET_next_line (p_in_file_id => f_out_file
														,p_out_line_txt => v_v2_data
														,p_out_eof => v_eof
														,p_out_error_msg => v_err_msg);

					 /* //////////////////////////////////////////////////////////////
					 //  Ambil Extention dari file, Jika ext = .do , maka load DOAHM
					 //  Jika ext = fm1 or fm2, maka load ppblist
					 ////////////////////////////////////////////////////////////// */

					 IF NOT v_eof THEN
					 	  -- Ambil extention
					 	 -- v_ext := UPPER(SUBSTR(v_v2_data,INSTR(v_v2_data,'.',1)+1, LENGTH(v_v2_data)));
					 	  v_ext := UPPER(SUBSTR(v_v2_data,INSTR(v_v2_data,'.',1)+1, 3));
					 	  v_nm  := UPPER(SUBSTR(v_v2_data,2,6));

--					    message(v_nm ||'.'||v_ext);pause;

					 	  -- Cek sama dengan tanggal proses dan no dealer
					 	  IF v_ext = rtrim(:upload.nodlr) and v_nm=to_char(:upload.tglproses,'RRMMDD') THEN
				      Grd$Loading_Util.P_UPLOAD_FAKTUR (
									      p_in_path_filename => :upload.filename||'\'||v_v2_data,
									      p_err_file => f_err_file,
									      p_n_line_no => v_all2_rec,
									      p_n_rec_ok => v_rec2_ok,
												p_n_rec_not_ok => v_rec2_not_ok);
					 	  END IF;	

						 v_all_rec := v_all_rec + v_all2_rec;
						 v_rec_ok := v_rec_ok + v_rec2_ok;
						 v_rec_not_ok := v_rec_not_ok + v_rec2_not_ok;
					 END IF;
				END LOOP;
      -- Sending message to user
--			Message_Util.Show_Message('Info', v_rec_ok||' record(s) inserted from '||
--																			'all '||v_all_rec||' record(s)');

			/*//////////////////////////////////////////////////////////////////
  	  // Close file
    	////////////////////////////////////////////////////////////////// */
			v_b_return := GRD$FILE_UTIL.F_CLOSE_File(p_in_file_id => f_out_file);
      v_b_return := GRD$FILE_UTIL.F_CLOSE_File(p_in_file_id => f_err_file);

			ELSE
		    /*//////////////////////////////////////////////////////////////////
	  	  // Open file unsuccess
	    	///////////////////////////////////////////////////////////////// */
				Message_util.Show_Message('Error', 'Cannot open file '||
												'C:\loadfak.txt'||'. With Err Msg : '||v_err_msg||'. Check File and directory name.');
      END IF; --Open File
  	END IF;
END;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: