Thursday, February 08, 2007

Oracle SQL Developer - ORA-00942

I recently tried to user SQL Developer with a schema and found that I could not view packages, procedures or functions. I would get ORA-00942: table or view des not exist. I still don't know why, but granting SELECT_CATALOG_ROLE to the user solved the problem.

Update: I had a similar problem in Oracle Data Miner (Data Mining). I granted unlimited tablespace to the user and the problem went away. It may have been a tablespace issue for SQL Developer as well...

Friday, February 02, 2007

Oracle Portal Client IP Address (REMOTE_ADDR)

You may want to show the IP address of the client (the browser) on a Portal page. This can be tough because the page is built by the parallel page engine (PPE). At that point, the actual requestor is the mid-tier itself. You need to make a call that goes all the way to the database without being redirected through the PPE. I do this by creating a pl/sql procedure (ideally part of a package) that I then call from a bit of javascript in the page (possibly through a UI Temple / HTML Template). Note: if the user is going through a proxy you will get the proxy IP. If you have a reverse proxy setup (e.g. multiple Oracle Web Caches or Apache Reverse Proxy) you can set things up to get the correct browser IP. See my other post on setting up chained Web Caches.

Here is the procedure, it's fairly straightforward.


CREATE OR REPLACE procedure c2_getClientIP is
l_ip varchar2(200);
begin
l_ip := owa_util.get_cgi_env('REMOTE_ADDR');
htp.p(l_ip);
end;
/

grant execute on c2_getClientIP to public;


Then you put a little javascript in the page to call that procedure.


<script language="Javascript">
function xmlhttpPost(strURL) {
var xmlHttpReq = false;
var self = this;
// Mozilla/Safari
if (window.XMLHttpRequest) {
self.xmlHttpReq = new XMLHttpRequest();
}
// IE
else if (window.ActiveXObject) {
self.xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
}
self.xmlHttpReq.open('GET', strURL, true);
self.xmlHttpReq.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
self.xmlHttpReq.onreadystatechange = function() {
if (self.xmlHttpReq.readyState == 4) {
updatepage(self.xmlHttpReq.responseText);
}
}
self.xmlHttpReq.send(getquerystring());
}

function getquerystring() {
var form = document.forms['f1'];
var word = form.word.value;
qstr = 'w=' + escape(word); // NOTE: no '?' before querystring
return qstr;
}

function updatepage(str){
document.getElementById("result").innerHTML = str;
}
</script>

<form name="f1">
word: <input name="word" type="text"> <input value="Go" type="button" onclick='JavaScript:xmlhttpPost("/pls/portal/portal.c2_getClientIP")'>
<div id="result"></div>
</form>



You can put the javascript in a template or as a text item. You can modify this however you want to either just show the IP, put in a variable, etc.

DBMS_XMLSCHEMA.GENERATESCHEMA and ORA-21500

You may run into this error. You want to generate an xsd based on on object type. You create the object:

CREATE OR REPLACE TYPE my_test_type
AS OBJECT (
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20)
);


Then you want to get the associated xsd:

SQL> Set pagesize 9999
SQL> Set linesize 132
SQL> Set long 9999
SQL>
SQL> select DBMS_XMLSCHEMA.GENERATESCHEMA( 'APPS', 'MY_TEST_TYPE')
2 from dual
3 /


Instead of getting the xsd, you get the following:

ERROR:
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]


The problem is not at the database, but with sql*plus. You are probably using a 9i sqlplus client. Try a 10g sqlplus and it should work fine.

As always, if this helps, drop me a comment. It would be great to know that this actually helped someone.