dbms_scheduler Ruunning OS Command or Shell Script from PL/SQL, EXPORT oracle database to .dmp file

Hi there i will share a little bit trick, that allow you to export oracle database using pl/sql.

I have Windows client and my oracle 10g DB base on Ubuntu(LInux),
i want create some procedure that will execute linux command to export oracle database.

ok here there are :
i will divide the method base on 2 model.

1. U will execute by calling .sh file

#Create exp.sh file

#!/bin/sh
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH
exp user/pass@orcl file=/U_re_directory/exp.dmp

#Run this script on pl/SQL

 BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
 job_name=>'testjob',
 job_type=>'EXECUTABLE',
 job_action=>'U_re_directory/exp.sh',
 enabled=>true,
 auto_drop=>true);
 end;
 /

2. U will execute by typing the script directly 

begin
   dbms_scheduler.create_job (job_name    => 'myjob',
                              job_type    => 'executable',
                              job_action  => '/bin/sh',
                              number_of_arguments => 2,
                              auto_drop   => true);
   dbms_scheduler.set_job_argument_value ('myjob', 1,'-c');
   dbms_scheduler.set_job_argument_value ('myjob', 2,'ORACLE_HOME=/opt/oracle/product/10.2.0/db

PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH
exp assyifa/assyifa@orcl file=/U_re_directory/exp.dmp');
   dbms_scheduler.run_job ('myjob');
end;
/

:) Done!

Note :

How to see scheduled job status

select status,additional_info from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='Scheduled_NAME';

How to drop a scheduled job in oracle

BEGIN
  dbms_scheduler.drop_job(job_name => 'Scheduled_NAME');
END;

Grant privilege tu user

SQL> conn system
SQL> grant create job to your_user;
SQL> grant create EXTERNAL job to your_user;
sources :

https://oracle-base.com/articles/misc/oracle-shell-scripting
http://rohmad.net/2008/07/24/menjalankan-os-command-atau-shell-script-dari-plsql/
https://askubuntu.com/questions/38661/how-do-i-run-sh-files
https://stackoverflow.com/questions/37458051/how-to-drop-a-scheduled-job-in-oracle
http://www.dba-oracle.com/t_execute_operating_system_os_command_oracle.htm

Compile .fmb in folder

one file

ifcmp60 Module=c:\workspaces\project\forms\section\form.fmb Userid=scott/tiger@db batch=yes module_type=form

in folder

FOR %i IN (.fmb) DO ifcmp60 Userid=scott/tiger@db batch=yes module_type=form module=%i batch=yes module_type=form compile_all=yes window_state=minimize


source :






https://dukez.wordpress.com/2010/11/15/compile-oracle-forms-6i-from-command-line-with-olb/

https://www.scribd.com/document/63561272/Batch-Compile-Script-for-Forms-Reports-Libraries-Menus-and-Also-Converting-Binary-to-Text-and-Back-ID-191529-1

How to sort the result from string_agg() / order by string_agg on oracle 10g

 Hi there now day i will share how to order by string_agg() result
and here it's the simple code :

select string_agg(prod,' | ') FROM
  (SELECT product as prod FROM tblproducts ORDER BY product )MAIN;

source : https://stackoverflow.com/questions/24906826/how-to-sort-the-result-from-string-agg

How to Copy a List of Files in a Windows Folder Into an Excel List

https://smallbusiness.chron.com/copy-list-files-windows-folder-excel-list-40032.html

Ghostscript Batch file - execute and quit - gswin32c

Hi there i'm trying execute GhostScript on batch file

Problem is when first line is executed, it opens GhostScript window. i have to manually type quit or close the window to return the control to parent to execute the next command in batch.

To avoid Ghostscript opening a window, don't use gswin32.exe.
Use gswin32c.exe instead. (The c in the name is to indicate it's for console only...)

and this is the script

gswin32c -dNOPAUSE -dBATCH -sDEVICE=pdfwrite -sOUTPUTFILE=pdfresult.pdf pdf1.pdf pdf2.pdf pdf3.pdf

https://stackoverflow.com/questions/14859490/ghostscript-gswin32c-hangs-but-gswin32-works

Oracle 10g script how to convert image to pdf and merge multiple pdf file

declare
    OUT_FILE TEXT_IO.FILE_TYPE;
    ITEM VARCHAR2(10000);
   
localpath varchar2(1000);
kumpulanfilepdf varchar2(1000);
    xcrecord number;
    nama_file varchar2(100);
    cursor c is
    select rj_no,a.reg_no,reg_name,vno_sep
    from rstxn_RJHDRS a,rsmst_pasiens b
    where a.reg_no=b.reg_no
    and rj_status='L'
    and cek_bayar='1'
    and klaim_id in (select klaim_id from RSMST_KLAIMTYPES where klaim_status='BPJS')
    and to_char(rj_date,'mm/yyyy')=:tool_rj.bulan;
    c_rec c%rowtype;
   
BEGIN
xcrecord:=1;


        open c;
        loop
            fetch c into c_rec;
          exit when  c%notfound;
    --membuat nama file
    if c_rec.vno_sep is not null then
        nama_file:=c_rec.vno_sep;
    else
        nama_file:=xcrecord;
    end if;
localpath :='c:\RJ\'||replace(:tool_rj.bulan,'/','_')||'\'||nama_file||'_'||replace(c_rec.reg_name, chr(32), '')||'\';   
--////////////COPY bpjs_PATH//////////////////////////
OUT_FILE := TEXT_IO.FOPEN('c:\anshary.bat','W');
ITEM := 'md '||localpath||'
del c:\anshary.bat';
TEXT_IO.PUT(OUT_FILE,ITEM);
TEXT_IO.FCLOSE(OUT_FILE);
--//////////////////////////////////////

host('c:\anshary.bat',NO_SCREEN );
   
 --export KWITANSI TOTAL            
    declare
  pl_id ParamList;
  BEGIN

          pl_id := Get_Parameter_List('tmpdata');
                
                 IF NOT Id_Null(pl_id) THEN
                 Destroy_Parameter_List( pl_id );
                 END IF;
                
                
  pl_id := Create_Parameter_List('tmpdata');
 
   --Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
  Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'no');
  Add_Parameter(pl_id, 'rjno', TEXT_PARAMETER, c_rec.rj_no);
  Add_Parameter(pl_id, 'td', TEXT_PARAMETER, c_rec.reg_name);
  Add_Parameter(pl_id, 'Destype' , Text_Parameter , 'File');
  Add_Parameter(pl_id, 'Desname' , Text_Parameter , localpath||'simrs01'||'.pdf');
  Add_Parameter(pl_id, 'Desformat', Text_Parameter , 'Pdf');
   Run_Product(REPORTS, 'exppdfbpjs.rep', SYNCHRONOUS, runtime,FILESYSTEM, pl_id, NULL);
  END;

 xcrecord:=xcrecord+1;

-- ///////////////////////////////
 declare
         cursor cxxx is
    select uploadbpjs
    from RSTXN_RJUPLOADBPJSES
    where rj_no=c_rec.rj_no;
    c_recxxx cxxx%rowtype;
    vbpjspath varchar2(100);
 BEGIN
     select bpjs_path into vbpjspath from rsmst_identitases;   

        open cxxx;
        loop
            fetch cxxx into c_recxxx;
          exit when  cxxx%notfound;
 --////////////COPY bpjs_PATH//////////////////////////
OUT_FILE := TEXT_IO.FOPEN('c:\anshary.bat','W');

ITEM := 'copy '||'"'||vbpjspath||c_recxxx.uploadbpjs||'" "'||localpath||'"
del c:\anshary.bat';
TEXT_IO.PUT(OUT_FILE,ITEM);
TEXT_IO.FCLOSE(OUT_FILE);
--//////////////////////////////////////

host('c:\anshary.bat',NO_SCREEN );

        end loop;
        close cxxx;

 --////////////MERGE PDF//////////////////////////
 select string_AGG(localpath||uploadbpjs) into kumpulanfilepdf from RSTXN_RJUPLOADBPJSES where rj_no=c_rec.rj_no;
 kumpulanfilepdf:=replace(kumpulanfilepdf,',',chr(32));
 OUT_FILE := TEXT_IO.FOPEN('c:\anshary.bat','W');
 ITEM := 'gswin32 -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE='||localpath||nama_file||'.pdf'||' '||kumpulanfilepdf||' '||localpath||'simrs01'||'.pdf
 del '||kumpulanfilepdf||' '||localpath||'simrs01'||'.pdf
 del c:\anshary.bat';
 TEXT_IO.PUT(OUT_FILE,ITEM);
 TEXT_IO.FCLOSE(OUT_FILE);
--//////////////////////////////////////

host('c:\anshary.bat',NO_SCREEN );
       
        end;
-- ///////////////////////////////       
        end loop;
       
msg_alert('Proses selesai.','I',false);
END;