Oracle in World: 2/15/09

Saturday, February 21, 2009

File manipulation in oracle with UTL_FILE package -Part 1

UTL_FILE package has various subprograms which helps us file manipulation in oracle.
In this part file opening and closing functions and file managing functions will be discussed. Below is the list of the subprograms of UTL_FILE package along with their works related in this area.

1)FOPEN function: The FOPEN function of UTL_FILE package opens a file. You can open 50 files at a time.
The syntax of FOPEN function is,

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

where, location is the directory name of the file.

file_name is the name of the file without directory path.

open_mode can have value of 'r' or 'w' or 'a' or 'rb' or 'wb'. Where,
r = read text
w = write text
a = append text
rb = read byte mode
wb = write byte mode
ab = append byte mode

If the file does not exists on the file system and yet you try to open the file with "a" or "ab" mode then new file is created and opened in write mode.

The max_linesize parameter specify the maximum number of characters in each line including new line character. The default value of this parameter is 1024 which means if the linesize is greater than 1024 characters then only 1024 characters will be read. The maximum value of this parameter can be specified to 32767.

The returning file handler must be specified of type UTL_FILE.FILE_TYPE.

2)FOPEN_NCHAR function: The FOPEN_NCHAR function is similar to FOPEN function in terms of parameters and return types but it is used to open a file in Unicode for input or output. The syntax is,

UTL_FILE.FOPEN_NCHAR (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

3)FREMOVE procedure: The FREMOVE procedure remove a file from OS file system. The syntax is,
UTL_FILE.FREMOVE (
location IN VARCHAR2,
filename IN VARCHAR2);

4)FRENAME procedure: The FRENAME procedure rename an existing file to a new name. It is just like "mv" command on unix, rename/ren command on windows. The syntax is,

UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);

The default is no overwrite if one already exist in the destination directory.

5)FCOPY procedure: The FCOPY procedure copy contents from one file to another newly created file. By default whole contents of the file is copied if start_line and end_line parameters are not specified of the procedure. The syntax is,

UTL_FILE.FCOPY (
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line IN BINARY_INTEGER DEFAULT 1,
end_line IN BINARY_INTEGER DEFAULT NULL);

6)FCLOSE procedure: The FCLOSE procedure closes an open file identified by a file handle. The syntax is,

UTL_FILE.FCLOSE (
file IN OUT FILE_TYPE);

Note that, if there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

7)FCLOSE_ALL Procedure: The FCLOSE_ALL procedure closes all open file handles for the session. The syntax is,

UTL_FILE.FCLOSE_ALL;

Example with these procedures/functions:

Create two directory S_DIR and D_DIR which is corresponds C and D drive respectively. Then a PL/SQL which at first create a file under S_DIR named file1.txt using FOPEN and FCLOSE.
Copy file from S_DIR/file1.txt to D_DIR/copy_of_file1.txt.
Move file from S_DIR/file1.txt to to D_DIR/copy2_of_file1.txt.
Finally remove the file from D_DIR/copy_of_file1.txt
At last we will get copy2_of_file1.txt under D: drive.


SQL> create or replace directory S_DIR as 'C:';

Directory created.

SQL> create or replace directory D_DIR as 'D:';

Directory created.

SQL> DECLARE
2    fileHandler1 UTL_FILE.FILE_TYPE;
3  BEGIN
4    fileHandler1 := UTL_FILE.FOPEN('S_DIR', 'file1.txt', 'a');
5    UTL_FILE.FCLOSE(fileHandler1);
6    UTL_FILE.fcopy('S_DIR','file1.txt','D_DIR','copy_of_file1.txt');
7    UTL_FILE.FRENAME('S_DIR','file1.txt','D_DIR','copy2_of_file1.txt',TRUE);
8    UTL_FILE.FREMOVE('D_DIR','copy_of_file1.txt');
9  END;
10  /

PL/SQL procedure successfully completed.

How to run ddl statements within pl/sql

You can't simply run any DDL statements within PL/SQL. Running simply DDL will return PLS-00103 as below.


SQL> begin
  2  create table t(a number);
  3  end;
  4  /
create table t(a number);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

There are various methods by which you can run DDL statements within PL/SQL. Starting with oracle 8i you can run DDL statements by simply writing "execute immediate" and then DDL statements within single quote as below.


SQL> begin
  2  execute immediate'create table t(a number)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> desc t
 Name       Null?    Type
------      ------   -------------
 A                   NUMBER

If you are inside a procedure then simply execute immediate will not enough to perform DDL operation. Execution of the procedure will return ORA-01031: insufficient privileges as below.


SQL> create or replace procedure p as
  2  begin
  3  execute immediate'create table t2(a number)';
  4  end;
  5  /

Procedure created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ARJU.P", line 3
ORA-06512: at line 1

To overcome this error just add "grant create table to user_name" statement before actual DDL statements. Note that if you run your procedure as SYS user then this will not result error but to run as other user extra grant permission is needed before execution. Remember that it is very bad practice to use SYS user as normal operation.


SQL> create or replace procedure p as
  2      begin
  3      execute immediate'grant create table to arju';
  4      execute immediate'create table t2(a number)';
  5      end;
  6      /

Procedure created.

SQL> exec p

PL/SQL procedure successfully completed.

SQL> desc t2
 Name      Null?     Type
 --------- --------- -------- 
 A                   NUMBER

Another method is using of DBMS_SQL package.


SQL> CREATE OR REPLACE PROCEDURE dynamic_sql AS
  2       v_cursor integer;
  3       row_process  integer;
  4     BEGIN
  5       v_cursor := DBMS_SQL.OPEN_CURSOR;
  6            DBMS_SQL.PARSE(v_cursor, 'GRANT CREATE TABLE TO ARJU',DBMS_SQL.NATIVE);
  7            row_process := DBMS_SQL.EXECUTE(v_cursor);
  8       DBMS_SQL.PARSE(v_cursor, 'CREATE TABLE Test_SQL (col1 DATE)',DBMS_SQL.NATIVE);
  9       row_process := DBMS_SQL.EXECUTE(v_cursor);
 10       DBMS_SQL.CLOSE_CURSOR(v_cursor);
 11     END;
 12     /

Procedure created.

SQL> exec dynamic_sql

PL/SQL procedure successfully completed.

SQL> desc test_sql
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               DATE

How to read/write file in oracle with pl/sql

The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS.

Steps for writing to a OS file system through PL/SQL
Note that this example is under windows file system.
Step 01: Create directory.


SQL>Create or replace directory "FILE_DIR" as 'G:\Logs';

Note that FILE_DIR name is the alias of the physically existing directory of G:\Logs. So there must exists Logs directory under G directory. And to create directory the user must have DBA role or create directory privilege.

Step 02: Give permission to the user who will use the directory FILE_DIR.
If read permission is given then following user can read file from the directory. If write permission is given then following user can write to the directory. Here I have given read and write permission to user ARJU on directory FILE_DIR by two statements.


SQL> GRANT READ ON DIRECTORY FILE_DIR TO ARJU;
SQL> GRANT WRITE ON DIRECTORY FILE_DIR TO ARJU;

You can give both permission by only one statement as,


SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO ARJU;

Also you can give permission to all database users by,


SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO PUBLIC;

Step 03: Provide access to UTL_FILE package


SQL>GRANT EXECUTE ON UTL_FILE TO ARJU;

Step 04: As user ARJU write to the file.


SQL>CONN ARJU/a

SQL>DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'w');
  UTL_FILE.PUTF(fileHandler, 'This is the first Line. \nThis is the second line.');
  UTL_FILE.FCLOSE(fileHandler);
END;
/

After executing above PL/SQL, under G:\Logs myfile.txt would be created and contents of it would be,

This is the first Line.
This is the second line.

Steps for reading file from OS file system through PL/SQL.
After step 1,2 and 3 of above execute following PL/SQL.


SQL>set serverout on
SQL>DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  buffer    CLOB;
BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'r');
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line('File Data: '||buffer);
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line(buffer);
  UTL_FILE.FCLOSE(fileHandler);
END;
/

Here for printing two lines I call UTL_FILE.GET_LINE two times as UTL_FILE.GET_LINE stops reading whenever it finds a newline or by default up to 1024 bytes.

Thursday, February 19, 2009

How to know dependent objects/which objects are dependent under an objects

From dba_dependencies/user_dependencies/all_dependencies view you can know about dependencies between procedures, packages, functions, package bodies, and triggers.
You can know the dependent objects of a procedure ERROR_CODE by following query.
On which objects an object depends

SQL> col OBJECT_NAME for a30
SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE ( object_name, object_type ) IN ( SELECT
           referenced_name, referenced_type FROM dba_dependencies WHERE name =
           'ERROR_CODE' ); 

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_
------------------------------ ------------------- ------------------------------ ------- ---------
DUAL                           SYNONYM             PUBLIC                         VALID   14-OCT-07
STANDARD                       PACKAGE             SYS                            VALID   14-OCT-07
SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE             SYS                            VALID   14-OCT-07
DBMS_OUTPUT                    SYNONYM             PUBLIC                         VALID   14-OCT-07
REGISTERED_PHONES              TABLE               MAXIM                          VALID   15-FEB-09
SUBSCRIBERS                    TABLE               MAXIM                          VALID   20-JAN-09
FIRST_LEG_SQ                   SEQUENCE            MAXIM                          VALID   20-JAN-09
ACCESS_NUMBER                  TABLE               MAXIM                          VALID   20-JAN-09
BLACKLIST_CALLER               TABLE               MAXIM                          VALID   20-JAN-09
DO_NOT_DIAL                    TABLE               MAXIM                          VALID   20-JAN-09
HISTORY                        TABLE               MAXIM                          VALID   20-JAN-09
RATE_PLAN_M                    TABLE               MAXIM                          VALID   08-FEB-09
CHECK_ABILITY                  FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP                         FUNCTION            MAXIM                          VALID   20-JAN-09
FIRST_LEG_ACC                  TABLE               MAXIM                          VALID   25-JAN-09

15 rows selected.

Which Objects are Dependent on an object
Also, you can know the which objects are dependent on an object by using,

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE ( object_name,object_type) in(SELECT name, type from
           dba_dependencies
           WHERE referenced_name='SUBSCRIBERS'); 

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_
------------------------------ ------------------- ------------------------------ ------- ---------
AUTH_ERROR_CODE                FUNCTION            MAXIM                          VALID   25-JAN-09
CALRATE                        FUNCTION            MAXIM                          VALID   01-FEB-09
CALRATEDUMMY                   FUNCTION            MAXIM                          VALID   20-JAN-09
CALRATEDUMMY_800               FUNCTION            MAXIM                          VALID   20-JAN-09
CALRATE_800                    FUNCTION            MAXIM                          VALID   20-JAN-09
CHECK_ABILITY                  FUNCTION            MAXIM                          VALID   20-JAN-09
CHECK_ABILITY_800              FUNCTION            MAXIM                          VALID   20-JAN-09
CREDIT_AMT_OR_TIME             FUNCTION            MAXIM                          VALID   20-JAN-09
GETCOUNTRYNAME                 FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP                         FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP_800                     FUNCTION            MAXIM                          VALID   08-FEB-09
MAP_TO_SPEEDDIAL               PROCEDURE           MAXIM                          VALID   20-JAN-09
UPDATE_USER_STATUS             TRIGGER             MAXIM                          VALID   19-FEB-09
UPDATE_HISTORY_FOR_REFUND      TRIGGER             MAXIM                          VALID   20-JAN-09
INSERT_HISTORY_DETAIL          TRIGGER             MAXIM                          VALID   20-JAN-09
DELETE_SUBSCRIBERS             TRIGGER             MAXIM                          VALID   20-JAN-09

16 rows selected.

How to search a string/key value from PL/SQL code

From dba_source/all_source/user_source view you can find the text source of objects such as FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE and TYPE BODY. You can query from these views in order to find any string/key value inside the objects.

By following query you can find any tables or columns or any strings or expressions referenced in your PL/SQL code.

SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');

If you run above query from Sql*Plus then enter the string you are searching for when prompted for KEYWORD. If you use another tool to run above Sql then replace &KEYWORD with the string you are searching for.

Example: I search for Testing string and I found testing word is found inside P_TEST procedure at line number 4.

SQL> SELECT type, name, line
2    FROM   user_source
3   WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%');
Enter value for keyword: Testing
old   3:  WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%')
new   3:  WHERE  UPPER(text) LIKE UPPER('%Testing%')

TYPE         NAME                                 LINE
------------ ------------------------------ ----------
PROCEDURE    P_TEST                                  4

How to know when a table/object's ddl/code was last modified

From the dba_objects/user_objects/all_objects view you can know about the information about when an object(table,index,function etc) was created or when the last DDL operation was done against the table or when last compilation was done.

As of other oracle views.
DBA_OBJECTS contains all database objects.
USER_OBJECTS contains all objects that is owned by the current user.
ALL_OBJECTS contains all objects on which current user has access.

Note that obj is the synonym of USER_OBJECTS view.

The CREATED column of the view contains date about when an object was created.

The LAST_DDL_TIME column of the view contains date about when the object was last modified by a DDL statement. Note that this column value also contain the timing of revoke and grant that was issued against the object. Similarly on procedure, function, trigger if you compile the object then only LAST_DDL_TIME is only modified.

The TIMESTAMP column of the view contains timestamp of the last ddl time excluding any grants, revoke or any compile time.

Before proceed let's set nls_date_format on sql*plus so that we can see the timings of date data type.

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST' 
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 21-SEP-08 00:49:16 2008-09-21:00:43:11 VALID

Now I am adding a column to the table. After adding column see the LAST_DDL_TIME
and TIMESTAMP column value is changed.

SQL> alter table test add col2 number;

Table altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:02:29 2009-02-20:11:02:29 VALID

SQL> select sysdate from dual;

SYSDATE
------------------
20-FEB-09 11:02:47

Now I grant select on test table to user arju. After grant note that LAST_DDL_TIME is changed but TIMESTAMP value is not changed.

SQL> grant select on test to arju;

Grant succeeded.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST' 
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:12:33 2009-02-20:11:02:29 VALID

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST' 
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 10:42:11 2009-02-20:10:42:11 VALID

SQL> alter procedure p_test compile;

Procedure altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST' 
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 11:18:41 2009-02-20:10:42:11 VALID

Avoid huge whitespaces above table in blogger

Problem Description
This problem is common issue while displaying table on your blogspot/blogger post. Whenever you construct table using HTML inside blogger, above table a huge blank lines/white spaces are printed. In this post I will test it along with writings of how to remove those blank spaces.

Problem Demonstration
Below is the html of a simple table structure. If you are new in html arena then tag
<table> indicates you are going to draw a table.
<th> indicates table header.
<tr> indicates table row.
<td> indicates table data.
Table html is:


<table border="1">
<th>Heading 01</th>
<th>Heading 02</th>
<th>Heading 03</th>
<tr><td>Row 01 Data01</td>
<td>Row 01 Data 02</td>
<td>Row 01 Data 03</td>
</tr>
<tr><td>Row 02 Data 01</td>
<td>Row 02 Data 02</td>
<td>Row 03 Data 03</td>
</tr>
</table>

And the output of above html is

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

From the output you see above table large amount of blank lines is printed. This happens on your blog post. On your own machine above html shows ok and no space is printed there but in case of blogger post a large blank space is printed.

Cause of the Problem
While typing html you pressed ENTER key many times and this cause the Blogger software to add a line breaks tags for each time you press the ENTER key. That's why you are seeing a lot of white spaces above the table.

In fact the number of line breaks printed before the table, is the amount of line breaks inside your script. Blogger generated line breaks is printed before the table html.

Solution of the Problem
You can avoid blogger generated line breaks by three ways. Inside the html no line breaks avoid print line breaks in your blog post.
Method 1)Writing HTML without pressing any ENTER key.
<table border="1"><th>Heading 01</th><th>Heading 02</th><th>Heading 03</th><tr><td>Row 01 Data01</td><td>Row 01 Data 02</td><td>Row 01 Data 03</td></tr><tr><td>Row 02 Data 01</td><td>Row 02 Data 02</td><td>Row 03 Data 03</td></tr></table>

Above html output is,

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

Note that no white spaces are printed after html as no we did not keep any line breaks inside out html. But writing code in this way is not user friendly and also a cumbersome thing. Below is the way of using <div> which in fact turns the line breaks off inside blogger post.

Method 2)Change blogger Dashboard Setting:

i)Sign into Dashboard

ii)Select the blog that you want to change. You may have many blogs and choose as you like.

iii)Click the SETTINGS > FORMATTING tab.

iv)In the middle of the page find the "Convert line breaks" keyword by default which is set to "Yes". Make the setting "No".

v)After change it in the bottom click Save Settings.

And now you no longer find any blank lines above table in your html.

But note that by doing so, you may play havoc with the formatting of your posts such as no paragraphs. Also setting in this way will affect all posts in your blogger. So it is global setting.

Method 3: Using of style and div tag as below.


<style type="text/css">.nobrtable br { display: none }</style>
<div class="nobrtable">
<table border="1">
<th>Heading 01</th>
<th>Heading 02</th>
<th>Heading 03</th>
<tr><td>Row 01 Data01</td>
<td>Row 01 Data 02</td>
<td>Row 01 Data 03</td>
</tr>
<tr><td>Row 02 Data 01</td>
<td>Row 02 Data 02</td>
<td>Row 03 Data 03</td>
</tr>
</table>
</div>

Below is the output of this html,

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

Related Documents
Different types of web hosting services based on application
Which database does google use?
What is web hosting service?
List of Available Advertising Network Companies
The security certificate has expired or is not yet valid.

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, February 21, 2009

File manipulation in oracle with UTL_FILE package -Part 1

How to run ddl statements within pl/sql

How to read/write file in oracle with pl/sql

Thursday, February 19, 2009

How to know dependent objects/which objects are dependent under an objects

How to search a string/key value from PL/SQL code

How to know when a table/object's ddl/code was last modified

Avoid huge whitespaces above table in blogger

Different types of web hosting services based on application

Which database does google use?

What is web hosting service?

List of Available Advertising Network Companies

The security certificate has expired or is not yet valid.

The security certificate has expired or is not yet valid.

Tag Cloud

Blog Archive

Which Oracle database version you use?