oracle-storedproc.sql

CREATE OR REPLACE PACKAGE PKGCOM_SECURITY IS

  -- Author  : 
  -- Created : 2/13/2013 11:45:32 AM
  -- Purpose : 
  
  -- Public function and procedure declarations
  PROCEDURE INSERT_USER_SECURITY(p_ssid IN table_name.ssid%TYPE,
          p_return OUT VARCHAR2);

END PKGCOM_SECURITY;

CREATE OR REPLACE PACKAGE BODY PKGCOM_SECURITY IS
  			 
  PROCEDURE INSERT_USER_SECURITY(p_ssid IN table_name.ssid%TYPE,  
          p_return OUT VARCHAR2) IS
     -- Variable declarations     
     v_insert_flag NUMBER(1) := 1;                    
     
  BEGIN
    IF v_insert_flag = 1 THEN
      INSERT INTO table_name(ssid) VALUES(p_ssid);
      COMMIT;
    END IF;
    p_return := 1;
  EXCEPTION
    WHEN OTHERS THEN
      p_retval := 'PKGCOM_SECURITY.INSERT_USER_SECURITY: ' || SQLCODE || ' SQL Error Msg: ' 
               ||SUBSTR(SQLERRM, 1, 1950)||'<br>'||p_ssid;
    	ROLLBACK;
    	RETURN;
  END INSERT_USER_SECURITY;
  
END PKGCOM_SECURITY;



CREATE OR REPLACE PACKAGE BODY pkgimscore_entity IS
    PROCEDURE search(p_text IN VARCHAR2,
              p_cursor OUT SYS_REFCURSOR) IS
    -- Variable declarations
    v_text VARCHAR2(25);
    BEGIN
         v_text := '%'||p_text||'%';
         OPEN p_cursor FOR
         SELECT *
               FROM abc WHERE LOWER(legal_entity_id) LIKE lower(v_text)
               OR LOWER(legal_entity_name) LIKE lower(v_text)
               OR LOWER(gecc_entity_id) LIKE lower(v_text);
    EXCEPTION
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('pkgimscore_entity.search: ' || SQLCODE || ' SQL Error Msg: '||SUBSTR(SQLERRM, 1, 1950));
         RAISE;
    END search;
END pkgimscore_entity;