PL/SQL Oracle - Collections in Records

declare 
    type numlist is table of number;
    n numlist := numlist(10,23,31,12,45,12,34,45,56,12);
    
    procedure print_numlist (the_list NumList) is output varchar2(128);
    begin 
        if n.count = 0 then 
     dbms_output.put_line ('no elements in collection..');
    else 
       for i in the_list.first .. the_list.last loop 
            output := output || nvl (to_char(the_list(i)) , 'NULL' ) || ' ';
            dbms_output.put_line ('data ke ' || i || ' => '|| to_char(the_list(i)));
       end loop;
       dbms_output.put_line (' Output => ' || output );
    end if;
    end;
begin 
    dbms_output.put_line ('tees type ');
    print_numlist (n);
    
end;
/

sdfgsfdg



-- source : https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABEDEGG

SQL Oracle - Fungsi Character

kali ini saya akan memberikan list dari fungsi yang di sediakan oleh oracle untuk memanipulasi Character ...

Function	Description	Example	Result
regexp_count	menghitung Jumlah Karakter	select regexp_count( 'belajaroracle', 'a' ) from dual	3
CONCAT	menggabungkan 2 karakter atau column	SELECT CONCAT('Mike ', 'Hunt') FROM dual;	Mike Hunt
SUBSTR,INSTR	mengambil character sebelum special character	SELECT NVL (SUBSTR ('ABC_BLAH', 0, INSTR ('ABC_BLAH', '_') - 1), 'ABC_BLAH') TES_SUBSTR1,        NVL (SUBSTR ('123434-4', 0, INSTR ('123434-4', '-') - 1), '123434-4') TES_SUBSTR2   FROM DUAL	ABC, 123434

Format Date 

-- Date and Time Format Model 
-- source : http://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements004.htm#sthref478

-- blog reference for Date Function http://erporacleapps.blogspot.com/2013/04/how-to-work-with-date-functions.html

DATE AND TIME
SELECT TO_DATE('1998 05 20','YYYY MM DD') TES_DATE FROM DUAL
Output : 20/05/1998

select to_date (to_char ('01-JAN-16') || ' 23:59:59', 'DD-MON-RR HH24:MI:SS')  concat_date_with_time from dual;
Output  : 01/01/2016 23.59.59

SELECT TO_CHAR(TO_DATE('27-OCT-18', 'DD-MON-RR') ,'fmDay Month YYYY') as tesDate FROM DUAL;
Output : Saturday October 2018

NEXT_DAY 
select next_day('04-Dec-19','Wed') from dual;  -- 11/12/2019
select  to_char ( add_months( trunc(sysdate,'y'), level-1 ), 'Month' ) IN_Month , 
       add_months( trunc(sysdate,'y'), level-1 ) open_period,  
       last_day ( add_months( trunc(sysdate,'y'), level-1 ) )  close_period
       from dual 
connect by level <= 12;

Output : 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle XMLTable

kali ini saya akan memberikan sedikit ilmu mengenai oracle ,
di Oracle ada sebuah Fitur  Function yaitu XMLTable ....

pertama buat lah sebuah table

CREATE TABLE EMPLOYEES2
(
   id     NUMBER,
   data   XMLTYPE
);

setelah itu tambahkan data berupa data XML  ke dalam table yang telah di buat sebelumnya

INSERT INTO EMPLOYEES2
     VALUES (1, xmltype ('
    
        John
        Watson
        30
        johnwatson@sh.com
    
    
        Sherlock
        Homes
        32
        sherlock@sh.com
    
    
        Jim
        Moriarty
        52
        jim@sh.com
    
    
        Mycroft
        Holmes
        41
        mycroft@sh.com
    
'));

-- link surce : https://www.viralpatel.net/oracle-xmltable-tutorial/

Basic Query - Oracle EBS

berikut ini adalah cara membuat procedure dalam oracle

begin
   for i in 1 .. 6
   loop
      dbms_output.put_line ('Cetak angka [' || i || '].');

      if mod(i, 2) = 0 then
         continue;
      end if;

      dbms_output.put_line ('Second Statement, index is [' || i || '].');
   end loop;
end;
/

dalam oracle bla bla .. ini tulisan nya belum bereees yaaah..