Dec 08 2016

Update Another Column On The Same Table With Trigger While Updating Table in Oracle

Category: Errors and Solutions,Procedure,SQLFatih Acar @ 11:01

If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512: at “trigger_name” line 7″,”ORA-04088:error during execution of trigger “trigger_name”” if you don’t use, you can get errors like that are “ORA-04091: table tablename is mutating, trigger/function may not see it”,”ORA-06512: at trigger_name”, line 6″,”ORA-04088: error during execution of trigger trigger_name”

You can resolve this problem with using before update trigger. You can update another column of updating the same table.

Trigger Example

My example is about that If status column of table1 change from 0 to 1, I will write another columns that are name, surname and studentno at the same table table1.

create table table1 (id number, status number, name varchar2(100), surname varchar2(100), studentno varchar2(100),address varchar2(500),city varchar2(100));

create or replace trigger trg_trigger_name_upt
before update of status on table1
referencing OLD as old NEW as new
for each row
v_studentno varchar2(100);
begin

IF :old.status = 0 AND :new.status = 1 and UPDATING THEN

:new.name := ‘Fatih’;
:new.surname := ‘Acar’;
v_studentno := seq_studentno.nextval;
:new.studentno := v_studentno;

END IF;

end;
/

4,616 total views, 10 views today

Tags: Oracle Administration, Oracle Error Solutions, Oracle SQL Query


Oct 17 2016

Obtain Surname, Last Name and Mid Name From Full Name in Oracle

Category: Procedure,SQLFatih Acar @ 13:43

If you have a column with full name data and you want to split this data, you can use below function to split operation.

Split Name ve Surname

Table : test
Data1 : Emin Fatih Acar
Data2 : Fatih Acar

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘,Instr(fullname,’ ‘,-1))-1) name,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname
from test;

Result:

Data1
name : Emin Fatih
surname : Acar

Data2
name : Fatih
surname : Acar

Split Name,Midname and Surname

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘)-1) name,
substr(fullname,instr(fullname,’ ‘)+1,Instr(fullname,’ ‘,-1,1)- instr(fullname,’ ‘)-1) midname,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname,
from test;

Result:

Data1
name : Emin
midname : Fatih
surname : Acar

Data2

name : Fatih
midname : null
surname : Acar

3,431 total views, 10 views today

Tags: Database Administration, Oracle Administration, Oracle SQL Query


Nov 15 2015

Auto Increment Primary Key With Trigger in Oracle 11g

Category: Administration,Procedure,SQLFatih Acar @ 14:50

You can provide auto increment primary with trigger by appling below operations.

Create Table

SQL> CREATE TABLE TESTUSER.TBL_TEST
(id number primary key,
name varchar2(100));

Create Sequence

SQL> CREATE SEQUENCE TESTUSER.SEQ_TEST_ID
START WITH 1
MAXVALUE 999999999
MINVALUE 1
CYCLE
NOCACHE
NOORDER;

Create Trigger For Auto Increment

SQL> CREATE OR REPLACE TRIGGER TESTUSER.TRG_AUTO_INC_TEST_ID
before insert on TESTUSER.TBL_TEST
for each row
begin
if inserting then
if :NEW.”ID” is null then
select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
end if;
end if;
end;
/

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
Continue reading “Auto Increment Primary Key With Trigger in Oracle 11g”

4,942 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle SQL Query


Apr 17 2014

What is Lightweight Job in Oracle

Category: Administration,ProcedureFatih Acar @ 14:46

Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Lightweight jobs have the following characteristics:

  • Unlike regular jobs, they are not schema objects.
  • They have a significant improvement in create and drop time over regular jobs because they do not have the overhead of creating a schema object.
  • They have lower average session creation time than regular jobs.
  • They have a small footprint on disk for job metadata and runtime data.

You designate a lightweight job by setting the job_style job attribute to LIGHTWEIGHT. The other job style is REGULAR, which is the default.

Like programs and schedules, regular jobs are schema objects. In releases before Oracle Database 11g Release 1, regular jobs were the only job style supported by the Scheduler.

A regular job offers the maximum flexibility but does entail some overhead when it is created or dropped. The user has fine-grained control of the privileges on the job, and the job can have as its action a program or a stored procedure owned by another user.

Continue reading “What is Lightweight Job in Oracle”

13,020 total views, 15 views today

Tags: Database Administration, Oracle, Oracle Administration


Sep 30 2013

Fine Grained Auditing (FGA) in Oracle

Category: Administration,ProcedureFatih Acar @ 21:35

Fine Grained Auditing (FGA), introduced with Oracle9i, can be understood as ‘policy-based auditing’. As opposed to the standard auditing functionality, FGA lets you specify the conditions necessary for an audit record. FGA policies are programatically bound to the object (table, view) by using the ‘dbms_fga’ package.

You have to have a valid set of Fine-Grained Audit policies to make it possible to specify access to the following items:

  • A set of database objects (views or tables)
  • A specific type of data (queried with SELECT, INSERT, UPDATE, and DELETE SQL statements)
  • A specific column
  • A specific value of a column

Procedure

begin
dbms_fga.add_policy
( object_schema=>’HR’
, object_name=>’EMPLOYEES’
, policy_name=>’SELECT_COUNT’
, audit_column => ‘FIRST_NAME’
, audit_condition => ”
, handler_schema => ‘AUDITEDUSER’
, handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
);
end;

CREATE OR REPLACE PACKAGE AUDIT_HANDLER
is
PROCEDURE HANDLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
end;
/

CREATE OR REPLACE PACKAGE BODY AUDIT_HANDLER
is
PROCEDURE HANDLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into TABLENAME
( whodidit, whenwasit, sql_executed)
values
( user, systimestamp, sys_context(‘userenv’,’current_sql’))
;
commit;
end HANDLE_ACCESS;
end;
/

begin
dbms_fga.add_policy
( object_schema=>’SCHEMANAME’
, object_name=>’TABLENAME’
, policy_name=>’ACCESS_HANDLED’
, audit_column => ‘COLUMNNAME’
, audit_condition => ”
, handler_schema => ‘AUDITEDUSER’
, handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
);
end;
/

begin
dbms_fga.drop_policy
( object_schema=>’SCHEMANAME’
, object_name=>’TABLENAME’
, policy_name=>’ACCESS_HANDLED’
);
end;

Source : Oracle Offical Documents

16,825 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration


Apr 18 2013

Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS

Category: Administration,Linux & Unix,Procedure,SQLFatih Acar @ 14:08

I will use Oracle 11g R2 database and SQL Server 2008 R2 on this example. If you want to connect from Oracle to SQL Server, you can use freeTDS connection library on Oracle Linux systems. If we use Windows Server for Oracle database, we can use direct Windows ODBC Driver Manager. On the other hand, we need some requirement for odbc connection on Linux System. FreeTDS is a odbc connection library for MsSQL Server and other database systems.

I made test below systems.

Systems

  • Oracle Linux 6.x / 7.x
  • Oracle 11g / 12c
  • Windows Server 2008 R2
  • MsSQL Server 2008 R2

dblink

Steps;

  • Download freetds-stable program
  • Install unixODBC and unixODBC-devel
  • Install freetds
  • Configure freetds.conf
  • Configure odbc.ini
  • Configure init
  • Configure Listener
  • Configure tnsnames
  • Create DbLink

Download freetds-stable

Download Page, Direct Download

Install unixODBC-devel

yum install unixODBC
yum install unixODBC-devel

Install freeTDS

tar zfvx freetds-stable.tgz
cd freetds-0.91
./configure –prefix=/usr/local/freetds –with-tdsver=8.0 –enable-msdblib –enable-dbmfix –with-gnu-ld
make
make install

Continue reading “Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS”

105,161 total views, 10 views today

Tags: FreeTDS ODBC Connection, Linux Administration, Oracle, Oracle Administration, System Administration


Feb 01 2013

Random Character Generator in Oracle

Category: Procedure,SQLFatih Acar @ 18:02

Oracle dbms_random package can generate random characters in character, numeric or alphanumeric formats.

dbms_random.value(Parameter, Value)

  • U – Upper case
  • L – Lower case
  • A – Alphanumeric
  • X – Alphanumeric with upper case alphabets.
  • P – Printable characters only.

Create Random Characters

SQL> select dbms_random.random value from dual;
Value : 531879542

SQL> select dbms_random.value from dual;
Value : 1

SQL> select dbms_random.value(100,1000) valuefrom dual;
Value : 452

SQL> select dbms_random.value(100000000000, 999999999999) value from dual;
Value : 515055428760

SQL> select dbms_random.string(‘U’, 10) str from dual;
Value : KGSQLEKTSX

SQL> select dbms_random.string(‘L’, 10) str from dual;
Value = ksfkwqwerv

SQL> select dbms_random.string(‘A’, 10) str from dual;
Value : RhjE+RojjL

SQL> select dbms_random.string(‘X’, 10) str from dual;
Value : GF:E3B4GRB

SQL> select dbms_random.string(‘P’, 10) str from dual;
Value : *Yw(IK/zsj

Continue reading “Random Character Generator in Oracle”

15,844 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration


Dec 25 2012

ACL Error Solution in Oracle 11g

Category: Administration,ProcedureFatih Acar @ 15:23

Oracle 11g have been used ACL procedure to send smtp e-mail. Default configuration can not send e-mail. You have to create below procedures for complete smtp configuration.

I used CONNUSER as ACL connection user and 100.10.10.11 IP address as mail server IP and NOTIFYUSER as user for notification for configuration. You can change this users or IP address.

Create ACL MailServer

SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line(‘ACL dropped…..’);
exception
when others then
dbms_output.put_line(‘Error dropping ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line(‘ACL created…..’);
exception
when others then
dbms_output.put_line(‘Error creating ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line(‘ACL assigned…..’);
exception
when others then
dbms_output.put_line(‘Error assigning ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line(‘ACL commited…..’);
end;

Procedure created.
/

Create ACL Connection

SQL> begin
mailserver_acl(
‘mailserver_acl.xml’,
‘ACL for used Email Server to connect’,
‘CONNUSER’,
TRUE,
‘connect’,
‘100.10.10.11’,
25);
end;

PL/SQL procedure successfully completed.
/

Add User to ACL Privilage

SQL> begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘mailserver_acl.xml’,’NOTIFYUSER‘,TRUE,’connect’);
commit;
end;
/

PL/SQL procedure successfully completed.

10,794 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration


May 10 2012

Delete Duplicate Rows Without All Records in Oracle

Category: Administration,Procedure,SQLFatih Acar @ 10:23

If you want to delete duplicate rows but not all rows in table, you can use a procedure. For example, you have 10 duplicate rows and you want to delete 9 rows, then you can use procedure to delete process. For instance, you have a table as tmp_table. tmp_table’s columns are id,name,surname,city,phone. You want to delete duplicate for name,surname,city. You can use below procedure for this process.

Delete Duplicate Rows Procedure

CREATE OR REPLACE PROCEDURE deleteduplicate
IS
BEGIN
FOR duplicates IN ( SELECT max(id) id, name, surname, city
FROM tmp_table GROUP BY name,surname,city
HAVING count(*)>1)

LOOP
DELETE FROM tmp_table t
WHERE t.name = duplicates.name
AND t.surname = duplicates.surname
AND t.city = duplicates.city
AND t.id != duplicates.id;
END LOOP;
COMMIT;
END;

9,026 total views, 5 views today

Tags: Database Administration, Oracle, Stored Procedure


Jul 03 2011

XML file export with stored procedure in Oracle

Category: ProcedureFatih Acar @ 19:50

This example provides export xml file. You must create a directory before create procedure. After, you must give grant for user. Finally, You can create procedure and execute.

Create Directory and Grant

You must be sys user and can execute this queries;

CREATE DIRECTORY XMLDUMP AS ‘C:\XmlDirectoryFileName’;
SELECT * FROM DBA_DIRECTORIES;
GRANT READ,WRITE ON DIRECTORY XMLDUMP TO HR;

Create Procedure

CREATE OR REPLACE PROCEDURE PR_XMLEXPORT
IS
CURSOR CRS IS
SELECT first_name,last_name,salary FROM employees;

REC CRS%ROWTYPE;
XML_CONTENT VARCHAR2(10000) := ”;
FILENAME varchar2(100) := ’employees_salary.xml’;
FILEWRITE UTL_FILE.FILE_TYPE;

BEGIN

OPEN CRS;

XML_CONTENT := XML_CONTENT || ‘< ?xml version="1.0" encoding="ISO-8859-9" ? >‘;

LOOP
FETCH CRS INTO REC;
EXIT WHEN CRS%NOTFOUND;

XML_CONTENT := XML_CONTENT || ‘< employee>‘;
XML_CONTENT := XML_CONTENT || ‘< first_name>‘||TO_CHAR(REC.FIRST_NAME)||’< /first_name>‘;
XML_CONTENT := XML_CONTENT || ‘< last_name>‘||TO_CHAR(REC.LAST_NAME)||’< /last_name>‘;
XML_CONTENT := XML_CONTENT || ‘< salary>‘||TO_CHAR(REC.SALARY)||’< /salary>‘;
XML_CONTENT := XML_CONTENT || ‘< /employee>‘;

END LOOP;

XML_CONTENT := XML_CONTENT || ‘< /employees >‘;

FILEWRITE := UTL_FILE.FOPEN(‘XMLDUMP’,’sehirleregorenes.xml’,’w’,32767);
UTL_FILE.PUTF(FILEWRITE,XML_CONTENT);
UTL_FILE.FCLOSE(FILEWRITE);

COMMIT;

CLOSE CRS;

END;

22,766 total views, 5 views today

Tags: Oracle, SQL, Stored Procedure