Export metadata only using expdp.

expdp system/password schemas=SCHEMA_NAME directory=DMPDIR dumpfile=file_name.dmp logfile=explogfile.log content=METADATA_ONLY

Human Resource Management System (C Programming Project)

Overview: The followings feature has been developed using C programming Language.

1. Add New Employee
2. List of Employees
3. Employee Attendance
4. Employee Payment
5. Employee Payment List
6. Employee Search
7. Exit


#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include <string.h>  
#include <ctype.h>

COORD coord = {0,0}; /// top-left corner of window

void gotoxy(int x,int y)
{
    coord.X = x;
    coord.Y = y;
    SetConsoleCursorPosition(GetStdHandle(STD_OUTPUT_HANDLE),coord);
}

int IsNumberValue(char *s)
{
    //return value : true if the string is all numbers.
    while(*s)
        if(!isdigit(*s++))
            return 0;
    return 1;
}

int IsCharacterValue(char *s)
{
    //return value : true if the string is all numbers. if (*c <= ' ' || *c >= 127) return 0;

    while(*s)
        if(!isalpha(*s++))
            return 0;
    return 1;
}

int AddressCheck(char *array)
{
    int j,x;
    char val;
    int val_flag=0;

    j=strlen(array);
    for (x=0; x<j; x++)
    {
        val=array[x];
        {
            if (isalpha(val) ||(val)==' ' || (val)=='#' || (val)=='_') // check every character of str
            {
                val_flag=0;
            }
            else
            {
                val_flag=1;
                return 1;
            }
        }
    }
    return 0;
}

int EmailCheck(char *array)
{
    int j,x;
    char val;

    j=strlen(array);
    for (x=0; x<j; x++)
    {
        val=array[x];
        if (val=='@')
            return 1;
    }
    return 0;
}

/** Main function started */

int main()
{
    FILE *file_employee;
    FILE *file_attendance;
    FILE *file_experience;
    FILE *file_education;
    FILE *file_payment;

    char another, choice;

    /** structure that represent a employee */
    struct employee
    {
        char employee_name[100];
        char employee_city[100];
        char employee_mob_no[11];
        char employee_email[100];
        char employee_address[100];
        float basic_salary;
    };

    struct employee emp_temp; /// structure variable creation

    struct employee_attendance
    {
        int employee_id;
        int attend_month;
        int attend_year;
        int total_office;
        int total_attendance;
        int total_absent;
    };

    struct employee_attendance employee_attendance_temp;

    struct employee_experience
    {
        char company_name[200];
        char company_location[200];
        char position[200];
        char responsibilities[1000];
        char start_date[200];
        char end_date[200];
    };

    struct employee_experience employee_experience_temp;

    struct employee_education
    {
        char certification[200];
        char institute[200];
        char pass_year[200];
        char result[200];
    };

    struct employee_education employee_education_temp;

    struct employee_payment
    {
        int employee_id;
        int pay_month;
        int pay_year;
        char pay_date[200];;
        int total_payment;
    };

    struct employee_payment employee_payment_temp;

    char file_data[200][200];
    char single_line[200];
    char search_str[200];
    int match_flag=0;

    void emp_experience()
    {
        char another_exp='y';
        while (another_exp=='y'||another_exp=='Y')
        {
            system("cls");
            gotoxy(30,1);
            printf("\n Employment History ");
            file_experience = fopen("file_experience.txt", "a");
            fseek(file_experience,0,SEEK_END);

Position11:
            printf("\nCompany Name : ");
            gets(employee_experience_temp.company_name);

            if(!IsCharacterValue(employee_experience_temp.company_name))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position11;
            }

Position12:
            printf("\nCompany Location : ");
            gets(employee_experience_temp.company_location);

            if(!IsCharacterValue(employee_experience_temp.company_location))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position12;
            }

Position13:
            printf("\nPosition Held : ");
            gets(employee_experience_temp.position);

            if(!IsCharacterValue(employee_experience_temp.position))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position13;
            }
            printf("\nResponsibilities : ");
            gets(employee_experience_temp.responsibilities);

            printf("\nFrom Date : ");
            gets(employee_experience_temp.start_date);
            printf("\nTo Date : ");
            gets(employee_experience_temp.end_date);
            fprintf(file_experience, "%s,%s,%s,%s,%s,%s\n", employee_experience_temp.company_name,
                    employee_experience_temp.company_location,
                    employee_experience_temp.position,employee_experience_temp.responsibilities,
                    employee_experience_temp.start_date,employee_experience_temp.end_date
                   ); // write to file
            fclose(file_experience);
            printf("\nAdd Another Employment History (y/n) ");
            another_exp=getche();
        }
    }

    void emp_education()
    {
        char another_education='y';
        while (another_education=='y'||another_education=='Y')
        {
            system("cls");
            gotoxy(30,1);
            printf("\n Academic Qualification ");
            file_education = fopen("file_education.txt", "a");
            fseek(file_education,0,SEEK_END);
            printf("\nDegree Title : ");
            gets(employee_education_temp.certification);
            printf("\nInstitute Name : ");
            gets(employee_education_temp.institute);

Position21:
            printf("\nYear of Passing : ");
            gets(employee_education_temp.pass_year);

            if(!IsNumberValue(employee_education_temp.pass_year))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position21;
            }


            printf("\nResult : ");
            gets(employee_education_temp.result);
            fprintf(file_education, "%s,%s,%s,%s\n", employee_education_temp.certification,employee_education_temp.institute,employee_education_temp.pass_year,employee_education_temp.result
                   );
            fclose(file_education);
            printf("\nAdd Another Academic Qualification (y/n) ");
            another_education=getche();
        }
        printf("\nAdd Employment History (y/n) ");
        another = getche();
        if (another=='y' || another=='Y')
        {
            emp_experience();
        }
    }


    int employee_id_gen()
    {
        FILE * file_empid;
        int employee_id;

        file_empid=fopen("employee_id.txt", "r");
        if (!file_empid)
        {
            fclose(file_empid);
            file_empid=fopen("employee_id.txt", "w");
            fprintf(file_empid,"100");
            employee_id=100;
        }
        else
        {
            fscanf(file_empid, "%d", &employee_id);
            employee_id ++ ;
            fclose(file_empid);
            file_empid=fopen("employee_id.txt", "w");
            printf("%d", employee_id);
            fprintf(file_empid,"%d", employee_id);
        }
        fclose(file_empid);
        return employee_id ;
    }


    void employee_add()
    {
        int new_emp_id=100 ;
        new_emp_id = employee_id_gen();
        system("cls");
        file_employee = fopen("file_employee.txt", "a");  /// Open the using append mode to add employee information with existing
        /// information.
        fseek(file_employee,0,SEEK_END); /// search the file and move cursor to end of the file
        gotoxy(30,1);
        printf("Employee Details Information \n");
        another = 'n';

Position1:
        printf("\nEmployee name: ");
        gets(emp_temp.employee_name);

        if(!IsCharacterValue(emp_temp.employee_name))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position1;
        }

Position2:
        printf("\nCity: ");
        gets(emp_temp.employee_city);

        if(!IsCharacterValue(emp_temp.employee_city))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position2;
        }


Position3:
        printf("\nMobile Number: ");
        gets(emp_temp.employee_mob_no);

        if(!IsNumberValue(emp_temp.employee_mob_no))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position3;
        }

Position4:
        printf("\nEmail: ");
        gets(emp_temp.employee_email);

        if(!EmailCheck(emp_temp.employee_email))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position4;
        }

Position5:
        printf("\nAddress: ");
        gets(emp_temp.employee_address);

        if(AddressCheck(emp_temp.employee_address))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position5;
        }

        printf("\nEmployee Salary: ");
        scanf("%f", &emp_temp.basic_salary);

        fprintf(file_employee, "%d,%s,%s,%s,%s,%s,%f\n",new_emp_id,emp_temp.employee_name,emp_temp.employee_city,emp_temp.employee_mob_no,emp_temp.employee_email,emp_temp.employee_address,emp_temp.basic_salary);
        fclose(file_employee);
        printf("\nEmployee Added With ID %d \n",new_emp_id);
        printf("\nAdd Academic Qualification (y/n) ");
        another = getche();
        if (another=='y' || another=='Y')
        {
            emp_education();
        }
    }

    void employee_list()
    {
        system("cls");
        int c;
        FILE *file_employee;
        file_employee = fopen("file_employee.txt", "r");
        if (file_employee)
        {
            while ((c = getc(file_employee)) != EOF)
                putchar(c);
            fclose(file_employee);
        }
        getch();
    }

    void employee_search()
    {
        system("cls");
        int c;
        FILE *file_employee;
        file_employee = fopen("file_employee.txt", "r");
        if (file_employee)
        {
            while ((c = getc(file_employee)) != EOF)
                putchar(c);
            fclose(file_employee);
        }
        getch();
    }

    void emp_attendance()
    {
        char another_attendance='y';
        while (another_attendance=='y'||another_attendance=='Y')
        {
            system("cls");
            file_attendance = fopen("file_attendance.txt", "a");
            fseek(file_attendance,0,SEEK_END);
            int total_absent=0;
            printf("\nEmployee ID : ");
            scanf("%i",&employee_attendance_temp.employee_id);
            printf("\nMonth : ");
            scanf("%i",&employee_attendance_temp.attend_month);
            printf("\nYear : ");
            scanf("%i",&employee_attendance_temp.attend_year);
            printf("\nTotal Office: ");
            scanf("%i", &employee_attendance_temp.total_office);
            printf("\nTotal Attend : ");
            scanf("%i", &employee_attendance_temp.total_attendance);
            total_absent=employee_attendance_temp.total_office-employee_attendance_temp.total_attendance;
            printf("\nTotal Absent :%i ",total_absent);
            fprintf(file_attendance, "%i,%i,%i,%i,%i,%i\n", employee_attendance_temp.employee_id,
                    employee_attendance_temp.attend_month,
                    employee_attendance_temp.attend_year,
                    employee_attendance_temp.total_office,
                    employee_attendance_temp.total_attendance,
                    total_absent
                   ); // write to file
            fclose(file_attendance);
            printf("\nAdd Another Attendance (y/n) ");
            another_attendance=getche();
        }
    }

    void emp_payment()
    {
        char another_payment='y';
        while (another_payment=='y'||another_payment=='Y')
        {
            system("cls");
            file_payment = fopen("file_payment.txt", "a");
            fseek(file_payment,0,SEEK_END);
            int total_absent=0;
            printf("\nEmployee ID : ");
            scanf("%i",&employee_payment_temp.employee_id);
            printf("\nMonth : ");
            scanf("%i",&employee_payment_temp.pay_month);
            printf("\nYear : ");
            scanf("%i",&employee_payment_temp.pay_year);
            printf("\nPayment Date : ");
            scanf("%s", &employee_payment_temp.pay_date);
            printf("\nTotal Payment : ");
            scanf("%i", &employee_payment_temp.total_payment);

            fprintf(file_payment, "%i,%i,%i,%s,%i\n", employee_payment_temp.employee_id,
                    employee_payment_temp.pay_month,
                    employee_payment_temp.pay_year,
                    employee_payment_temp.pay_date,
                    employee_payment_temp.total_payment
                   );
            fclose(file_payment);
            printf("\nAdd Another Payment (y/n) ");
            another_payment=getche();
        }
    }

    void payment_list()
    {

        system("cls");
        int line;
        FILE *file_payment;
        file_payment = fopen("file_payment.txt", "r");
        if (file_payment)
        {
            while ((line = getc(file_payment)) != EOF)  /// Print the information untile End of File.
                putchar(line);
            fclose(file_payment);
        }
        getch();
    }

    int string_comparing(char *array)
    {
        int i,j,array_length=0,total_str=0;
        int search_str_length=0;
        char val;

        array_length=strlen(array);
        search_str_length=strlen(search_str);
        match_flag=0;

        for (i=0; i<array_length; i++)
        {
            val=array[i];
            total_str=total_str+1;
            if (match_flag==1)
            {
                break;
            }
            if (val==',')
            {
                for(j=0; j<search_str_length; j++)
                {
                    if (array[(i-(total_str-1)+j)]==search_str[j])
                    {
                        match_flag=1;
                    }
                    else
                    {
                        match_flag=0;
                    }
                }
                total_str=0;
            }
        }
        return 0;
    }

    void employee_searching()
    {
        system("cls");
        int i=0,n=0;
        match_flag=0;
        int total_result=0;
        FILE *file;
        printf("\nEnter Search String : ");
        gets(search_str);
        file = fopen("file_employee.txt", "r");
        while(fgets(file_data[n++],200,file))
        {
        }
        fclose(file);

        for (i=0 ; i<n; i++)
        {
            strcpy(single_line,file_data[i]);
            string_comparing(single_line);
            if (match_flag==1)
            {
                printf("%s",single_line);
                ++total_result;
            }
        }

        if (total_result==0)
        {
            printf("Result Not Found.");
        }

        getch();
    }

    system("cls"); ///clear the console window
    gotoxy(30,1);
    printf("Please press enter to start the project");
    getch();  /// Waiting for pressing enter.
    /// infinite loop continues untile the break statement encounter
    while(1)
    {
        system("cls"); ///clear the console window
        gotoxy(30,1);
        printf("1. Add New Employee");
        gotoxy(30,2);
        printf("2. List of Employees");
        gotoxy(30,3);
        printf("3. Employee Attendance");
        gotoxy(30,4);
        printf("4. Employee Payment");
        gotoxy(30,5);
        printf("5. Employee Payment List");
        gotoxy(30,6);
        printf("6. Employee Search");
        gotoxy(30,7);
        printf("7. Exit");
        gotoxy(30,9);
        printf("Your Choice: ");
        fflush(stdin); /// flush the input buffer
        choice  = getche(); /// get the input from keyboard
        switch(choice)
        {
        case '1':
            employee_add();
            break;
        case '2':
            employee_list();
            break;
        case '3':
            emp_attendance();
            break;
        case '4':
            emp_payment();
            break;
        case '5':
            payment_list();
            break;
        case '6':
            employee_searching();
            break;
        case '7':
            exit(0); /// exit from the program
        }
    }
    return 0;
}

Creating Unique Index By Using Table Name and Column Serial Number.

1. Create procedure for generating scripts and creating Unique Constraints.

CREATE OR REPLACE PROCEDURE SP_TABLE_CONSTRAINT
(P_TABLE_NAME IN VARCHAR2,
 P_COLUMN_SL IN VARCHAR2
 )
 IS
 V_SQL_SCRIPTS VARCHAR2(4000);
 V_COLUMN_LIST VARCHAR2(1000);
 BEGIN
    SELECT TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLUMN_LIST
    INTO V_COLUMN_LIST
    FROM USER_TAB_COLUMNS C, (SELECT REGEXP_SUBSTR(P_COLUMN_SL,'[^,]+', 1, LEVEL) COLUMN_SL FROM DUAL
     CONNECT BY REGEXP_SUBSTR(P_COLUMN_SL, '[^,]+', 1, LEVEL) IS NOT NULL) S
    WHERE TABLE_NAME=P_TABLE_NAME
    AND C.COLUMN_ID=S.COLUMN_SL;
   
    V_SQL_SCRIPTS:='CREATE UNIQUE INDEX IDX_'||P_TABLE_NAME||'_UNQ_'||REPLACE(P_COLUMN_SL,',')||' ON '||P_TABLE_NAME||'('||V_COLUMN_LIST||')';
   
    EXECUTE IMMEDIATE V_SQL_SCRIPTS;
   
    DBMS_OUTPUT.PUT_LINE(V_SQL_SCRIPTS);
 
  EXCEPTION
 
     WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20100,SQLERRM);
   
 END;

2. Call Procedure for Creating Unique Constraints.

DECLARE
  P_TABLE_NAME VARCHAR2(32767);
  P_COLUMN_SL VARCHAR2(32767);

BEGIN
  P_TABLE_NAME := 'CONNECTIONS';
  P_COLUMN_SL := '1,2';

  SP_TABLE_CONSTRAINT ( P_TABLE_NAME, P_COLUMN_SL );

END;

ORA-00245: control file backup failed; target is likely on a local file system

Cause: From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue. For non-RAC database, this doesn't change anything. But for RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances. 

Solution: The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile. So placed controlfile into the Shared Disk.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on CH3 channel at 04/05/2017 01:54:55
ORA-00245: control file backup failed; target is likely on a local file system

RMAN>

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 5 11:32:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ERPDB (DBID=2929713817)

RMAN> show snapshot controlfile name;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name erpdb are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_erpdb1.f'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';

new RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';

new RMAN configuration parameters are successfully stored

RMAN> show snapshot controlfile name;

RMAN configuration parameters for database with db_unique_name erpdb are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';

RMAN> exit

Recovery Manager complete.

bash-4.3$