Use Pl/SQL to compare 2 tables

This Script is used to generate Dynamic SQL.

The SQL is used to compare 2 tables using minus function




CREATE OR REPLACE procedure SelTab2Minus
( name_in IN varchar2,table_in IN varchar2 )
IS
stmt varchar2(2000);
col_name VARCHAR2(30);

cursor c1 is
select column_name
from dba_tab_cols
where owner= name_in and table_name = table_in;

BEGIN



open c1;
LOOP
fetch c1 into col_name;
-- DBMS_OUTPUT.PUT_LINE(stmt);
stmt := stmt || col_name ;
EXIT WHEN c1%NOTFOUND;
stmt := stmt ||',';
END LOOP;
close c1;
--DBMS_OUTPUT.PUT_LINE(stmt );
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in);
--DBMS_OUTPUT.PUT_LINE(' from ' || name_in||'.'||table_in);
DBMS_OUTPUT.PUT_LINE(' minus ');
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in||'@VASMD_46');

END;
/

Startup Listener with password using Script

If the listener.ora set with password, you can't stop listener like "lsnrctl stop".

You will receive the error message like below :

[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 06-MAY-2011 07:48:15

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-01169: The listener has not recognized the password


I wrote a script to stop listener with password

======== Script Start Hear ===========
#!/usr/bash

export PATH
unset USERNAME
export ORACLE_HOME=/home/oracle/OraHome1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=ABC
export JRE_HOME=/usr/java/jre1.6.0_24

lsnrctl< Current Listener is LISTENER
LSNRCTL> The command completed successfully
LSNRCTL> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
The command completed successfully