Archive for the ‘DBA’ Category

All things Oracle

September 6, 2011

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.

Tags:, , , , , ,
Posted in ACE, Apex, Database development, DBA, Developer, Oracle Forms, Oracle Forms Modernization, PL/SQL, SQL | 11 Comments »

Read from anydata column

March 9, 2011

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more (more…)

Posted in DBA, Oracle, PL/SQL, Queue, select, SQL, SQL Developer, SQLDeveloper, Tip, Tips and Tricks | Leave a Comment »

Calling Webservices from pl/sql

June 1, 2008

For a client of us we had to call a webservice from a database, and the received information that we received from this call was needed in another procedure.

It was the first time for me that I had to do this and I believe that not so many people now of this functionality in the database and that’s why it is obviously a good idea to post this on our blog.

It is even possible to let the database be a webservice provider, but in this case the webservice already existed and should simply be called by the database.

What was the objective: we wanted to call the webservice from the database, get the resulting XML file and analyze some of the content of the returned XML file and then do some actions with the information that the webservice provided us.

Therefore I wrote 3 procedures in a package:
• fnc$_get_xml
• fnc$_handle_xml
• prc$_ws_call

As you will see when you look at the code it is possible to make this much more dynamically but for this case we only had to call only one specific webservice, as usual you can make it very complex but for the blog I made the procedures and functions as simple as possible.

The first function that I will explain is the prc$_ws_call
This procedure will contact the function fnc$_get_xml that will get the xml file. After this function the fnc$_handle_xml will be called to retrieve specific information out of the XML file.
When this is done the received values will be printed


PROCEDURE prc$_ws_call
IS
v_xml VARCHAR2(32767);
v_type cab_base_adres.type%TYPE;
r_receive r_info;
BEGIN
v_xml := fnc$_get_xml(p_search => ‘search value’); –call to webservice
r_receive := fnc$_handle_xml(v_xml); –analization of xml content
dbms_output.put_line(‘value nr1=’||r_receive.value1) ;
dbms_output.put_line(‘value nr2=’||r_receive.value2) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
END;

fnc$_get_xml
This function will receive a parameter(search) this parameter will be included in the soap call(called v_soap_request).
Ones that I have build my soap envelope, I have to create a httpRequest, this is the variable v_httpRequest which is of the type utl_http.req.
For this variable I have to set some parameters like the webservice url that I will call, the content-type (plain text in this case), the content_length of the soap envelope and the specification that this is a soapaction. This is the preparation for the call to the webservice.

Next thing to do is to write this data to the body of the http request, this is done with the utl_http.write_text where we give in the http_request and the soap_request.
Now we want to receive the response from our soap call.
This will be created with the utl_http.get_response which is of the utl_http.resp type.

Next thing we want, is to have this file in a readable form. Therefore we use the utl_http.read_text, that will translate the response in a readable variable.

FUNCTION fnc$_get_xml(p_search VARCHAR2)
RETURN VARCHAR2
IS
v_soapRequest VARCHAR2(32000);
v_soapResponse VARCHAR2(32767);
v_httpRequest utl_http.req;
v_httpResponse utl_http.resp;
BEGIN
v_soapRequest :=
‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”no”?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=”http://schemas.xmlsoap.org/soap/envelope/&#8221; xmlns:apachesoap=”http://xml.apache.org/xml-soap&#8221; xmlns:impl=”<the webservice>” xmlns:intf=”<the webservice>” xmlns:soapenc=”http://schemas.xmlsoap.org/soap/encoding/&#8221; xmlns:tns1=”" xmlns:wsdl=”http://schemas.xmlsoap.org/wsdl/&#8221; xmlns:wsdlsoap=”http://schemas.xmlsoap.org/wsdl/soap/&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; >
<SOAP-ENV:Body>
<mns: <webservice name>xmlns:mns=”<services link>” SOAP-ENV:encodingStyle=”http://schemas.xmlsoap.org/soap/encoding/”&gt;
<inputAddress xsi:type=”tns1: <input parameter name>”>
<input search xsi:type=”xsd:string”>’||p_search||’</search>
</mns: <webservice name> >
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>’;

v_httpRequest:= utl_http.begin_request
(”
,’POST’
,’HTTP/1.1′);

utl_http.set_header(v_httpRequest, ‘Content-Type’, ‘text/xml’);
utl_http.set_header(v_httpRequest, ‘Content-Length’, length(v_soapRequest));
utl_http.set_header(v_httpRequest, ‘SOAPAction’, ”);

utl_http.write_text(v_httpRequest, v_soapRequest);
v_httpResponse:= utl_http.get_response(v_httpRequest);
utl_http.read_text(v_httpResponse, v_soapResponse);
utl_http.end_response(v_httpResponse);
RETURN v_soapResponse;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
dbms_output.put_line(‘Error in fnc$_get_xml’) ;
utl_http.end_response(v_httpResponse);
raise;
RETURN NULL;
END;

Great, we have now received the xml file that contains the result of our request. Now we want to retrieve the data we need, out of this XML file.
Therefore I will call the fnc$_handle_xml function with the received XM file as parameter.

I will have to handle the content of the xml file. To make this easier I am going to put the content of the XML file into an XMLType by using the XMLType.createXML function. Now the parameter resp contains the XML file. But I am only interested in a certain part of the XML file. By using the ‘extract’ function I am able to get a certain part out of the hierarchical structure of the XML file.

Next thing that I want to receive is the information of certain parts of this resp variable. I will put his in the resp1 variable which make it possible to always use the resp file for the next value I want to retrieve.

In this example I get 2 values out of the XML file. And I will return this back to the calling procedure.

FUNCTION fnc$_handle_xml(p_xml VARCHAR2)
RETURN r_info
IS
resp XMLType;
resp1 XMLType;
r_result r_info;
BEGIN
resp:= XMLType.createXML(p_xml);
resp:= resp.extract(‘/soap:Envelope/soap:Body/child::node()’
, ‘xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”&#8216;
);

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
);
IF resp1 IS NOT NULL THEN
r_result.value1 := resp1.getStringVal();
END IF;

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
);
IF resp1 IS NOT NULL THEN
r_result.value2 := resp1.getStringVal();
END IF;

RETURN r_result;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
RETURN r_result;
END;

Posted in DBA, WebService | 4 Comments »

How to reformat the highwatermark of a table after a delete?

May 29, 2008

For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.

Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.

I thought that this could have something to do with my indexes but I didn’t know how to fix this.

That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.

So how do you fix this?

If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.

First option, and the one that worked best in my case was: use TRUNCATE TABLE

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe :-)

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

SHRINK SPACE
You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!

Posted in DBA | 2 Comments »

SYSMAN Account is Locked

February 12, 2008

This morning I opened Enterprise Manager and instead of the regular “login”-screen, I found following screen:

 

Everything is up and running but Enterprise Manager is not able to connect to the database instance. Sounds like a connection problem, not?

These are the steps I followed to solve this issue and to reset the “sysman”-account:

STEP1: Check EM log file:

[oracle@myserver log]$ pwd


/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD
/sysman/log

 [oracle@ myserver log]$ tail -50 emoms.log
…

2008-02-11 23:12:04,968 [ApplicationServerThread-11]
 ERROR app.SessionObjectManager sessionDestroyed.128 - 
java.sql.SQLException: ORA-28000: the account is locked

java.sql.SQLException: ORA-28000: the account is locked
…

STEP 2: Check database users:

SYSTEM> select username, account_status from dba_users;
USERNAME                       ACCOUNT_STATUS
------------------------------ -------------
MGMT_VIEW                      OPEN
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
SYSMAN                         LOCKED(TIMED)

 

STEP 3: Stop the Database Console

[oracle@myserver dbascripts]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0

Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.

https://myserver.mydomain:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

 ...  Stopped.

[oracle@myserver dbascripts]$

STEP 4: Reset the SYSMAN-account


[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> alter user sysman identified by <new_password> account unlock;
User altered.

STEP 5: Reset the SYSMAN-account in the “targets.xml”-file ($ORACLE_HOME/myserver.mydomain _mySID/sysman/emd) and modify the “Username”-Property and the “Password”-property as follows:

[oracle@myserver emd]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/emd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml
…
<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>
…

STEP 6: Reset the SYSMAN-account in the “emoms.properties”-file ($ORACLE_HOME/myserver.my_domain_mySID/sysman.config) and modify the “oracle.sysman.eml.mntr.emdRepPwd”-parameter and the “oracle.sysman.eml.mntr.emdRepPwdEncrypted” in the following way:

[oracle@myserver config]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/config
[oracle@myserver config]$ cp emoms.properties emoms.properties.ori
[oracle@myserver config]$ vi emoms.properties
…
oracle.sysman.eml.mntr.emdRepPwd=<new_password>
…
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
…

STEP 7: Start the Database Console

[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory 
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/log

 

STEP 8: Test

Posted in DBA, Enterprise Manager, sysman | 23 Comments »

Follow

Get every new post delivered to your Inbox.