Saturday, November 15, 2014

ORA-20001 is an invalid identifier

Weird error received while working with DBMS_STATS package...
Was working on implementing copy_table_stats using dbms_stats package for our partitioned tables and started getting this ORA-20001 partition_name is an invalid identifier.
I don't see any issue at all as the same code works great for other table partitions.
Only thing noticed is that the partition name given for the error-ed table is pure numeric.
But, I am defining a local variable with VARCHAR and storing the name into this local variable before using it in copy_table_stats proc so I would assume it wont be an issue.
Anyways, to overcome this issue I have to enclose this partition name with double quotes (" ") while passing on to copy_table_stats proc.

eg.,
dbms_stats.copy_table_stats(i.table_owner, i.table_name, chr(34)||t_prev_part_name||chr(34), chr(34)||i.partition_name||chr(34));

That took care of this issue.

Sunday, September 28, 2014

Calling/Executing OS Commands from PL/SQL

You may never know when the need arrives to make an Operating System Command call from a PL/SQL Procedure...

Recently came across such requirement and found the below code and thought of updating my blog.

I used this procedure along with the Extract methods as mentioned in earlier post to extract about 300 million records from about 200+ tables by compressing and ftping to another server. All in 2:30 hours with one session.

Making an OS call from PL/SQL is of two folds:
1. Create JAVA Source Object
2. Create a wrapper procedure to call the JAVA Class created above.

JAVA Procedure Code:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        // Use the appropriate path for your windows version.
        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";    // Windows NT/2000
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";    // Windows XP/2003
        //finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe";  // Windows 64-bit
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/sh";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }

      final Process pr = Runtime.getRuntime().exec(finalCommand);
      pr.waitFor();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }

  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }

};
/

Wrapper to call Java Source:
CREATE OR REPLACE procedure host_command (p_command  in  varchar2)
as language java
name 'Host.executeCommand (java.lang.String)';
/

Grants:
Now, grant DBMS_JAVA permissions to a specific user who should be allowed to make OS calls. Please be very very careful in giving this grant.

CONN / AS SYSDBA

DECLARE
  l_schema VARCHAR2(30) := 'USER_TO_MAKE_OS_CALL'; -- Adjust as required.
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/

Example:
Now, use this wrapper proc in your pl/sql to make any OS command.

t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite  
host_command(t_os_call);

The above example shows that t_os_call variable is set to execute gzip with options built dynamically and host_command procedure uses this variable to execute this command in the OS.
Note:
Use dbms_java.set_output along with dbms_output.enable to get the actual command output from the OS call.
eg., sample showing dbms_java.set_output and storing the results
declare
t_output dbms_output.chararr;
begin
  dbms_output.disable; --flushing the pool
  dbms_output.enable(1000000);
  dbms_java.set_output(1000000);
  
  t_os_call := '/bin/gzip --force '||t_os_path||'/*.dat'; --using -force option to overwrite
  
  host_command(t_os_call);
  
  dbms_output.get_lines(t_output, t_lines);
  
  for i in 1.. t_lines loop
    dbms_output.put_line(t_output(i));
    p_message := p_message||t_output(i);
  end loop;
end;

Thursday, September 25, 2014

Extract/Export Data from Tables into flat files...

We all know that the best and fastest way of exporting data from a table(s) in Oracle is using either EXP or EXPDP.
What if there is a need to fetch records based on some conditions or for certain criteria etc.,? Well, EXP and EXPDP supports with WHERE clause right so whats the big deal? hmm... let me think of another reason...
Yeah, how about exporting data from a query which joins more than one table??? Yes. neither EXP or EXPDP cant do that.
Also, with EXPDP you need to have an access to directory that is defined in the database.

Anyways, lets get to the point.
What I want to show in this post is, a way to extract data from any table dynamically using two options:
1. using plain old DBMS_SQL method to execute Dynamic SQL and
2. using combination of DBMS_SQL and NDS (Native Dynamic SQL) with EXECUTE IMMEDIATE.

Don't want to go too deep in to each (you can read manual if interested in knowing every single bit of it).
Here is the sample code demonstrating 1st option using only DBMS_SQL.
High Level Steps:
1. Form a Query
2. Execute Query
3. Fetch the Results

declare
t_cursor integer default dbms_sql.open_cursor;
t_sql varchar2(4000) := 'select * from user_tables' ; --> this query here can be built dynamically
t_col_count number;
t_col_value varchar2(4000);
t_desc_tbl dbms_sql.desc_tab;
t_exec_status integer;
t_rec_count number := 0;
t_col_separator char(2) := '||';
t_record varchar2(32567);
t_column_string varchar2(4000) := null;
begin
  --parse SQL
  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL
  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);

  --Show column names from the t_desc_tbl
  for x in 1 .. t_col_count loop
    t_column_string := t_column_string ||t_desc_tbl(x).col_name|| ',';
  end loop;
  dbms_output.put_line('Columns => '||rtrim(t_column_string,','));
     
  dbms_output.put_line('Fetching define '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  --define the output for the columns
  for j in 1..t_col_count loop
    dbms_sql.define_column(t_cursor, j, t_col_value, 4000);
    dbms_output.put_line('Column Value '||t_col_value);
  end loop;

  dbms_output.put_line('Execute '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
  --execute the SQL
  t_exec_status := dbms_sql.execute(t_cursor);
     
  --Fetch the records from the above execution
  dbms_output.put_line('Fetching Rows '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
  while dbms_sql.fetch_rows(t_cursor) > 0
  loop
    t_rec_count := t_rec_count + 1;
    for k in 1..t_col_count loop
      dbms_sql.column_value(t_cursor, k, t_col_value);
      t_record := t_record||t_col_value||t_col_separator;                   
    end loop;
    dbms_output.put_line('Record# '||t_rec_count||'=> '||rtrim(t_record,t_col_separator));
    t_record := null;
  end loop;
  dbms_sql.close_cursor(t_cursor);
end;


Here is the sample code using DBMS_SQL along with EXECUTE IMMEDIATE. 
High Level Steps:
1. Form a Query
2. Get the column(s) list from the Query
3. Re-form a query with individual Columns - showing an example to fetch the records as pipe delimited 
4. Execute and Fetch query using BULK COLLECT 

declare
t_cursor integer default dbms_sql.open_cursor;
t_sql varchar2(4000) := 'select * from user_tables' ;
t_col_count number;
t_desc_tbl dbms_sql.desc_tab;
t_rec_count number := 0;
t_col_separator char(2) := '||';
t_separator_csv varchar2(10) := null;
t_column_string varchar2(4000) := null;
t_bulk_limit number := 10000; --Define bulk collect limit
t_line_buffer varchar2(32767);
type csv_cur_type is ref cursor;
csv_cur csv_cur_type;
type myarray is table of varchar2(4000); --assuming the entire record length is 4000 bytes
t_data myarray;
begin
  --parse SQL
  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL
  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);
 
  --Close cursor as this is not needed anymore
  dbms_sql.close_cursor(t_cursor);

  --Show column names from the t_desc_tbl
  for x in 1 .. t_col_count loop
    t_column_string := t_column_string ||t_separator_csv||t_desc_tbl(x).col_name;
    t_separator_csv := '||''' || t_col_separator || '''||';
  end loop;
 
  dbms_output.put_line('Columns String to use in dynamic SQL');
  dbms_output.put_line(t_column_string);
 
  --now repalce select clause in SQL to use the above column string   
  --t_sql := regexp_replace(upper(t_sql), '^SELECT (.*?) FROM', 'SELECT ' || t_column_string || ' FROM');
  t_sql := replace(t_sql, ' * from', ' '||t_column_string || ' from');
  dbms_output.put_line('Actual SQL With Column String ');
  dbms_output.put_line(t_sql);

  --fetch records using bulk collect and process each row
  open csv_cur for t_sql;
  loop
    fetch csv_cur bulk collect
      into t_data limit t_bulk_limit;
    for j in 1 .. t_data.count loop
      begin
        if length(t_line_buffer || t_data(j)) >= 30000 then
          dbms_output.put_line(t_line_buffer);
          t_line_buffer := t_data(j);
        else
          if t_line_buffer is null then
            t_line_buffer := t_data(j);
          else
            t_line_buffer := t_line_buffer || chr(10) || t_data(j);
          end if;
        end if;
      end;
      t_rec_count := t_rec_count + 1;
    end loop;
    exit when csv_cur%notfound;
  end loop;
  close csv_cur;
  --show remaining records
  dbms_output.put_line(t_line_buffer);
end;
/

Now compare the results from option#1 and #2 and you will be amazed to see how best the 2nd option is using Execute Immediate with BULK COLLECT.
You can easily replace dbms_output with utl_file to write the content to a flat file on the server side.

Hope this helps me when I am in search of the samples :)


Thursday, August 21, 2014

VirtualBox: Accessing windows shared folder in Linux guest

Make sure you have installed Guest Additions.
Share a folder on the host machine by going to settings/SharedFolders
Now, login to Linux guest machine and run following commands:

# sudo mkdir /media/some_name
# sudo mount -t vboxsf Host_Shared_Folder_Name /media/some_name

Now cd to /media/some_name and you will see all your windows files.

Add those commands to /etc/init.d/rc.local file to start this mount automatically on the reboot.

Monday, August 18, 2014

Converting xls, xlsx files into csv file thru command line

Thanks to "ScottF", "plang" and "Jon of All Trades" from "StackOverflow" for this solution.

I had a situation where there are hundreds of xls files that needs to be loaded into Oracle and feeling lazy (as usual) to convert all of them into csv file to use in sqlldr.
So, doing little google found this solution:

Copy below code and write into a file Convert_xls2csv.vbs file:
-------------------------------------------------------------------------------------------------------
if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv "
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
-------------------------------------------------------------------------------------------------------

Now, create another Convert2csv.bat batch file and copy the below to run thru the loop:

-------------------------------------------------------------------------------------------------------
FOR /f "delims=" %%i IN ('DIR "*.xls" /b') DO Convert_xls2csv.vbs "%%i" "%%i.csv"
-------------------------------------------------------------------------------------------------------

Please these two files in the directory where you have all your xls(x) files and run the batch file and relax :)

Original Source:

Sunday, August 17, 2014

Fetching records in buckets

This is not a pagination but fetching records in buckets form. Meaning, if you have a table that has huge number of rows and wants to process them in buckets with multiple sessions simultaneously by dividing the rows.

Using analytic function NTAIL:

create table san_bucket_test (col1 number, col2 varchar2(30), statusvarchar2(20));

begin
  for i in 1..20 loop
    insert into san_bucket_test values (i, 'Record # '||i, 'PENDING');
  end loop;
end;
/

select *
  from (
        select sbt.*, (ntile(4) over (order by sbt.col1)) bucket
          from san_bucket_test sbt
         where status = 'PENDING'
       )
 where bucket =3;

As you see above, the above NTILE function with argument 4 divides the rows into 4 buckets and the outer join with bucket=3 will only gets the data for 3rd bucket.