SQL & PL/SQL
I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".
SQL> rem Ejemplo con TRUNCATE:
SQL> rem Para empezar hay que crear una tabla:
SQL> create table truncate_example as select * from dba_tables
SQL> rem La tabla tiene muchas líneas:
SQL> select count(*) from truncate_example
SQL> rem Y 10 pedazos en el disco:
This is my first entry here.
I would like to criticize criticism for case sensitive object naming in Oracle and PL/SQL.
As you might know - Oracle is converting object names to uppercase by default. Whenever you create object like
CREATE TABLE my_table(my_column NUMBER);
it would be created as
At the same time some people might create table
CREATE TABLE myTable(myColumn NUMBER);
which would become
What is tricky about the above is the way Oracle is parsing queries.
KILLING SESSIONS IN ORACLE
Author JP Vijaykumar
Date Jan 27th 2009
Modified Jan 29th 2009
One of my procedure is taking long time to complete.
I have worked in a table with one timestamp field namely Actiondate. We have updated the sysdate value of inserting time to that field. It contains around 30 lakhs records.
I want to get the records which are inserted a particular day.
I run the following 2 queries..
select * from history
where trunc(actiondate) > trunc(sysdate)
order by docintno
select * from history
where trunc(actiondate) > '01-dec-2009'
order by docintno
Both are taking too much time to give the results.
Both SQL Trace and TKPROF help to find the statistics of an SQL statement which could be used for query optimization.
Start Oracle SQLTRACE:
In Oracle, to start an SQLTRACE for the current session, execute:
ALTER SESSION SET SQL_TRACE = TRUE;
Stop Oracle SQLTRACE
In Oracle, to stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;
SQL for SAS Programmers - Introduction
What is SQL?
SQL stands for Structured Query Language and was designed for development and maintenance within a Database Management System (DBMS).
Many a time, developers ask for procedures to kill there own sessions in the oracle db.
Many of our developers are scattered around the globe in different time zones.
During on-call rotation, developers will be calling me, round the clock, to kill their sessions.
To resolve this issue, I came up with this package, consisting of a pipelined function
and a procedure. The pipelined function, displays the users' sessions in the db.
The procedure kills the developer's session only.
Oracle10g Regular Expression
Oracle database 10g added a new feature "Regular Expression" enhancement that can be used with SQL and PL/SQL statements.
There are four regular expression functions :-
The functions REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_REPLACE basically extend the functionality of other well known string functions SUBSTR, INSTR and REPLACE.
REGEXP_LIKE is basically an operator that is similar to existing LIKE operator.
The above regular expression functions are very efficient and powerful.
A regular ex
I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.