Sunday 26 October 2014
Tuesday 21 October 2014
Converting a line to line Letter Case in bash script.
Here is an example of converting a sentence to Letter Case.
Suppose there is a string "hello world how are you" so when you convert it it should be converted to "Hello World How Are You". I have tried a bash script which does the same after taking an input from the user. It exclusively uses upper case conversion formula ^^ and substring formula ${string:startpos:length.}.
Just follow the example
#!/bin/bash
# Author Subhroneel Ganguly
# Date 21.10.2014
echo Enter String :
read line
lettercase=""
for wrd in $line
do
let size=${#wrd}
firstchar=${wrd:0:1}
firstchar=${firstchar^^}
lettercase=$lettercase$firstchar${wrd:1:$size}" "
done
echo $lettercase
If you want more tutorials subscribe to my channel : https://www.youtube.com/subhro190776.
Suppose there is a string "hello world how are you" so when you convert it it should be converted to "Hello World How Are You". I have tried a bash script which does the same after taking an input from the user. It exclusively uses upper case conversion formula ^^ and substring formula ${string:startpos:length.}.
Just follow the example
#!/bin/bash
# Author Subhroneel Ganguly
# Date 21.10.2014
echo Enter String :
read line
lettercase=""
for wrd in $line
do
let size=${#wrd}
firstchar=${wrd:0:1}
firstchar=${firstchar^^}
lettercase=$lettercase$firstchar${wrd:1:$size}" "
done
echo $lettercase
If you want more tutorials subscribe to my channel : https://www.youtube.com/subhro190776.
How to abbreviate a line in Linux bash script
Today I am trying to abbreviate a line through linux bash script and found it so easy to do it with scripting so I am posting it here.
Here I have user read command to read one line (ended with pressing enter key) and the program read the entire line (one word at a time) and abbreviate with the first letter of each word.
#!/bin/bash
# Author Subhroneel Ganguly
# Date 21.10.2014
echo Enter String :
read line
# read reads an entire line and stores it in line variable.
abbr=""
#initializing abbr to ""
for wrd in $line
do
# loop reading one word per iteration
abbr=$abbr${wrd:0:1}"."
# :0:1 is position:number of character
done
echo ${abbr^^}
# ^^ convert the entire string (abbr not $abbr) to upper case.
For more tutorial subscribe to my youtube channel : https://www.youtube.com/subhro190776
Here I have user read command to read one line (ended with pressing enter key) and the program read the entire line (one word at a time) and abbreviate with the first letter of each word.
#!/bin/bash
# Author Subhroneel Ganguly
# Date 21.10.2014
echo Enter String :
read line
# read reads an entire line and stores it in line variable.
abbr=""
#initializing abbr to ""
for wrd in $line
do
# loop reading one word per iteration
abbr=$abbr${wrd:0:1}"."
# :0:1 is position:number of character
done
echo ${abbr^^}
# ^^ convert the entire string (abbr not $abbr) to upper case.
For more tutorial subscribe to my youtube channel : https://www.youtube.com/subhro190776
Monday 20 October 2014
Using PRAGMA EXCEPTION_INIT for user defined exception in PL/SQL
What is PRAGMA.
PRAGMA is a compiler directive. When you define exception you can set an error_number and error message to your exception.
A compiler directive is an instruction to the compiler to complete a task before formally starting to compile the program, thus they are sometimes called pre-processor directives. Among other items, during the pre-processor step the compiler is looking for compiler directives and processes them as they are encountered.
So, when the program is compiled the exception is already initialized. So during compilation plsql understands you defined exception.
In this section we are going to write an user defined exception.
It is very simple. We are going to use one condition, if condition does not satisfy then we will raise an exception.
declare
lessthanzero exception;
pragma exception_init(lessthanzero,-20000);
n number;
begin
n:=-4;
if n<0 then
raise_application_error(-20000,'Value must be greater than zero');
end if;
dbms_output.put_line('The value is '||n);
exception
when greaterthanzero then
dbms_output.put_line(sqlerrm(-2000));
end;
NB: If you try with n:=-4 dbms_output message will be "Value must be greater than zero"
and if value is positive i.e. n:=4 then dbms_output message will be "The value is 4".
You can also watch the video on plsql packages in Youtube : https://www.youtube.com/watch?v=6i9VQcBQOfI
Also can subscribe to my channel : https://www.youtube.com/subhro190776
PRAGMA is a compiler directive. When you define exception you can set an error_number and error message to your exception.
A compiler directive is an instruction to the compiler to complete a task before formally starting to compile the program, thus they are sometimes called pre-processor directives. Among other items, during the pre-processor step the compiler is looking for compiler directives and processes them as they are encountered.
So, when the program is compiled the exception is already initialized. So during compilation plsql understands you defined exception.
In this section we are going to write an user defined exception.
It is very simple. We are going to use one condition, if condition does not satisfy then we will raise an exception.
declare
lessthanzero exception;
pragma exception_init(lessthanzero,-20000);
n number;
begin
n:=-4;
if n<0 then
raise_application_error(-20000,'Value must be greater than zero');
end if;
dbms_output.put_line('The value is '||n);
exception
when greaterthanzero then
dbms_output.put_line(sqlerrm(-2000));
end;
NB: If you try with n:=-4 dbms_output message will be "Value must be greater than zero"
and if value is positive i.e. n:=4 then dbms_output message will be "The value is 4".
You can also watch the video on plsql packages in Youtube : https://www.youtube.com/watch?v=6i9VQcBQOfI
Also can subscribe to my channel : https://www.youtube.com/subhro190776
How to write a database package in Oracle PL/SQL.
Packages in Oracle PL/SQL is all in a box. It consists of everything from procedures, functions, global variables, cursors, exceptions every thing that you can do in a PL/SQL block. So here I am just trying to write a small PL/SQL package.
When starting writing package we should just keep in mind one thing that packages consist of a declaration (specification) section and a definition (body) section. You cannot compile a body of package without creating a specification. So here I am going to write a package spec and a package body.
I will be using 1 table employee_master and columns used are emp_name, date_of_birth and emp_id as search key.
--Package specification
create or replace package emp_pkg is
type tbl_emp is table of employee_master%rowtype;
function getEmpName(empid number) return varchar;
function getEmpDOB(empid number) return Date;
procedure displayEmpName;
end emp_pkg;
--Package Body
create or replace package body emp_pkg is
function getEmpName(empid number) return varchar is
empName employee_master.emp_name%type;
begin
select emp_name into empName from employee_master
where emp_id = empid;
return empName;
exception
when no_data_found then
return null;
end getEmpName;
function getEmpDOB(empid number) return Date is
empDOB employee_master.date_of_birth%type;
begin
select date_of_birth into empDOB from employee_master
where emp_id = empid;
return empDOB;
exception
when no_data_found then
return null;
end getEmpDOB;
procedure displayEmpName is
cursor rec is select * from employee_master where emp_grade='S';
v_tbl_emp tbl_emp;
begin
open rec;
loop
fetch rec bulk collect into v_tbl_emp;
for i in 1..v_tbl_emp.count loop
dbms_output.put_line(v_tbl_emp(i).emp_name||','||v_tbl_emp(i).date_of_birth);
end loop;
end loop;
end displayEmpName;
end emp_pkg;
You can also watch the video on plsql packages in Youtube : https://www.youtube.com/watch?v=tHi0Xifws_4
Also can subscribe to my channel : https://www.youtube.com/subhro190776
When starting writing package we should just keep in mind one thing that packages consist of a declaration (specification) section and a definition (body) section. You cannot compile a body of package without creating a specification. So here I am going to write a package spec and a package body.
I will be using 1 table employee_master and columns used are emp_name, date_of_birth and emp_id as search key.
--Package specification
create or replace package emp_pkg is
type tbl_emp is table of employee_master%rowtype;
function getEmpName(empid number) return varchar;
function getEmpDOB(empid number) return Date;
procedure displayEmpName;
end emp_pkg;
--Package Body
create or replace package body emp_pkg is
function getEmpName(empid number) return varchar is
empName employee_master.emp_name%type;
begin
select emp_name into empName from employee_master
where emp_id = empid;
return empName;
exception
when no_data_found then
return null;
end getEmpName;
function getEmpDOB(empid number) return Date is
empDOB employee_master.date_of_birth%type;
begin
select date_of_birth into empDOB from employee_master
where emp_id = empid;
return empDOB;
exception
when no_data_found then
return null;
end getEmpDOB;
procedure displayEmpName is
cursor rec is select * from employee_master where emp_grade='S';
v_tbl_emp tbl_emp;
begin
open rec;
loop
fetch rec bulk collect into v_tbl_emp;
for i in 1..v_tbl_emp.count loop
dbms_output.put_line(v_tbl_emp(i).emp_name||','||v_tbl_emp(i).date_of_birth);
end loop;
end loop;
end displayEmpName;
end emp_pkg;
You can also watch the video on plsql packages in Youtube : https://www.youtube.com/watch?v=tHi0Xifws_4
Also can subscribe to my channel : https://www.youtube.com/subhro190776
Wednesday 15 October 2014
How to write a shell script to find factorial of number
We are going to find factorial of numbers. Factorials are a very important part of algebra, particularly series mathematics.
Here is a program written in bash shell script to find factorial of all the numbers from 0 to n where n is a number enter by user.
#!/bin/bash
#Author: Subhroneel Ganguly
#Date: 15.10.2014
echo Enter Range:
read RANGE
# UM is asigned to 0. We are starting from 0 and NUM will go until RANGE and factorial will be calculated from 0 to RANGE.
NUM=0
while [ "$NUM" -le "$RANGE" ]
do
# FACT is initialize to 1, this has to advantages. One is obvious that we can easily multiply incremented numbers to it to calculate factorial. Another is for 0
# and 1 we will by default get factorial calculated, without entering the loop.
FACT=1
# Starting I from 2 (Number for which factorial will be calculated, 0 and 1 is excluded as we have already calculated factorial of it as FACT=1
I=2
while [ "$I" -le "$NUM" ]
do
# Incremented value of I is multiplied to FACT, (logic of factorial) 5 x 4 x 3 x 2 x 1 for NUM=5
let FACT*=I
let I+=1
done
echo Factorial of $NUM is $FACT
let NUM+=1
done
Also can subscribe to my channel in youtube : https://www.youtube.com/subhro190776
Here is a program written in bash shell script to find factorial of all the numbers from 0 to n where n is a number enter by user.
#!/bin/bash
#Author: Subhroneel Ganguly
#Date: 15.10.2014
echo Enter Range:
read RANGE
# UM is asigned to 0. We are starting from 0 and NUM will go until RANGE and factorial will be calculated from 0 to RANGE.
NUM=0
while [ "$NUM" -le "$RANGE" ]
do
# FACT is initialize to 1, this has to advantages. One is obvious that we can easily multiply incremented numbers to it to calculate factorial. Another is for 0
# and 1 we will by default get factorial calculated, without entering the loop.
FACT=1
# Starting I from 2 (Number for which factorial will be calculated, 0 and 1 is excluded as we have already calculated factorial of it as FACT=1
I=2
while [ "$I" -le "$NUM" ]
do
# Incremented value of I is multiplied to FACT, (logic of factorial) 5 x 4 x 3 x 2 x 1 for NUM=5
let FACT*=I
let I+=1
done
echo Factorial of $NUM is $FACT
let NUM+=1
done
Also can subscribe to my channel in youtube : https://www.youtube.com/subhro190776
How to share Linux file system from Windows Operating System
Sharing linux file system from windows requires a package ti be installed in linux known as SAMBA. This guide is for only Red hat / CentOS users for both 32 and 64 bit OS.
First of all you need to install three packages
For 64 bit OS
1. samba-common-3.6.9-151.el6.x86_64
2. samba-winbind-3.6.9-151.el6.x86_64
3. samba-3.6.9-151.el6.x86_64
4. samba-client-3.6.9-151.el6.x86_64
5. samba-winbind-clients-3.6.9-151.el6.x86_64
For 32 bit
1. samba-common-3.6.9-151.el6.i686
2. samba-winbind-3.6.9-151.el6.i686
3. samba-3.6.9-151.el6.i686
4. samba-client-3.6.9-151.i686
5. samba-winbind-clients-3.6.9-151.el6.i686
I have mentioned here a particular version, you can download by searching with keywords like this
32 bit OS
samba-common el6 i686
samba-winbind el6 i686
samba el6 i686
samba-client el6 i686
samba-winbind-clients el6 i686
64 bit OS
samba-common el6 x86_64
samba-winbind el6 x86_64
samba el6 x86_64
samba-client el6 x86_64
samba-winbind-clients el6 x86_64
You can also try this keyword with yum install provided you have good repository update.
After installing samba
Add samba users.
Go to terminal su as root and add type these commands
smbpasswd -a username # Give your own username i.e. oracle or root. -a option add the user to samba user list if they don't exists.
[root@RHEL6 ~]# smbpasswd -a oracle
New SMB password: # provide your new password
Retype new SMB password: # retype your password
open /etc/samba/smb.conf with vim or gedit as root user
Add / Update this lines
workgroup = WORKGROUP # It can be other name than WORKGROUP depends on your WORKGROUP / DOMAIN name of windows OS, keep it same as windows
server string = Samba Server Version %v
netbios name = RHEL6 # This is you Linux hostname, netbios name is identified by windows to show on network browser.
Now you have to add this lines for specific directory you want to share.
Suppose you want to share /usr/share/, add this lines at the bottom of the file.
[share]
path=/usr/share
read only = no
writable = yes
browsable = yes
valid users = subhroneel root oracle these are samba user name you have created just before.
available = yes
Suppose you want to share /var/www/html/, add this lines at the bottom of the file.
[webshare]
path=/var/www/html
read only = no
writable = yes
browsable = yes
valid users = subhroneel root # these are samba user name you have created just before.
available = yes
It's all done.
Just go to your Windows machine and access your linux machie by ip address, (hostname do not work always, depends on firewall settings on both windows and linux). Provide your samba username and password. Just keep in mind that the user with which you are accessing you linuxmachine should be owner of the directory shared.
THERE IS ANOTHER EASY WAY TO GET ACCESS TO HOME DIRECTORY. No need to add share connfiguration in smb.cong
run this SELINUX command as root user
setsebool -P samba_enable_home_dirs=1
After you run this command when you log in with a particular user you will get read/wrtie access to home directory of that user.
First of all you need to install three packages
For 64 bit OS
1. samba-common-3.6.9-151.el6.x86_64
2. samba-winbind-3.6.9-151.el6.x86_64
3. samba-3.6.9-151.el6.x86_64
4. samba-client-3.6.9-151.el6.x86_64
5. samba-winbind-clients-3.6.9-151.el6.x86_64
For 32 bit
1. samba-common-3.6.9-151.el6.i686
2. samba-winbind-3.6.9-151.el6.i686
3. samba-3.6.9-151.el6.i686
4. samba-client-3.6.9-151.i686
5. samba-winbind-clients-3.6.9-151.el6.i686
I have mentioned here a particular version, you can download by searching with keywords like this
32 bit OS
samba-common el6 i686
samba-winbind el6 i686
samba el6 i686
samba-client el6 i686
samba-winbind-clients el6 i686
64 bit OS
samba-common el6 x86_64
samba-winbind el6 x86_64
samba el6 x86_64
samba-client el6 x86_64
samba-winbind-clients el6 x86_64
You can also try this keyword with yum install provided you have good repository update.
After installing samba
Add samba users.
Go to terminal su as root and add type these commands
smbpasswd -a username # Give your own username i.e. oracle or root. -a option add the user to samba user list if they don't exists.
[root@RHEL6 ~]# smbpasswd -a oracle
New SMB password: # provide your new password
Retype new SMB password: # retype your password
open /etc/samba/smb.conf with vim or gedit as root user
Add / Update this lines
workgroup = WORKGROUP # It can be other name than WORKGROUP depends on your WORKGROUP / DOMAIN name of windows OS, keep it same as windows
server string = Samba Server Version %v
netbios name = RHEL6 # This is you Linux hostname, netbios name is identified by windows to show on network browser.
Now you have to add this lines for specific directory you want to share.
Suppose you want to share /usr/share/, add this lines at the bottom of the file.
[share]
path=/usr/share
read only = no
writable = yes
browsable = yes
valid users = subhroneel root oracle these are samba user name you have created just before.
available = yes
Suppose you want to share /var/www/html/, add this lines at the bottom of the file.
[webshare]
path=/var/www/html
read only = no
writable = yes
browsable = yes
valid users = subhroneel root # these are samba user name you have created just before.
available = yes
It's all done.
Just go to your Windows machine and access your linux machie by ip address, (hostname do not work always, depends on firewall settings on both windows and linux). Provide your samba username and password. Just keep in mind that the user with which you are accessing you linuxmachine should be owner of the directory shared.
THERE IS ANOTHER EASY WAY TO GET ACCESS TO HOME DIRECTORY. No need to add share connfiguration in smb.cong
run this SELINUX command as root user
setsebool -P samba_enable_home_dirs=1
After you run this command when you log in with a particular user you will get read/wrtie access to home directory of that user.
How to return multiple value from a procedures / functions in PL/SQL
Generally what we know that functions return values but procedures not. But still take any programming language be it C++, Visual Basic, Java you can always send parameters by-references who value can be modified inside functions. And such case can be explained as returning values from a procedures/ functions through parameters.
Same is applicable in PL/SQL procedures / functions.
I will be writing one test script which will consists of a procedure and a block from where this procedure will be called. The function will swap two numbers. I am not explaining the logic of swap procedure as I expect you know the logic.
declare
p number;
q number;
procedure swap(x out number,y out number)
tmp number;
begin
tmp:= x;
x:= y;
y:= tmp;
end swap;
begin
p:= 10;
q:= 20;
dbms_output.print_line("Before swap value of p = "||p||" value of q = "||q);
swap(p,q);
dbms_output.print_line("After swap value of p = "||p||" value of q = "||q);
end;
--Just check out that value of x and y are swapped and as they are OUT parameter therefore value of p and q are also changed.
Two pass a variable by reference in PL/SQL you need to pass the variable with OUT parameter.
You can also watch the video on OUT parameter in Youtube : https://www.youtube.com/watch?v=5Qw26Xiwy7s
Also can subscribe to my channel : https://www.youtube.com/subhro190776
Same is applicable in PL/SQL procedures / functions.
I will be writing one test script which will consists of a procedure and a block from where this procedure will be called. The function will swap two numbers. I am not explaining the logic of swap procedure as I expect you know the logic.
declare
p number;
q number;
procedure swap(x out number,y out number)
tmp number;
begin
tmp:= x;
x:= y;
y:= tmp;
end swap;
begin
p:= 10;
q:= 20;
dbms_output.print_line("Before swap value of p = "||p||" value of q = "||q);
swap(p,q);
dbms_output.print_line("After swap value of p = "||p||" value of q = "||q);
end;
--Just check out that value of x and y are swapped and as they are OUT parameter therefore value of p and q are also changed.
Two pass a variable by reference in PL/SQL you need to pass the variable with OUT parameter.
You can also watch the video on OUT parameter in Youtube : https://www.youtube.com/watch?v=5Qw26Xiwy7s
Also can subscribe to my channel : https://www.youtube.com/subhro190776
How to write a bash script to find prime numbers within a range.
Prime number is not the actual goal of this program, the actual goal is to show how nested loop works in bash script. I am giving the program below and will describe the logic of the program.
#!/bin/bash
#Author: Subhroneel Ganguly
#Date: 15.10.2014
echo Enter Range:
# Read user input and store into RANGE variable
read RANGE
# Initialize NUM to 1
NUM=1
#looping NUM from 1 to RANGE and each number will be checked for prime.
while [ "$NUM" -le "$RANGE" ]
do
# variable I is the divisor to test for prime number
I=2
# variable FLAG will indicate whether a number is prime or not.
FLAG=0
# condition is checked if NUM less than to we will do nothing and value of FLAG will be 0 and hence number will be non prime.
if [ "$NUM" -gt 2 ]
then
while [ "$I" -lt "$NUM" ]
do
# % returns modulus, if number gives 0 remainder then it is divisible by the number and hence non prime. Notice we have started I from 2 and will run until NUM-1
if [ $(($NUM%$I)) -eq 0 ]
then
flag=1
fi
let I+=1
done
else
FLAG=1
fi
if [ "$FLAG" -eq 1 ]
then
echo $NUM is not prime
else
echo $NUM is prime
fi
let NUM+=1
done
You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=LkLH6FgRmtw
Press like if you like the video and you can also subscribe to my channel : My Youtube Channel
#!/bin/bash
#Author: Subhroneel Ganguly
#Date: 15.10.2014
echo Enter Range:
# Read user input and store into RANGE variable
read RANGE
# Initialize NUM to 1
NUM=1
#looping NUM from 1 to RANGE and each number will be checked for prime.
while [ "$NUM" -le "$RANGE" ]
do
# variable I is the divisor to test for prime number
I=2
# variable FLAG will indicate whether a number is prime or not.
FLAG=0
# condition is checked if NUM less than to we will do nothing and value of FLAG will be 0 and hence number will be non prime.
if [ "$NUM" -gt 2 ]
then
while [ "$I" -lt "$NUM" ]
do
# % returns modulus, if number gives 0 remainder then it is divisible by the number and hence non prime. Notice we have started I from 2 and will run until NUM-1
if [ $(($NUM%$I)) -eq 0 ]
then
flag=1
fi
let I+=1
done
else
FLAG=1
fi
if [ "$FLAG" -eq 1 ]
then
echo $NUM is not prime
else
echo $NUM is prime
fi
let NUM+=1
done
You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=LkLH6FgRmtw
Press like if you like the video and you can also subscribe to my channel : My Youtube Channel
Using batch select (BULK COLLECT) for fectching records in python.
This content will be helpful for those who have worked with BULK COLLECT in PL/SQL.
BULK COLLECT helps to fetch record in batch process, which helps in optimization of data fetch.
Say in PL/SQL here is a block of PLSQL, which is using BULK COLLECT for query optimization.
declare
cursor rec is select * from employee_master;
emp employee_master%rowtype;
type emprows is TABLE of emp;
t_emprows emprows;
begin
open rec;
loop
fetch rec BULK COLLECT into t_emprows LIMIT 50;
for i in t_emprows.first..t_emprows.last loop
dbms_output.put_lines(.t_emprows(i).emp_name);
end loop;
end loop;
end;
In the above example you can see how bulk collect with limit clause restrict umber of rows fetched into memory. This lessens the disk I/O, and hence fasten the fetch time.
This can also be done in python. Here is an example which fetches record from table and write into a file in csv format.
import cx_Oracle
# con = cx_Oracle.connect('username/password@[ipaddress or hostname]/SID')
# for example con = cx_Oracle.connect('scott/tiger@127.0.0.1/orcl')
con = cx_Oracle.connect('GREENPLY/GREENPLY@192.168.0.109/orcl.localdomain')
cur = con.cursor()
cur.execute('select emp_code,emp_name from employee_master order by 2')
file = open("file.csv", "w")
flag=False
while not flag:
count=0
for row in cur.fetchmany(50):
file.write(row[0] + ',' + row[1] + '\n')
count=count+1
if count<50:
break
file.close()
cur.close()
con.close()
print("File successfully exported")
BULK COLLECT helps to fetch record in batch process, which helps in optimization of data fetch.
Say in PL/SQL here is a block of PLSQL, which is using BULK COLLECT for query optimization.
declare
cursor rec is select * from employee_master;
emp employee_master%rowtype;
type emprows is TABLE of emp;
t_emprows emprows;
begin
open rec;
loop
fetch rec BULK COLLECT into t_emprows LIMIT 50;
for i in t_emprows.first..t_emprows.last loop
dbms_output.put_lines(.t_emprows(i).emp_name);
end loop;
end loop;
end;
In the above example you can see how bulk collect with limit clause restrict umber of rows fetched into memory. This lessens the disk I/O, and hence fasten the fetch time.
This can also be done in python. Here is an example which fetches record from table and write into a file in csv format.
import cx_Oracle
# con = cx_Oracle.connect('username/password@[ipaddress or hostname]/SID')
# for example con = cx_Oracle.connect('scott/tiger@127.0.0.1/orcl')
con = cx_Oracle.connect('GREENPLY/GREENPLY@192.168.0.109/orcl.localdomain')
cur = con.cursor()
cur.execute('select emp_code,emp_name from employee_master order by 2')
file = open("file.csv", "w")
flag=False
while not flag:
count=0
for row in cur.fetchmany(50):
file.write(row[0] + ',' + row[1] + '\n')
count=count+1
if count<50:
break
file.close()
cur.close()
con.close()
print("File successfully exported")
Tuesday 14 October 2014
How to search a file in Linux using find command
Say you want to search a file incredible.txt in C: drive. type dir/s c:\incredible.txt, it will search for incredible.txt in entire C: drive.
In linux also you can do the same. There is a command find.
Syntax to search from / all the directory and sub-directory
find / -depth -name incredible.txt
/ is the directory from where search begins. It can be any sub-directory also, i.e. /usr/share/
depth option search all sub-directory after searching directory.
name option is for the name of the file to search instead of any other attribute.
Just remember one thing best possible result for search is to search as root user. Searching as normal user may fail into some directory if you don't have access to them. i.e. system directory.
Use of wild card is applicable in find command.
You can use wild card into file name.
Suppose you want to search all files with name starting with file and succeed with anything.
Command will be find / -depth -name file*
How to execute command as a root user without been logged in or switched in as root.
As we know if you are not a root user and as a user you do not have the read, write or execute permission on a file then you can do nothing.
If you even authorized to log in as root, but still it's a tire some job type do su - , type password
and after doing your job again exit back to user.
There is an short way to perform this, without going into long process.
Suppose you want to restart network service, so the long process to do such is
$su -
password:
#service network restart
And the shortest way to do so is
$ su -c "service network restart"
password: Enter your password here
YOU ARE DONE.
Take another example
You have a file myfile.txt whose owner is root and you only have read permission to it.
If you type $ vim myfile.txt, file will open in vim but you are ot able to save any changes you made into the file.
But if you type $ su - "vim myfile.txt", it will only ask for the root password and the file will open for read and write.
How to rename a file in Linux
As such there is no specific command to rename file in Linux but you can use mv command to rename file in Linux.
1. Suppose you have a file name oldfile.txt and you need to reame it as newfile.txt
So the command should be
# mv oldfile.txt newfile.txt
It's just like moving file within the same directory from one name to aother.
2. In the same way you can get verbose output while renaming (moving) files. mv command will explain what is been done during the process.
mv -v oldfile.txt newfile.txt
3. You can also make mv iteractive by using -i option
mv -i oldfile.txt newfile.txt
To get the details manual of mv command type
# man mv.
1. Suppose you have a file name oldfile.txt and you need to reame it as newfile.txt
So the command should be
# mv oldfile.txt newfile.txt
It's just like moving file within the same directory from one name to aother.
2. In the same way you can get verbose output while renaming (moving) files. mv command will explain what is been done during the process.
mv -v oldfile.txt newfile.txt
3. You can also make mv iteractive by using -i option
mv -i oldfile.txt newfile.txt
To get the details manual of mv command type
# man mv.
How to write WHEN_VALIDATE trigger in Oracle Forms.
WHEN_VALIDATE_RECORD and WHEN_VALIDATE_ITEM is 2 most useful trigger used in Oracle Forms. It able to restrict user to entering data invalid to business logic. Developer can implement business logic into when validate trigger. Here is the step how to write when validate trigger.
As you can see the picture above, right click on the desired item and select PL/SQL editor.
I am using the name of the block and item as :block1.item1
Here is the trigger code. It will check for item cannot be blank and item value should be greater than 0.
if :block1.item1 is null then
alrt_msg('Item data cannot be blank');
raise form_trigger_failure;
end if;
if :block1.item1<=0 then
alrt_msg('Item value should be greater than zero');
raise form_trigger_failure;
end if;
raise form_trigger_failure is actually restrict user from skipping the validation.
WHEN_VALIDATE_RECORD
when_validate_record trigger check business logic in record level. This trigger is required when you need record level validation.
Don't forget to add this trigger at block level.
Suppose there is an entry form consisting of emp_code, pay, incentive.
Now if someone basic is zero or blank then
This is the when_validate_record plsql code.
if nvl(:block1.pay,0)=0 and nvl(:block1.incentive,0)>0 then
alrt_msg('Incentive cannot be greater than zero when basic is blank');
raise form_trigger_failure;
end if;
We have used nvl to return default value if item is null.
You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=_SbYRyF48dc
Press like if you like the video and you can also subscribe to my channel : https://www.youtube.com/subhro190776
As you can see the picture above, right click on the desired item and select PL/SQL editor.
I am using the name of the block and item as :block1.item1
Here is the trigger code. It will check for item cannot be blank and item value should be greater than 0.
if :block1.item1 is null then
alrt_msg('Item data cannot be blank');
raise form_trigger_failure;
end if;
if :block1.item1<=0 then
alrt_msg('Item value should be greater than zero');
raise form_trigger_failure;
end if;
raise form_trigger_failure is actually restrict user from skipping the validation.
WHEN_VALIDATE_RECORD
when_validate_record trigger check business logic in record level. This trigger is required when you need record level validation.
Don't forget to add this trigger at block level.
Suppose there is an entry form consisting of emp_code, pay, incentive.
Now if someone basic is zero or blank then
This is the when_validate_record plsql code.
if nvl(:block1.pay,0)=0 and nvl(:block1.incentive,0)>0 then
alrt_msg('Incentive cannot be greater than zero when basic is blank');
raise form_trigger_failure;
end if;
We have used nvl to return default value if item is null.
You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=_SbYRyF48dc
Press like if you like the video and you can also subscribe to my channel : https://www.youtube.com/subhro190776
How to connect to Oracle database 12c from Developer 2000, Forms and Reports.
That is they are unable to connect to Oracle 12c database. Problem is Oracle 12c itself rejects connection from Developer 6i.
It can be solved. Just need to make one changes in database server end.
I will be guiding for the methods for both linux and windows environment.
Simply open sqlnet.ora file on database server end.
In linux we assume you installed Oracle database in /u01 directory and using version 12.1.0
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
In windows we assume you installed Oracle database in c:\ drive and using version 12.1.0
C:\app\oracle\product\12.1.0\dbhome_1\network\admin\sqlnet.ora
add this line to the end of the file
SQLNET.ALLOWED_LOGON_VERSION=8
This will surely solve connectivity problem from Developer 2000 to Oracle 12c database.
You can also watch this tutorial in YouTube with the following link : https://www.youtube.com/watch?v=pgswMkMJx1w
Press like if you like the video and you can also subscribe to my channel : https://www.youtube.com/subhro190776
How to create an ALERT window in Oracle FORMS.
It is not always useful and unconventional to use message utility in Oracle Forms. Message utility only display message on left corner of status bar. And sometimes we use a trick to display alert window in Forms by invoking message procedure twice. But that is not a solution. So we should learn how to create an alert window and I am sure after going through this lesson you can easily create one alert window in your application.
As above picture shows, open object browser and double click on Alerts Node or select Alerts node and click on + button on the toolbar on the left pane.
Open the property pallet of alert item, and set the Alert style, Button label, I have taken 2 buttons,
as you can see, I have set OK and Cancel of Button 1 Label and Button 2 Label ad set Default
Alert button as Button 2.
Create a program unit named MSG (I assume you know how to create program unit) . I will give
the code for the MSG program, MSG program will actually will call the alert window.
PROCEDURE msg(msgstr varchar2) IS
alrt_id ALERT:= find_alert('ALRT'); -- ALRT is the name of the Alert item you have named.
ret number;
BEGIN
if not id_null(alrt_id) then
set_alert_property(alrt_id,title,msgstr); -- Message title for the alert window.
set_alert_property(alrt_id,message_text,msgstr); -- Message set for the alert window.
ret:= show_alert(alrt_id);
if ret = ALERT_BUTTON1 then
message('Pressed Ok');
elseif ret = ALERT_BUTTON2 then
exit_form;
end if;
end if;
END;
Now all you need to do is create a button in your form window and invoke the MSG procedure.
As above picture shows, open object browser and double click on Alerts Node or select Alerts node and click on + button on the toolbar on the left pane.
Open the property pallet of alert item, and set the Alert style, Button label, I have taken 2 buttons,
as you can see, I have set OK and Cancel of Button 1 Label and Button 2 Label ad set Default
Alert button as Button 2.
Create a program unit named MSG (I assume you know how to create program unit) . I will give
the code for the MSG program, MSG program will actually will call the alert window.
PROCEDURE msg(msgstr varchar2) IS
alrt_id ALERT:= find_alert('ALRT'); -- ALRT is the name of the Alert item you have named.
ret number;
BEGIN
if not id_null(alrt_id) then
set_alert_property(alrt_id,title,msgstr); -- Message title for the alert window.
set_alert_property(alrt_id,message_text,msgstr); -- Message set for the alert window.
ret:= show_alert(alrt_id);
if ret = ALERT_BUTTON1 then
message('Pressed Ok');
elseif ret = ALERT_BUTTON2 then
exit_form;
end if;
end if;
END;
Now all you need to do is create a button in your form window and invoke the MSG procedure.
How to handle PL/SQL errors through Oracle PL/SQL exceptions
Oracle PL/SQL exceptions.
Oracle has it's own inbuilt exception handlers to handle PL/SQL errors in different situations.
Here we are going to discuss about a few PL/SQL exceptions, it's occurrence and it's solutions
Since we are not working with any database here we will try to avoid any specific table releated queries
1. NO_DATA_FOUND
NO_DATA_FOUND is a exception handler which can be used when a certain sql query returns no
results.
------------------------------------------------------------------------------------------------------------------------
declare
x umber;
begin
select 1 into x from dual where 1<>1;
-- As you can see a query has been firedwith a false condition.
dbms_output.put_line(x||' returned as output') ;
exception
when NO_DATA_FOUND then
dbms_output.put_line('Query returns no result') ;
end;
------------------------------------------------------------------------------------------------------------------------
If you run this pl/sql block output will be obviously "Query returns no result", but suppose you change the query as such select 1 into x from dual where 1=1; then output will be "1 returned as output".
2. TOO_MAY_ROWS
TOO_MAY_ROWS exception occurs when you attempt to get value from records by select into
clause which returns more than one rows. Say you have a table called employee, which supposed
to have unique department code in a particular year. The query should be like this
Field name : dept_code, year (yyyy)
Possible data :
emp_code emp_name dept_code year
111 Sam 13 2010
111 Sam 17 2011
111 Sam 25 2012
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Query returned more than one rows"
/* now see i have not used where year = value, therefore the query returned 3 rows i the above
case, but if I have used where year= (say) 2010, then it returned one single row and exception
didn't have occured */
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee where year=2010;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Dept Code is 13"
3. ZERO_DIVIDE
ZERO_DIVIDE is an exception which occurs when you try to divide any number by zero i your
plsql block. Here is a simple query to show you how to catch this exception.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select 1/0 into n from dual;
exception
when ZERO_DIVIDE then
dbms_output.put_line('Attempting to divide a number by zero');
end;
------------------------------------------------------------------------------------------------------------------------
-- Output : Attempting to divide a number by zero
As soon as plsql engine parse the query and find 1/0 it jumps to exception ZERO_DIVIDE.
4. INVALID_NUMBER
This exception occurs when you attempt to convert a non-numeric value to numeric value.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select to_number('123abcd') into n from dual;
exception
when INVALID_NUMBER then
dbms_output.put_line('Attempting to convert a non-numeric value to numeric one');
end;
------------------------------------------------------------------------------------------------------------------------
--output : Attempting to convert a non-numeric value to numeric one
5. CURSOR_ALREADY_OPEN
This exception occurs when a cursor which is already opened is opened again without closing it.
Here is an example
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
open rec;
open rec;
loop
fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when CURSOR_ALREADY_OPEN then
dbms_output.put_line('Attempting to open a cursor which is already opened');
end;
--output : Attempting to open a cursor which is already opened'
------------------------------------------------------------------------------------------------------------------------
As you can see the above plsql block the statement open rec is called twice, and exception occurred when it is called second time. If you remove the second occurrence of open rec the error will not occur.
6. ROW_TYPE_MISMATCH
This exception occurs when attempt to fetch record into a different type of rowtype
Just go through this example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
dept department%rowtype;
begin
open rec;
open rec;
loop
fetch rec into dept;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when ROW_TYPE_MISMATCH then
dbms_output.put_line('Attempting to fetch into object of a different rowtype');
end;
--output : Attempting to fetch into object of a different rowtype'
------------------------------------------------------------------------------------------------------------------------
Follow the fetch statement, it fetching employee data into department data.
7. INVALID_CURSOR
This error occurs if you attempt to fetch record from a cursor which is not opened.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
loop fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when INVALID_CURSOR then
dbms_output.put_line('Attempting to fetch record from cursor which is not opened');
end;
--output : Attempting to fetch record from cursor which is not opened'
------------------------------------------------------------------------------------------------------------------------
8. OTHERS
You can use OTHERS exception when you cannot determine the type of exception occurred.
You can use OTHERS instead for all the above problems
=======================================================================
You can also watch the video here : https://www.youtube.com/watch?v=XjisTe5YMxE
Press the LIKE button if you like the video.
You can also subscribe to my channel :- https://www.youtube.com/subhro190776
Oracle has it's own inbuilt exception handlers to handle PL/SQL errors in different situations.
Here we are going to discuss about a few PL/SQL exceptions, it's occurrence and it's solutions
Since we are not working with any database here we will try to avoid any specific table releated queries
1. NO_DATA_FOUND
NO_DATA_FOUND is a exception handler which can be used when a certain sql query returns no
results.
------------------------------------------------------------------------------------------------------------------------
declare
x umber;
begin
select 1 into x from dual where 1<>1;
-- As you can see a query has been firedwith a false condition.
dbms_output.put_line(x||' returned as output') ;
exception
when NO_DATA_FOUND then
dbms_output.put_line('Query returns no result') ;
end;
------------------------------------------------------------------------------------------------------------------------
If you run this pl/sql block output will be obviously "Query returns no result", but suppose you change the query as such select 1 into x from dual where 1=1; then output will be "1 returned as output".
2. TOO_MAY_ROWS
TOO_MAY_ROWS exception occurs when you attempt to get value from records by select into
clause which returns more than one rows. Say you have a table called employee, which supposed
to have unique department code in a particular year. The query should be like this
Field name : dept_code, year (yyyy)
Possible data :
emp_code emp_name dept_code year
111 Sam 13 2010
111 Sam 17 2011
111 Sam 25 2012
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Query returned more than one rows"
/* now see i have not used where year = value, therefore the query returned 3 rows i the above
case, but if I have used where year= (say) 2010, then it returned one single row and exception
didn't have occured */
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee where year=2010;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Dept Code is 13"
3. ZERO_DIVIDE
ZERO_DIVIDE is an exception which occurs when you try to divide any number by zero i your
plsql block. Here is a simple query to show you how to catch this exception.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select 1/0 into n from dual;
exception
when ZERO_DIVIDE then
dbms_output.put_line('Attempting to divide a number by zero');
end;
------------------------------------------------------------------------------------------------------------------------
-- Output : Attempting to divide a number by zero
As soon as plsql engine parse the query and find 1/0 it jumps to exception ZERO_DIVIDE.
4. INVALID_NUMBER
This exception occurs when you attempt to convert a non-numeric value to numeric value.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select to_number('123abcd') into n from dual;
exception
when INVALID_NUMBER then
dbms_output.put_line('Attempting to convert a non-numeric value to numeric one');
end;
------------------------------------------------------------------------------------------------------------------------
--output : Attempting to convert a non-numeric value to numeric one
5. CURSOR_ALREADY_OPEN
This exception occurs when a cursor which is already opened is opened again without closing it.
Here is an example
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
open rec;
open rec;
loop
fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when CURSOR_ALREADY_OPEN then
dbms_output.put_line('Attempting to open a cursor which is already opened');
end;
--output : Attempting to open a cursor which is already opened'
------------------------------------------------------------------------------------------------------------------------
As you can see the above plsql block the statement open rec is called twice, and exception occurred when it is called second time. If you remove the second occurrence of open rec the error will not occur.
6. ROW_TYPE_MISMATCH
This exception occurs when attempt to fetch record into a different type of rowtype
Just go through this example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
dept department%rowtype;
begin
open rec;
open rec;
loop
fetch rec into dept;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when ROW_TYPE_MISMATCH then
dbms_output.put_line('Attempting to fetch into object of a different rowtype');
end;
--output : Attempting to fetch into object of a different rowtype'
------------------------------------------------------------------------------------------------------------------------
Follow the fetch statement, it fetching employee data into department data.
7. INVALID_CURSOR
This error occurs if you attempt to fetch record from a cursor which is not opened.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
loop fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when INVALID_CURSOR then
dbms_output.put_line('Attempting to fetch record from cursor which is not opened');
end;
--output : Attempting to fetch record from cursor which is not opened'
------------------------------------------------------------------------------------------------------------------------
8. OTHERS
You can use OTHERS exception when you cannot determine the type of exception occurred.
You can use OTHERS instead for all the above problems
=======================================================================
You can also watch the video here : https://www.youtube.com/watch?v=XjisTe5YMxE
Press the LIKE button if you like the video.
You can also subscribe to my channel :- https://www.youtube.com/subhro190776
Monday 13 October 2014
How to count Words, alphabets, numbers in a text file using Python
File handling is damn easy in python. It's so flexible making your file handling more friendly. You get total controls over files.
Here I have written a small program that will count number of words, alphabets and numbers within a file read from the disk.
Total 4 functions are used isnumeric and isalpha are character functions, len function calculate the size of the array, split function splits one line of string into words into an array.
I am giving the code below, you can try this.
#!/usr/local/bin/python3.4
# pleases follow the indention as it is most important in python. python don't use begin/end, if/end if or loop/
# end loop so python identify statement by indention.
def WordCount(file):
wrdcnt=0
# loop below get one line of the file content per iteration
for line in file:
# Split the line into words with default space delimiter and returns an array of words
words=line.split()
# len function get the size of the array i.e. number of words in the line
wrdcnt=wrdcnt+len(words)
return wrdcnt
def patternCount(pat):
patCount=0
# loop below get one line of the file content per iteration
for line in file:
# Checks whether pattern exists in the line, if true increment wrdcnt by 1
patCount = patCount + line.count(pat)
return patCount
def AlphaCount(file):
alphacnt=0
# loop below get one line of the file content per iteration
for line in file:
# loop below get one character of the line per iteration
for ch in line:
# Checks whether character is alphabet, if true increment wrdcnt by 1
if ch.isalpha()==True:
alphacnt=alphacnt+1
return alphacnt
def NumCount(file):
numcnt=0
# loop below get one line of the file content per iteration
for line in file:
# loop below get one character of the line per iteration
for ch in line:
# Checks whether character is number, if true increment numcnt by 1
if ch.isnumeric()==True:
numcnt=numcnt+1
return numcnt
filename="wordCount.py"
# Opens the file for reading
file=open(filename,"r")
# prints name of the file by %s (filename) and %d (WordCount(file))
print("Number of words in %s is %d"%(filename,WordCount(file)))
# Get the file pointer to the beginning of the file
file.seek(0,0)
# prints name of the file by %s (filename) and %d (AlphaCount(file))
print("Number of alphabets in %s is %d"%(filename,AlphaCount(file)))
# Get the file pointer to the beginning of the file
file.seek(0,0)
# prints name of the file by %s (filename) and %d (NumCount(file))
print("Number of numerics in %s is %d"%(filename,NumCount(file)))
file.seek(0,0)
print("Patter print exists for %d times "%(patternCount("print")))
file.close()
Here I have written a small program that will count number of words, alphabets and numbers within a file read from the disk.
Total 4 functions are used isnumeric and isalpha are character functions, len function calculate the size of the array, split function splits one line of string into words into an array.
I am giving the code below, you can try this.
#!/usr/local/bin/python3.4
# pleases follow the indention as it is most important in python. python don't use begin/end, if/end if or loop/
# end loop so python identify statement by indention.
def WordCount(file):
wrdcnt=0
# loop below get one line of the file content per iteration
for line in file:
# Split the line into words with default space delimiter and returns an array of words
words=line.split()
# len function get the size of the array i.e. number of words in the line
wrdcnt=wrdcnt+len(words)
return wrdcnt
def patternCount(pat):
patCount=0
# loop below get one line of the file content per iteration
for line in file:
# Checks whether pattern exists in the line, if true increment wrdcnt by 1
patCount = patCount + line.count(pat)
return patCount
def AlphaCount(file):
alphacnt=0
# loop below get one line of the file content per iteration
for line in file:
# loop below get one character of the line per iteration
for ch in line:
# Checks whether character is alphabet, if true increment wrdcnt by 1
if ch.isalpha()==True:
alphacnt=alphacnt+1
return alphacnt
def NumCount(file):
numcnt=0
# loop below get one line of the file content per iteration
for line in file:
# loop below get one character of the line per iteration
for ch in line:
# Checks whether character is number, if true increment numcnt by 1
if ch.isnumeric()==True:
numcnt=numcnt+1
return numcnt
filename="wordCount.py"
# Opens the file for reading
file=open(filename,"r")
# prints name of the file by %s (filename) and %d (WordCount(file))
print("Number of words in %s is %d"%(filename,WordCount(file)))
# Get the file pointer to the beginning of the file
file.seek(0,0)
# prints name of the file by %s (filename) and %d (AlphaCount(file))
print("Number of alphabets in %s is %d"%(filename,AlphaCount(file)))
# Get the file pointer to the beginning of the file
file.seek(0,0)
# prints name of the file by %s (filename) and %d (NumCount(file))
print("Number of numerics in %s is %d"%(filename,NumCount(file)))
file.seek(0,0)
print("Patter print exists for %d times "%(patternCount("print")))
file.close()
Sunday 12 October 2014
How to export data from Oracle 12c database to Excel file.
All you need is WAMP Server, I am usig version 2.5.
You can download it from http://sourceforge.net/projects/wampserver/files/latest/download
After installing wamp server goto php.ini file located in wamppath\apache\apacheversion\bin\php.ini
The wamp path is the path where the wamp is installed.
The directory apacheversion is named as the version you have installed. i.e. apache2.4.9
After opening php.ini (say in notepad) search for string oci, you will find
;extension=php_oci8.dll
;extension=php_oci_11g.dll
If you are using Oracle version <=10g, un-comment extension=php_oci8.dll (remove ; from begining)
Else if you are using 11g then un-comment extension=php_oci8_11g.dll
But if you are using 12c you need to add a line in the file, extension=php_oci8_12c.dll.
You will also need to download php_oci8_12c.dll . It can be downloaded in zipped format containing all dll's from version 8 to 12.
Link for the client download : http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/.
I am giving you the links, you need to download your needed version like if you have php 5.4 in 64 bit OS download php_oci8-2.0.8-5.5-nts-vc11-x64.zip
Restart your service.
Make sure your Oracle service ad Listener service is Up and running.
Here is the sample code to connect to Oracle and export data to Excel file
//===============================================================
<?php
//$conn=oci_connect('userame','password','hostname/oracle_servicename');
$conn=oci_connect('hr','hr','192.168.0.109/orcl.localdomain');
if(!$conn)
{
$err = oci_error();
trigger_error(htmlentities($err['message'], ENT_QUOTES), E_USER_ERROR);
}
function cleanData($str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
$stid = oci_parse($conn, 'SELECT emp_code, emp_name FROM GREENPLY.employee_master');
oci_execute($stid);
$filename = "ora_data.xls";
header("Content-Type: text/plain");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel;charset=UTF-16LE");
/* using implode directly to $row is creating some problem (the columns value coming twice) thats
why I have first created array from string and then split it into string with tab delimiter. */
echo implode("\t",explode("\\t", "Emp Code\\tEmp Name")) . "\r\n";
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false)
{
array_walk($row, 'cleanData');
echo implode("\t",explode("\\t", $row[0] . '\\t' . $row[1])) . "\r\n";
}
oci_free_statement($stid);
oci_close($conn);
//http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/php_oci8-2.0.8-5.5-ts-vc11-x64.zip
//Source Code Link : https://drive.google.com/file/d/0BznrW3lgX0ozaTFTeDJEWWd6VE0/view?usp=sharing
?>
You can download it from http://sourceforge.net/projects/wampserver/files/latest/download
After installing wamp server goto php.ini file located in wamppath\apache\apacheversion\bin\php.ini
The wamp path is the path where the wamp is installed.
The directory apacheversion is named as the version you have installed. i.e. apache2.4.9
After opening php.ini (say in notepad) search for string oci, you will find
;extension=php_oci8.dll
;extension=php_oci_11g.dll
If you are using Oracle version <=10g, un-comment extension=php_oci8.dll (remove ; from begining)
Else if you are using 11g then un-comment extension=php_oci8_11g.dll
But if you are using 12c you need to add a line in the file, extension=php_oci8_12c.dll.
You will also need to download php_oci8_12c.dll . It can be downloaded in zipped format containing all dll's from version 8 to 12.
Link for the client download : http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/.
I am giving you the links, you need to download your needed version like if you have php 5.4 in 64 bit OS download php_oci8-2.0.8-5.5-nts-vc11-x64.zip
Wednesday, March 12, 2014 7:27 PM 566414 php_oci8-2.0.8-5.3-nts-vc9-x86.zip Wednesday, March 12, 2014 7:31 PM 579365 php_oci8-2.0.8-5.3-ts-vc9-x86.zip Wednesday, March 12, 2014 7:20 PM 562128 php_oci8-2.0.8-5.4-nts-vc9-x86.zip Wednesday, March 12, 2014 7:23 PM 577956 php_oci8-2.0.8-5.4-ts-vc9-x86.zip Wednesday, March 12, 2014 7:06 PM 575229 php_oci8-2.0.8-5.5-nts-vc11-x64.zip Wednesday, March 12, 2014 7:13 PM 564670 php_oci8-2.0.8-5.5-nts-vc11-x86.zip Wednesday, March 12, 2014 7:09 PM 589774 php_oci8-2.0.8-5.5-ts-vc11-x64.zip Wednesday, March 12, 2014 7:16 PM 579897 php_oci8-2.0.8-5.5-ts-vc11-x86.zip Thursday, April 10, 2014 11:03 PM 584008 php_oci8-2.0.8-5.6-nts-vc11-x64.zip Thursday, April 10, 2014 10:55 PM 573662 php_oci8-2.0.8-5.6-nts-vc11-x86.zip Thursday, April 10, 2014 11:07 PM 593400 php_oci8-2.0.8-5.6-ts-vc11-x64.zip Thursday, April 10, 2014 10:59 PM 581718 php_oci8-2.0.8-5.6-ts-vc11-x86.zip
Extract the files in wamppath\bin\php\phpversion\ext\ directory
i.e. C:\wamp\bin\php\php5.5.12\ext\
Restart your service.
Make sure your Oracle service ad Listener service is Up and running.
Here is the sample code to connect to Oracle and export data to Excel file
//===============================================================
<?php
//$conn=oci_connect('userame','password','hostname/oracle_servicename');
$conn=oci_connect('hr','hr','192.168.0.109/orcl.localdomain');
if(!$conn)
{
$err = oci_error();
trigger_error(htmlentities($err['message'], ENT_QUOTES), E_USER_ERROR);
}
function cleanData($str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
$stid = oci_parse($conn, 'SELECT emp_code, emp_name FROM GREENPLY.employee_master');
oci_execute($stid);
$filename = "ora_data.xls";
header("Content-Type: text/plain");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel;charset=UTF-16LE");
/* using implode directly to $row is creating some problem (the columns value coming twice) thats
why I have first created array from string and then split it into string with tab delimiter. */
echo implode("\t",explode("\\t", "Emp Code\\tEmp Name")) . "\r\n";
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false)
{
array_walk($row, 'cleanData');
echo implode("\t",explode("\\t", $row[0] . '\\t' . $row[1])) . "\r\n";
}
oci_free_statement($stid);
oci_close($conn);
//http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/php_oci8-2.0.8-5.5-ts-vc11-x64.zip
//Source Code Link : https://drive.google.com/file/d/0BznrW3lgX0ozaTFTeDJEWWd6VE0/view?usp=sharing
?>
How to connect to Oracle 12c database using Python.
Python is an indent based language. Does not contain any begin or end like if / end if, loop / end loop.
Beginning of scope is in hanging indent and the entire scope is indented.
Like
>>if(codition)
>> staterment
>> staterment
Just look into the indent for the statement below if condition.
Now about oracle client library in python.
You can download oracle client cx_Oracle from here : https://pypi.python.org/pypi/cx_Oracle/5.1.3.
Just choose according to your version of python installed.
Install it in your machine if you are using Windows 32 bit download win32-py else if you are using Windows 64 bit download win-amd64-py.
Here is the sample code below which will help you to connect to Oracle.
#---------------------------------------------------------------------------------------------------
import cx_Oracle
# con = cx_Oracle.connect('username/password@[ipaddress or hostname]/SID')
# for example con = cx_Oracle.connect('scott/tiger@127.0.0.1/orcl')
# Above 2 lies are format of connection string and sample connection string.
# Below is the actual connection string I have used. orcl.localdomain is the service name you have configured with net config assistant in oracle databsae server.
con = cx_Oracle.connect('hr/hr@192.168.0.109/orcl.localdomain')
cur = con.cursor()
cur.execute('select section_code,section_name from section_master order by 2')
for row in cur:
print(row)
#closing all opened objects
file.close()
cur.close()
con.close()
print("File successfully exported")
#---------------------------------------------------------------------------------------------------
Please comment if you have any queries.
You can also find video tut0rialfor this lesson inhttps://www.youtube.com/watch?v=w3WVqn3WySs
Also you can subscribe to my youtube channel https://www.youtube.com/subhro190776
Beginning of scope is in hanging indent and the entire scope is indented.
Like
>>if(codition)
>> staterment
>> staterment
Just look into the indent for the statement below if condition.
Now about oracle client library in python.
You can download oracle client cx_Oracle from here : https://pypi.python.org/pypi/cx_Oracle/5.1.3.
| cx_Oracle-5.1.3-11g.win-amd64-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 323KB |
| cx_Oracle-5.1.3-11g.win-amd64-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-11g.win-amd64-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-11g.win32-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 288KB |
| cx_Oracle-5.1.3-11g.win32-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 283KB |
| cx_Oracle-5.1.3-11g.win32-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 282KB |
| cx_Oracle-5.1.3-12c.win-amd64-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 323KB |
| cx_Oracle-5.1.3-12c.win-amd64-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-12c.win-amd64-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-12c.win32-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 288KB |
| cx_Oracle-5.1.3-12c.win32-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 283KB |
| cx_Oracle-5.1.3-12c.win32-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 282KB |
| cx_Oracle-5.1.3.tar.gz (md5) | Source | 2014-05-25 | 102KB |
Just choose according to your version of python installed.
Install it in your machine if you are using Windows 32 bit download win32-py else if you are using Windows 64 bit download win-amd64-py.
Here is the sample code below which will help you to connect to Oracle.
#---------------------------------------------------------------------------------------------------
import cx_Oracle
# con = cx_Oracle.connect('username/password@[ipaddress or hostname]/SID')
# for example con = cx_Oracle.connect('scott/tiger@127.0.0.1/orcl')
# Above 2 lies are format of connection string and sample connection string.
# Below is the actual connection string I have used. orcl.localdomain is the service name you have configured with net config assistant in oracle databsae server.
con = cx_Oracle.connect('hr/hr@192.168.0.109/orcl.localdomain')
cur = con.cursor()
cur.execute('select section_code,section_name from section_master order by 2')
for row in cur:
print(row)
#closing all opened objects
file.close()
cur.close()
con.close()
print("File successfully exported")
#---------------------------------------------------------------------------------------------------
Please comment if you have any queries.
You can also find video tut0rialfor this lesson inhttps://www.youtube.com/watch?v=w3WVqn3WySs
Also you can subscribe to my youtube channel https://www.youtube.com/subhro190776
How to export data from Oracle 12c database using python 3.4
Like
>>if(codition)
>> staterment
>> staterment
Just look into the indent for the statement below if condition.
Now about oracle client library in python.
You can download oracle client cx_Oracle from here : https://pypi.python.org/pypi/cx_Oracle/5.1.3.
| cx_Oracle-5.1.3-11g.win-amd64-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 323KB |
| cx_Oracle-5.1.3-11g.win-amd64-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-11g.win-amd64-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-11g.win32-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 288KB |
| cx_Oracle-5.1.3-11g.win32-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 283KB |
| cx_Oracle-5.1.3-11g.win32-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 282KB |
| cx_Oracle-5.1.3-12c.win-amd64-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 323KB |
| cx_Oracle-5.1.3-12c.win-amd64-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-12c.win-amd64-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 322KB |
| cx_Oracle-5.1.3-12c.win32-py2.7.exe (md5) | MS Windows installer | 2.7 | 2014-05-25 | 288KB |
| cx_Oracle-5.1.3-12c.win32-py3.3.exe (md5) | MS Windows installer | 3.3 | 2014-05-25 | 283KB |
| cx_Oracle-5.1.3-12c.win32-py3.4.exe (md5) | MS Windows installer | 3.4 | 2014-05-25 | 282KB |
| cx_Oracle-5.1.3.tar.gz (md5) | Source | 2014-05-25 | 102KB |
Just choose according to your version of python installed.
Install it in your machine if you are using Windows 32 bit download win32-py else if you are using Windows 64 bit download win-amd64-py.
Here is the sample code below which will help you to connect to Oracle and export csv data.
#---------------------------------------------------------------------------------------------------
import cx_Oracle
# con = cx_Oracle.connect('username/password@[ipaddress or hostname]/SID')
# for example con = cx_Oracle.connect('scott/tiger@127.0.0.1/orcl')
# Above 2 lies are format of connection string and sample connection string.
# Below is the actual connection string I have used. orcl.localdomain is the service name you have configured with net config assistant in oracle databsae server.
con = cx_Oracle.connect('hr/hr@192.168.0.109/orcl.localdomain')
cur = con.cursor()
cur.execute('select section_code,section_name from section_master order by 2')
# using inbuilt open function to create new /open existing file for writing
file = open("file.csv", "w")
for row in cur:
# row[0] and row[1] where 0 and 1 are index of row cursor.
file.write(row[0] + ',' + row[1] + '\n')
#closing all opened objects
file.close()
cur.close()
con.close()
print("File successfully exported")
#---------------------------------------------------------------------------------------------------
Please comment if you have any queries.
You can also find video tut0rialfor this lesson in https://www.youtube.com/watch?v=85WoKxiEC-E
Also you can subscribe to my youtube channel https://www.youtube.com/subhro190776
Connect to Oracle 12c release 1 using PHP
All you need is WAMP Server, I am usig version 2.5.
You can download it from http://sourceforge.net/projects/wampserver/files/latest/download
After installing wamp server goto php.ini file located in wamppath\apache\apacheversion\bin\php.ini
The wamp path is the path where the wamp is installed.
The directory apacheversion is named as the version you have installed. i.e. apache2.4.9
After opening php.ini (say in notepad) search for string oci, you will find
;extension=php_oci8.dll
;extension=php_oci_11g.dll
If you are using Oracle version <=10g, un-comment extension=php_oci8.dll (remove ; from begining)
Else if you are using 11g then un-comment extension=php_oci8_11g.dll
But if you are using 12c you need to add a line in the file, extension=php_oci8_12c.dll.
You will also need to download php_oci8_12c.dll . It can be downloaded in zipped format containing all dll's from version 8 to 12.
Link for the client download : http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/.
I am giving you the links, you need to download your needed version like if you have php 5.4 in 64 bit OS download php_oci8-2.0.8-5.5-nts-vc11-x64.zip
Restart your service.
Make sure your Oracle service ad Listener service is Up and running.
Here is the sample code to connect to Oracle
//===============================================================
<?php
//$conn=oci_connect('userame','password','hostname/oracle_servicename');
/*username and password is your database username (schema name) and password
hostname can be computer netbios name or ip address, i prefer ip address. oracle service name is the service name you have created in you database server through net configuration assistant.
*/
$conn=oci_connect('hr','hr','192.168.0.109/orcl.localdomain');
//orcl.localdomain is my oracle service name
if($conn)
echo "Connection succeded";
else
{
echo "Connection failed";
$err = oci_error();
trigger_error(htmlentities($err['message'], ENT_QUOTES), E_USER_ERROR);
}
?>
<table border=1 cellpadding=5><tr><td> Section Code</td><td>Section Name</td></tr>
<?php
// table name column names are samples.
$stid = oci_parse($conn, 'SELECT section_code, section_name FROM section_master');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
?>
<tr>
<!-- Rows are printed by index starting from zero in order they are put in the select statement. -->
<td><?php echo $row[0] ?></td>
<td><?php echo $row[1] ?></td>
</tr>
<?php
}
// Frre variables from memory and close connection object.
oci_free_statement($stid);
oci_close($conn);
//http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/php_oci8-2.0.8-5.5-ts-vc11-x64.zip
?>
</table>
?>
//===============================================================
Try this code. It's very interesting. You will enjoy connecting to oracle database using php, it's so flexible.
If you have any queries do comment.
You can find video tutorial on https://www.youtube.com/subhro190776
You can download it from http://sourceforge.net/projects/wampserver/files/latest/download
After installing wamp server goto php.ini file located in wamppath\apache\apacheversion\bin\php.ini
The wamp path is the path where the wamp is installed.
The directory apacheversion is named as the version you have installed. i.e. apache2.4.9
After opening php.ini (say in notepad) search for string oci, you will find
;extension=php_oci8.dll
;extension=php_oci_11g.dll
If you are using Oracle version <=10g, un-comment extension=php_oci8.dll (remove ; from begining)
Else if you are using 11g then un-comment extension=php_oci8_11g.dll
But if you are using 12c you need to add a line in the file, extension=php_oci8_12c.dll.
You will also need to download php_oci8_12c.dll . It can be downloaded in zipped format containing all dll's from version 8 to 12.
Link for the client download : http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/.
I am giving you the links, you need to download your needed version like if you have php 5.4 in 64 bit OS download php_oci8-2.0.8-5.5-nts-vc11-x64.zip
Wednesday, March 12, 2014 7:27 PM 566414 php_oci8-2.0.8-5.3-nts-vc9-x86.zip Wednesday, March 12, 2014 7:31 PM 579365 php_oci8-2.0.8-5.3-ts-vc9-x86.zip Wednesday, March 12, 2014 7:20 PM 562128 php_oci8-2.0.8-5.4-nts-vc9-x86.zip Wednesday, March 12, 2014 7:23 PM 577956 php_oci8-2.0.8-5.4-ts-vc9-x86.zip Wednesday, March 12, 2014 7:06 PM 575229 php_oci8-2.0.8-5.5-nts-vc11-x64.zip Wednesday, March 12, 2014 7:13 PM 564670 php_oci8-2.0.8-5.5-nts-vc11-x86.zip Wednesday, March 12, 2014 7:09 PM 589774 php_oci8-2.0.8-5.5-ts-vc11-x64.zip Wednesday, March 12, 2014 7:16 PM 579897 php_oci8-2.0.8-5.5-ts-vc11-x86.zip Thursday, April 10, 2014 11:03 PM 584008 php_oci8-2.0.8-5.6-nts-vc11-x64.zip Thursday, April 10, 2014 10:55 PM 573662 php_oci8-2.0.8-5.6-nts-vc11-x86.zip Thursday, April 10, 2014 11:07 PM 593400 php_oci8-2.0.8-5.6-ts-vc11-x64.zip Thursday, April 10, 2014 10:59 PM 581718 php_oci8-2.0.8-5.6-ts-vc11-x86.zip
Extract the files in wamppath\bin\php\phpversion\ext\ directory
i.e. C:\wamp\bin\php\php5.5.12\ext\
Restart your service.
Make sure your Oracle service ad Listener service is Up and running.
Here is the sample code to connect to Oracle
//===============================================================
<?php
//$conn=oci_connect('userame','password','hostname/oracle_servicename');
/*username and password is your database username (schema name) and password
hostname can be computer netbios name or ip address, i prefer ip address. oracle service name is the service name you have created in you database server through net configuration assistant.
*/
$conn=oci_connect('hr','hr','192.168.0.109/orcl.localdomain');
//orcl.localdomain is my oracle service name
if($conn)
echo "Connection succeded";
else
{
echo "Connection failed";
$err = oci_error();
trigger_error(htmlentities($err['message'], ENT_QUOTES), E_USER_ERROR);
}
?>
<table border=1 cellpadding=5><tr><td> Section Code</td><td>Section Name</td></tr>
<?php
// table name column names are samples.
$stid = oci_parse($conn, 'SELECT section_code, section_name FROM section_master');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
?>
<tr>
<!-- Rows are printed by index starting from zero in order they are put in the select statement. -->
<td><?php echo $row[0] ?></td>
<td><?php echo $row[1] ?></td>
</tr>
<?php
}
// Frre variables from memory and close connection object.
oci_free_statement($stid);
oci_close($conn);
//http://windows.php.net/downloads/pecl/releases/oci8/2.0.8/php_oci8-2.0.8-5.5-ts-vc11-x64.zip
?>
</table>
?>
//===============================================================
Try this code. It's very interesting. You will enjoy connecting to oracle database using php, it's so flexible.
If you have any queries do comment.
You can find video tutorial on https://www.youtube.com/subhro190776
Subscribe to:
Posts (Atom)