Dynamic SQL UPDATE

NEED TO UPDATE table columns with dynamic values (not known in advance).

A. ORACLE 

i.Single-Column update

   [Working example.. just replace table names]

   UPDATE station s

        SET vhf_network_id = 

                (

                SELECT vhf_network_id

                FROM vhf_network v  

                WHERE s.client_license_id = v.client_license_id

                )

        WHERE s.station_charge_id = 5;
 

ii. Multiple update

    The key here is to maintain the order and count of the columns in the source columnlist and destination columnlist

  UPDATE
    SET () = 

          (
          SELECT
          FROM
          WHERE

          )

    WHERE ;

B. POSTGRES EQUIVALENT

   Working example (difference with the above is the absence of INNER SELECT)

  UPDATE station s
      SET vhf_network_id = v.vhf_network_id
    FROM vhf_network v
    WHERE s.client_license_id = vhf_network.client_license_id;

Emptying/Reloading a postgres Database without droping it

Hey.. i know u r not a lazy DBA......

Ever had to re-load a postgres database without having the privileges needed to create a database/schema nor the ones needed to drop it ?

Case In Point;- On OpenShift (RedHat Cloud) you cannot drop the provided database nor create another one...

The schema had so many tables, thus dropping each table one by one is never an option.

After some googling i came up with this.. and found it worthwile to share;

run DELETE SCHEMA public CASCADE;
then CREATE SCHEMA public;

voila!!!!!!

Avoid 'SQL LIKE' - Use Full Text Search

Hello random googler,

Welcome back

Do u often find your self needing to do 'dirty' SQL LIKEs like the following;

SELECT member_id 

FROM vw_members

WHERE upper(member_name) LIKE 'JAY%' OR

upper(member_county) LIKE 'NAI%' OR

upper(staff_no) LIKE 'S01%' OR 

upper(mobile_no) LIKE '072%' OR 'i am tired of this stuff and this is only one permutation assuming the first 3 characters hav been filled...ouuuch'

? (yes question mark... this is where the question ends!!!)


If you answer is yes OR 'Y%'  ;-) ;-) then you are ready for FULL TEXT SEARCH

ONE TECHNIQUE...
Make your SQL VIEWS read for FTS by always having a column called 'ts_doc' that should contain the 'searchable' columns(of ur choice) ??????????


EG (postgres)

CREATE OR REPLACE VIEW vw_members AS

SELECT members.member_id, members.member_name, member.staff_no, members.mobile_no, andanyothercolumnyouwhishcoziassumeuknowsql,

 to_tsvector(COALESCE(members.member_name,'')||' '||COALESCE(member.staff_no,'') ||' '|| COALESCE(members.mobile_no,'')) as ts_doc

So that in your queries just become

SELECT blahblah

FROM vw_members

WHERE ts_doc @@ to_tsquery(' searchQuery:*') ";

NB: searchQuery is nothing but a cleanup to create a valid FTS query:-
searchQuery = searchQuery.replace(" ",":* & ") - in java, but of course u can use ur prefered technique... php, sql,

CONCLUSION
With the above u can use more than one search text(in whatever order or case) to narrow down the result..
for example with searchQuery like 'mik DS1 nai' it will search in all the columns (defined in ts_doc) for words starting with mik or DS1 or nai... thereby matching all entries for nairobi, all names starting with mik eg mike,mikel etc etc

NB: approach is 'begins with....'
(those who do LIKE '%xyz%' look for another evangelist !!!!)

example is in postgres

adios

Oracle and Postgres Cummulative Sum aka Running Totals

Hello Random Googler !!!

You landed here today... u r welcome.

Now consider the following output.

The 'interesting' column is the last one

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	3000
Shamim	01-JUL-13	1000	4000
Abdul	01-JUL-13	1000	5000
Jim	01-AUG-13	1000	6000
ZeGuru	05-SEP-13	1000	7000
Duli	06-OCT-13	1000	8000

Another example
(grouped by date... sorry partitioned by date)

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	1000
Shamim	01-JUL-13	1000	2000
Abdul	01-JUL-13	1000	3000
Jim	01-AUG-13	1000	1000
ZeGuru	05-SEP-13	1000	1000
Duli	06-OCT-13	1000	1000

The first one was achieved by;

SELECT name, date, amount, sum(amount) over(order by payment_id) cummulative_sum
FROM vw_payment

THE second was achieved by;

SELECT name, date, amount, sum(amount) over(partition by date, order by payment_id) cummulative_sum
FROM vw_payment

(the partition by clause was used to restart the running totals for each date)


do i need to say that payment_id is just the PK (incremental/serial integer) of the original table ?

By the way this works for both PostgreSQL and Oracle

Again u r welcome

Oracle isNumeric function

AFAIK there is no in-built function to do such but there are lots of ways to achieve the same using the functions already available.

Here is a list of some;

Option 1: Using to_number()
Attempt to convert the input/variable to a anumber.. if it succeeds then isNumeric is true otherwise false

CREATE OR REPLACE FUNCTION isNumeric (var in varchar) return char is

   dummy number;

begin

     select to_number(trim(var)) into dummy from dual; 

   if (dummy is null) then

      return '0';

   else

      return '1';

  end if;  

exception

   when others then

       return '0';

end;

/

Option 2: Using regex

Find foreign table using a given CONSTRAINT NAME

Ever tried to delete a record only to be told get ORA-02292
"integrity constraint (CONSTRAINT_NAME) violated - child record found"

How do you identify the table that is related to the constraint mentioned above. Of course if you explicitly name ur FK constraints(very unlikely) you have no problem.

ORACLE




SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = 'SYS_C0011582'

ER diagram from an existing ORACLE DB

In SQL Developer 3, this is very straight forward;

View > Data Modeler > Browser.

The browser will show up as one of the tabs along the left-hand side.
Click on the Browser tab, expand the design, right-click Relational Models and select 'New Relational Model'.
Then just drag the tables you want onto the model.

To save/export the diagram to a PDF do;

File > Data Modeler > Print Diagram > PDF

Follow instructions to finish

happy modelling

XOR in Oracle

I had a situation where an XOR implementation was needed in a SELECT query. Since Oracle 10g doesnt have this operator predefined i did some gymnastics and ended up with this solution.

I decided to share this (among others) since human knowledge belongs to mankind

1. XOR in oracle

Example 1 - chars only

Eg we want to display results where ismale='1' XOR iscitizen='1' (ie either but not both)

Solution
SELECT ismale, iscitizen, (ismale + iscitizen) as xor
FROM tablename
WHERE ismale + iscitizen = 1;

NB: oracle will add(mathematical) chars as it would integers

Example 2 - varchars

Eg we want to display results where gender='male' XOR countryid='KE' (ie either but not both)

Solution
SELECT gender, countryid, (decode(gender,'male','1','0') + decode(countryid,'KE','1','0')) as xor
FROM tablename
WHERE (decode(gender,'male','1','0') + decode(countryid,'KE','1','0')) = 1;

NB: oracle will add chars as it would integers

Oracle - Date Functions

Examples

1. Get the date of the next/following Saturday from today
SELECT NEXT_DAY(SYSDATE, 'SAT') FROM dual;

2. Number of months between two dates
SELECT MONTHS_BETWEEN(actiondate, SYSDATE) FROM clientlicenses;

3. Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') as NEW_YEAR FROM dual;

Oracle - Syntax

1. Decode
This works as if-then-else flow control

Here i want to display the relevant application form depending on the licensename

SELECT
decode(vwclientlicenses.licensename,'Land Mobile Service','RF 1','Aircraft Station','RF 14','Maritime Station','RF 14B','Port Operations(Coast) Radio','RF 14B','Amateur Band Radio','RF 2','Citizen Band Radio','RF 1B','Terrestrial Point to Point Fixed Links','RF 3','Fixed Wireless Access Network','RF 3','Terrestrial Point to Multipoint Fixed Links','RF 3','Cellular Network','RF 3','Broadcasting (Radio) - Commercial Free To Air','RF 4','Broadcasting (TV) - Commercial Free To Air','RF 5','RF 1') as applicationform
FROM vwclientlicenses;

2. Coalesce
This allows us to trap NULL values and handle them accordingly

Example

SELECT
correspondenceaction.correspondenceactionid,
coalesce(esc.fullname,us.fullname) as forwardedby,
('' || correspondence.cckreference || '
' || correspondence.correspondencesource || '
' || correspondence.subject || '') as corrrespondencedetails
FROM correspondenceaction
INNER JOIN correspondence ON correspondenceaction.correspondenceid=correspondence.correspondenceid
INNER JOIN users ac ON correspondenceaction.actorid = ac.userid
LEFT JOIN users esc ON correspondenceaction.escalatedby = esc.userid
LEFT JOIN users us ON correspondenceaction.userid = us.userid;

3. Database links

CREATE DATABASE LINK "ERP_LINK.CCK"
CONNECT TO "OPENBR" IDENTIFIED BY VALUES '058C24497EA0E9171D374EA0A3A6D411E09B5CA906AFC989CA' --hash of password
USING '172.100.3.22:1530/imiserp';

Oracle 10g - Configure Email

UTL Mail Package for sending mail

1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql

2. GRANT
GRANT execute ON utl_mail TO ;
(or)
GRANT execute ON utl_mail TO PUBLIC;

3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;

I prefer setting this inside the procedure/function sending the mail


4. Restart the DBMS
>shutdown immediate
>startup


SENDING EMAIL

Showing only the relevant part of the function :

IF(email = '1')THEN

--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;

Oracle 10g - Command Line

NB: Linux Server Assummed

1. Manual Startup

#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //connect to sqlplus as sysdba
SQL>startup //start up the db
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //start Enterprise Manager

NB: shutting down = shutdown immediate (from withing sqlplus)

2. Related (run as user oracle)

#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm

Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)

#netca //configure listener

Oracle

Administration

1. Manual Startup

#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //start sqlplus
SQL>startup //start it up
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //Enterprise Manager

shutting down = shutdown immediate

2. Related (run as user oracle)

#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm

Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)

#netca //configure listener

3. Enterprise Manager
Login:
User: sys
Pass: invent
Connect: SYSDBA
DB Login:
User: sys
Pass: invent
Connect: SYSDBA

Hosted(172.100.3.22):
User:sys
Pass:Imis2goke
Connect: SYSDBA

System Administration
1. Local http://localhost.localdomain:5500/em/
2. Hosted http://172.100.3.22:5501/em/

Manage (Create,Edit n Delete) Users, Roles, etc

Create User > Edit Roles > Priviledges >

Roles
All Except:
-DBA
-GLOBAL_AQ_USER_ROLE

Privileges
All Except:
-FREEZE_ANY_WORKSPACE


String Manipulation
Position Of Substring
INSTR(string, y [,start [, o]]) returns the location of substring y within string.
Optionally you can specify the start at a start position from the beginning of string and find the specific numbered occurance o of y.
For example INSTR(’abracadabraabraxas’, ’ra’, 4, 2) returns the location of the second occurance of the substring ’ra’ starting from location 4. It gives location 14 since the search ignores the first 3 characters (the first character is location 1).


Extract Substring
SUBSTR(string, start [, count]) returns a string by clipping out the subsection of string starting at location start and including count characters.
If count is omitted it will clip until the end of the string.

//get rid of leading and trailing characters
update channeltemp set channelplanname = substr(channelplanname,2, length(CHANNELPLANNAME)-2)

Execution Plan


Exception Handling

Trees - Connect By