Below is an example of storing any strings (eg passwords) in your database in a more secure and encrypted way.

-----------------------------------------------------
create or replace function custom_hash (p_username in varchar2, p_password in varchar2)
return varchar2
is
l_password varchar2(4000);
l_salt varchar2(4000) := 'ZJDCQTLTM85X893HOAFHU2KBDXHJBP';
begin

-- This function should be wrapped, as the hash algorhythm is exposed here.
-- You can change the value of l_salt or the method of which to call the
-- DBMS_OBFUSCATOIN toolkit, but you much reset all of your passwords
-- if you choose to do this.

l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
(input_string => p_password || substr(l_salt,10,13) || p_username ||
substr(l_salt, 4,10)));
return l_password;
end;

How to invoke a shell script from Stored Procedure?

This is acheived via creating an external Java procedure, which calls the shell script. Tested Sample Code is attached.

URL for reference is http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

**********************************************************
Run this procedure as sys or equivalent user
Change the filename and Schema name to your requirement.
**********************************************************

begin
dbms_java.grant_permission
('ILM_TOOLKIT',
'java.io.FilePermission',
'/app/ilm_demo_reset/ilm_demo_reset.sh',
'execute');

dbms_java.grant_permission
('ILM_TOOLKIT',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/


**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
**********************************************************

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
**********************************************************

create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/


**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
Argument: p_cmd The shell you want to run.
**********************************************************

create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/


*************************************************************
To run the command
*************************************************************
set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
exec rc('/app/ilm_demo_reset/ilm_demo_reset.sh');









------------------------------------------------------------------------------------
There is another way to run shell scripts from PL/SQL using DBMS.SCHEDULER.

To use DBMS.SCHEDULER, first create a job as the db user that you want to use to use to invoke the shell script. To create a job in your own schema, you need to have the CREATE JOB privilege. A user with the CREATE ANY JOB privilege can create a job in any schema. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run. http://st-doc.us.oracle.com/11/111/appdev.111/b28419/d_sched.htm#i1000363

BEGIN

sys.dbms_scheduler.create_job(

job_name => 'RUN_TEST_SCRIPT',

job_type => 'EXECUTABLE',

job_action => '/app/oracle/product/tds/test.sh',

comments => 'Run my test.sh',

auto_drop => FALSE,

enabled => TRUE);

END;

/

Note: The job runs on creation, you could create the job with auto_drop => TRUE, but in this case we want to preserve the job so that it can be called later.

Then you can run the job to execute the shell script by running the following in PL/SQL

BEGIN

DBMS_SCHEDULER.RUN_JOB (

job_name => 'RUN_TEST_SCRIPT');

END;

/

Note: Be sure to include #!/bin/bash in the first line of the shell script that you are running. Here is the test script that I used:

#!/bin/bash

echo "You got to $0" > /tmp/test.log;

How to execute a PL-SQL procedure via URL?

Step 1: Create the procedure under your
Step 2: Grant execute on to anonymous;
Step 3: Edit $ORACLE_HOME/apex/core/wwv_flow_epg_include_local.sql


Add your in list. The sql has comments which are self explanatory. Please read them before modifying.

Step 4: Invoke the procedure with url with following convention

http://:/apex/.?p=

I have tested the working (without any parametes) and it works fine for me.

Tip: To see text returned on procedure execution use "htp.p" package htp.p('your message');

A sample procedure which prints back on html page:

create or replace procedure test_p
as
begin
htp.p('Welcome!');
-- your code
-- htp.p('End program')
end;

/

How to retrieve view definition?

set long 500000000
set arraysize 1
set maxdata 50000

>select text from user_viewswhere view_name = 'Whatever';

If your dont set above parameters, text comes truncated.

How to make Oracle Table Read only?
(For complete details refer: http://www.dizwell.com/prod/node/60)

• You could lock the table in exclusive mode. But the lock disappears, and DML resumes, when the locking session terminates.

connect / as sysdbalock table scott.emp in exclusive mode;
connect scott/tiger
select sum(sal) from emp;
SUM(SAL)
----------29025
update emp set sal=sal*2;
[session hangs]

• You could simply revoke DML privileges on a table from all users, but risk forgetting one or two, or risk the possibility of a user hacking into the database with higher privileges.

create or replace function lockme
(object_schema in varchar2, object_name in varchar2)
return varchar2 is predicate varchar2(20);
begin predicate := '1=2';
return predicate;
end;
/
exec dbms_rls.add_policy('SCOTT','EMP','LCKPOL',-'SCOTT','LOCKME',’INSERT, UPDATE, DELETE’)

This creates a function which returns a never-true predicate (that is, 1 never equals 2), and uses Oracle’s Virtual Private Database functionality to attach a policy to the table which causes the function to fire and to append that predicate to whatever DML-style SQL is issued against the EMP table. Selects should be fine, however:
SYS is exempt from Virtual Private Database policies. Your table really isn’t made read-only by this technique -merely protected from casual DML issued by ordinary users. Which might genuinely be all that you neded. But if the reason you wanted to make the table read-only was to prevent any modification of this table’s data, then this mechanism (clever and convenient as it is) fails that test. The DBA can always by-pass the mechanism.

• You could create a 'no DML' trigger, but risk the trigger being dropped or invalidated, and hence by-passed, by an ordinary user.

create or replace trigger lock_emp
before insert or update or delete on scott.emp
begin
raise_application_error(-20001, 'EMP is now Read-Only');
end;
/

• You could apply a VPD policy to the table, but that never applies to SYS

• You could make the table's tablespace read-only, and rely on auditing to let you know if an administrator tries to change that status (capturing the culprit after the event)

alter tablespace ro_tables read only;
alter tablespace ro_tables read write; (to restore)

• You could truly lock down the table by moving it to read-only tablespace and burning the data files involved onto physically read-only media. That is un-circumventable by anyone.

What is Mutating Table Errors?

Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors:

• A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.)
• A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

How to delete duplicate rows using analytic functions?

delete from emp
where rowid in
(select rid
from (select rowid rid, row_number() over
( partition by ENAME order by rowid ) rn from emp
)
where rn <> 1
);

This is much more fast and efficient way of removing duplicates compared to
standard approach.

Oracle SQL

How to find the nth largest salary?

There are many ways to do this out of many few possible are listed below:

1.
Select ename , sal , rank
from
( select x.ename , x.sal , rownum rank
from
( select e.ename, e.sal
from
emp e order by e.sal desc
) x
)
where rank = N

2.
select ename, salfrom (select ename, sal, rownum() (partition by ename order by sal desc) rank from emp)where rank = N

3.
Select * from emp where sal =(select min(sal) from emp A where &n >= (select count(*) from empwhere sal >= a.sal))

4.
SELECT * FROM (SELECT sal,ROWNUM RN FROM (Select distinct salfrom emp order by sal desc) WHERE ROWNUM < &n )WHERE RN = &n-1;

5.
select t.salfrom(select sal, rank() over (order by sal desc) rnkfrom emp) twhere t.rnk =&n;

-----------------------------------------------------------------------------------------------