Whatever topic has been discussed on this blog is my own finding and views, not necessary match with others. I strongly recommend you to do a test before you implement the piece of advice given at my blog.
Wrappingis the process of hiding PL/SQL source code. Wrapping
helps to protect your source code from business competitors and others who
might misuse it.
You can wrap PL/SQL source code with either thewraputility orDBMS_DDLsubprograms. Thewraputility wraps a single source file, such as a SQL*Plus
script. TheDBMS_DDLsubprograms wrap a single dynamically generated PL/SQL
unit, such as a singleCREATEPROCEDUREstatement.
Wrapped source files can be moved, backed up, and
processed by SQL*Plus and the Import and Export utilities, but they are not
visible through the static data dictionary views*_SOURCE.
Guidelines
for Wrapping
·Wrap only the body of a package or
object type, not the specification.
This allows other developers to see the information they
must use the package or type, but prevents them from seeing its implementation.
·Wrap code only after you have
finished editing it.
You cannot edit PL/SQL source code inside wrapped files.
Either wrap your code after it is ready to ship to users or include the
wrapping operation as part of your build environment.
To change wrapped PL/SQL code, edit the original source
file and then wrap it again.
·Before distributing a wrapped file,
view it in a text editor to be sure that all important parts are wrapped.
Limitations of Wrapping
·Wrapping is not a secure method for
hiding passwords or table names.
Wrapping a PL/SQL unit prevents most users from examining
the source code, but might not stop all of them.
·Wrapping does not hide the source
code for triggers.
To hide the workings of a trigger, write a one-line
trigger that invokes a wrapped subprogram.
·Wrapping does not detect syntax or
semantic errors.
Wrapping detects only tokenization errors (for example,
runaway strings), not syntax or semantic errors (for example, nonexistent
tables or views). Syntax or semantic errors are detected during PL/SQL
compilation or when executing the output file in SQL*Plus.
·Wrapped PL/SQL units are not
downward-compatible.
Wrapped PL/SQL units are upward-compatible between Oracle
Database releases, but are not downward-compatible. For example, you can load
files processed by the V8.1.5wraputility into a V8.1.6 Oracle Database, but you cannot
load files processed by the V8.1.6wraputility into a V8.1.5 Oracle Database.
There are two way to wrap your pl/sql code.
·Wrap
·DBMS_DDL
Wrapping can be done with the wrap utility and DBMS_DDL subprograms.
The wrap utility is run from the command line and processes an input SQL file, such as a SQL*Plus installation script. The DBMS_DDL subprograms wrap a single PL/SQL unit, such as a single CREATE PROCEDURE command, that has been generated dynamically.
WRAPPING ORACLE PL/SQL CODE WITH THE DBMS_DDL CREATE_WRAPPED PROCEDURE:
Create an anonymous block with your code (which needs to be encrypted) in generate_spec and generate_body, enclosed in quotes.
DECLARE
/* The package_text variable contains the text to create the package spec and body */
package_text VARCHAR2(32767);
FUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN 'CREATE PACKAGE ' || pkgname || ' AS
PROCEDURE add (x NUMBER, y NUMBER); END ' || pkgname || ';';
END generate_spec;
FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN 'CREATE PACKAGE BODY ' || pkgname || ' AS
PROCEDURE add (x NUMBER, y NUMBER) IS
BEGIN dbms_output.put_line (x+y); END add; END ' || pkgname || ';';
END generate_body;
BEGIN
-- generate package spec
package_text := generate_spec('Calculation');
-- create and wrap the package spec
SYS.DBMS_DDL.CREATE_WRAPPED(package_text);
-- generate package body
package_text := generate_body('Calculation');
-- create and wrap the package body
SYS.DBMS_DDL.CREATE_WRAPPED(package_text);
END;
The package object – calculation will get created in the data base in encrypted format.
Sql> Calculation.add(10, 20);
Check the all sources table. You can see an encrypted version of your code.
WRAPPING ORACLE PL/SQL CODE WITH THE WRAP UTILITY:
The wrap utility processes an input SQL file and obfuscates only the PL/SQL units in the file, such as a package specification, package body, function, procedure, type specification, or type body. It does not obfuscate PL/SQL content in anonymous blocks or triggers or non-PL/SQL code. To run the wrap utility, enter the wrap command at your operating system prompt using the following syntax: wrap iname=input_file [oname=output_file] Do not use any spaces around the equal signs. Example:
1.Write a procedure abc(), in a file abc.sql (do not compile it in database) and save it in C:\
create procedure abc is
begin
dbms_output.put_line('Hello World');
end abc;
2.First generate the wrapped code for this sql file using the following command.
wrap iname=C:\abc.sql
You will get the following message:
PL/SQL Wrapper: Release 9.0.1.3.1- Production on Sat Jun 04 2011
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing C:\abc.sql to abc.plb
3.The wrapped file code abc.plb is created in the same directory (C:\abc.sql). It will look like this
4.Now compile the encrypted code (abc.plb) in the database.
SQL> @C:\abc.plb
Procedure created.
5.So, our source code is not visible through the USER_SOURCE, ALL_SOURCE, or DBA_SOURCE data dictionary views.
Until Oracle comes up with an in-built functionality to validate an email address, you can use the following custom stored function. Compile it in the database and pass an email address to see if the email address is VALID, INVALID or UNKNOWN.
For example, if you pass following email addresses, the returning result will be as follow:
CREATE OR REPLACE FUNCTION xxcc_validate_email(p_email IN VARCHAR2)
RETURN VARCHAR2
--===============================================================================
-- Filename : XXCC_VALIDATE_EMAIL_FNC.sql
---------------------------------------------------------------------------------
-- Programmer : test_mail
-- Date : 24-Jul-2013
-- Language : PL/SQL
-- Location : $XXCC_TOP/install/
-- Purpose : This function validates an email address using PL/SQL
---------------------------------------------------------------------------------
-- Description :
-- Takes a string (p_email) as an input parameter. Through a series of
-- validation checks, this function determines whether the email address is
-- valid or invalid.
--
---------------------------------------------------------------------------------
-- Rules:
-- The following rules are based on the article "Characters in the local part
-- of a mail address" found in:
-- <http://www.ramindia03.blogspot.com>
--
-- 1) email cannot be NULL (length of NULL value is undetermined)
-- 2) email has to be minimum 5 characters long
-- 3) email requires at least one '@' and one '.' sign
-- 4) email cannot start or end with '@' sign
-- 5) email cannot end with '.' sign
-- 6) '.' sign cannot be before or after '@' sign
-- 7) double dots '..' are not permitted in an email
-- 8) invalid characters are: ` ~ ! # $ % ^ & * ( ) " | { } [ ] : ; , < > ? \ /
-- and single quote and space characters
-- 9) domain part must consists of strings
-- 10) email cannot contain two '@' sign
--
-- Note:
-- The returned value from this function is usually used against an email
-- address, which will be (or is supposed to be) used with UNIX's "mail" command.
-- Therefore, few more restrictions are applied to this functions.
--
----------------------------------------------------------------------------------------------------
-- Update History:
-- Date Updated By Description
-- --------- --------------------------- ---------------------------------------------------------
-- 24-Jul-13 Authour R Added test for double 'at' symbol in email.
-- Modified invalid character test to be in a single string.
-- 12-Aug-13 Authour R First version.
--==================================================================================================
IS
v_at_pos PLS_INTEGER; -- position variable for at (@) sign
v_dot_pos PLS_INTEGER; -- position variable for dot (.) sign
v_length PLS_INTEGER; -- holds the length of email
v_email_ch VARCHAR2(1); -- check character in an email
v_result VARCHAR2(10) DEFAULT 'VALID'; -- result variable
BEGIN
----------------------------------------------------------
-- variable assignments
----------------------------------------------------------
v_at_pos := INSTR(p_email, '@', -1); -- position for last occurrence of '@' sign
v_dot_pos := INSTR(p_email, '.', -1); -- position for last occurrence of '.' sign
v_length := LENGTH(p_email); -- length of entire email address
----------------------------------------------------------
-- First Round Check:
-- Rules for invalid email address
----------------------------------------------------------
IF p_email IS NULL OR -- email cannot be null
v_length < 5 OR -- email length should be at least 5 characters
v_at_pos = 0 OR -- email requires at least one '@' sign
v_dot_pos = 0 OR -- email requires at least one '.' sign
v_at_pos = 1 OR -- email cannot start with '@' sign
v_dot_pos = v_at_pos - 1 OR -- dot (.) sign cannot be right before at (@) sign
v_dot_pos = v_at_pos + 1 OR -- dot (.) sign cannot be right after at (@) sign
v_dot_pos = v_length OR -- email cannot end with dot '.' sign
v_at_pos = v_length OR -- email cannot end with at '@' sign
/* double dots are not permitted */
INSTR(SUBSTR(p_email, 1, (v_at_pos - 1)), '..') > 0 OR
INSTR(SUBSTR(p_email, v_at_pos + 1), '..') > 0 OR
/* requires one '.' sign after '@' sign */
INSTR(SUBSTR(p_email, v_at_pos), '.') = 0 OR
(INSTR(p_email, '@') > 0 AND INSTR(p_email, '@', INSTR(p_email, '@') + 1) > 0) OR
/* this following code finds out if the domain part is a number or a string */
UPPER(SUBSTR(p_email, v_at_pos, v_length)) = LOWER(SUBSTR(p_email, v_at_pos, v_length))
THEN
v_result := 'INVALID';
END IF;
----------------------------------------------------------
-- Second Round Check:
-- Invalid characters should not be in the email address.
-- Validate using INSTR method
----------------------------------------------------------
FOR i IN 1..v_length
LOOP
v_email_ch := SUBSTR(p_email, i, 1);
IF INSTR(' `~!#$%^&*"|(){}[]:;,<>?\/''''', v_email_ch) > 0
THEN
v_result := 'INVALID';
EXIT;
END IF;
SET SERVEROUTPUT ONEXEC dbms_output.put_line( dbms_db_version.version );
for example
If you just want to check the version information of the Oracle database, simply connect and login to the Oracle database with SQL *Plus. Upon login, you will see:
use this below function to get exact database version
CREATE FUNCTION fn_which_edition RETURN VARCHAR2 IS
/*
Purpose: determine which database edition
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
Ram A 12-Aug-2013 Initial Build
*/
-- Banner
CURSOR c_get_banner
IS
SELECT banner
FROM v$version
WHERE UPPER(banner) LIKE UPPER('Oracle Database%');
vrec_banner c_get_banner%ROWTYPE; -- row record
v_database VARCHAR2(32767); --
BEGIN
-- Get banner to get edition
OPEN c_get_banner;
FETCH c_get_banner INTO vrec_banner;
CLOSE c_get_banner;
-- Check for Database type
IF INSTR( UPPER(vrec_banner.banner), 'EXPRESS') > 0
THEN
v_database := 'EXPRESS';
ELSIF INSTR( UPPER(vrec_banner.banner), 'STANDARD') > 0
THEN
v_database := 'STANDARD';
ELSIF INSTR( UPPER(vrec_banner.banner), 'PERSONAL') > 0
THEN
v_database := 'PERSONAL';
ELSIF INSTR( UPPER(vrec_banner.banner), 'ENTERPRISE') > 0
THEN
v_database := 'ENTERPRISE';
ELSE
v_database := 'UNKNOWN';
END IF;
RETURN v_database;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'ERROR:' || SQLERRM(SQLCODE);
END fn_which_edition; -- function fn_which_edition
/
This one is a little wily, so John suggested we post it. Removing a label/value from a drop down should be super-simple. In Oracle Forms, however, you ought to know the shortcut key:
CTRL + SHIFT + ,
Why that combination? Well, it might look strange in writing, but when you type it, you will notice that “SHIFT + ,” makes a less-than symbol “<“. I won’t go as far as to say this is completely intuitive, but I hope this helps someone.
