Oracle procedure create

PRC_BUILD_PK

PRC_CREATESEQUENCE

**********************************
**********************************
1st step
==========================================
CREATE OR REPLACE PROCEDURE RAJUK_MONEY.prc_build_pk (
   p_schemaname     IN       VARCHAR2,
   p_sequencename   IN       VARCHAR2,
   p_prefix         IN       VARCHAR2,
   p_company_no     IN       VARCHAR2,
   p_pk             OUT      VARCHAR2,
   p_yrmnflag       IN       VARCHAR2 DEFAULT 'N'
)
--********************
-- updated by - zakir,STO,ATIL on 11-sep-2011
--fixing length segment , dynamic length . dynamic sequence creation to call another procedure.
--********************
AUTHID CURRENT_USER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   schema_exist      NUMBER;
   tab_exist         NUMBER;
   column_exist      NUMBER;
   sequence_exist    NUMBER;
   sql_stmt          VARCHAR2 (32000);
   seq_number        NUMBER;
   seq_name_length   NUMBER (30);
   v_company_no      VARCHAR2 (16);
   v_seqlength       INT;
BEGIN
   IF p_yrmnflag = 'Y'
   THEN
--13=maxlength
--4=YYMM
--2=Company_no length
-->13-4-2=7
      v_seqlength := 7 - nvl(LENGTH (p_prefix),0);
   ELSE
--13=maxlength
--2=Company_no length
-->13-2=11
      v_seqlength := 11 - nvl(LENGTH (p_prefix),0);
   END IF;
  
--DBMS_OUTPUT.put_line ('v_seqlength'||v_seqlength);

   SELECT LENGTH (p_sequencename)
     INTO seq_name_length
     FROM DUAL;

   IF seq_name_length > 30
   THEN
      DBMS_OUTPUT.put_line
         ('Length of Sequence-Name exceeds convention. Please provide a name between 1 and 30 in length.'
         );
   ELSE
      SELECT COUNT (object_name)
        INTO sequence_exist
        FROM user_objects
       WHERE                            --upper(owner)=upper(p_schemaname) and
             UPPER (object_type) = 'SEQUENCE'
         AND UPPER (object_name) = UPPER (p_sequencename);

      IF sequence_exist > 0
      THEN
         NULL;                              -- no need to create the sequence
      ELSE
         --   IF p_yrmnflag = 'Y'
          --  THEN
               -- create a new sequence with the name of the variable
         prc_createsequence (p_sequencename,
                             1,
                             1,
                             1,
                             TO_NUMBER (LPAD (9, v_seqlength, 9)),
                             'NOCACHE',
                             'CYCLE',
                             v_seqlength
                            );
/*
            sql_stmt :=
                  'CREATE SEQUENCE '
               || p_sequencename
               || ' START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999 NOCACHE CYCLE ORDER';

  */     --ELSE-- create a new sequence with the name of the variable
           -- sql_stmt :=--      'CREATE SEQUENCE '
             --  || p_sequencename-- || ' START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE ORDER';
         --END IF;
--         EXECUTE IMMEDIATE sql_stmt;
         --sql_stmt := '';
      END IF;

      -- now select the next value of the sequence
      sql_stmt := 'select ' || p_sequencename || '.NEXTVAL FROM DUAL';

      EXECUTE IMMEDIATE sql_stmt
                   INTO seq_number;

      IF p_yrmnflag = 'N'
      THEN
         p_pk :=
               p_prefix
            || p_company_no
            || LPAD (TO_CHAR (seq_number), v_seqlength, '0');
      ELSIF p_yrmnflag = 'Y'
      THEN
         IF LENGTH (p_company_no) = 1
         THEN
            v_company_no := '0' || p_company_no;
         ELSE
            v_company_no := p_company_no;
         END IF;

         p_pk :=
               p_prefix
            || v_company_no
            || TO_CHAR (SYSDATE, 'RRMM')
            || LPAD (TO_CHAR (seq_number), v_seqlength, '0');
      END IF;
   END IF;
END;
/


2nd step :
============================================
CREATE OR REPLACE PROCEDURE RAJUK_MONEY.prc_createsequence (
   p_sequencename   IN   VARCHAR2,
   p_seq_start number,
   p_seq_increment number,
   p_seq_MINVALUE number,
   p_seq_MaxVALUE number,
   p_seq_cache varchar2,
   p_seq_cycle varchar2,
   p_length         IN   NUMBER
)
AUTHID CURRENT_USER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   schema_exist     NUMBER;
   sequence_exist   NUMBER;
   sql_stmt         VARCHAR2 (32000);
   v_maxvalue       VARCHAR (30);
BEGIN
   BEGIN
      SELECT COUNT (object_name)
        INTO sequence_exist
        FROM user_objects
       WHERE UPPER (object_type) = 'SEQUENCE'
         AND UPPER (object_name) = UPPER (p_sequencename);
   END;

   BEGIN
      IF sequence_exist > 0
      THEN
         sql_stmt := 'drop SEQUENCE ' || p_sequencename;

         EXECUTE IMMEDIATE sql_stmt;
      END IF;
   END;

   BEGIN
      v_maxvalue := p_seq_MaxVALUE;--LPAD (9, p_length, 9);
     
      sql_stmt :=
            'CREATE SEQUENCE '
         || p_sequencename || ' START WITH ' ||p_seq_start||' INCREMENT BY '||p_seq_increment||' MINVALUE '||p_seq_MINVALUE||' MAXVALUE '
         || v_maxvalue
         || ' '||p_seq_cache||' '|| p_seq_cycle ||' ORDER';

     
      dbms_output.PUT_LINE(sql_stmt);
     
      EXECUTE IMMEDIATE sql_stmt;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/