What I learned Today

Date: 9/27/2011

Uses of rsync command for bulk file transfer. It will take less time as well you can transfer upto 1 L files(Advantage over SCP~ 15k)

http://www.thegeekstuff.com/2010/09/rsync-command-examples/

Using Jconsole to monitor application:

http://java.sun.com/developer/technicalArticles/J2SE/jconsole.html

http://download.oracle.com/javase/6/docs/technotes/guides/management/jconsole.html

Date:9/28/2011

Date:10/13/2011

How to fight with manager

How to use function in Excel

Nothing :(


Practice.

--Ref cursors

CREATE OR REPLACE PROCEDURE GET_EMP_DET ( p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_recordset FOR
        SELECT   ename, job, sal
            FROM emp
           WHERE deptno = p_deptno
        ORDER BY ename;
END;
/

--Global temporary tables
--issue for error ORA-14452: attempt to create, alter or drop an index on temporary table already in use

DECLARE
    ddl_exist_gtt_exception   EXCEPTION;
    PRAGMA EXCEPTION_INIT (ddl_exist_gtt_exception, -14452);
    obj_has_ref_exception     EXCEPTION;
    PRAGMA EXCEPTION_INIT (obj_has_ref_exception, -00357);
BEGIN
    EXECUTE IMMEDIATE 'drop table my_temp_table';
EXCEPTION
    WHEN ddl_exist_gtt_exception
    THEN
        --PLS-00357: Table,View Or Sequence reference 'MY_TEMP_TABLE' not allowed in this context
        BEGIN
            DBMS_OUTPUT.put_line ('GTT   ' || my_temp_table || 'Already exists:');
        EXCEPTION
            WHEN obj_has_ref_exception
            THEN
                DBMS_OUTPUT.put_line ('Table my_temp_table directly referenced in the code');
        END;

        DBMS_OUTPUT.put_line ('GTT   ' || 'my_temp_table ' || 'Already exists:');
END;
/

CREATE GLOBAL TEMPORARY TABLE my_temp_table (ename VARCHAR2 (10), job VARCHAR2 (10), sal NUMBER (7, 2))
ON COMMIT PRESERVE ROWS;

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
    l_cursor   SYS_REFCURSOR;
    l_ename    emp.ename%TYPE;
    l_job      emp.job%TYPE;
    l_sal      emp.sal%TYPE;
BEGIN
    GET_EMP_DET ( p_deptno => 30, p_recordset => l_cursor);

    LOOP
        FETCH l_cursor
        INTO l_ename, l_job, l_sal;

        INSERT INTO my_temp_table
             VALUES (l_ename, l_job, l_sal);

        EXIT WHEN l_cursor%NOTFOUND;
        DBMS_OUTPUT.put_line (l_ename || '   ' || l_job || '   ' || l_sal);
    END LOOP;

    CLOSE l_cursor;
END;
/


---Running sum of records


column pct_dept format 99.9
column pct_overall format 99.9
break on deptno skip 1

select deptno,
           ename,
           sal,
           sum(sal) over (partition by deptno order by sal,ename) cum_sal,
           round(100*ratio_to_report(sal)
                over (partition by deptno), 1 ) pct_dept,
           round(100*ratio_to_report(sal) over () , 1 ) pct_overall
      from emp
     order by deptno, sal
/

--Versioning of dml's



--Example of sleep and isolation of transaction.

Trans1.

 declare
 tmp number:=0;
 begin
 select count(*)  into tmp from anand10 where a=3;
 dbms_output.put_line(tmp);
  dbms_lock.sleep(5) ;
 if tmp>0 then
update anand10 set b=100 where a=3;
 end if;
 end;
 /


 Trans2

 SQL> update anand10 set a=60 where a=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> update anand10 set a=60 where a=3;

1 row updated.

SQL> rollback;

Rollback complete.

SQL>


--Example of

Trans1:


--Example of conditional paralell execution

 declare
 tmp number:=0;
 begin
while (tmp<>60)
loop
dbms_output.put_line('The value of b is:  '||tmp);
dbms_lock.sleep(5);
 select b  into tmp from anand10 where a=3;
end loop;
 end;
 /

Trans2:

SQL> select * from anand10;

         A          B
---------- ----------
         1         10
         2         20
         3        100

SQL> update anand10 set b=60 where a=3;

1 row updated.

SQL> commit;

Commit complete.


Trans1:

The value of b is:  0
The value of b is:  100
The value of b is:  100
The value of b is:  100
The value of b is:  100
The value of b is:  100
The value of b is:  100
The value of b is:  100

===============
10-03-2016
===============

1. We cannot use input parameters entered to .sql file from outside inside the proc body directly. If we want that input parameter to be used inside proc/function, we have to pass that as input parameter in that proc/function.
Ex: create or replace PROCEDURE enableRMF (p_user IN VARCHAR2)
AS
v_user VARCHAR2(30);
BEGIN
v_user := UPPER(p_user);
    UPDATE Tab1
       SET col1='XXXX'
     WHERE     SCHEMA_NAME = v_user;
END;
/

EXECUTE enableRMF ('&1');

Below is wrong

create or replace PROCEDURE enableRMF
AS
v_user VARCHAR2(30);
BEGIN
v_user := UPPER('&1');
    UPDATE Tab1
       SET col1='XXXX'
     WHERE     SCHEMA_NAME = v_user;
 END;
/

EXECUTE enableRMF;

2. We can use derived column names in order by clause. Order by gets executed on top of resultset of the query so sorting can be done based on derived column names.

Ex: select f_name||l_name as name from emp order by name;