• View
• Edit
• History
• Print

OracleSQL

Here is how to execute a PL/SQL package function in an SQL Client app (SQLPLUS, SQLDeveloper?, etc)

declare
outvar JAAS_UTILS.principal_ref;
begin
outvar :=  JAAS_UTILS.get_user_authentication('a', b', 'c');
end;

Here is a way to create an SHA-1 hash in an Oracle 10g database. It uses a java stored procedure so your database needs to have Java installed. The SHA1Encoder class has been modified from one by Frank Nimphius, hence the big copyright notice.

It is worth noting that this example uses base64 encoding to convert the hash to ascii, many examples on the web use a hex conversion, so if your encrypted passwords don't match that may be the cause.

create or replace and compile
java source named "Sha1" as

import java.security.*;
import sun.misc.BASE64Encoder;

/*
* @(#)DBLoginModuleSHA1Encoder.java  1.0 March 2005
*
* Copyright (c) 2001-2005 by Oracle. All Rights Reserved.
*
* Oracle grants you ("Licensee") a non-exclusive, royalty free, license to use,
* modify and redistribute this software in source and binary code form,
* provided that i) this copyright notice and license appear on all copies of
* the software; and ii) Licensee does not utilize the software in a manner
* which is disparaging to Oracle.
*
* This software is provided "AS IS," without a warranty of any kind. ALL
* EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY
* IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
* NON-INFRINGEMENT, ARE HEREBY EXCLUDED. ORACLE AND ITS LICENSORS SHALL NOT BE
* LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING
* OR DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL ORACLE OR ITS
* LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT,
* INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER
* CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF
* OR INABILITY TO USE SOFTWARE, EVEN IF ORACLE HAS BEEN ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGES.
*
* This software is not designed or intended for use in on-line control of
* aircraft, air traffic, aircraft navigation or aircraft communications; or in
* the design, construction, operation or maintenance of any nuclear
* facility. Licensee represents and warrants that it will not use or
* redistribute the Software for such purposes.
*/

public class SHA1Encoder {

public static String getSHA1HashBase64 (String message, String key) {
try {
String pwCompareStr = "";
byte[] messageByte = message.getBytes();
// of no key is provided, the message string gets encrypted with itself

byte[] keyByte = null;
if(key != null ) keyByte = key.getBytes();
else keyByte = message.getBytes();

// get SHA1 instance
MessageDigest sha1 = MessageDigest.getInstance("SHA1");
sha1.update(messageByte);
byte[] digestByte = sha1.digest(keyByte);

// base 64 encoding
BASE64Encoder b64Encoder = new BASE64Encoder();
pwCompareStr = (b64Encoder.encode(digestByte));

return pwCompareStr;
}

catch (NoSuchAlgorithmException e) {
}
return null;
}
}

/

Create Or Replace Package sha1 Is
Function getHash(p_str In Varchar2, c_key In Varchar2) Return Varchar2;
End sha1;
/
Create Or Replace Package Body sha1 Is
Function getHash (p_str In Varchar2, c_key In Varchar2) Return Varchar2
As Language Java Name
'SHA1Encoder.getSHA1HashBase64(java.lang.String, java.lang.String) return java.lang.String';

End sha1;
/

You can then do something like

select SHA1.getHash('abc', 'key') from dual

Basic stored proc syntax is

CREATE PROCEDURE remove_emp
(employee_id    in    NUMBER)
AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;

A better table describe with a flag for primary key

set head off
set wrap off
set lin 50
select tc.column_name, cstrt.constraint_type
from user_tab_columns tc,
(select  cc.constraint_name, constraint_type, cc.column_name
from user_cons_columns cc, user_constraints cs
where cc.constraint_name=cs.constraint_name(+)
and constraint_type='P') cstrt
where tc.column_name=cstrt.column_name(+)
and tc.table_name='&1'
order by cstrt.constraint_type ;

This is a way to select and sort/order columns of type varray in Oracle tables, (VARRAYS are good for numeric arrays and naturally ordered lists that aren't usually queried or sorted - such as address lines).

select a.col1,a.col2,a.col3, ... b.*
from table1 a, table(varray_col_name) b
where ...
order by a.col1, b.column_value;

Oracle has a merge command which will insert if a value does not exist but it only works with data in tables, so suppose you want to create a user if they do not exist, but not get an error if they do. (Obviously this can be easily done in PL/SQL but sometimes executing PL/SQL blocks is not available).

create table tmp_users (userid varchar2(50));

insert into tmp_users values('xx');

merge INTO users tgt USING
(select userid from tmp_users where userid='xx') tu
ON (tgt.userid=tu.userid)
WHEN NOT matched THEN
INSERT (tgt.userid)
VALUES(tu.USERID);