One step ahead

SMB file sharing between Mac OS Catalina to Ubuntu Mantic 23.10

noreply@blogger.com (Subhroneel) — Sat, 23 Mar 2024 12:45:00 +0000

Connect to SQL Server Database 2019 using Oracle SQL Developer

noreply@blogger.com (Subhroneel) — Sat, 04 Nov 2023 16:56:00 +0000

Migrate Data from SQL Server Database 2019 to Oracle Database 21c using Oracle SQL Developer

noreply@blogger.com (Subhroneel) — Sat, 04 Nov 2023 16:56:00 +0000

FTP file sharing from Mac OS Catalina to Ubuntu 22.04

noreply@blogger.com (Subhroneel) — Sat, 04 Nov 2023 16:54:00 +0000

Install MacOS Catalina 10.5 On Virtual box 6 1 Ubuntu 22 04

noreply@blogger.com (Subhroneel) — Sat, 04 Nov 2023 16:53:00 +0000

Using Oracle DML Database Trigger For Auditing Purpose

noreply@blogger.com (Subhroneel) — Mon, 28 Nov 2022 11:32:00 +0000

Auditing Table means keeping monitoring of all the dml activities occurred on a specific table of the database like Insert, Update or Delete single row row at a time from the table and when. It is like tracking the users activity who are messing with your data.

Monitoring user activity for missing, manipulating records is important part for a business. In this video I tried to show how to use trigger on a table to get information about the client machine, ip address etc. All this information will be stored in a table which can be used in presentation to analyses any manipulated data.
Please download the following script from the below link

https://drive.google.com/file/d/1fH79eeLUd-Xcz42qSjsAfK8wYfgtE1y9/view?usp=sharing

Extract data from all tables of all schema into csv file using UTL_DIR

noreply@blogger.com (Subhroneel) — Tue, 14 Jan 2020 10:44:00 +0000

I was working on a metadata comparison project using UTL_DIR to write to file on database server As having knowledge on data dictionary I have manipulated dba_* tables to get information and extract it to csv files. I am providing below the list of database metadata object I used to get the information.

1.   DBA_USERS
2.   DBA_TABLES
3.   DBA_TAB_PRIVS
4.   DBA_ROLE_PRIVS
5.   DBA_TAB_COLUMNS
6.   DBA_INDEXES
7.   DBA_PART_TABLES
8.   DBA_VIEWS
9.   DBA_MVIEWS
10. DBA_SOURCE
11. DBA_SEQUENCES
12. DBA_SYNONYMS
13. DBA_LOBS
14. DBA_SCHEDULER_JOBS

For UTL_FILE there are 2 output handler, one for extracting the metadata and another to log the activity.

This extractor will extract all schema objects metadata which is ORACLE_MAINTAINED='N' (Applicable in 12c only).

The script in given below This script will run on any Oracle 12c environment logged in as SYSTEM or SYS users.

---------------------------------------------------------------------------------------------------------------------------------

set serveroutput on;
declare
refcur_cur SYS_REFCURSOR;
type view_record_name_t is RECORD
(
OWNER dba_VIEWS.OWNER%type,
VIEW_NAME dba_VIEWS.VIEW_NAME%type,
TEXT_LENGTH dba_VIEWS.TEXT_LENGTH%type
);
all_users_record_name dba_users%rowtype;
view_record_name view_record_name_t;
table_record_name dba_tables%rowtype;
--column_record_name column_record_name_t;
column_record_name dba_tab_columns%rowtype;
index_record_name dba_indexes%rowtype;
partition_record_name dba_part_tables%rowtype;
mview_record_name dba_mviews%rowtype;
source_record_name dba_source%rowtype;
sequence_record_name dba_sequences%rowtype;
synonym_record_name dba_synonyms%rowtype;
lobs_record_name dba_lobs%rowtype;
jobs_record_name dba_scheduler_jobs%rowtype;
tab_privs_record_name dba_tab_privs%rowtype;
role_privs_record_name dba_role_privs%rowtype;
users_record_name dba_users%rowtype;
fhandle UTL_FILE.FILE_TYPE;
fhandle_err UTL_FILE.FILE_TYPE;
uname user_users.username%type;
systimes varchar2(15);
rowconut number;
serverHostName varchar2(100);
serverInstanceName varchar2(100);
begin

begin
select max(username) into uname from user_users;
EXCEPTION
when OTHERS then
     uname := 'dummy';
end;

serverHostName:= sys_context('userenv','server_host');
serverInstanceName:= sys_context('userenv','instance_name') ;

fhandle_err := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );

fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_users_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );

open refcur_cur for select * FROM DBA_USERS WHERE ORACLE_MAINTAINED='N' order by USERNAME;
utl_file.put_line(fhandle_err,'Start extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'USERNAME~USER_ID~ACCOUNT_STATUS~LOCK_DATE~EXPIRY_DATE');
        loop
            fetch refcur_cur into users_record_name;
            utl_file.put_line(fhandle,users_record_name.USERNAME||'~'||users_record_name.USER_ID||'~'||users_record_name.ACCOUNT_STATUS||'~'||users_record_name.LOCK_DATE||'~'||users_record_name.EXPIRY_DATE);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_tab_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );

open refcur_cur for select * from dba_tab_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,5;
utl_file.put_line(fhandle_err,'Start extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'GRANTEE~OWNER~TABLE_NAME~GRANTOR~PRIVILEGE~GRANTABLE~HIERARCHY');
        loop
            fetch refcur_cur into tab_privs_record_name;
            utl_file.put_line(fhandle,tab_privs_record_name.GRANTEE||'~'||tab_privs_record_name.OWNER||'~'||tab_privs_record_name.TABLE_NAME||'~'||tab_privs_record_name.GRANTOR||'~'||tab_privs_record_name.PRIVILEGE||'~'||tab_privs_record_name.GRANTABLE||'~'||tab_privs_record_name.HIERARCHY);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_role_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );

open refcur_cur for select * from dba_role_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,4;
utl_file.put_line(fhandle_err,'Start extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'GRANTEE~GRANTED_ROLE~ADMIN_OPTION~DEFAULT_ROLE'
                         ) ;
        loop
            fetch refcur_cur into role_privs_record_name;
            utl_file.put_line(fhandle,role_privs_record_name.GRANTEE||'~'||role_privs_record_name.GRANTED_ROLE||'~'||role_privs_record_name.ADMIN_OPTION||'~'||role_privs_record_name.DEFAULT_ROLE);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location => 'UTL_DIR'     -- File location
            ,filename=> uname||'_tables_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );

open refcur_cur for select * from dba_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;
utl_file.put_line(fhandle_err,'Start extracting tables....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~TABLE_NAME~TABLESPACE_NAME~STATUS~NUM_ROWS');
        loop
            fetch refcur_cur into table_record_name;
            begin
                execute immediate 'select count(*) from '||table_record_name.OWNER||'.'||table_record_name.TABLE_NAME into rowconut;
                EXCEPTION
                    when others then
                    rowconut:=0;
            end;
            utl_file.put_line(fhandle,table_record_name.OWNER||'~'||table_record_name.TABLE_NAME||'~'||table_record_name.TABLESPACE_NAME||'~'||table_record_name.STATUS||'~'||rowconut);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting tables from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location => 'UTL_DIR'     -- File location
            ,filename=> uname||'_tab_columns_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );
--nvl(OWNER,' ') OWNER,nvl(TABLE_NAME,' ') TABLE_NAME,nvl(COLUMN_NAME,' ') COLUMN_NAME,nvl(DATA_TYPE,' ') DATA_TYPE,nvl(DATA_LENGTH,0) DATA_LENGTH,nvl(DATA_PRECISION,0) DATA_PRECISION,nvl(DATA_SCALE,0) DATA_SCALE,nvl(NULLABLE,' ') NULLABLE,nvl(COLUMN_ID,0) COLUMN_ID
open refcur_cur for select * from dba_tab_columns where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by table_name,column_id;
utl_file.put_line(fhandle_err,'Start extracting columns....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~TABLE_NAME~COLUMN_NAME~DATA_TYPE~DATA_LENGTH~DATA_PRECISION~DATA_SCALE~NULLABLE~COLUMN_ID'
                         );
        loop
            fetch refcur_cur into column_record_name;
            utl_file.put_line(fhandle,column_record_name.OWNER||'~'||column_record_name.TABLE_NAME||'~'||column_record_name.COLUMN_NAME||'~'||column_record_name.DATA_TYPE||'~'||column_record_name.DATA_LENGTH||'~'||column_record_name.DATA_PRECISION||'~'||column_record_name.DATA_SCALE||'~'||column_record_name.NULLABLE||'~'||column_record_name.COLUMN_ID);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting columns from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--elsif i.object_type='INDEX' then
        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_indexes_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',
                    max_linesize => 32767 -- Open mode: w = write.
                        );
        open refcur_cur for
            select * from dba_indexes where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TABLE_NAME,INDEX_NAME;
utl_file.put_line(fhandle_err,'Start extracting indexes....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~INDEX_NAME~INDEX_TYPE~TABLE_OWNER~TABLE_NAME~TABLE_TYPE~UNIQUENESS~COMPRESSION~TABLESPACE_NAME~DISTINCT_KEYS~STATUS~NUM_ROWS'
                         );
        loop
            fetch refcur_cur into index_record_name;
            utl_file.put_line(fhandle, index_record_name.OWNER||'~'||index_record_name.INDEX_NAME||'~'||index_record_name.INDEX_TYPE||'~'||index_record_name.TABLE_OWNER||'~'||index_record_name.TABLE_NAME||'~'||index_record_name.TABLE_TYPE||'~'||index_record_name.UNIQUENESS||'~'||index_record_name.COMPRESSION||'~'||index_record_name.TABLESPACE_NAME||'~'||index_record_name.DISTINCT_KEYS||'~'||index_record_name.STATUS||'~'||index_record_name.NUM_ROWS);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting indexes from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='TABLE PARTITION' then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_table_partitions_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_part_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;

utl_file.put_line(fhandle_err,'Start extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~TABLE_NAME~PARTITIONING_TYPE~SUBPARTITIONING_TYPE~PARTITION_COUNT~PARTITIONING_KEY_COUNT~STATUS~DEF_TABLESPACE_NAME'
                         );
        loop
            fetch refcur_cur into partition_record_name;
            utl_file.put_line(fhandle,partition_record_name.OWNER||'~'||partition_record_name.TABLE_NAME||'~'||partition_record_name.SUBPARTITIONING_TYPE||'~'||partition_record_name.PARTITION_COUNT||'~'||partition_record_name.PARTITIONING_KEY_COUNT||'~'||partition_record_name.STATUS||'~'||partition_record_name.DEF_TABLESPACE_NAME);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='VIEW' then
        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_views_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select nvl(OWNER,' ') OWNER,nvl(VIEW_NAME,' ') VIEW_NAME,nvl(TEXT_LENGTH,0) TEXT_LENGTH from dba_views where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by view_name;

utl_file.put_line(fhandle_err,'Start extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~VIEW_NAME~TEXT_LENGTH');--,TEXT');
        loop
            BEGIN
                fetch refcur_cur into view_record_name;
                utl_file.put_line(fhandle,view_record_name.OWNER||'~'||view_record_name.VIEW_NAME||'~'||view_record_name.TEXT_LENGTH);--||'~'||view_record_name.TEXT);
                EXCEPTION
                    when OTHERS then
                    exit;
            END;
            exit when refcur_cur%notfound;
    -- Open mode: w = write.
    end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='MATERIALIZED VIEW' then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_mviews_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_mviews where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~MVIEW_NAME~CONTAINER_NAME~QUERY~QUERY_LEN');
        loop
            fetch refcur_cur into mview_record_name;
            utl_file.put_line(fhandle, mview_record_name.OWNER||'~'||mview_record_name.MVIEW_NAME||'~'||mview_record_name.CONTAINER_NAME||'~'||mview_record_name.QUERY||'~'||mview_record_name.QUERY_LEN);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    elsif (i.object_type='FUNCTION' or i.object_type='TYPE' or i.object_type='PACKAGE BODY' or i.object_type='PACKAGE' or i.object_type='TRIGGER') then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_types_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_source where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TYPE,NAME,LINE;
utl_file.put_line(fhandle_err,'Start extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~NAME~TYPE~LINE~TEXT'
                         );
        loop
            fetch refcur_cur into source_record_name;
            utl_file.put_line(fhandle,source_record_name.OWNER||'~'||source_record_name.NAME||'~'||source_record_name.TYPE||'~'||source_record_name.LINE||'~'||source_record_name.TEXT);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SEQUENCE' then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_sequences_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_sequences where sequence_owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'SEQUENCE_OWNER~SEQUENCE_NAME~MIN_VALUE~MAX_VALUE~INCREMENT_BY~CYCLE_FLAG~ORDER_FLAG~CACHE_SIZE~LAST_NUMBER');
        loop
            fetch refcur_cur into sequence_record_name;
            utl_file.put_line(fhandle,sequence_record_name.SEQUENCE_OWNER||'~'||sequence_record_name.SEQUENCE_NAME||'~'||sequence_record_name.MIN_VALUE||'~'||sequence_record_name.MAX_VALUE||'~'||sequence_record_name.INCREMENT_BY||'~'||sequence_record_name.CYCLE_FLAG||'~'||sequence_record_name.ORDER_FLAG||'~'||sequence_record_name.CACHE_SIZE||'~'||sequence_record_name.LAST_NUMBER);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SYNONYM' then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_synonyms_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_synonyms where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~SYNONYM_NAME~TABLE_OWNER~TABLE_NAME~DB_LINK'
                         );
        loop
            fetch refcur_cur into synonym_record_name;
            utl_file.put_line(fhandle,synonym_record_name.OWNER||'~'||synonym_record_name.SYNONYM_NAME||'~'||synonym_record_name.TABLE_OWNER||'~'||synonym_record_name.TABLE_NAME||'~'||synonym_record_name.DB_LINK);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='INDEX PARTITION' then
    --elsif i.object_type='LOB' then

        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_lobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_lobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~TABLE_NAME~COLUMN_NAME~SEGMENT_NAME~TABLESPACE_NAME~INDEX_NAME~CHUNK~ENCRYPT~FORMAT'
                         );
        loop
            fetch refcur_cur into lobs_record_name;
            utl_file.put_line(fhandle,lobs_record_name.OWNER||'~'||lobs_record_name.TABLE_NAME||'~'||lobs_record_name.COLUMN_NAME||'~'||lobs_record_name.SEGMENT_NAME||'~'||lobs_record_name.TABLESPACE_NAME||'~'||lobs_record_name.INDEX_NAME||'~'||lobs_record_name.CHUNK||'~'||lobs_record_name.ENCRYPT||'~'||lobs_record_name.FORMAT);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='JOB' then
        fhandle := utl_file.fopen(
                    location => 'UTL_DIR'     -- File location
                    ,filename=> uname||'_jobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open refcur_cur for
            select * from dba_scheduler_jobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle, 'OWNER~JOB_NAME~JOB_STYLE~JOB_CREATOR~JOB_TYPE~JOB_ACTION~NUMBER_OF_ARGUMENTS~SCHEDULE_OWNER~SCHEDULE_NAME~SCHEDULE_TYPE~START_DATE~REPEAT_INTERVAL~END_DATE~ENABLED'
                         );
        loop
            fetch refcur_cur into jobs_record_name;
            utl_file.put_line(fhandle,jobs_record_name.OWNER||'~'||jobs_record_name.JOB_NAME||'~'||jobs_record_name.JOB_STYLE||'~'||jobs_record_name.JOB_CREATOR||'~'||jobs_record_name.JOB_TYPE||'~'||jobs_record_name.JOB_ACTION||'~'||jobs_record_name.NUMBER_OF_ARGUMENTS||'~'||jobs_record_name.SCHEDULE_OWNER||'~'||jobs_record_name.SCHEDULE_NAME||'~'||jobs_record_name.SCHEDULE_TYPE||'~'||jobs_record_name.START_DATE||'~'||jobs_record_name.REPEAT_INTERVAL||'~'||jobs_record_name.END_DATE||'~'||jobs_record_name.ENABLED);
            exit when refcur_cur%notfound;
        end loop;
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    end if;
utl_file.fclose(fhandle_err);
--end loop;

EXCEPTION
-- WHEN UTL_FILE.INVALID_PATH THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20000,'File location is invalid.');

-- WHEN UTL_FILE.INVALID_MODE THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The open_mode parameter in FOPEN is invalid.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20001,'The open_mode parameter in FOPEN is invalid.');

-- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20002,'File handle is invalid.');

-- WHEN UTL_FILE.INVALID_OPERATION THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File could not be opened or operated on as requested.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20003,'File could not be opened or operated on as requested.');

-- WHEN UTL_FILE.READ_ERROR THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the read operation.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20004,'Operating system error occurred during the read operation.');

-- WHEN UTL_FILE.WRITE_ERROR THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the write operation.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20005,'Operating system error occurred during the write operation.');

-- WHEN UTL_FILE.INTERNAL_ERROR THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Unspecified PL/SQL error.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20006,'Unspecified PL/SQL error.');

-- WHEN UTL_FILE.CHARSETMISMATCH THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
--                                    'operations use nonchar functions such as PUTF or GET_LINE.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20007,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
--                                    'operations use nonchar functions such as PUTF or GET_LINE.');

-- WHEN UTL_FILE.FILE_OPEN THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested operation failed because the file is open.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20008,'The requested operation failed because the file is open.');

-- WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
--                                    'be within the range 1 to 32767.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20009,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
--                                    'be within the range 1 to 32767.');

-- WHEN UTL_FILE.INVALID_FILENAME THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The filename parameter is invalid.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20010,'The filename parameter is invalid.');

-- WHEN UTL_FILE.ACCESS_DENIED THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Permission to access to the file location is denied.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20011,'Permission to access to the file location is denied.');

-- WHEN UTL_FILE.INVALID_OFFSET THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
--                                    'it should be greater than 0 and less than the total ' ||
--                                    'number of bytes in the file.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20012,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
--                                    'it should be greater than 0 and less than the total ' ||
--                                    'number of bytes in the file.');

-- WHEN UTL_FILE.DELETE_FAILED THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file delete operation failed.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20013,'The requested file delete operation failed.');

-- WHEN UTL_FILE.RENAME_FAILED THEN
--    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file rename operation failed.');
    -- utl_file.fclose(fhandle_err);
--    RAISE_APPLICATION_ERROR(-20014,'The requested file rename operation failed.');

WHEN OTHERS THEN
    utl_file.fclose(fhandle);
    utl_file.fclose(fhandle_err);
    begin
        fhandle_err := utl_file.fopen(
                            location => 'UTL_DIR'     -- File location
                            ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                            ,open_mode    => 'a', -- Open mode: w = write.
        max_linesize => 32767 );
            utl_file.put_line(fhandle_err,sqlcode||' - '||sqlerrm);
            utl_file.fclose(fhandle_err);
            EXCEPTION
                when others then
                utl_file.fclose(fhandle_err);
    end;
    RAISE;
end;
/

Connecting to non oracle databse from SQLDeveloper using third party database drivers.

noreply@blogger.com (Subhroneel) — Sun, 12 Jan 2020 20:04:00 +0000

Oracle SQLDeveloper is exclusively create to connect to Oracle database using either ojdbc driver or oracle instant client. But SQL Developer also provide utility to connect to non oracle database using Third party jdbc driver.

In my limitation of knowledge I have connected to databases like DB2, SQL Server, Sysbase and MySQL.

In this post I will not show how to connect ot individual database but as whole the steps to connect to third party database.

Select Tools from the main menu and select Preferences. From the preferences window expand the database node from the left pane. From there select Third Party jdbc driver and a list box will be found with a label Third-Party driver path.

On the bottom you will find an Add Entry button, click on the button to add new third party jdbc driver. Browse your PC to find the jdbc driver with extension .jar. Click on Ok after you see the entry ofvthe jdbc driver along with it's path into the listbox. Click on Ok.

Suppose you add jdbc driver sqljdbc6.jar to connect to SQL Server database. You will find a tab right left to the Oracle tab. Make your connection entry under the tab and click on connect.

In my later post I will come up with more on this as how to connect to different databases using jdbc driver and link to find such jdbc drivers.

Difference between coalesce and nvl in Oracle PLSQL.

noreply@blogger.com (Subhroneel) — Sun, 12 Jan 2020 19:23:00 +0000

We all know about basic of nvl function in oracle and somehow a bit about coalesce. But question is when to use nvl and when to use coalesce.

Suppose in a case where you are trying to read a column value and if it is null we read some default values like 0 in case of number and for character say something like 'X'.

So if the name of the column is Salary we write nvl(Salary,0) or coalesce(Salary,0). Now it is know that coalesce is a bit slower than nvl. As coalesce can take multiple arguments but nvl can take only 2. So when it is q question of only checking one single variable then nvl is the best option. But if you need to check multiple variable then coalesce is better option.

Suppose we have 4 columns to check i.e. coalesce(Amount1, Amount2, Amount3, Amount4,0). This can also be written using case when statement, i.e. case when Amount1 is not null then Amount1 when Amount2 is not null then Amount2 when Amount3 is not null then Amount3 when Amount4 is not null then Amount4 else 0 end. In case with nvl it can be written as nvl(Amount1,nvl(Amount2,nvl(Amount3,nvl(Amount4,0)))). But using so many nvl statement is not only junky code but also can lead to performance issue. So it is good to use coalsce in case of more than one column or else nvl.

How to make database objects accessible using synonyms in oracle.

noreply@blogger.com (Subhroneel) — Sun, 12 Jan 2020 19:23:00 +0000

When it comes to application development it is not always happens that application developer are so much sound with database knowledge. They know their own part of task to execute. Like to connect to database, to bind with the database object and to fetch data, insert / update or delete data. But it is not possible for them to take account of each and every database objects.

Let us start with an example. An application is accessing a database schema ACC_DATA which contains all the tables / views. Now it is obvious that it will never have direct access to this schema user rather it will have to connect from an application user. In our case let us consider a technical user ACC_APP. Now ACC_APP have read / write access to ACC_DATA. That mean it can select / insert / update and delete from tables in ACC_DATA.

Now supposed some new views have been created in ACC_DATA and application developer have been instructed to add those views in some new reports to be displayed in the application GUI. Now these application handles several other schema users data through ACC_APP. So it is not possible for the developer to keep track as from which schema these tables are supposed to fetch data and use schemaname.table name inside the application code and it is not also feasible in all scenario to user this kind of schemaname.tablename as it is not permissible. So what is the solution?

The solution is to create a snyonym for the respective schema.tablename in ACC_APP. And developer can directly user the synonym name which will access the tablename using the synonym thinking it as the actual tablename, so it acts as pseudo tablename.

So whenever a new table is added, the request is send to dba to add one synonym into ACC_APP.

Suppose a new table CUST_DATA is created in ACC_DATA. We already consider that grant select any table privileges is provivded to the user ACC_APP. So exclusive privileges are not required to provide for this table to ACC_APP and we assume the privilege is already provided when the table is created.

So DBA should run one command like CREATE SYNONYM CUST_DATA for ACC_DATA.CUST_DATA.

Now let us consider the situation where none of the SYNONYMS existis in ACC_APP and the dba has to create synonyms for all existing tables / VIEWS in ACC_DATA in ACC_APP. So if the number of tables / views are thousands then dba should be absconding from office from the very next day. So dba shouls create a script which will create SYNONYMS for all existing tables / views in ACC_APP. Here below I an writing down that script.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='ACC_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM ACC_APP.'||I.TABLE_NAME||' FOR ACC_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/

So all tables / views of ACC_DATA will be accessible with this pseudo tablename / synonyms within ACC_APP.

Working with ROLES in Oracle

noreply@blogger.com (Subhroneel) — Sun, 12 Jan 2020 18:47:00 +0000

Role is kind of template which not only helps in encapsulating set of privileges provided to a certain user but also it helps in maintaining business rules for organization. User management can be handled smoothly with use of roles. Granting ad-hoc privileges to users is not recommend at enterprise level architecture.

In this content I will try to explain how role helps in managing business rules and security.

Suppose a company X have one datawarehouse application handling application for daily batch and transaction. It uses one oracle database connecting a schema X_DATA. X_Data contains all the business objects and hold the versioned data everyday after the daily batch job completes and provide reports to end user through application. Now end user have to login to the database using individual personal user which will access the objects of X_DATA.

From this set of application users there are 2 types of users.
1. User who can view the data from report and update certain data if necessary
2. User who can only view the data.

So for providing such kind of privileges 2 roles are created.
i. READ_DWH_ROLE - This will provide select privileges to all tables / view of X_DATA to the user.
2. READ_WRITE_DWH_ROLE - This will provide select / update privileges to users to tables / views of X_DATA

Now let us see what command was used by DBA to add privileges to the above role on X_DATA schema user.

i. READ_DWH_ROLE - grant select any tables on X_DATA to READ_DWH_ROLE.
ii. READ_WRITE_DWH_ROLE - grant select any table,update any table on X_DATA to READ_WRITE_DWH_ROLE

Now end user request the required role from some tool or mail with manager's approval to dba to provide access of the role to the user if exists or create new user and provide access.

Lets us assume that there is an end user Mike Anderson requesting a new user mikean requesting the role READ_DWH_ROLE and another user Peter Vaun requesting the role READ_WRITE_DWH_ROLE. The request got approved by manager and went to DBA.

DBA will login to system user and will create the to new user. Since this user will not be owner of any db objects but only access objects of X_DATA.

CREATE USER MIKEAN IDENTIFIED BY MIKEAN DEFAULT TABLESPACE TBLPSPC_X_DATA;
CREATE USER PETVAUN IDENTIFIED BY PETVAUN DEFAULT TABLESPACE TBLPSPC_X_DATA;

GRANT CONNECT TO MIKEAN;
GRANT CONNECT TO PETVAUN;

GRANT READ_DWH_ROLE TO MIKEAN;
GRANT READ_WRITE_DWH_ROLE TO PETVAUN;

So it's all set. Now mikean can read tables / views of all X_DATA witn select query or some application accessing the object using X_DATA.table_name, X_DATA.viewname and petvaun can read / update tables/ views of X_DATA.

Now suppose the application developer is not aware of accessing this objects from X_DATA schema and he is only provided the tablename / view name and if he put the table or view name inside his application like select * from tablename instead of select * from X_DATA.tablename the application will definitely get and oracle error as table or view does not exists. In that case it is neccessary for the user to add another request for creating synonym privileges and create synonyms for all table and view of X_DATA on itself.

In that case dba has to run first grant create synonym privileges to the usrs.

GRANT CREATE ANY SNONYM TO MIKEAN;
GRANT CREATE ANY SYONYOM TO PETVAUN;

Then create a script like this and execute.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='X_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM MIKEAN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
        EXECUTE IMMEDIATE 'CREATE SYNONYM PETVAUN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/
Now if the application user the table names / view names it will actually use the synoym which will actually invoke the underlying tablename and viewname.

So this is the first part of creating role and adding privilege and assigning it to the user.

Wait for the next post to find out more on roles.

Updating view in Oracle plsql

noreply@blogger.com (Subhroneel) — Sun, 12 Jan 2020 07:15:00 +0000

As we all know that a view columns cannot be updated like we do in table. But there are certain ways to update the corresponding table columns of a view using instead of triggers.

A trigger need to be created on the view that need to be updated with instead of clause and inside the trigger the corresponding table columns can be updated.

Let us see this with an example.

Say we have to tables.

1. Employee
The structure of the employee table is as such below.

EMPID    NUMBER(20),
EMPNAME VARCHAR2(100),
DEPTID   NUMBER(20),
AGE      NUMBER(3),
CTC      NUMBER

2. Department
DEPTID    NUMBER(10),
DEPTNAME NUMBER

Now we have a view name employee_view whose query is as below

CREATE OR REPLACE VIEW EMPLOYEE_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID

This view can be easily inserted / updated / deleted using an insert / update / delete statement.

update employee_view set empname = :empname, deptname = :deptname where empid = :empid;

So in what case a view is not updatable?

1. Using distinct operator
2. Using group or order by clause
3. Using connect by, start with clause
4. Using a sub query
5. Using any kind of functions or oracle internal aggregated functions (point number 2 applicable)
6. Using join condition.

In the above case a instead of trigger need to be created. Let us assume that the view query is a such

CREATE OR REPLACE VIEW EMPLOYEE_DEPT_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID
ORDER BY DEPTNAME,EMPNAME
Now if we try to insert / update / delete it will not allow as join condition have been used in the query.

In this scenario we need to add one instead of trigger which will insert into (may not be for the above view query as not all columns are present and depending on not null constraints or check constraints) the table, update the table or delete from table based on the dml query that will be written within the instead of trigger.

Here given below are given 2 instead of trigger on insert / update or delete.

Update

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_VIEW AS
INSTEAD OF UPDATE ON EMPLOYEE_VIEW
FOR EACH ROW
BEGIN
   UPDATE EMPLOYEE SET EMPNAME=:NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID = :OLD.EMPID;
   UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :OLD.DEPTID;
EMD;/
As from the above we observe that for update we are taking all :new.fieldname for the updated value and since key field is empid for employee and deptid is key field for deprtment.

Now lets insert data into the EMPLOYEE_DEPT_VIEW

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF INSERT ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
INSERT INTO EMPLOYEE VALUES(:NEW.EMPID,:NEW.EMPNAME,:NEW.DEPTID,:NEW.AGE,:NEW.CTC);
INSERT INTO DEPARTMENT VALUES(:NEW.DEPTID,:NEW.DEPTNAME);
END;

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF DELETE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
DELETE FROM EMPLOYEE WHERE EMPID=:NEW.EMPID;
DELETE FROM DEPARTMENT WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like delete from EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will delete corresponding record from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be deleted but the other matching record will be deleted.

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF UPDATE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
UPDATE EMPLOYEE SET EMPNAME = :NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID=:NEW.EMPID;
UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like update EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will update corresponding record columns from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be updated but the other matching record will be updated.

Guess the output of this recursion problem.

noreply@blogger.com (Subhroneel) — Fri, 27 May 2016 10:49:00 +0000

#include"iostream"

using namespace std;

void func(int bool)

{

if(!bool)

return 0;

cout<<bool;

func(!bool);

cout<<bool;

}

int main()

{

func(123);

}

What is the output?

1. 123123

2, 123

3. Stack Overflow

4. 0

#cprogramming #cpluplus #cprogramming #recursion

Operator overloading in c++

noreply@blogger.com (Subhroneel) — Fri, 13 May 2016 01:59:00 +0000

A small example of how to overload operators in c++. In this program we have binary operator, relation operator, stream operator used on String class. Constructor are also use to directly assign string to class.

#include <iostream.h>
#include<string.h>

class String
{
char s[50];
public:
String()
{
   strcpy(s,NULL);
}

String(char *v)
{
strcpy(s,v);
}

String(String& v)
{
strcpy(s,v.s);
}

String operator +=(String a){
   return String(strcat(s,a.s));
}

int operator ==(String a)
{
    return !strcmp(s,a.s);
}

int operator !=(String a)
{
    return strcmp(s,a.s);
}

int operator >(String a)
{
    return strcmp(s,a.s);
}

int operator <(String a)
{
    return strcmp(s,a.s);
}

String operator =(String b){
   return String(strcpy(s,b.s));
}

String operator +(String b){
    String s1(s);
   return String(strcat(s1.s,b.s));
}

friend ostream &operator<<(ostream &, String );
friend istream &operator>>(istream &, String &);
};

ostream &operator<<(ostream &os, String ob)
{
os << ob.s;
return os;
}

istream &operator>>(istream &is, String &ob)
{
is>>ob.s;
return is;
}

void main()
{

String obj("Hello C ");
String obj1("Hello C++ ");
if(obj1>obj)
cout<<obj1<<" is greater than "<<obj;
else if(obj1<obj)
    cout<<obj1<<" is less than "<<obj;
else
    cout<<"Both "<<obj1<<" and "<<obj<<" are equal";
return;
}
#cprogramming #clanguage #c++

Using #Translation #Command In #Linux

noreply@blogger.com (Subhroneel) — Sun, 24 Jan 2016 18:12:00 +0000

https://youtu.be/5wKB2OJYItI

Creating your own http server using python script.

noreply@blogger.com (Subhroneel) — Thu, 17 Dec 2015 18:25:00 +0000

This lesson is about creating your own web server without using apache. To do this we are using two libraries.
BaseHTTPServer and SimpleHTTPServer. BaseHTTPServer creates the http daemon and SimpleHTTPServer
creates the http handler. The program takes one argument which is the port number to which server will be running.
If no port number is mentioned server will run on port 8000.

So here is the code for your Web Server.

import sys
import BaseHTTPServer
from SimpleHTTPServer import SimpleHTTPRequestHandler

Handler = SimpleHTTPRequestHandler
Server = BaseHTTPServer.HTTPServer

Protocol = "HTTP/1.1"

if sys.argv[1:]:
port = int(sys.argv[1])
else:
port = 8000

server_address = ("127.0.0.1", port)
# if you are opt to use this on your local machine 127.0.0.1, but you want to use it as web server in a network
# with various sub net then use 0.0.0.0 instead.

Handler.protocol_version = Protocol
httpd = Server(server_address, Handler)
print("HTTP Server started...")
httpd.serve_forever()

After saving this files (i.e. PythonWebServer.py), goto the command line and type
python PythonWebServer.py 8000 (8000 port number is optional.)

The directory where you executed PythonWebServer.py is the root directory of your web server.
Create a html file on the root, named say firstPage.html

Now open your browser and type localhost:8000/firstPage.html
And your html page will be parsed and displayed.

This type of web server can be used on your local desktop or small home network.

Executing python script from Web Browser without using Apache.

noreply@blogger.com (Subhroneel) — Mon, 14 Dec 2015 07:10:00 +0000

In one of our last blog we have demonstrated that how to execute python script on web browser using Apache Web

Server. In this tutorial we will you how to execute python script in browser without using apache web server.. Python has one of it's own library CGIHTTPServer.py which creates a web server capable of performing as web server. For creating this web server all you need is python application version 2.7.
Download link of python 2.7
1. For 32bit version
2. For 64 bit version.

After downloading and installing, goto the python root directory.(i.e. c:\python27)
You have to run this command here.

C:\Python27>python -m CGIHTTPServer

When you run this command it will start the server at default port 8000.
If you want to run it in a different port just add a port number after the command given above

C:\Python27>python -m CGIHTTPServer 9000

Now check whether you have a folder named cgi-bin on the python root directory.If not create one
using md or mkdir cgi-bin and changed to cgi-bin directory.
Put all your executable python script on this folder.

Now go to the browser and type localhost:9000/cgi-bin/sample.py

Though the server will run by typing localhost:9000 but python code will only parsed by browser when executing script from cgi-bin folder.

Example sample.py
#D!\Python27\python

print "Content-type: text/html\n"

print "<html><head><title>Hello CGI</title></head>"
print "<body><h2>Hello from CGIHTTPServer<h2>r</body></html>"

Executing this script will show

Hello from CGIHTTPServer

Server Client socket programming in Java

noreply@blogger.com (Subhroneel) — Tue, 08 Dec 2015 10:05:00 +0000

This tutorial is made with a purpose of understanding the basic concepts of socket programming with Java. For socket programming you need a package java.net which is within J2SE API's, which consists of classes and interface for low level communications. java.net supports communication with 2 types of protocols,
TCP (Transmission Control Protocol) and UDP (User Datagram Protocol). In this tutorial we are dealing with to application, one Server and another Client. When the Server program starts it listens for client request on the host address given and on a given port number. Ports already in use cannot be used, server is also set to a timeout after 10 seconds. Then the client program which whenever connects to the server, server accepts connection and sends a reply to the client.

1. SocketServer.java.

/*Import these 2 packages, first one is for socket programming and the second one is for input output streaming. */
import java.net.*;
import java.io.*;

public class SocketServer extends Thread
{
   private ServerSocket serverSocket;

   public SocketServer(int port) throws IOException
   {

      serverSocket = new ServerSocket(port);
      serverSocket.setSoTimeout(10000);
    }

    public void run()
    {
       while(true)
       {
           try
           {
               System.out.println("Waiting for client on port " + serverSocket.getLocalPort() + "....");
               Socket server = serverSocket.accept();
               System.out.println("Connected to " + server.getRemoteSocketAddress());
               DataInputStream in = new DataInputStream(server.getInputStream());
               System.out.println(in.readUTF());
               DataOutputStream out = new DataOutputStream(server.getOutputStream());
               out.writeUTF("Thanks for connecting to " + server.getLocalSocketAddress());
               server.close();
           }
           catch(SocketTimeoutException s)
           {
               System.out.println("\nSocket time out..");
               break;
           }
           catch(IOException e)
           {
               System.out.println(e.getMessage());
               break;
           }
       }
    }

    public static void main(String[] args)
    {
       int port = Integer.parseInt(args[0]);
       try
       {
           Thread t = new SocketServer(port);
           t.start();
       }
       catch(IOException e)
       {
           System.out.println(e.getMessage());
       }
    }
}

2. SocketClient.java

import java.net.*;
import java.io.*;

public class SocketClient
{
    public static void main(String[] args)
    {
        String serverName = args[0];
        int port = Integer.parseInt(args[1]);
        try
        {
            System.out.println("Connecting to " + serverName + " on port " + port);
            Socket client = new Socket(serverName, port);
            //client.connect();
            System.out.println("Connected to " + client.getRemoteSocketAddress());
            OutputStream outServer = client.getOutputStream();
            DataOutputStream out = new DataOutputStream(outServer);
            out.writeUTF("\nHello from " + client.getLocalSocketAddress());
            InputStream inServer = client.getInputStream();
            DataInputStream in = new DataInputStream(inServer);
            System.out.println("Server says..." + in.readUTF());
            client.close();
        }
        catch(IOException e)
        {
            System.out.println(e.getMessage());
        }
    }
}

How to enable python code execute in browser using Wamp.

noreply@blogger.com (Subhroneel) — Tue, 24 Nov 2015 18:21:00 +0000

This becomes a head scratching job and bit confusing too to configure web server to execute Python code. It's not rocket science but seems we get confused on googling and getting different suggestions. So we will provide here the perfect configuration to execute .py files in browser.
First of all open httpd.conf file from the \wamp\bin\apache\apachex.x.x\conf\ directory of your wamp directory. Search for the line <Directory "c:/wamp/www/"> (assuming that wamp is installed in C drive and your root directory is c:\wamp\www, you may also use any other root directory).

Uncomment these line if commented.

LoadModule cgi_module modules/mod_cgi.so
#LoadModule proxy_fcgi_module modules/mod_proxy_fcgi.so

Add this line if not exists.

Options +ExecCGI, if you are not sure what else to add, because by default all options are added if you do not mention them and adding any options restrict it to added options, unless you add one + sign before the option.

If "Options" line exists uncomment it if commented.

Add ExecCGI at the end.

Options Indexes FollowSymLinks ExecCGI.

Also add this line if does not exists

AddHandler cgi-script .cgi .py

Now put you python script in the root folder or some sub directory of root folder.

Do not forget to add this line at the begining of the script.

#!c:\pythonPath (i.e. #!c:\python34\python)

If you do not add this line script will not execute as browser will not know where is the python program to compile your script.

Now say your wamp base url is http://localhost:8090/ and your python script is in root directory. c:\wamp\www, then type http://localhost:8090/programName.py

Here is a sample program. This program will connect to the wamp server and display the welcome page.

#!D:/Python34/python

import socket

s=socket.socket(socket.AF_INET,socket.SOCK_STREAM)

server = 'localhost'

port = 8090

request = "GET / HTTP/1.1\r\nHost:" + server + ":" + str(port) + "\r\n\r\n"

s.connect((server, port))

s.send(request.encode('UTF-8'))

print("Content-Type: text/html\n\n")

result = "."

while len(result)>0:
    result = s.recv(4096)
    chunk = result.decode('UTF-8')
    print(chunk)

#python #pythonprogramming #wamp #pythonscript

Python dictionary

noreply@blogger.com (Subhroneel) — Wed, 28 Oct 2015 10:32:00 +0000

In python dictionary Values each key is separated from it's values by a colon {:},
each items in dictionary is separated by commas, and all items are enclosed in braces {}.
Keys of a dictionary should be unique. No duplicate. Values can be of any type but keys
should be immutable.

#!/usr/bin/python

dic = {'Code': '001', 'Name': 'Alex', 'Age': 27}
dic1 = {'Code': '001', 'Name': 'Peter', 'Age': 21}

print "dic['Code']: ", dic['Code'] # prints dic['Code']: 001
print "dic['Name']: ", dic['Name'] # prints dic['Name']: Alex
print "dic['Age']: ", dic['Age'] # prints dic['Age']: 27

#example functions and methods used with python dictionary

#compare function compares 2 dictionaries
# cmp returns 1 on mismatch and 0 on match
if cmp(dic,dic1)==1:
print "Both dictionaries are not equal"
else:
print "Both dictionaries are equal"

# len function prints the length of dictionaries

print "Length of dic : ",len(dic)

# Removing entry with key 'Code'
del dic['Code']

# Deleting all entries in dic
dic.clear()

# Deleting entire dictionary
del dic

# fromkeys copy keys to another dictionary.
# It has 2 parameters
# 1. seq - the list of values used for the key preparation
# 2. value - optional, if provided then entire element values will be set to this value

dic2 = dic1.fromkeys(dic1,10)

print "dic2['Code']: ", dic2.get('Code') # prints dic2['Code']: 002

# has_keys returns true if the key exists in the dictionary

if dic1.has_keys('Name'):
print "The kay name exits'

# keys() Returns list of dictionary keys.

print dic1.keys()

Download Microsoft Visual Studio 2015 Torrent Download.

noreply@blogger.com (Subhroneel) — Thu, 08 Oct 2015 10:58:00 +0000

Visual Studio 2015 Enterprise ISO - Core-X

Torrent Download Link

Magnet Link

Hash : 845B06793CAC0A6B6AF535694467BDCA04ABDF37

Using ngView in AngularJS to create multiple page view.

noreply@blogger.com (Subhroneel) — Thu, 08 Oct 2015 09:21:00 +0000

AngularJS is one of the leading JavaScript framework which is doing something simply awesome. Whenever we talk about ngView we have to think about routeConfig which is actually making multiple page views possible. So with routeConfig we can embed a whole website into a single webpage. In this example we have not used multiple html page but rather used template to embed html document object into div block using ngView. So you can try this code to embed as many template you like based on the key parameter you provide on your url. Route Config use a angular function config which embed html block provided in the template with .when and if any of the parameter key does not match then goes to .otherwise where it automatically redirects it to a error page which displays an error message. Template of error key is also defined in .when.

<html ng-app="app">
<head>
<script src="angular.min.js"></script>
<script src="angular-route.min.js"></script>
<script>
app = angular.module('app',['ngRoute']);
app.controller('MyCtrl',function($scope) {
});
app.config(function($routeProvider) {
$routeProvider
.when('/', {
     template: '<h2>You are at the Welcome page</h2>',
     controller: 'MyCtrl' }
)
.when('/Documents',{
     template: '<h2>Read online tutorials</h2>',
     controller: 'MyCtrl' }
)
.when('/Downloads',{
     template: '<h2>Download utility software</h2>',
     controller: 'MyCtrl' }
)
.when('/error',{
     template : '<h2>Oops you have entered a wrong url',
     controller: 'MyCtrl' }
)
.otherwise( {
     redirectTo: '/error'
      }
);
});
</script>
</head>
<body ng-controller="MyCtrl">
<div ng-view></div>
</body>
</html>

Installing Apache Tomcat on MAC OSx

noreply@blogger.com (Subhroneel) — Thu, 08 Oct 2015 07:59:00 +0000

Apache Tomcat is one of the most used web server.
Installing Apache Tomcat on MAC is as simple at it can be.
All you need is to download it from Apache Website.
Here we are downloading Apache Tomcat 8.0 version link to
the tar.gz is provided here.

http://www.us.apache.org/dist/tomcat/tomcat-8/v8.0.27/bin/apache-tomcat-8.0.27.tar.gz
download this and extract it. The best place to extract is /opt/.

Create a directory called Tomcat8.0 on /opt/ and extract it there. You are all ready.

To start the service manually navigate to this directory. /opt/Tomcat8.0/bin/startup.sh
and to shutdown manually /opt/Tomcat8.0/bin/shutdown.sh

To configure Tomcat to automatically start service at boot, configure launchd_wrapper.sh
or org.apache.tomcat.plist. Here is the github link for configuration of both the script.
https://gist.github.com/mystix/661713.

Remember the default port is 8080. You can change server.xml to change port number for
both normal and SSL (https) connection. BY default for http it is 8080 and for https it
is 8443.

Installing skype on Mac OSx Mavericks

noreply@blogger.com (Subhroneel) — Thu, 08 Oct 2015 07:35:00 +0000

Installing Skype in MAC OS x Mavericks.
Skype is perhaps one of the best VOIP interface
used by millions of people around the globe. Easy to
use, easily importable contact lists from facebook or
google. Making voice / video calls from Skype to skype,
from skype to phone which is actually cost you. Send
message or attach files.

We will be installing skype in MAC OSX Mavericks.
First thing you need to do is to download disk image
from official skype website.

Here is the link to download skype for MAC OSx
http://www.skype.com/en/download-skype/skype-for-mac/downloading/

After download is complete file name will be something like this
Skype_MajorVersion.MinorVersion.MinorVersion.SubVersion.dmg
(i.e. Skype_7.13.428.dmg)
Open the file from the download location and you will find 2
icons shows side by side. One is the skype installer to be installed and another is the Application icon. Hold down and drag the skype icon
to the application icon and your installation starts. After installation completes go to the application folder and drag the skype program icon to the launcher toolbar.

Skype is ready to use.

Universal Oracle data exporter using Visual Basic.

noreply@blogger.com (Subhroneel) — Thu, 01 Oct 2015 07:45:00 +0000

As soon as you click on connect the to section Export Tables is enabled. If you select Sql from Export data for combo then right section will be enabled for custom sql. If you select Tables then list of tables for the user schema will be populated on the Combo. You can either select a single table or if you do not select any table then all tables will be exported when you click on Export, but if you select one table then that particular table will be exported.

This is the interface from where data will be exported. The project is uploaded in google drive and code is provided in tis article.

Download the Code from - Google Drive

Option Explicit
Private con As Object
Private rs As Object
Private rs1 As Object
Private table_name As String, fieldName As String, _
recordString As String, fieldValue As String
Private Sub cmdExportType_Click()
    If cmdExportType.Text = "Tables" Then
        customExportFrame.Enabled = False
        If cmbTableList.ListCount = 0 Then
            Call populateListofTables
        End If
        cmbTableList.Enabled = True
    Else
        customExportFrame.Enabled = True
        cmbTableList.Enabled = False
    End If
End Sub

Private Sub Command1_Click()
    If cmdExportType.Text = "Tables" Then
        Call getListofTablesAndData
    Else
        Call GetSqlData
    End If
End Sub

Private Sub cmdConnect_Click()
    Call OpenConnection(txtUser.Text, txtPasswd.Text, txtHostName.Text)
    tableListFrame.Enabled = True
End Sub

Private Sub Form_Load()
    cmdExportType.Clear
    cmdExportType.AddItem "Tables"
    cmdExportType.AddItem "Sql"
    tableListFrame.Enabled = False
    customExportFrame.Enabled = False
End Sub

Private Sub populateListofTables()
    Set rs1 = CreateObject("ADODB.Recordset")
    rs1.Open "select table_name from cat where table_type = 'TABLE' order by table_name", con
    cmbTableList.Clear
    While Not rs1.EOF
        cmbTableList.AddItem VBA.IIf(IsNull(rs1(0)), "", rs1(0))
        rs1.MoveNext
        DoEvents
    Wend
    rs1.Close
    Set rs1 = Nothing
End Sub

Private Sub getListofTablesAndData()
   Call GenerateTableData(cmbTableList.Text)
End Sub

Private Sub GetSqlData()
    Dim SqlStr As String, fieldString As String
    Dim rs1 As Object
    Dim i As Integer
    SqlStr = txtSql.Text
    Set rs1 = CreateObject("ADODB.RecordSet")
    rs1.Open SqlStr, con, 2
    Open App.Path & "\csv_data\" & tblName.Text & ".csv" For Output As #1
    fieldString = ""
    For i = 0 To rs1.Fields.Count - 1
        fieldString = fieldString & rs1(i).Name & ","
        DoEvents
    Next i
    fieldString = VBA.Left(fieldString, VBA.Len(fieldString) - 1)
    Print #1, fieldString
    recordString = ""
    PBRecords.Value = 0
    If rs1.RecordCount > 0 Then
        PBRecords.Max = rs1.RecordCount
        While Not rs1.EOF
            recordString = ""
            For i = 0 To rs1.Fields.Count - 1
                fieldValue = VBA.IIf(IsNull(rs1(i)), "", rs1(i))
                fieldValue = VBA.Replace(fieldValue, ",", " ")
                recordString = recordString & fieldValue & ","
                DoEvents
            Next i
            recordString = VBA.Left$(recordString, VBA.Len(recordString) - 1)
            Print #1, recordString
            DoEvents
            rs1.MoveNext
            PBRecords.Value = PBRecords.Value + 1
            lblRecords.Caption = Round((PBRecords.Value / PBRecords.Max) * 100, 2) & "%"
        Wend
    End If
    Close #1
    rs1.Close
End Sub

Private Sub OpenConnection(user As String, pass As String, serviceName As String)
   On Error GoTo OpenConnection_Error

    If con Is Nothing Then
        Set con = CreateObject("ADODB.Connection")
        con.CursorLocation = adUseClient
        con.ConnectionString = "Provider=MSDAORA.1;User ID=" & user & ";Password=" & pass & ";Data Source=" & serviceName & ";Persist Security Info=False"
        con.Open
    End If

   On Error GoTo 0
   Exit Sub

OpenConnection_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenConnection of Form Form1"
End Sub

Private Sub OpenConnections(userName As String, passwd As String, dataSource As String)
    If con Is Nothing Then
        Set con = CreateObject("ADODB.Connection")
        con.CursorLocation = adUseClient
        con.ConnectionString = "Provider=MSDAORA.1;User ID=" & userName & ";Password=" & passwd & _
        ";Data Source=" & dataSource & ";Persist Security Info=False"
        con.Open
    End If
End Sub

Private Sub GenerateTableData(Optional singleTable As String)
    Dim i As Integer
    If rs Is Nothing Then
        Set rs = CreateObject("ADODB.Recordset")
        Set rs1 = CreateObject("ADODB.Recordset")
        rs.Open "select * from cat where table_type = 'TABLE'" & VBA.IIf(singleTable <> "", _
        " and table_name = '" & singleTable & "'", ""), con
        PBTables.Value = 0
        PBTables.Max = rs.RecordCount
        While Not rs.EOF

            table_name = VBA.IIf(IsNull(rs(0)), "", rs(0))
            Form1.Caption = "Exporting data for " & table_name & " table "
            If VBA.Trim$(table_name) <> "" Then
                Open App.Path & "\csv_data\" & table_name & ".csv" For Output As #1
                rs1.Open "select * from " & table_name, con, 1
                fieldName = ""
                For i = 0 To rs1.Fields.Count - 1
                    fieldName = fieldName & rs1(i).Name & ","
                    DoEvents
                Next i
                fieldName = VBA.Left$(fieldName, VBA.Len(fieldName) - 1)
                Print #1, fieldName
                PBRecords.Value = 0
                If rs1.RecordCount > 0 Then
                    PBRecords.Max = rs1.RecordCount
                    While Not rs1.EOF
                        recordString = ""
                        For i = 0 To rs1.Fields.Count - 1
               '             On Error Resume Next
                            fieldValue = getValue(rs1, i, table_name, PBTables.Value + 1)
                            fieldValue = VBA.Replace(fieldValue, ",", " ")
                            recordString = recordString & fieldValue & ","
                            DoEvents
                        Next i
                        recordString = VBA.Left$(recordString, VBA.Len(recordString) - 1)
                        Print #1, recordString
                        DoEvents
                        rs1.MoveNext
                        PBRecords.Value = PBRecords.Value + 1
                        lblRecords.Caption = Round((PBRecords.Value / PBRecords.Max) * 100, 2) & "%"
                        lblRecords.Refresh
                    Wend
                    PBTables.Value = PBTables.Value + 1
                    lblTables.Caption = Round((PBTables.Value / PBTables.Max) * 100, 2) & "%"
                    lblTables.Refresh
                End If
                Close #1
                rs1.Close
            End If
            DoEvents
            rs.MoveNext
        Wend
        Set rs = Nothing
        Set rs1 = Nothing
    End If
End Sub

Private Function getValue(r As Object, ByVal idx As Long, ByVal tbl_name As String, recNo As Long) As String
    On Error GoTo Err1
    getValue = VBA.IIf(IsNull(r(idx).Value), "", r(idx).Value)
    Exit Function
Err1:
    Open App.Path & "\errLog.log" For Append As #2
    Print #2, "Error on " & tbl_name & " table on column " & VBA.IIf(IsNull(r(idx).Name), "", r(idx).Name) & ", index " & idx & " on record number " & recNo & " whose column " & VBA.IIf(IsNull(r(0).Name), "", r(0).Name) & " value is " & VBA.IIf(IsNull(r(0).Value), "", r(0).Value)
    Close #2
End Function

Private Sub GetLatLongData()
    Call OpenConnections("bckv", "bckv", "ORCL")
    Open App.Path & "\lat_long.csv" For Input As #1
    Dim newLine As String
    Dim var
    Line Input #1, newLine
    While Not EOF(1)
        Line Input #1, newLine
        var = VBA.Split(newLine, ",")
        Call con.Execute("insert into citieslatlong (City,ProvinceState,Country,Latitude,Longitude) " & _
        "values('" & VBA.Replace(var(0), "'", "''") & "','" & VBA.Replace(var(1), "'", "''") & "','" & VBA.Replace(var(2), "'", "''") & "','" & VBA.Replace(var(3), "'", "''") & "','" & VBA.Replace(var(4), "'", "''") & "')")
    Wend
    Close #1
End Sub

Private Sub txtHostName_Change()
    cmdConnect.Enabled = (txtUser.Text <> "" And txtPasswd.Text <> "" And txtHostName.Text <> "")
End Sub

Private Sub txtPasswd_Change()
    cmdConnect.Enabled = (txtUser.Text <> "" And txtPasswd.Text <> "" And txtHostName.Text <> "")
End Sub

Private Sub txtUser_Change()
    cmdConnect.Enabled = (txtUser.Text <> "" And txtPasswd.Text <> "" And txtHostName.Text <> "")
End Sub