Run Powershell Command from Oracle Procedure

Question

I need to print pdf documents to my network printer from my android studio application. I want to print the pdf as is with all images and formatting, however, the solutions I have found only allow for the extraction and printing of text from the pdf.

I have also seen solutions mentioning tools like Ghostscript etc which are supposed to convert the pdf to a postscript file, but these tools do not work with Android Studio or at-least I haven't figured out how to integrate them into my application. I cannot pay for tools like jPDFPrint which does exactly what I need.

I started thinking about a work around and came upon the idea of sending my pdf as a blob to my oracle database and invoke a power shell command from a procedure to print it to a specific printer.

I've created and tested the below command to print to my network printer from my PC which works great.

    Start-Process -FilePath “c:\test.pdf” –Verb PrintTo '\\PrintServer\PrinterName' -PassThru | %{sleep 10;$_} | kill

Now I need help with the oracle part. Is it possible to invoke or run a powershell command from within oracle 12c and pass it the pdf blob as well as the printer name?

Answer 1

To extract the BLOB to a PDF.

Create you system folder, eg c:\printthis.

Then create the Oracle directory object mapped to this folder:

CREATE OR REPLACE DIRECTORY print_dir AS 'C:\print_this';

GRANT READ WRITE ON DIRECTORY print_dir TO PUBLIC;

This procedure to extract the BLOB to a file.

CREATE OR REPLACE PROCEDURE extract_pdf ( p_id IN VARCHAR2 ) AS
   CURSOR c1 IS 
      SELECT doc_blob, doc_name
      FROM doc_table
      WHERE doc_id = p_id;
   r1         c1%ROWTYPE;
   v_size     NUMBER( 5,0) := 32000;
   v_len      NUMBER(38,0);
   v_buffer   RAW(32000);
   v_file     UTL_FILE.FILE_TYPE;
BEGIN
   OPEN c1;
   FETCH c1 INTO r1;
   v_file  := UTL_FILE.FOPEN('PRINT_DIR', r1.doc_name, 'wb', 32760 );
   v_start := 1;
   v_len   := DBMS_LOB.GETLENGTH( r.bbl_fic );
   WHILE v_start <= v_len LOOP
      DBMS_LOB.READ(
             r.bbl_fic,
             LEAST( v_len - v_start + 1, v_size ),
             v_start,
             v_buffer
           );
      UTL_FILE.PUT_RAW( v_file, v_buffer );
      UTL_FILE.FFLUSH( v_file ); 
      v_start := v_start + v_size;
    END LOOP;
    UTL_FILE.FCLOSE( v_file );
--  Write the CMD file
    v_file := UTL_FILE.FOPEN('PRINT_DIR', r1.doc_name || '.bat', 'w' );
    UTL_FILE.PUT_LINE(     'Start-Process -FilePath “c:\print_this\' || r1.doc_name || '” –Verb PrintTo '\\PrintServer\PrinterName' -PassThru | %{sleep 10;$_} | kill' );
    UTL_FILE.CLOSE(v_file);
END;
/

A Windows At job that runs, polls the c:\print_this folder for files and runs the .bat command, then deletes it.

@ECHO OFF
setlocal enabledelayedexpansion
for %%f in (c:\print_this\*.bat) do (
  echo "Name: %%f"
  powershell %%f
  del %%f
)

The question remains of how to stitch this together. Your Android app calls your procedure to store the PDF as a BLOB. It must also be able to call the procedure suggested above to extract the saved BLOB to a database server file, so this procedure to extract the ODF AND create the print bat script is called after the save BLOB.

The Task Scheduler job polls the directory for the bat scripts, runs them deletes them.

You cannot directly issue a command to the server host from within Oracle. You can use DBMS_SCHEDULER. In the above example, the job would take the doc_id as a parameter, and execute this via DBMS_SCHEDULER.RUN_JOB. I cant remember how to do this precisely, but I hope my other suggestion with the Task Scheduler in Windows is fruitful for you.