Posts Tagged "SQL"

Inserting/Update a long String into a CLOB in Oracle database

Posted by Pourya Shahroudi on May 26, 2010 in Software Development | 0 comments

Inserting a long String into a CLOB field in Oracle database using SQL queries is quite simple, but at the same time can be confusing. Mainly because personally couldn’t find good tutorials on the web when I was trying to do this. Here is a simple way to do it:
To insert a ClOB field you need to have a good query browser. The reason is that I will be using variables to do this, I was not able to do this using DbVisualizer but it works perfectly fine in TOAD.
Here is how to do it:

-- First: you need to declare a variable to hold the long CLOB value.
declare
clobVariable varchar2(32767) :='YOUR LONG CLOB VALUE GOES HERE';

begin
-- ALL the queries you need to run as to be inside the begin tag.
-- As you can see I'm using the variable 'clobVariable' in the insert statement to insert into the table.
-- You can use this variable in any combination of SQL statements.

insert into sometbale(id,clob_column) values(1,clobVariable);
end;
/
Read More

SQL insert row if doesn’t exist

Posted by Pourya Shahroudi on Apr 16, 2010 in Software Development | 1 comment

There are cases in which you like to insert rows into the database only if it doesn’t exist. There are databases that allow you to insert conditions into the SQL to test the number of rows in the table and insert the new row only of there are no rows currently in the table. But unfortunately you cant do that with many other databases.
Here is a simple way to use regular SQL notations to insert rows into the database table only if it doesn’t exit.
lets assume you have a table called users with the following schema:

name(varchar)
family(varchar)
email(varchar)

lets assume we are interested in inserting a row with the following values only if it doesn’t exist in the database table:
name: Pourya
family: Shahroudi
email: something@something.com

INSERT INTO USERS(name,family,email)
SELECT 'Pourya', 'Shahroudi','something@something.com' FROM USERS
WHERE NOT EXISTS(
    SELECT name,family,emial FROM USERS
    WHERE name='Pourya'
    AND family='Shahroudi' and email='something@something.com'
) and ROWNUM = 1;

As you can see this query does a SELECT on the database table with the values we want to insert if this row doesn’t exist then they are inserted into the database table.
NOTE: “ROWNUM = 1” is extremely important in this query if you don’t have this included the query will try to insert as many records as you currently have in the table into the table.

Read More

Inserting records to database IF NOT EXISTS

Posted by Pourya Shahroudi on Jan 22, 2010 in Software Development | 0 comments

I have recently been involved in a task that requires generation of SQL queries. The queries are eventually supposed to be executed on the database. One of the challenges I was facing was to check if the record already exists in the database or not. If it doesn’t exists then I had to insert it into the database, otherwise the record had to be updated.
If you are facing similar problem then you could use the EXISTS keyword to check for existing records on the db.

Read More