Creating a complex stored procedure

Database
Fan

Creating a complex stored procedure

Hi All ,

I have been working on simple stored procedures , but off late I have tried to work on few complex ones.

My Idea is to generate Test cases and logs by just running this procedure alone with out any manual intevention. However , I have tried this doing oracle and was able to do in more effective way.

Below is the snapshot of the SQl used to generate the logs and Test case.

create or replace PROCEDURE SQL_ENGINE(
    SRC_INPUT IN VARCHAR2,
    TGT_INPUT IN VARCHAR2,
    DBLINK    IN VARCHAR2)

--- Declare local variables here---

BEGIN
  dbms_utility.comma_to_table ( LIST => REGEXP_REPLACE(SRC_INPUT,'(^|,)','\1x') , tablen => SRC_T_COUNT , tab => SRC_array );
  dbms_utility.comma_to_table ( LIST => REGEXP_REPLACE(TGT_INPUT,'(^|,)','\1x') , tablen => TGT_T_COUNT , TAB => TGT_array );
  DBMS_UTILITY.exec_ddl_statement ('TRUNCATE TABLE SMART_SQL_ENGINE_ERR');
  DBMS_UTILITY.EXEC_DDL_STATEMENT ('TRUNCATE TABLE SMART_SQL_ENGINE');

  IF (TGT_T_COUNT < SRC_T_COUNT) THEN
    DBMS_OUTPUT.PUT_LINE('LESS NUMBER OF TARGET TABLES ARE ENTERED.  
Provide correct Input.  
Program Exiting.');
    RETURN;
  ELSIF (TGT_T_COUNT > SRC_T_COUNT) THEN
    DBMS_OUTPUT.PUT_LINE('LESS NUMBER OF SOURCE TABLES ARE ENTERED.  
Provide correct Input.  
Program Exiting.');
    RETURN;
  END IF;
  EXECUTE IMMEDIATE 'SELECT count(*) from ALL_DB_LINKS WHERE ''@''||DB_LINK LIKE '''||DBLINK||'%''' INTO DBLINK_CHECK;
  IF DBLINK_CHECK = 0 THEN
  INSERT INTO SMART_SQL_ENGINE_ERR VALUES
        ('db_link',DBLINK
        );
    DBMS_OUTPUT.PUT_LINE('DBLINK '||DBLINK||'entered is INVALID' );
  ELSE
    DBMS_OUTPUT.PUT_LINE('DBLINK '||DBLINK||'entered is VALID' );
  END IF;
  FOR I IN 1 .. SRC_T_COUNT
  LOOP
    SRC := SUBSTR(SRC_ARRAY(I),2);
    TGT := SUBSTR(TGT_ARRAY(I),2);
    EXECUTE immediate 'SELECT COUNT(*) from all_tab_columns where table_name = '''|| SRC ||''' ' INTO SRC_T_CHECK;
    EXECUTE immediate 'SELECT COUNT(*) from all_tab_columns where table_name = '''|| TGT ||''' ' INTO TGT_T_CHECK;
    IF SRC_T_CHECK = 0 THEN
      INSERT INTO SMART_SQL_ENGINE_ERR VALUES
        ('SOURCE',SRC
        );
      DBMS_OUTPUT.PUT_LINE('SOURCE TABLE NAME '||SRC||'entered is INVALID' );
    ELSIF TGT_T_CHECK = 0 THEN
      INSERT INTO SMART_SQL_ENGINE_ERR VALUES
        ('TARGET',TGT
        );
      DBMS_OUTPUT.PUT_LINE('TARGET TABLE NAME '||TGT||'entered is INVALID' );
    END IF;
  END LOOP;
  EXECUTE immediate 'SELECT COUNT(*) FROM SMART_SQL_ENGINE_ERR' INTO ERR_CHECK;
  IF(ERR_CHECK > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Inputs provided are wrong. Kindly check SMART_SQL_ENGINE_ERR table.' );
    RETURN;
  ELSE
    DBMS_OUTPUT.PUT_LINE('All Table Names and Database link entered are valid. Testing can be started. ' );
  END IF;
  FOR I IN 1 .. SRC_T_COUNT
  LOOP
    SRC := SUBSTR
    (
      SRC_ARRAY(I),2
    )
    ;
    TGT := SUBSTR(TGT_ARRAY(I),2);
    DBMS_OUTPUT.PUT_LINE('
---------------------------------------------------------------------------------------------------------------------
TESTING :'||TO_CHAR(I) ||' Executing Test cases for below: ');
    DBMS_OUTPUT.PUT_LINE( ' SRC TABLE ''' ||SUBSTR(SRC_ARRAY(I),2)||''' '|| ' TGT TABLE ''' ||SUBSTR(TGT_ARRAY(I),2)||'''
---------------------------------------------------------------------------------------------------------------------' );
    ---------------------------------------------------------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('
TEST CASE 1: TABLE PROPERTIES VALIDATION ');
    ---------------------------------------------------------------------------------------------------------------------
    COLNAME_SQL := 'SELECT COUNT(*) FROM
(
SELECT column_name,data_type,data_precision,data_scale,char_length,nullable from all_tab_columns where table_name ='''|| SRC ||'''
MINUS
SELECT Column_Name,Data_Type,Data_Precision,Data_Scale,Char_Length,Nullable from all_tab_columns'|| DBLINK ||' where table_name = ''' ||SRC|| '''
)' ;
    EXECUTE IMMEDIATE COLNAME_SQL INTO COLNAME_COUNT;
    IF COLNAME_COUNT = 0 THEN
      TC_RES        := 'Pass';
      DBMS_OUTPUT.PUT_LINE('RESULT: TEST CASE PASSED.' );
    ELSE
      TC_RES := 'Fail';
      DBMS_OUTPUT.PUT_LINE('RESULT: TEST CASE FAILED' );
    END IF;
    ---------------------------------------------------------------------------------------------------------------------
    --UPDATE RESULT TABLE TC_TABLE_PROP_VALIDATION
    ---------------------------------------------------------------------------------------------------------------------
    EXECUTE immediate 'SELECT (COUNT(*)+1) FROM SMART_SQL_ENGINE' INTO tc_id;
    TC_DESC    := 'Table Properties Validation:
Verify that target table column names, datatype is as per source table.
SQL QUERY:
' ||COLNAME_SQL;
    TC_EXP_RES := 'Target table column name , datatype should be as per the source table.
Minus Query should return zero.';
    TC_ACT_RES := 'Query Output:
query has returned '|| COLNAME_COUNT ||' records.';
    UPD_LOG    := 'INSERT INTO SMART_SQL_ENGINE VALUES ('||TC_ID||', '||TC_DESC||', '||TC_EXP_RES||', '||TC_ACT_RES||', '|| TC_RES||' )';
    DBMS_OUTPUT.PUT_LINE(UPD_LOG);
    INSERT
    INTO SMART_SQL_ENGINE VALUES
      (
        TC_ID,
        TC_DESC,
        TC_EXP_RES,
        TC_ACT_RES,
        TC_RES
      );
    ---------------------------------------------------------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('TESTING :'||TO_CHAR(I) ||' TESTING FOR BELOW IS COMPLETED: ');
    DBMS_OUTPUT.PUT_LINE(' SRC TABLE ''' ||SUBSTR(SRC_ARRAY(I),2)||''' '|| ' TGT TABLE ''' ||SUBSTR(TGT_ARRAY(I),2)||'''
---------------------------------------------------------------------------------------------------------------------' );

Below are the tables I will be creating , so that those can be used in the procedure.

CREATE Multiset TABLE "SMART_SQL_ENGINE" 

    "TEST CASE ID" VARCHAR(100),
    "TEST CASES DESCRIPTION" VARCHAR(4000),
    "EXPECTED RESULT" VARCHAR(4000),
    "ACTUAL RESULT" VARCHAR(4000),
    "PASS/FAIL" VARCHAR(20)
)
;
CREATE multiset  TABLE SMART_SQL_ENGINE_ERR
(
    "TABLE TYPE" VARCHAR(100),
    "TABLE NAME" VARCHAR(100)
)
;

Please let me know if this helps.

1 REPLY
Fan

Re: Creating a complex stored procedure

Hi all ,

This will hold true only for when both Src and Tgts are 1:1 loads