PLSQL herkesin bilmesi gerekenler-2

Source kodu şifreleme

Yazdığınız kodları bodylerini şifrelememiz mümkündür. Zaten hazır gelen oracle paketlerinde şifrelemesi yapılmıstır.

Şifrelenmiş kodları user_source ile bakılsada ekranda görmemiz mümkün olmayacaktır.

WRAP INAME=.sql ONAME=.plb

WRAP INAME=exec_ddl.sql ONAME=exec_ddl.plb

CREATE OR REPLACE PROCEDURE exec_ddl (p_statement_txt
VARCHAR2) IS
lv_exec_cursor_num PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
lv_rows_processed_num PLS_INTEGER := 0;
BEGIN
DBMS_SQL.PARSE (lv_exec_cursor_num, p_statement_txt,
DBMS_SQL.NATIVE);
lv_rows_processed_num := DBMS_SQL.EXECUTE
(lv_exec_cursor_num);
DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN (lv_exec_cursor_num) THEN
DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
END IF;
END exec_ddl;
/

CREATE OR REPLACE PROCEDURE exec_ddl wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd…

DECLARE
WRAPPED_TEXT VARCHAR2(1000);
BEGIN
WRAPPED_TEXT := ‘CREATE PROCEDURE xyz AS BEGIN
DBMS_OUTPUT.PUT_LINE(”EXECUTED PROCEDURE XYZ”); END;’;
DBMS_DDL.CREATE_WRAPPED(WRAPPED_TEXT);
END;

10gR2> EXECUTE XYZ
EXECUTED PROCEDURE XYZ
10gR2> SELECT * FROM USER_SOURCE WHERE NAME = ‘XYZ’;
NAME TYPE LINE TEXT
———- ———- ———- —————————
XYZ PROCEDURE 1 PROCEDURE xyz wrapped
a000000
b2
abcd
abcd

4-Autonomous Transactions

Bu yapı sayesinde bir transaction diğerinin içinde olmasına rağmen bağımsız çalışabilir.
Böylece commitlenmemiş bir kredi kartı işleminin bile audit benzeri bir tabloya yazılmasını sağlayabiliriz.

CREATE OR REPLACE PROCEDURE insert_product
(p_product_num VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO s_product
(product_id, product_name)
VALUES
(p_product_num, ‘NEW PRODUCT’);
COMMIT;
END;
/

BEGIN
INSERT INTO s_inventory
(warehouse_id, product_id)
VALUES
(10501, 10011);
insert_product(232);
ROLLBACK;
END;
/

sonuc
– Product 232 Inserted
– hic kayıt insert edlmiştir. s_inventory

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s