Category Archives: programming

Unknown's avatar Frank 10:23 pm on February 10, 2018  

PL/SQL varray and record examples

using varray

set serveroutput ON

DECLARE 
   type aud_int_type IS varray(10) of integer; 
   type aud_str_type IS varray(10) of varchar2(60); 
   audits aud_int_type;
   audit_names aud_str_type;
   total integer;
begin
  audits := aud_int_type(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML);
  
  audit_names := aud_str_type('SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML');

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i)) then
      dbms_output.put_line(audits(i) || ' initialized');
    else
      dbms_output.put_line(audits(i) || ' not initialized');
    end if;
  end LOOP;

  total := audit_names.count;
  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || (audit_names(i) || ');');
  end LOOP;
end;
/

using record

set serveroutput ON

DECLARE 
   type aud_type IS record (value integer, name varchar2(60)); 
   type aud_tab IS table of aud_type INDEX by binary_integer;
   audits aud_tab;
   total integer;
begin
  audits(1).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD;
  audits(2).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD;
  audits(3).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES;
  audits(4).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS;
  audits(5).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD;
  audits(6).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML;
  
  audits(1).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD';
  audits(2).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD';
  audits(3).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES';
  audits(4).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS';
  audits(5).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD';
  audits(6).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML';

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i).value) then
      dbms_output.put_line(audits(i).name || ' initialized');
    else
      dbms_output.put_line(audits(i).name || ' not initialized');
    end if;
  end LOOP;

  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || audits(i).name || ');');
  end LOOP;
end;
/
Unknown's avatar Frank 4:59 am on August 24, 2016
Tags: jdbc   

example jdbc program

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
class TableCount
{
public static void main(String[] args)
{
TableCount t = new TableCount();
t.run(args);
}
public void run(String[] args)
{
try
{
Connection conn = getConnection(args[0], args[1], args[2]);
getTableCount(conn, args[3]);
conn.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static Connection getConnection(Properties p) throws Exception
{
Connection conn = null;
String username = p.getProperty("USERNAME");
String password = p.getProperty("PASSWORD");
String url = p.getProperty("URL");
String driver = p.getProperty("DRIVER");
System.out.println(username);
System.out.println(url);
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
return conn;
}
public static Connection getConnection(String file, String username, String password) throws Exception
{
Properties props = loadProperties(file);
props.setProperty("USERNAME", username);
props.setProperty("PASSWORD", password);
return getConnection(props);
}
private static Properties loadProperties(String file) throws FileNotFoundException, IOException
{
Properties props = new Properties();
props.load(new FileInputStream(new File(file)));
return props;
}
public void getTableCount(Connection conn, String owner) throws SQLException
{
String sql = null;
ResultSet rs = null;
PreparedStatement stmt = null;
sql = "SELECT count(*) c from all_tables where owner = ? ";
try
{
stmt = conn.prepareStatement(sql);
stmt.setString(1, owner);
rs = stmt.executeQuery();
while (rs.next())
{
long count = rs.getLong("c");
System.out.println("~~");
System.out.println(count);
}
}
catch(SQLException exception)
{
exception.printStackTrace();
}
finally
{
}
}
}
view raw TableCount.java hosted with ❤ by GitHub
Unknown's avatar Frank 2:55 am on August 24, 2016  

perl by example

Source code,

sub build_script
{
my $cols = shift;
foreach my $col (@$cols)
{
print($col . ",\n");
}
}
sub parse_source_file
{
my $filename = shift;
my $table_name = shift;
my $file;
my $line;
my $cols = ();
if (!open($file, "<", $filename))
{
print "file not found\n";
return;
}
while ($line = <$file>)
{
$line =~ s/\W+$//;
my @fields = split(',', $line);
if ($fields[0] eq $table_name)
{
push(@cols, $fields[1]);
}
}
close($file);
return \@cols;
}
sub main
{
my $db = uc($ARGV[0]);
my $table = uc($ARGV[1]);
my $source = $ARGV[2];
my $cols = parse_source_file($source, $table);
build_script($cols);
}
main
view raw parser.pl hosted with ❤ by GitHub