create Large Clob ,convert Clob to XML and Convert XML to row and Column

declare
v_clob clob;
v_varchar VARCHAR2(32767);
x         xmltype;
l  number;
begin
 v_varchar := '
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7369</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7368</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7379</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7309</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7319</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7329</empno>
    <ename>SMITH</ename>
    <job>CLERK</job>
    <hiredate>17-DEC-1980</hiredate>
  </employee>
  <employee>
    <empno>7499</empno>
    <ename>ALLEN</ename>
    <job>SALESMAN</job>
    <hiredate>20-FEB-1981</hiredate>
  </employee>
   ';
l:=LENGTH(v_varchar);
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

 DBMS_LOB.WRITEAPPEND(v_clob,length('<employees>'),'<employees>');
For i in 1..30 loop
  DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
end loop;
 DBMS_LOB.WRITEAPPEND(v_clob,length('</employees>'),'</employees>');
 dbms_output.put_line(DBMS_LOB.GETLENGTH(v_clob));
x := xmltype.createxml(v_clob);
FOR cur_rec IN (
    SELECT xt.*
    FROM   XMLTABLE('/employees/employee'
             PASSING x
             COLUMNS
               empno     VARCHAR2(4)  PATH 'empno',
               ename     VARCHAR2(10) PATH 'ename',
               job       VARCHAR2(9)  PATH 'job',
               hiredate  VARCHAR2(11) PATH 'hiredate'
             ) xt)
  LOOP
    DBMS_OUTPUT.put_line('empno=' || cur_rec.empno ||
                         '  ename=' || cur_rec.ename ||
                         '  job=' || cur_rec.job||
                         '  hiredate=' || cur_rec.hiredate);
  END LOOP;
end ;

Oracle Sequence

A Sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys.
You can refer to sequence values in SQL statements with these Pseudocolumns:

CURRVAL : Returns current value for sequence
NEXTVAL:  Increments the sequence and returns the next value 

To Access in another schema ,you need to provide select grant on sequence. 

Where To USE
You can use CURRVAL and NEXTVAL in the following locations:

• The select list of a SELECT statement that is not contained in a subquery, materialized view, or view
• The select list of a subquery in an INSERT statement
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE statement

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:

• A subquery in a DELETE, SELECT, or UPDATE statement
• A query of a view or of a materialized view
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY clause or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint.

Create Syntax:

 

Example :

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

INCREMENT BY Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

START WITH  Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE  Specify NOMAXVALUE to indicate a maximum value of 10²⁷ for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE  Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -10²⁶ for a descending sequence. This is the default.

CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE  Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

NOCACHE  Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.

ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

NOORDER  Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

 

 

Oracle PL SQL Associative Array example

DECLARE
v VARCHAR2(4000);
type v_ar is table of number index by varchar2(5);
v_ar_tab v_ar;
l_idx NUMBER;
l_pick_fmt VARCHAR2(4000);
l_pick_uom VARCHAR2(3);
l_pick_qty NUMBER;
CURSOR c IS SELECT 'MC-25,IC-10' pick FROM dual
            UNION ALL
            SELECT 'MC-25,IC-10' pick FROM dual;
BEGIN
        OPEN c;
        loop
          fetch c INTO v;
          exit when c%notfound;
         l_idx :=instr(v,',');
         --dbms_output.put_line(substr(v,1,instr(v,'-')-1));
         if l_idx =0 then
            v_ar_tab(substr(v,1,instr(v,'-')-1)) :=to_number(substr(v,instr(v,'-')+1)) ;
         ELSE
            loop
                      l_idx :=instr(v,',');
                      IF l_idx >0 THEN
                          l_pick_fmt  :=substr(v,1,l_idx-1);
                          v  :=substr(v,l_idx+1);
                      ELSE
                           l_pick_fmt :=v;
                           v:=null;
                      END IF;
                     l_pick_uom:=substr(l_pick_fmt,1,instr(l_pick_fmt,'-')-1);
                     l_pick_qty :=to_number(substr(l_pick_fmt,instr(l_pick_fmt,'-')+1));
                     IF  v_ar_tab.count >0 THEN
                        if (v_ar_tab.EXISTS(l_pick_uom)) THEN
                           
                           v_ar_tab(l_pick_uom) :=v_ar_tab(l_pick_uom) + l_pick_qty;
                           ELSE
                           v_ar_tab(l_pick_uom) :=l_pick_qty;
                         END IF;
                     ELSE
                        v_ar_tab(l_pick_uom) :=l_pick_qty;
                     END IF;                  
                    exit when v is null ;
            END loop;          
         END IF;
       
      end loop;    
         
          l_pick_uom := v_ar_tab.FIRST;  -- get subscript of first element
          WHILE l_pick_uom IS NOT NULL LOOP        
             dbms_output.put_line(l_pick_uom);
             dbms_output.put_line(v_ar_tab(l_pick_uom));
             l_pick_uom := v_ar_tab.NEXT(l_pick_uom);  -- get subscript of next element
          END LOOP;
 
end;

How to Add Constarint in Table Create Command

CREATE TABLE ProductSales(SalesID INT,ProductID INT,SalesPerson VARCHAR(25)CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),CONSTRAINT fk_productSales_pidFOREIGN KEY(ProductID)REFERENCES Products(ProductID) ON DELETE CASCADE
);


You can specify ON DELETE CASCADE also in Foreign Key Constraint
You Can also Create Foreign Key constraint Like this......


CREATE TABLE Orders
(
O_Id INT NOT NULL,
OrderNo INT NOT NULL,
P_Id INT,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)


You Can Also Add Constraint by Using Alter Command also 


ALTER TABLE ProductSalesADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)

Window beforeunload Event

 Description :
                       This event fires before page leave .You can also ask confirmation on page leave.

In JQuery Use this syntax to bind Beforeunload Event



$(window).bind("beforeunload",function(event) {
    if(hasChanged) return "You have unsaved changes";
});




And if You want to Perform some action on page leave and not want confirmation Dialog Box

$(window).bind("beforeunload",function(event) {

    if(hasChanged) alert('Hello');

         null;

});