Oracle
How to change default port of oracle after installation in ubuntu
When I was installing all apache, tomcat, oracle and mysql in my ubuntu 9.04. Apache took 80 port. Thereafter tomcat took 8080. When I setup oracle-xe I didn’t notice that. But oracle also demands 8080 port by default. That created conflict with tomcat. When I click “Applications > Oracle 10g Express Edition > Go to Database Home page” it showed an tomcat page not found error!!!
It was a really painful coz there was no way to reconfigure port for oracle neither any way to reinstall that would reconfigure. I googled for a while and found a solution.
No CommentsCreating auto increment sequence in oracle
In mysql we often use auto increment feature. But there is no such feature in oracle. Instead we have sequence and we need to use a sequence and a trigger to create auto increment functionality in oracle.
Create table
-- Admin Table CREATE TABLE admin ( id number(4) NOT NULL, name varchar2(50) NOT NULL, username varchar2(20) NOT NULL, password varchar2(20) NOT NULL, constraint admin_pk_1 PRIMARY KEY(id) );No Comments
MySQL enum equvalent alternate in oracle
One of Mysqls great features in enum data type. An ENUM is a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.
But in oracle there is no such data type. So if you want to restrict column value input like mysql enum datatype in oracle you can make use of an check constraint.
-- Student Table
CREATE TABLE student
(
reg number(10) NOT NULL,
name varchar2(50) NOT NULL,
dept varchar2(5) NOT NULL,
ses number(4) NOT NULL,
fname varchar2(50),
mname varchar2(50),
bday date,
gender varchar2(6),
cgpa number(4,2),
grade_letter varchar2(2),
constraint student_pk_1 PRIMARY KEY(reg), constraint student_en_1 CHECK(gender IN('Male', 'Female')),
constraint student_fk_1 FOREIGN KEY(dept) REFERENCES dept(code) ON DELETE cascade,
constraint student_fk_2 FOREIGN KEY(ses) REFERENCES ses(ses) ON DELETE cascade
);
Here in line 14 check function is working as like enum data type in mysql
No CommentsHow to implement query limit or pagination in oracle
There is no limit command like mysql in oracle. If you need to limit your query in oracle or want to make a pagination in oracle what you need to do is really rubbish!
You need to make a nested select query like below… uff
SELECT url, title, metadesc FROM (
SELECT url,title, metadesc, rownum rn FROM (
SELECT url, title, metadesc FROM web
)
WHERE rownum <= 60
)
WHERE rn > 50
This query is as same as mysql query like below
SELECT url,title,metaDesc FROM web LIMIT 50,10No Comments
How to connect to oracle database with php
We all are familier with php mysql applications. Now a days there is a very few php applications that uses oracle. Reference on how to connect to php oracle is also much low in number. I had trouble when searching for the solution on net.
To connect to php oracle. First you should enable oci module in your php.ini configuration. Generally it is located at “C:/windows”
So open “php.ini” with a notepad. Find the line
Calling oracle procedures using C#
I have this procedure stored in oracle database.
-- procedure to to calculate student cgpa/grade letter of whole semester
create or replace procedure calc_sem_cgpa(regIn in number, semIn varchar2)
as
cursor result is
select cgpa.gpa, syllabus.credit
from cgpa,syllabus,student
where
cgpa.reg = regIn
and cgpa.semester = semIn
and cgpa.course = syllabus.course
and cgpa.semester = syllabus.semester
and student.reg = cgpa.reg
and student.d ept = syllabus.dept
and student.ses = syllabus.ses;
eachResult result%rowtype;
sum_cgpa number(5,-2) := 0;
sum_credit number(5,2) := 0;
final_cgpa number(4,2) := 0;
countRow number(2) := 0;
begin
open result;
loop
fetch result into eachResult;
exit when result%notfound;
if eachResult.gpa > 0.0 then
sum_cgpa := sum_cgpa + eachResult.gpa * eachResult.credit;
sum_credit := sum_credit + eachResult.credit;
end if;
end loop;
if sum_credit > 0 then
final_cgpa := round(sum_cgpa/sum_credit, 2);
else
final_cgpa := 0;
end if;
select count(*) into countRow from cgpa_sem
where cgpa_sem.reg = regIn
and cgpa_sem.semester = semIn;
if countRow > 0 then
update cgpa_sem set cgpa = final_cgpa, credits_comp = sum_credit
where reg = regIn and semester = semIn;
else
insert into cgpa_sem(reg, semester, cgpa, credits_comp)
values(regIn, semIn, final_cgpa, sum_credit);
end if;
end;
/
No CommentsHow to connect to oracle database using java
I have oracle 10g installed on my ubuntu. I need a java program to connect with oracle database and fetch data from table.
To do this what I needed first is the oracle-jdbc connector. I found it in “/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/jdbc/lib” directory. Two jar files named “ojdbc14.jar” & “ojdbc14_g.jar”.
I openned Netbeans IDE created a new project and from the projects panel i right clicked on the project I connect with oracle and clicked on properties.
When properties window openned I went to Libraries to add those specific jar files.
Then I clicked “Add JAR/Folder” and selected those two files and hit ok.
That added those connector jar files in classpath for my current project. Now all what i needed to do is to write the correct code.
No CommentsHow to connect C# with oracle
This time I am gonna work with my database project. This course was on oracle database. So now I must connect my software interface with oracle. So first try’t with C#. This is how I did it
To connect to oracle database using C# first you what you must do is to install Oracle Database and Microsoft Visual Studio in your computer.
You also should setup oracle cilent in order to have them work with each other.
