Category: filepermission

Pandas, Numpy, Encryption/Decryption, Hidden Files In Python (Crossover between Space Stone, Reality Stone & Mind Stone of Python-Verse)

Posted on by SatyakiDe in Data Science, exposure, filepermission, function, numpy, objects, operating system, Pandas, Python, read, replace, Technology

So, here we come up with another crossover of Space Stone, Reality Stone & Mind Stone of Python-Verse. It is indeed exciting & I cannot wait to explore that part further. Today, in this post, we’ll see how one application can integrate all these key ingredients in Python to serve the purpose. Our key focus will be involving popular packages like Pandas, Numpy & Popular Encryption-Decryption techniques, which include some hidden files as well.

So, our objective here is to proceed with the encryption & decryption technique. But, there is a catch. We need to store some salt or tokenized value inside a hidden file. Our application will extract the salt value from it & then based on that it will perform Encrypt/Decrypt on the data.

Why do we need this approach?

The answer is simple. On many occasions, we don’t want to store our right credentials in configuration files. Also, we don’t want to keep our keys to open to other developers. There are many ways you can achieve this kind of security.  Today, I’ll be showing a different approach to make the same.

Let’s explore.

As usual, I’ll provide the solution, which is tested in Windows & MAC & provide the script. Also, I’ll explain the critical lines of those scripts to understand it from a layman point of view. And, I won’t explain any script, which I’ve already explained in my earlier post. So, you have to refer my old post for that.

To encrypt & decrypt, we need the following files, which contains credentials in a csv. Please find the sample data –

Config_orig.csv

Orig_File

Please see the file, which will be hidden by the application process.

Token_Salt_File

As you can see, this column contains the salt, which will be used in our Encryption/Decryption.

1. clsL.py (This script will create the csv files or any intermediate debug csv file after the corresponding process. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
###########################################
#### Written By: SATYAKI DE        ########
#### Written On: 25-Jan-2019       ########
####                               ########
#### Objective: Log File           ########
###########################################
import pandas as p
import platform as pl
from clsParam import clsParam as cf

class clsL(object):
    def __init__(self):
        self.path = cf.config['PATH']

    def logr(self, Filename, Ind, df, subdir=None):
        try:
            x = p.DataFrame()
            x = df
            sd = subdir

            os_det = pl.system()

            if sd == None:
                if os_det == "Windows":
                    fullFileName = self.path + '\\' + Filename
                else:
                    fullFileName = self.path + '/' + Filename
            else:
                if os_det == "Windows":
                    fullFileName = self.path + '\\' + sd + "\\" + Filename
                else:
                    fullFileName = self.path + '/' + sd + "/" + Filename

            if Ind == 'Y':
                x.to_csv(fullFileName, index=False)

            return 0

        except Exception as e:
            y = str(e)
            print(y)
            return 3

2. clsParam.py (This is the script that will be used as a parameter file & will be used in other python scripts.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
###########################################
#### Written By: SATYAKI DE        ########
#### Written On: 25-Jan-2019       ########
#### Objective: Parameter File     ########
###########################################

import os
import platform as pl

class clsParam(object):

    config = {
        'FILENAME' : 'test.amca',
        'OSX_MOD_FILE_NM': '.test.amca',
        'CURR_PATH': os.path.dirname(os.path.realpath(__file__)),
        'NORMAL_FLAG': 32,
        'HIDDEN_FLAG': 34,
        'OS_DET': pl.system()
    }

 

3. clsWinHide.py (This script contains the core logic of hiding/unhiding a file under Windows OS. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
###########################################
#### Written By: SATYAKI DE          ######
#### Written On: 25-Jan-2019         ######
####                                 ######
#### This script will hide or Unhide ######
#### Files in Windows.               ######
###########################################

import win32file
import win32con
from clsParam import clsParam as cp

class clsWinHide(object):
    def __init__(self):
        self.path = cp.config['CURR_PATH']
        self.FileName = cp.config['FILENAME']
        self.normal_file_flag = cp.config['NORMAL_FLAG']

    def doit(self):
        try:
            path = self.path
            FileName = self.FileName

            FileNameWithPath = path + '\\' + FileName
            flags = win32file.GetFileAttributesW(FileNameWithPath)
            win32file.SetFileAttributes(FileNameWithPath,win32con.FILE_ATTRIBUTE_HIDDEN | flags)

            return 0
        except Exception as e:
            x = str(e)
            print(x)

            return 1

    def undoit(self):
        try:
            path = self.path
            FileName = self.FileName
            normal_file_flag = self.normal_file_flag

            FileNameWithPath = path + '\\' + FileName
            win32file.SetFileAttributes(FileNameWithPath,win32con.FILE_ATTRIBUTE_NORMAL | int(normal_file_flag))

            return 0
        except Exception as e:
            x = str(e)
            print(x)

            return 1

Key lines that we would like to explore are as follows –

def doit()

flags = win32file.GetFileAttributesW(FileNameWithPath)
win32file.SetFileAttributes(FileNameWithPath,win32con.FILE_ATTRIBUTE_HIDDEN | flags)

The above two lines under doit() functions are changing the file attributes in Windows OS to the hidden mode by assigning the FILE_ATTRIBUTE_HIDDEN property.

def undoit()

normal_file_flag = self.normal_file_flag

FileNameWithPath = path + '\\' + FileName
win32file.SetFileAttributes(FileNameWithPath,win32con.FILE_ATTRIBUTE_NORMAL | int(normal_file_flag))

As the script suggested, the application is setting the file attribute of a hidden file to FILE_ATTRIBUTE_NORMAL & set the correct flag from parameters, which leads to the file appears as a normal windows file.

4. clsOSXHide.py (This script contains the core logic of hiding/unhiding a file under OSX, i.e., MAC OS. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
###########################################
#### Written By: SATYAKI DE           #####
#### Written On: 25-Jan-2019          #####
####                                  #####
#### Objective: This script will hide #####
#### or Unhide the file in OSX.       #####
###########################################

import os
from clsParam import clsParam as cp

class clsOSXHide(object):
    def __init__(self):
        self.path = cp.config['CURR_PATH']
        self.FileName = cp.config['FILENAME']
        self.OSX_Mod_FileName = cp.config['OSX_MOD_FILE_NM']
        self.normal_file_flag = cp.config['NORMAL_FLAG']

    def doit(self):
        try:
            path = self.path
            FileName = self.FileName

            FileNameWithPath = path + '/' + FileName
            os.rename(FileNameWithPath, os.path.join(os.path.dirname(FileNameWithPath),'.'
                                                     + os.path.basename(FileNameWithPath)))

            return 0
        except Exception as e:
            x = str(e)
            print(x)

            return 1

    def undoit(self):
        try:
            path = self.path
            FileName = self.FileName
            OSX_Mod_FileName = self.OSX_Mod_FileName

            FileNameWithPath = path + '/' + FileName
            os.rename(OSX_Mod_FileName, FileNameWithPath)

            return 0
        except Exception as e:
            x = str(e)
            print(x)

            return 1

The key lines that we’ll be exploring here are as follows –

def doit()

FileNameWithPath = path + '/' + FileName
os.rename(FileNameWithPath, os.path.join(os.path.dirname(FileNameWithPath),'.'
                                         + os.path.basename(FileNameWithPath)))

In MAC or Linux, any file starts with ‘.’ will be considered as a hidden file. Hence, we’re changing the file type by doing this manipulation.

def undoit()

OSX_Mod_FileName = self.OSX_Mod_FileName

FileNameWithPath = path + '/' + FileName
os.rename(OSX_Mod_FileName, FileNameWithPath)

In this case, our application simply renaming a file with its the original file to get the file as a normal file.

Let’s understand that in Linux or MAC, you have a lot of other ways to restrict any files as it has much more granular level access control.  But, I thought, why not take a slightly different & fun way to achieve the same. After all, we’re building an Infinity War for Python verse. A little bit of fun will certainly make some sense. 🙂

5. clsProcess.py (This script will invoke any of the hide scripts, i.e. clsWinHide.py or clsOSXHide.py based on the OS platform. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
###########################################
#### Written By: SATYAKI DE          ######
#### Written On: 25-Jan-2019         ######
####                                 ######
#### Objective: Based on the OS, this######
#### script calls the actual script. ######
###########################################

from clsParam import clsParam as cp

plat_det = cp.config['OS_DET']

# Based on the platform
# Application is loading subprocess
# in order to avoid library missing
# case against cross platform

if plat_det == "Windows":
    import clsWinHide as win
else:
    import clsOSXHide as osx

# End of conditional class load

class clsProcess(object):
    def __init__(self):
        self.os_det = plat_det

    def doit(self):
        try:

            os_det = self.os_det
            print("OS Info: ", os_det)

            if os_det == "Windows":
                win_doit = win.clsWinHide()
                ret_val = win_doit.doit()
            else:
                osx_doit = osx.clsOSXHide()
                ret_val = osx_doit.doit()

            return ret_val
        except Exception as e:
            x = str(e)
            print(x)

            return 1

    def undoit(self):
        try:

            os_det = self.os_det
            print("OS Info: ", os_det)

            if os_det == "Windows":
                win_doit = win.clsWinHide()
                ret_val = win_doit.undoit()
            else:
                osx_doit = osx.clsOSXHide()
                ret_val = osx_doit.undoit()

            return ret_val
        except Exception as e:
            x = str(e)
            print(x)

            return 1

Key lines to explores are as follows –

from clsParam import clsParam as cp

plat_det = cp.config['OS_DET']

# Based on the platform
# Application is loading subprocess
# in order to avoid library missing
# case against cross platform

if plat_det == "Windows":
    import clsWinHide as win
else:
    import clsOSXHide as osx

This step is very essential to run the same python scripts in both the environments, e.g. in this case like MAC & Windows.

So, based on the platform details, which the application is getting from the clsParam class, it is loading the specific class to the application. And why it is so important.

Under Windows OS, this will work if you load both the class. But, under MAC, this will fail as the first program will try to load all the libraries & it may happen that the pywin32/pypiwin32 package might not available under MAC. Anyway, you are not even using that package. So, this conditional class loading is significant.

os_det = self.os_det
print("OS Info: ", os_det)

if os_det == "Windows":
    win_doit = win.clsWinHide()
    ret_val = win_doit.doit()
else:
    osx_doit = osx.clsOSXHide()
    ret_val = osx_doit.doit()

As you can see that, based on the OS, it is invoking the correct function of that corresponding class.

6. clsEnDec.py (This script will read the credentials from a csv file & then based on the salt captured from the hidden file, it will either encrypt or decrypt the content. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
###########################################
#### Written By: SATYAKI DE        ########
#### Written On: 25-Jan-2019       ########
#### Package Cryptography needs to ########
#### install in order to run this  ########
#### script.                       ########
####                               ########
#### Objective: This script will   ########
#### encrypt/decrypt based on the  ########
#### hidden supplied salt value.   ########
###########################################

import pandas as p
from cryptography.fernet import Fernet

class clsEnDec(object):

    def __init__(self, token):
        # Calculating Key
        self.token = token

    def encrypt_str(self):
        try:
            # Capturing the Salt Information
            salt = self.token
            # Fetching the content of lookup file
            df_orig = p.read_csv('Config_orig.csv', index_col=False)

            # Checking Individual Types inside the Dataframe
            cipher = Fernet(salt)

            df_orig['User'] = df_orig['User'].apply(lambda x1: cipher.encrypt(bytes(x1,'utf8')))
            df_orig['Pwd'] = df_orig['Pwd'].apply(lambda x2: cipher.encrypt(bytes(x2,'utf8')))

            # Writing to the File
            df_orig.to_csv('Encrypt_Config.csv', index=False)

            return 0
        except Exception as e:
            x = str(e)
            print(x)
            return 1

    def decrypt_str(self):
        try:
            # Capturing the Salt Information
            salt = self.token
            # Checking Individual Types inside the Dataframe
            cipher = Fernet(salt)

            # Fetching the Encrypted csv file
            df_orig = p.read_csv('Encrypt_Config.csv', index_col=False)

            df_orig['User'] = df_orig['User'].apply(lambda x1: str(cipher.decrypt(bytes(x1[2:-1],'utf8'))).replace("b'","").replace("'",""))
            df_orig['Pwd'] = df_orig['Pwd'].apply(lambda x2: str(cipher.decrypt(bytes(x2[2:-1],'utf8'))).replace("b'","").replace("'",""))

            # Writing to the file
            df_orig.to_csv('Decrypt_Config.csv', index=False)

            return 0
        except Exception as e:
            x = str(e)
            print(x)
            return 1

Key lines from this script are as follows –

def encrypt_str()

# Checking Individual Types inside the Dataframe
cipher = Fernet(salt)

df_orig['User'] = df_orig['User'].apply(lambda x1: cipher.encrypt(bytes(x1,'utf8')))
df_orig['Pwd'] = df_orig['Pwd'].apply(lambda x2: cipher.encrypt(bytes(x2,'utf8')))

So, once you captured the salt from that hidden file, the application is capturing that value over here. And, based on that both the field will be encrypted. But, note that cryptography package is required for this. And, you need to pass bytes value to work this thing. Hence, we’ve used bytes() function over here.

def decrypt_str()

cipher = Fernet(salt)

# Fetching the Encrypted csv file
df_orig = p.read_csv('Encrypt_Config.csv', index_col=False)

df_orig['User'] = df_orig['User'].apply(lambda x1: str(cipher.decrypt(bytes(x1[2:-1],'utf8'))).replace("b'","").replace("'",""))
df_orig['Pwd'] = df_orig['Pwd'].apply(lambda x2: str(cipher.decrypt(bytes(x2[2:-1],'utf8'))).replace("b'","").replace("'",""))

Again, in this step, our application is extracting the salt & then it retrieves the encrypted values of corresponding fields & applies the decryption logic on top of it. Note that, since we need to pass bytes value to get it to work. Hence, your output will be appended with (b’xxxxx’). To strip that, we’ve used the replace() functions. You can use regular expression using pattern matching as well.

7. callEnDec.py (This script will create the split csv files or final merge file after the corresponding process. However, this can be used as normal verbose debug logging as well. Hence, the name comes into the picture.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
###########################################
#### Written By: SATYAKI DE           #####
#### Written On: 25-Jan-2019          #####
####                                  #####
#### Objective: Main calling function #####
###########################################

import clsEnDec as ed
import clsProcess as h
from clsParam import clsParam as cp
import time as t
import pandas as p

def main():
    print("")
    print("#" * 60)
    print("Calling (Encryption/Decryption) Package!!")
    print("#" * 60)
    print("")

    # Unhiding the file
    x = h.clsProcess()
    ret_val_unhide = x.undoit()

    if ret_val_unhide == 0:
        print("Successfully Unhide the file!")
    else:
        print("Unsuccessful to Unhide the file!")

    # To See the Unhide file
    t.sleep(10)

    print("*" * 60)
    print("Proceeding with Encryption...")
    print("*" * 60)

    # Getting Salt Value from the hidden files
    # by temporarily making it available
    FileName = cp.config['FILENAME']
    df = p.read_csv(FileName, index_col=False)
    salt = str(df.iloc[0]['Token_Salt'])
    print("-" * 60)
    print("Salt: ", salt)
    print("-" * 60)

    # Calling the Encryption Method
    x = ed.clsEnDec(salt)
    ret_val = x.encrypt_str()

    if ret_val == 0:
        print("Encryption Successful!")
    else:
        print("Encryption Failure!")

    print("")
    print("*" * 60)
    print("Checking Decryption Now...")
    print("*" * 60)

    # Calling the Decryption Method
    ret_val1 = x.decrypt_str()

    if ret_val1 == 0:
        print("Decryption Successful!")
    else:
        print("Decryption Failure!")

    # Hiding the salt file
    x = h.clsProcess()
    ret_val_hide = x.doit()

    if ret_val_hide == 0:
        print("Successfully Hide the file!")
    else:
        print("Unsuccessful to Hide the file!")

    print("*" * 60)
    print("Operation Done!")
    print("*" * 60)

if __name__ == '__main__':
    main()

And, here comes the final calling methods.

The key lines that we would like to discuss –

# Getting Salt Value from the hidden files
# by temporarily making it available
FileName = cp.config['FILENAME']
df = p.read_csv(FileName, index_col=False)
salt = str(df.iloc[0]['Token_Salt'])

As I’ve shown that, we have our hidden files that contain only 1 row & 1 column. To extract the specific value we’ve used iloc with the row number as 0 along with the column name, i.e. Token_Salt.

Now, let’s see how it runs –

Windows (64 bit):

Win_Run

Mac (32 bit):

MAC_Run

So, from the screenshot, we can see our desired output & you can calculate the aggregated value based on our sample provided in the previous screenshot.

Let’s check the Encrypted & Decrypted values –

Encrypted Values (Encrypt_Config.csv):

Encrypted_File

Decrypted Values (Decrypt_Config.csv):

Decrypted_File

So, finally, we’ve achieved our target.

I hope this will give you some more idea about more insights into the Python verse. Let me know – how do you think about this post.

Till then – Happy Avenging!

Oracle procedure using Java

Posted on by SatyakiDe in call, clob, dbms_dwrite_file, dbms_java, extends, filenotfoundexception, filepermission, grant_permission, ioexception, java, objects, operating system, println, serveroutput, vpdb

Today, i’m going to discuss another powerful feature of Oracle. That is embedding your Java code inside Oracle Procedures. This gives a lot of flexibility & power to Oracle and certainly you can do plenty of things which generally are very difficult to implement directly.

In this purpose i cannot restrict myself to explanation made by  Bulusu Lakshman and that is –

From Oracle 9i a new environments are taking place where Java and PL/SQL can interact as two major database languages. There are many advantages to using both languages –

PL/SQL Advantage:

  • Intensive Database Access – It is faster than Java.
  • Oracle Specific Functionality that has no equivalent in Java such as using dbms_lock & dbms_alert.
  • Using the same data types and language construct as SQL providing seamless access to the database.

JAVA Advantage:

  • Automatic garbage collection, polymorphism, inheritance, multi-threading
  • Access to system resources outside of the database such as OS commands, files, sockets
  • Functionality not avialable in PL/SQL such as OS Commands, fine-grained security policies, image generation, easy sending of e-mails with attachements using JavaMail.

But, i dis-agree with him in case of fine grained security policies as Oracle has drastically improves it and introduces security policies like – VPDB (Virtual Private Database) & Database Vault. Anyway, we’ll discuss these topics on some other day.

For better understanding i’m follow categories and we will explore them one by one. Hope you get some basic idea on this powerful feature by Oracle.

Before proceed we have to know the basics of the main ingredients called dbms_java .

We’ve to prepare the environment.

In Sys,

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
sys@ORCL>
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','writeFileDescriptor','');

PL/SQL procedure successfully completed.

Elapsed: 00:00:53.54
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','readFileDescriptor','');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission','D:\Java_Output\*.*','read,write,execute,delete');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
sys@ORCL>

Let’s concentrate on our test cases.

In Scott,

Type: 1

scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:02.77
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and compile java source named "Print_Hello"
  2  as
  3    import java.io.*;
  4    public class Print_Hello
  5      {
  6         public static void dislay()
  7           {
  8             System.out.println("Hello World...... In Java Through Oracle....... ");
  9           }
 10      };
 11  /

Java created.

Elapsed: 00:00:44.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_print
  2  as
  3    language java name 'Print_Hello.dislay()';
  4  /

Procedure created.

Elapsed: 00:00:01.39
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);

Call completed.

Elapsed: 00:00:00.34
scott@ORCL>
scott@ORCL>set serveroutput on size 1000000;
scott@ORCL>
scott@ORCL>exec java_print;
Hello World...... In Java Through Oracle.......

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
scott@ORCL>

Type: 2 (Returning Value from JAVA)

scott@ORCL>
scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.13
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and resolve java source named "ReturnVal"
  2  as
  3    import java.io.*;
  4
  5    public class ReturnVal extends Object
  6      {
  7          public static String Display()
  8          throws IOException
  9          {
 10             return "Hello World";
 11          }
 12     };
 13  /

Java created.

Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace function ReturnVal
  2  return varchar2
  3  is
  4     language java
  5     name 'ReturnVal.Display() return String';
  6  /

Function created.

Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);

Call completed.

Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>column ReturnVal format a15
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>select ReturnVal from dual;

RETURNVAL
---------------
Hello World

Elapsed: 00:00:00.12
scott@ORCL>
scott@ORCL>

So, you can return the value from the compiled Java source, too.

Type: 3 (Reading console value into JAVA) 

scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL

  1  create or replace java source named "ConsoleRead"
  2  as
  3    import java.io.*;
  4    class ConsoleRead
  5      {
  6        public static void RDisplay(String Det)
  7          {
  8             String dd = Det;
  9             System.out.println("Value Passed In Java Is: " + dd);
 10             System.out.println("Exiting from the Java .....");
 11          }
 12*     };
 13  /

Java created.

scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_UserInput(InputStr in varchar2)
  2     as
  3       language java
  4       name 'ConsoleRead.RDisplay(java.lang.String)';
  5  /

Procedure created.

scott@ORCL>

scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

scott@ORCL>
scott@ORCL>
scott@ORCL>set serveroutput on size 100000
scott@ORCL>
scott@ORCL>exec java_UserInput('Satyaki');
Value Passed In Java Is: Satyaki
Exiting from the Java .....

PL/SQL procedure successfully completed.

scott@ORCL>

Type: 4 (Reading file from OS directory using JAVA) 

scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL

  1  create or replace java source named "ReadTextFile"
  2  as
  3    import java.io.*;
  4    class ReadTextFile
  5  {
  6     public static void Process(String FileName) throws IOException
  7       {
  8          int i;
  9          FileInputStream fin;
 10          try
 11           {
 12             fin = new FileInputStream(FileName);
 13           }
 14         catch(FileNotFoundException e)
 15           {
 16              System.out.println("File Not Found....");
 17              return;
 18           }
 19          catch(ArrayIndexOutOfBoundsException e)
 20           {
 21              System.out.println("Usage: showFile File");
 22              return;
 23           }
 24         do
 25          {
 26             i = fin.read();
 27             if(i != 1)
 28               System.out.println((char) i);
 29          }while(i != 1);
 30         fin.close();
 31       }
 32*  };
 33  /

Java created.

scott@ORCL>
scott@ORCL>create or replace procedure Java_ReadTextFile(FileNameWithPath in varchar2)
  2  as
  3    language java
  4    name 'ReadTextFile.Process(java.lang.String)';
  5  /

Procedure created.

scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

scott@ORCL>
scott@ORCL>
scott@ORCL>exec Java_ReadTextFile('D:\Java_Output\Trial.txt');

Type: 4 (Writing file in  OS directory using JAVA)


In Scott,

scott@ORCL>
scott@ORCL>create or replace java source named "DynWriteTextFile"
  2  as
  3    import java.io.*;
  4  class DynWriteTextFile
  5  {
  6    public static void proc(String ctent,String FlNameWithPath) throws IOException
  7      {
  8        int i,j;
  9        String FileNm = FlNameWithPath;
 10        RandomAccessFile rFile;
 11
 12        try
 13         {
 14           rFile = new RandomAccessFile(FileNm,"rw");
 15         }
 16        catch(FileNotFoundException e)
 17         {
 18            System.out.println("Error Writing Output File....");
 19            return;
 20         }
 21
 22        try
 23         {
 24           int ch;
 25
 26           System.out.println("Processing starts...");
 27
 28           ch = ctent.length();
 29
 30           rFile.seek(rFile.length());
 31           for(int k=0; k<ch; k=k+ctent.length())
 32            {
 33               rFile.writeBytes(ctent);
 34            }
 35         }
 36        catch(IOException e)
 37         {
 38           System.out.println("File Error....");
 39         }
 40        finally
 41     {
 42               try
 43        {
 44                  System.out.println("Successfully file generated....");
 45                  rFile.close();
 46        }
 47          catch(IOException oe)
 48      {
 49                System.out.println("Exception in the catch block of finally is: " +oe);
 50                System.exit(0);
 51      }
 52        }
 53     }
 54  };
 55  /

Java created.

Elapsed: 00:00:00.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure JavaDyn_WriteTextFile(para in varchar2,FileNameWithPath in varchar2)
  2  as
  3    language JAVA
  4    name 'DynWriteTextFile.proc(java.lang.String, java.lang.String)';
  5  /

Procedure created.

Elapsed: 00:00:00.15
scott@ORCL>

In Sys, 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

sys@ORCL>set timi on
sys@ORCL>
sys@ORCL>
sys@ORCL>create or replace public synonym dbms_dwrite_file for scott.JavaDyn_WriteTextFile;

Synonym created.

Elapsed: 00:00:00.08
sys@ORCL>
sys@ORCL>grant execute on dbms_dwrite_file to scott;

Grant succeeded.

Elapsed: 00:00:00.18
sys@ORCL>
 
In Scott,  

scott@ORCL>
scott@ORCL>create or replace procedure DWrite_Content(dt in date,FileNmWithPath in varchar2)
  2  is
  3    cursor c1
  4    is
  5      select empno,ename,sal
  6      from emp
  7      where hiredate = dt;
  8    r1 c1%rowtype;
  9
 10    str  varchar2(500);
 11  begin
 12     str:= replace(FileNmWithPath,'\','\\');
 13     dbms_dwrite_file('Employee No'||' '||'First Name'||' '||'Salary',str);
 14     dbms_dwrite_file(chr(10),str);
 15     dbms_dwrite_file('---------------------------------------------------',str);
 16     dbms_dwrite_file(chr(10),str);
 17     for r1 in c1
 18     loop
 19       dbms_dwrite_file(r1.empno||' '||r1.ename||' '||r1.sal,str);
 20       dbms_dwrite_file(chr(10),str);
 21     end loop;
 22  exception
 23      when others then
 24        dbms_output.put_line(sqlerrm);
 25  end;
 26  /

Procedure created.

Elapsed: 00:00:00.43
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

Elapsed: 00:00:00.02
scott@ORCL>
scott@ORCL>exec DWrite_Content(to_date('21-JUN-1999','DD-MON-YYYY'),'D:\Java_Output\satyaki.txt');
Processing starts...
Successfully file generated....

PL/SQL procedure successfully completed.
 
Hope, this thread will give you some basic idea about using your Java code with Oracle PL/SQL.
I’ll discuss another topic very soon. Till then – Keep following. 😉
Regards.