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

Comments

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

http://vtd-xml.sf.net
1:51 AM
Post a Comment

Popular posts from this blog

Azure implementation guidelines

In My post ‘ Service Bus Management ’ I pointed out a way of implementing a DTAP strategy for managing the Service Bus environment. There’s now a great post available from Microsoft which goes into more detail about other aspects of managing the Azure subscriptions and other artefacts which is quite useful when setting up your Azure environments. Follow this link to the ‘Azure Implementation Guidelines’ HTH, Sander
Read more

Focus and innovation - recap of the last 2 years

It's been a long time since my last post. Hereby a partial explanation for that, with the note that this blog will remain active for the purpose of being a resource for historical references. My IT career started with programming, and adventures within holland, as well as overseas. When I realised programming is something you can specify, I figured that providing added value is crucial for a long term career and decided to focus on process centric roles. I started with webservice integration and eventually from that to process integration using BizTalk. The great feeling when discussing high level processes and implementing solutions within days, even highly scalable and interfacing with LOB systems is great. After several years of implementing solutions like these, I started questioning if all of this work was justified by the business case, if the solution was actually contributing, if the choices had been valid. In other words, I needed to understand the choices made. When...
Read more

BizTalk Pub/sub vs Topics based routing–discussion

My colleagues and I had a brief discussion about the subject ‘Topics’ and what it brings… Do you all agree/disagree with the following statement: topic-based pub-sub messaging doesn’t really exist in BizTalk “In my opinion; I disagree, as BizTalk offers the following 'out-of-the-box' • Message Routing (f.e build in context properties like message type, trading partner, operation etc) • Content Routing (f.e. promoted properties, message inspection in orchestrations/pipelines icw orchestration logic)” My colleague Rene Brauwers Here my 2 cents…. I agree that it exists...however the current offerings in BizTalk are tightly coupled, making the usage far from perfect. When doing content based routing on custom fields (promoted properties), you will be making the solution message-coupled; Your custom message property schema is deployed, and the message filter on these fields have a direct relationship with this property schema. Making any change to this schema will g...
Read more