Example of Oracle Pipelined Function

Create Type Loop_Type is table of Varchar2(400)


Create or Replace Function  Find_Loop_F (P_End_loop in NUmber Default 10) Return Loop_Type PIPELINED
Is
    begin
        For  op in  1..P_End_loop loop
            Pipe row  (op);
            Pipe row  ('Value' );
        end loop;
        --Return;
end;


select * from (Table (Find_Loop_F(200)))

Send Email from PLSQL

DECLARE
    v_from_name         VARCHAR2(100) := 'admin@falickfaisal.com';
    v_to_name           VARCHAR2(100) := 'falickfaisal@gmail.com';

    v_subject           VARCHAR2(100) := 'This is an awesome mail';
    v_message_body      VARCHAR2(100) := 'Hey you!! Bla Bla Bluu';
    v_message_type      VARCHAR2(100) := 'text/plain';

    v_smtp_server       VARCHAR2(200)  := 'smtpout.asia.secureserver.net';
    n_smtp_server_port  NUMBER        := 25;
    conn                utl_smtp.connection;

    TYPE attach_info IS RECORD (
        attach_name     VARCHAR2(40),
        data_type       VARCHAR2(40) DEFAULT 'text/plain',
        attach_content  CLOB DEFAULT ''
    );
    TYPE array_attachments IS TABLE OF attach_info;
    attachments array_attachments := array_attachments();

    n_offset            NUMBER;
    n_amount            NUMBER        := 1900;
    v_crlf              VARCHAR2(5)   := CHR(13) || CHR(10);
    base64username VARCHAR2(40) := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('admin@falickfaisal.com'))); 
base64password  VARCHAR2(40):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('123456789')));



BEGIN

  -- Fill data for example
    attachments.extend(5);
    FOR i IN 1..5
    LOOP
        SELECT 'test' || to_char(i) || '.txt','text/plain','test' || to_char(i)
        INTO attachments(i)
        FROM dual;
    END LOOP;



  -- Open the SMTP connection ...
    conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
    utl_smtp.helo(conn, v_smtp_server);
    UTL_SMTP.command(conn, 'AUTH', 'LOGIN');
    UTL_SMTP.command(conn, base64username );
    UTL_SMTP.command(conn, base64password );

    utl_smtp.mail(conn, v_from_name);
    utl_smtp.rcpt(conn, v_to_name);

  -- Open data
    utl_smtp.open_data(conn);

  -- Message info
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_to_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));

  -- Message body
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));

  -- Attachment Part
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
                            || ' name="'|| attachments(i).attach_name || '"' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).attach_name || '"' || v_crlf || v_crlf));

    -- Attach body
        n_offset := 1;
        WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)
        LOOP
            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));
            n_offset := n_offset + n_amount;
        END LOOP;
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));
    END LOOP;

  -- Last boundry
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));

  -- Close data
    utl_smtp.close_data(conn);
    utl_smtp.quit(conn);

END;

WorkFlow : ORA-04061: existing state of has been invalidated ORA-04061: existing state of package

RA-04061: existing state of has been invalidated ORA-04061: existing state of package

Compile Package for Error  ORA-04061 in WorkFlow

alter package      Wf_Engine_Util  compile;
alter package      wf_engine       compile;
alter package      wf_notification compile;
alter package      wf_standard     compile;

Print Function for PLSQL Testing

For tracing PLSQL code , Print function  show output in DBMS Output as per Arguments.

1) For implementation of  this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.

  G_Debug_Enabled Boolean default false;
    procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                );

procedure test_Print_function;

2) Add Function Body in Package Specification Body

procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                )

    is

    begin

            if G_Debug_Enabled then

               Dbms_output.put_line('$ '||pString||' : '||pText_1||'  => '||pValue1||' '||pText_2||' => '||pValue2||' '||pText_3||' => '||pValue3||' '||pText_4||' => '||pValue4||' '||pText_5||' => '||pValue5||' '||pText_6||' => '||pValue6||' '||pText_7||' => '||pValue7||' '||pText_8||' => '||pValue8||' '||pText_9||' => '||pValue9);

              end if;

      end;

procedure test_Print_function

  is

  begin

          print('Function Test','TransactionID',10,'EmployeeName','mFaisal');

    end;

3) Now test test_print_function

begin

  -- Call the procedure

  PKG_wf_api.G_Debug_Enabled:=true;

  PKG_wf_api.test_print_function;

end;

Output: 

$ Function Test : TransactionID  => 10 EmployeeName => mFaisal  

first_rule of "The Forty Rules of Love"

#first_rule

"How we see God is a direct reflection of how we see ourselves.
If God brings to mind mostly fear and blame, it means there is too much fear and blame welled inside
us. If we see God as full of love and compassion, so are we.”

InList Function PLSQL

CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab PIPELINED
AS
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN;
END;

SELECT *
                         FROM   emp
                         WHERE  empno IN (SELECT * FROM TABLE(in_list(:empno)))
                         ORDER BY ename
Value of :empno is 7876,7934,7782

Enable / Disable Field in Oracle Forms

Case Study :

There are multiple columns in Oracle tabular form and they are depend on each other as Numeric sequence. User enter cannot enter value in columns if parent  of current item is null or child of current item is not null.

Add procedure in form and enter your your fields name in array as sequence you want .
after this call this code

begin 
when_New_item_instance;
end;

in When-new-item-instance trigger.


PROCEDURE when_New_item_instance IS

  type Items_List_t is varray(3) of Varchar2(200);

  l_Items_List Items_List_t := Items_List_t('BlockName.Item1',
                                            'BlockName.Item2',
                                            'BlockName.Item3');

  l_triggered_Item varchar2(200) default :system.trigger_item;

  l_item_enabled boolean default true;

  l_triggered_item_ind number;

BEGIN

  for ldx in 1 .. l_Items_List.count loop
 
    if l_triggered_Item = l_Items_List(ldx)
   
     then
   
      l_triggered_item_ind := ldx;
   
    end if;
 
  end loop;


if l_triggered_item_ind  is null then
null;
end;
  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind - 1)) is null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind + 1)) is not null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  if l_item_enabled then
 
    app_item_property.set_property(l_triggered_Item, enabled, property_on);
 
  else
 
    app_item_property.set_property(l_triggered_Item, enabled, property_off);
 
  end if;

END;





Note: 
Incase of non-EBS application 
replace 
app_item_property.set_property(l_triggered_Item, enabled, property_on);
with 
set_item_property(l_triggered_Item, enabled, property_on);