Database Technologies

Some people ask this, How to use the Nested tables in PPAS? Since there is no Constructor Function Available and Oracle Compatibility Documentation is based on Procedure/Functions. So, I thought to give an overview on this. We can use nested tables by creating some manual Constructor and Using those constructor function to fetch the data or inserting the data: Following is an example: create type test_type as object (col1 varchar(200)); create type nested_type as table of test_type; create table test_nested(col1 nested_type); Now, Create some constructor Functions which can convert the data types as given below: --- Constructor Function of test_type CREATE OR REPLACE FUNCTION test_type(varchar) return test_type as declare result test_type; Begin result=row($1); return result; END; And --Constructor Function of nested_type CREATE OR REPLACE FUNCTION nested_type(test_type[]) return nested_type as counter integer:=0; rec test_type; result nested_type; BEGIN for rec i

This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL. This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown. select * from junk_character; 'ORA-29275: partial multibyte character' Oracle Descritption for this error is given below: ORA-29275: partial multibyte character Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input. Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW. Which doesn't give much information. To find the the column and rows which have those junk/invisible character user can try following trick. a. Select the data column wise as given below: select col1 from tablename; select col2 from tablename; If you are sure about the columns which has those junk charac

As discussed in previous Blogs about Hot Standby in following links: 1. Monitoring Hot Standby 2. HotStandby in PostgreSQL 9.0. I came up with the following Monitoring Script for Hot Standby. #!/bin/bash # Filename: monitor_hotstandby # Usage is $0 -m master:port -s slave:port -b "PostgreSQL Bin Directory" # Author: Vibhor Kumar # Date: Jan 4th 2011 # E-mail: vibhor.aim@gmail.com while getopts "m:s:b:" opt;do case $opt in m) h1=`echo $OPTARG|cut -d":" -f1` p1=`echo $OPTARG|cut -d":" -f2`;; s) h2=`echo $OPTARG|cut -d":" -f1` p2=`echo $OPTARG|cut -d":" -f2`;; b) PGHOME="$OPTARG" esac done PSQL=$PGHOME/psql function usage() { if [ -z $h1 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 fi if [ -z $h2 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1

This is a Question which I had seen people generally ask, when they Move their DBs to PG or PGPlus Advanced Server and Look for Workaround of Oracle Feature of Traverse of Directed Graph in PG. Oracle Provides CONNECT BY NOCYCLE for traversing a Directed Graph. As mentioned in Following link: http://www.adp-gmbh.ch/ora/sql/connect_by_nocycle.html However, CONNECT BY NOCYCLE implementation, some times miss some paths for Traversing Graph as you can see in following Example: create table directed_graph ( node_from char(1), node_to char(1) ); insert into directed_graph values ('A', 'C'); insert into directed_graph values ('A', 'B'); insert into directed_graph values ('B', 'E'); insert into directed_graph values ('C', 'H'); insert into directed_graph values ('H', 'I'); insert into directed_graph values ('I', 'D'); insert into directed_graph values ('D', 'F'); insert

Lot of People think that COPY is only a Server Based Command. However its not completely true. User can copy the data of a table at client side using COPY Command. The Difference is only in syntax usage. User has to use backSlash before COPY Command as given below: testdb=# select * from test; id ---- 1 2 3 (3 rows) testdb=# \copy test to '/tmp/test.copy' testdb=# \q We can see output of a file at Client side as given below: cat /tmp/test.copy 1 2 3 Similarly User can also copy the data in a file to Server using \COPY as given below: \COPY test from '/tmp/test.copy' iclive1460=# \COPY test from '/tmp/test.copy' iclive1460=# select * from test; id ---- 1 2 3 1 2 3 (6 rows) Note:: One should remember while using \COPY Command, one should not terminate command with semicolon ';'

SQL Injection is method of Hacking Data inside the Database. Using SQL Injection, Hackers do multiple Attempt of SQLs to understand the Structure of tables and Try to get the hint of data inside a table. For example, if a Hacker knows that UserName and Password of a Website are stored in a database table say member, then he can write query something like given below to fetch all the data inside the function as given below: select * from tablename where 1=1; Or select * from tablename where 'x'='x'; Similarly, suppose Hacker wants to know the structure (Columns) of any table, then he can try multiple attempts of finding the column name by using the HAVING OR GROUP BY CLAUSE and can find the Columns Names Based on Error. As given below: postgres=# select * from test_sql_injection group by 1 having 1=1; ERROR: column "test_sql_injection.col" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test_sql_injection gro

One of my colleague has asked about this. She was doing some testing and wanted to drop a user which has objects across the databases. While dropping user/role, DBA/Admin will get following error messages: postgres=# drop user test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 2 objects in database test Above error messages gives the sufficient information to Admin that there are some objects depend/Owned by the user exists in other database. For dropping such user, there are two methods: 1. Reassign all the objects owned by the user to some other user and then drop the user. Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process. Command Which can be are following: REASSIGN OWNED BY old_role to new_role; DROP USER old_role; Note:: reassign command need to be executed for all the databases under one PG instance. 2. First Drop all the objects own