Oracle Tech

How to generate random password in oracle
SELECT Upper(DBMS_RANDOM.STRING('B', 1)) || DBMS_RANDOM.STRING('A', 5) ||    DBMS_RANDOM.VALUE(1, 1) || Lower(DBMS_RANDOM.STRING('A', 1)) FROM DUAL;

How to convert  string(1,2,3,4,5) to table

 SELECT * FROM TABLE(CAST(SPLIT('0,1,2') AS SPLIT_TBL));

create or replace function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;


How to split and trim from a string?
SELECT SUBSTR(RELDATE_IN, 1, 11),SUBSTR(RELDATE_IN, 13, 11)  INTO RELIEVINGDATE, RESIGNDATE FROM DUAL;

How to Get rows if parameter value null and not null?

select * from ademployee WHERE ((ADEMPCODE_IN IS NOT NULL AND RH.ADEMPCODE = ADEMPCODE_IN) OR (ADEMPCODE_IN IS NULL AND 1 = 1))

How to change date format in oracle?

select TO_CHAR(SUBMITDATE, 'dd-Mon-yyyy') as SUBMITDATE from tblname

Example of NVL :

 SELECT NVL(MAX(transid), 0) + 1 INTO VARAPPROVALDETAILID FROM EMP.

How to handle a Exception in Oracle?
insert into emp(id,name) values('1','Ram') ;
Commit;
RESULT_OUT := 1;
 EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      err_num    := SQLCODE;
      err_msg    := SQLERRM;
      ERRMSG     := 'ERROR NUMBER:' || err_num || 'ERROR MESSAGE:' ||
                    err_msg;
      RESULT_OUT := 0;
      DBMS_OUTPUT.PUT_LINE('ERROR NUMBER:' || err_num || 'ERROR MESSAGE:' ||
                           err_msg);

How to Insert from XML in Oracle?

 PROCEDURE SPROC_getdetail(INTERVIEWXML_IN IN VARCHAR2,
                           RESULT_OUT OUT NUMBER,
                           ERRMSG     OUT VARCHAR2) IS
 
    err_num                varchar2(200);
    err_msg                varchar2(3000);
    V_Q               varchar2(200);
    V_O               varchar2(200);
    V_T                    varchar2(200);
    V_QT                   varchar2(200);
    NAVXML                 XMLDOM.DOMDOCUMENT;
    RESPONSE               XMLDOM.DOMNODELIST;
    CURNODE                XMLDOM.DOMNODE;
    PARSER                 XMLPARSER.PARSER;
    NODECOUNT              INTEGER;
    v_array                mytabletype;
  BEGIN

/*
<Interview>
  <I>
     <Q>fdgdfg</Q>
     <O>fgdgd</O>
     <T>fdgfdg</T>
     <QT>fdgfdg</QT>   
  </I>
  <I>
     <Q>fdgdfg</Q>
     <O>fgdgd</O>
     <T>fdgfdg</T>
     <QT>fdgfdg</QT>   
  </I>
</Interview>
/*

  
    PARSER := XMLPARSER.NEWPARSER;
    XMLPARSER.PARSEBUFFER(PARSER, INTERVIEWXML_IN);
    NAVXML := XMLPARSER.GETDOCUMENT(PARSER);
    XMLPARSER.FREEPARSER(PARSER);

    CURNODE := XMLDOM.MAKENODE(NAVXML);
 
    RESPONSE  := XSLPROCESSOR.SELECTNODES(CURNODE, '/INTERVIEW/I');
    NODECOUNT := XMLDOM.GETLENGTH(RESPONSE);
 
    FOR J IN 0 .. NODECOUNT - 1 LOOP
   
      CURNODE                  := XMLDOM.ITEM(RESPONSE, J);
      V_Q                      := XSLPROCESSOR.VALUEOF(CURNODE, 'Q');
      V_O                      := XSLPROCESSOR.VALUEOF(CURNODE, 'O');
      V_T                      := XSLPROCESSOR.VALUEOF(CURNODE, 'T');
      V_QT                     := XSLPROCESSOR.VALUEOF(CURNODE, 'QT');   
    
    Insert into emp values(V_Q,V_O,V_T,V_QT);

    END LOOP;
    COMMIT;
        RESULT_OUT := 1;
   
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      err_num    := SQLCODE;
      err_msg    := SQLERRM;
      ERRMSG     := 'ERROR NUMBER:' || err_num || 'ERROR MESSAGE:' ||
                    err_msg;
      RESULT_OUT := 0;
      DBMS_OUTPUT.PUT_LINE('ERROR NUMBER:' || err_num || 'ERROR MESSAGE:' ||
                           err_msg);
                          
  END SPROC_INSERTINTERVIEWANSWER;

How to use transaction and rollback in Oracle?

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;

 How to get all rows values in one column ?

select rtrim (xmlagg (xmlelement (e, SISHDRESOLVINGGROUPID || ',')).extract ('//text()'), ',') into RESOLVERGRPID from
(SELECT ecode FROM employees);