14 Replies Latest reply: Apr 8, 2013 11:58 PM by Purvesh K RSS

    calling java file on server

    998578
      Hello there. I created file directory on my server and loaded java file through Oracle Directory Resource in Developer. How do I call and execute this file through SQL developer and to use it someday in Apex. Thanks.
        • 1. Re: calling java file on server
          Purvesh K
          You will be required to create a Function that will call the Java Class.

          A demonstration is provided as Developing Java Stored Procedure.

          If you wish to call the Java class from Apex or SQL, then simply call the Function, which in turn, calls the Java class.
          • 2. Re: calling java file on server
            Karthick_Arp
            Branko1986 wrote:
            Hello there. I created file directory on my server and loaded java file through Oracle Directory Resource in Developer. How do I call and execute this file through SQL developer and to use it someday in Apex. Thanks.
            Is it a PL/SQL related question? For SQL Developer and Apex there are seperate forum.

            {forum:id=260} and {forum:id=137}
            • 3. Re: calling java file on server
              998578
              yes. I need Pl/SQL function
              • 4. Re: calling java file on server
                Purvesh K
                Branko1986 wrote:
                yes. I need Pl/SQL function
                Okay. Did you take any effort to read the link I mentioned in my post? I think it does guide you on how to write such a function.

                So, if you give it a try and post your best effort, experts here can help you resolve your problem.
                • 5. Re: calling java file on server
                  998578
                  Sorry for waiting. I have some problems with SQL* Plus. I will get back to you soon as possible. Thank you for your tip.
                  • 6. Re: calling java file on server
                    998578
                    Ok,this is my Java code:

                     package sign;
                     
                    import java.io.FileInputStream;
                    import java.io.FileOutputStream;
                    import java.io.IOException;
                    import java.security.GeneralSecurityException;
                    import java.security.KeyStore;
                    import java.security.PrivateKey;
                    import java.security.Security;
                    import java.security.cert.Certificate;
                     
                    import org.bouncycastle.jce.provider.BouncyCastleProvider;
                     
                    import com.itextpdf.text.DocumentException;
                    import com.itextpdf.text.Rectangle;
                    import com.itextpdf.text.pdf.PdfReader;
                    import com.itextpdf.text.pdf.PdfSignatureAppearance;
                    import com.itextpdf.text.pdf.PdfStamper;
                    import com.itextpdf.text.pdf.security.BouncyCastleDigest;
                    import com.itextpdf.text.pdf.security.DigestAlgorithms;
                    import com.itextpdf.text.pdf.security.ExternalDigest;
                    import com.itextpdf.text.pdf.security.ExternalSignature;
                    import com.itextpdf.text.pdf.security.MakeSignature;
                    import com.itextpdf.text.pdf.security.MakeSignature.CryptoStandard;
                    import com.itextpdf.text.pdf.security.PrivateKeySignature;
                     
                    public class Sign {
                     public static final String KEYSTORE = "C:/..file on server../2/ks";
                        public static final char[] PASSWORD = "password".toCharArray();
                        public static final String SRC = "C:/..file on server../unsigned1.pdf";
                        public static final String DEST = "C:/..file on server..unsigned1_signed.pdf";
                     
                        public void sign(String src, String dest,
                                Certificate[] chain,
                                PrivateKey pk, String digestAlgorithm, String provider,
                                CryptoStandard subfilter,
                                String reason, String location)
                                        throws GeneralSecurityException, IOException, DocumentException {
                            // Creating the reader and the stamper
                            PdfReader reader = new PdfReader(src);
                            FileOutputStream os = new FileOutputStream(dest);
                            PdfStamper stamper = PdfStamper.createSignature(reader, os, '\0');
                            // Creating the appearance
                            PdfSignatureAppearance appearance = stamper.getSignatureAppearance();
                            appearance.setReason(reason);
                            appearance.setLocation(location);
                            appearance.setVisibleSignature(new Rectangle(36, 748, 144, 780), 1, "sig");
                            // Creating the signature
                            ExternalDigest digest = new BouncyCastleDigest();
                            ExternalSignature signature = new PrivateKeySignature(pk, digestAlgorithm, provider);
                            MakeSignature.signDetached(appearance, digest, signature, chain, null, null, null, 0, subfilter);
                        }
                     
                        public static void main(String[] args) throws GeneralSecurityException, IOException, DocumentException {
                            BouncyCastleProvider provider = new BouncyCastleProvider();
                            Security.addProvider(provider);
                            KeyStore ks = KeyStore.getInstance(KeyStore.getDefaultType());
                            ks.load(new FileInputStream(KEYSTORE), PASSWORD);
                            String alias = (String)ks.aliases().nextElement();
                            PrivateKey pk = (PrivateKey) ks.getKey(alias, PASSWORD);
                            Certificate[] chain = ks.getCertificateChain(alias);
                            Sign  = new Sign();
                            app.sign(SRC, String.format(DEST, 1), chain, pk, DigestAlgorithms.SHA256, provider.getName(), CryptoStandard.CMS, "Test 1", "Ghent");
                            app.sign(SRC, String.format(DEST, 2), chain, pk, DigestAlgorithms.SHA512, provider.getName(), CryptoStandard.CMS, "Test 2", "Ghent");
                            app.sign(SRC, String.format(DEST, 3), chain, pk, DigestAlgorithms.SHA256, provider.getName(), CryptoStandard.CADES, "Test 3", "Ghent");
                            app.sign(SRC, String.format(DEST, 4), chain, pk, DigestAlgorithms.RIPEMD160, provider.getName(), CryptoStandard.CADES, "Test 4", "Ghent");
                        }
                    }
                    it works well in Netbeans, and i did this:
                    SQL> CREATE OR REPLACE FUNCTION sign_sys RETURN VARCHAR2
                    2 AS LANGUAGE JAVA
                    3 NAME 'Sign.sign() return java.lang.String';
                    
                    SQL> VARIABLE Sign VARCHAR2(50);
                    
                    SQL> CALL sign_sys() INTO :Sign;
                    i get this error:
                    ERROR at line 1:
                    ORA-29540: class Sys does not exist
                    Tried with different naming but still get error for class
                    • 7. Re: calling java file on server
                      Purvesh K
                      Branko1986 wrote:

                      it works well in Netbeans, and i did this:
                      SQL> CREATE OR REPLACE FUNCTION sign_sys RETURN VARCHAR2
                      2 AS LANGUAGE JAVA
                      3 NAME 'Sign.sign() return java.lang.String';
                      
                      SQL> VARIABLE Sign VARCHAR2(50);
                      
                      SQL> CALL sign_sys() INTO :Sign;
                      i get this error:
                      ERROR at line 1:
                      ORA-29540: class Sys does not exist
                      Tried with different naming but still get error for class
                      Did you Load the Jar and create the function in same Schema? Because, if you haven't then you would need to do so.

                      Another guess is, Did the Loadjava command execute successfully? If you are not sure, then try to execute below query to see if objects are compiled:
                      select
                        OWNER
                       ,DBMS_JAVA.LONGNAME(OBJECT_NAME) as OBJECT_NAME
                       ,STATUS
                      from
                        ALL_OBJECTS
                      where
                        OBJECT_TYPE like 'JAVA%'
                      You may also want to try the LoadJava with Verbose and Resolve options to get better understanding of what is happening and post here is you still face any problem with the Commands and its error message stack.
                      • 8. Re: calling java file on server
                        998578
                        Thanks for your tip. Yes jar files are in the same schema. Or do you think that i load this .java file with Java code as .jar file on server in this schema? When i execute this query above it shows me that this class exist in schema? Thank you
                        • 9. Re: calling java file on server
                          Purvesh K
                          Branko1986 wrote:
                          Thanks for your tip. Yes jar files are in the same schema. Or do you think that i load this .java file with Java code as .jar file on server in this schema?
                          I guess that would not change the outcome as they would be compiled eventually. But still when things do not work, I would suggest the same.
                          When i execute this query above it shows me that this class exist in schema? Thank you
                          And I guess they are in Valid state, right?

                          Can you show us the output of
                          loadjava -user username/password@db_instance -verbose -resolve YourClassName.java|.class
                          • 10. Re: calling java file on server
                            Purvesh K
                            For a reference, see a below demonstration:
                            I:\dbhome_1\BIN>sqlplus HR/HR
                            
                            SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 7 16:37:19 2013
                            
                            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                            
                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            
                            SQL> select *
                              2  from dual
                              3  ;
                            
                            D
                            -
                            X
                            
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  create or replace and compile
                              2  java source named "HelloWorld"
                              3  as
                              4  public class HelloWorld {
                              5     public static String changeCase(String inputValue){
                              6             return inputValue.toUpperCase();
                              7     }
                              8* }
                            SQL> /
                            
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  create or replace and compile
                              2  java source named "HelloWorld"
                              3  as
                              4  public class HelloWorld {
                              5     public static String changeCase(String inputValue){
                              6             return inputValue.toUpperCase(null);
                              7     }
                              8* }
                            SQL> /
                            
                            SQL> select changecase('abc') chcase from dual;
                            
                            CHCASE
                            --------------------
                            ABC
                            Using, the Create Java Source syntax, you would not have to use the Loadjava command.

                            Also, I would suggest you to start with a basic example that matches your Java method call and then start converting the class into your code that you have prepared.

                            Let us know your attempt and if you encounter any errors.
                            • 11. Re: calling java file on server
                              998578
                              Thanks for the suggestions. When I run last line i get this error:
                                     
                              ERROR at line 1:
                              ORA-00904: "CHANGECASE": invalid identifier
                              • 12. Re: calling java file on server
                                Purvesh K
                                Sorry, I forgot to mention the PL/SQL Wrapper. Here is the complete code:
                                SQL> create or replace and compile
                                  2  java source named "HelloWorld"
                                  3  as
                                  4  public class HelloWorld {
                                  5     public static String changeCase(String inputValue){
                                  6             return inputValue.toUpperCase();
                                  7     }
                                  8  }
                                  9  /
                                
                                Java created.
                                
                                SQL> create or replace function changecase(p_inp varchar2) return varchar2
                                  2  as language java
                                  3  name 'HelloWorld.changeCase(java.lang.String) return java.lang.String';
                                  4  /
                                
                                Function created.
                                
                                SQL> select changecase('abc') chcase from dual;
                                
                                CHCASE
                                --------------------------------------------------------------------------------
                                ABC
                                • 13. Re: calling java file on server
                                  998578
                                  Thank you. It works fine now. I will try on my own code. Thank you again.
                                  • 14. Re: calling java file on server
                                    Purvesh K
                                    If this closes your question, then please Close the thread by marking as Answered.