Monday, May 25, 2009

Oracle snippets

Below is a collection of snippets i've used during a project implementation. If you're working with Oracle, you might be able to gain some quick insight how you can use it for the benefit of your project.



Grant / permissions
GRANT ALL ON TO ;

PL/SQL

Cursors

CURSOR c_orderlines
IS
SELECT order_pk
FROM orders;
BEGIN

-- !!turn off the notifications!!

v_process := 1;
v_id := 0;

DBMS_OUTPUT.put_line('Started @' || sysdate);

OPEN c_orderlines;
FETCH c_orderlines INTO v_id;

WHILE c_orderlines%found LOOP

FETCH c_orderlines INTO v_id;

END LOOP;

CLOSE c_orderlines;

END;

System tables
all_tab_columns This is like the syscolumns/systables in SQL Server
all_source This allow searches in all entities that exist


Inline SQL
DECLARE
.....
BEGIN
END;

Retrieve the name of the package
$$PLSQL_UNIT

Output
DBMS_OUTPUT.put_line

Queries
Top selection: where rownum < 5

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Statistical queries
select id as a, max(reference_id) keep ( dense_rank last order by order_creation_ts) as b

Variables – RowTypes
Variables defined as:
c_orderlines%rowtype;
allows usage in the PL/SQL by:
c_orderlines.fieldx
c_orderlines.fieldy

Updating
-- this will check if the field 'x' has changed
updating('long_desc')) THEN

usefull tools
Toad
SQLPlus
SQLPlusW

Calling webservices from a database
http://mennan.kagitkalem.com/HowToCallWebServicesInsideOracleViaPLSQL.aspx

ERRORS:
No listener ==> use the IPADRESS RATHER THAN THE NAME
Action header ==> do NOT set the Action header, this is obsolete

Toad knowledge
Key combinations
CTRL+T Intellisense
F9 execute, output in grid

1 comment:

dontcare said...

if xpath/parsing performance is important, you should look at vtd-xml

http://vtd-xml.sf.net