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

UK Connected Systems User Group – BizTalk Services questions

This Wednesday I’m attending the CSUG – The Hybrid Organisation (sold out!). During my evaluation of several Integration products (BizTalk / BizTalk Services / Neuron ESB / MuleESB) I had some concerns/questions on BizTalk Services. My goal is to get answers on the following scenario a) I receive a HTTP Post with an Xml Body payload b) This contains a signature which i need to validate c) Transform the Xml body payload to another Xml message d) Perform a HTTP Post with the new Xml message to an external HTTP url and sign the message e) Route the response back, similar to the aforementioned steps, where an error needs to be mapped to a dedicated error response Challenges I'm wondering how to cope with some of the following challenges in this scenario, and would like to know resource information available which addresses some of these challenges; Validating / Signing of messages A requirement is to validate the incoming message by inspecting an signature in the message
Read more

Setting up a build server with the BizTalk Deployment Framework

Image
This chapter describes the steps to a TFS Build Server to set up BizTalk deployment with the following functionality: • Build solution • Perform unit testing Using BizUnit • Create Deployment Packages Using BDTF • Deploy to Server Using R packages created in previous step • Create documentation BizTalkDocumenter For this the following steps are required: • Installation Components (30 min) • Configuring a build account (30 min) • Setting up the build service (30 min) • Scrum template installation (30 min) • Share for the build output directory (10 min) • Adjustments Build Template for use BTDF (30 min) • Design of build definitions (60 min) • Setting BTDF (60 min) • Use BTDF • Build execution / scheduling UPDATE (21-06-2013) - When using sharedassemblies, the following URL is a big help http://mohamedradwan.wordpress.com/2013/01/31/set-custom-assemblies-path-for-host-build-service-on-the-cloud/ - For viewing the basic instructions a video is available on the following blog http:
Read more