sql*plus without tnsnames

just enter the following:

sqlplus username/password@//hostname:1521/sidname

(It can be placed in field “User Name” if working with the SQL*plus interface.)

invalid password in a combination of oracle 10g and 11g products

When it is the second time you have a “HUH am I crazy or what?” moment when dealing with an environment with a oracle 10g product talking to an 11g product, and you’re really glad you finally remember the reason of the first ‘HUH’ time, it’s time to write it down.

In the latest situation we [...]

force ddl procedure to execute as calling user

Say you have a user DBAUSER with DBA rights. As DBAUSER you need to create a trigger in a different schema.
As DBAUSER you simply enter

CREATE OR REPLACE TRIGGER otherschema.my_trigger ..

without any problems.
You need to create the same trigger in a couple of different schema’s though so you decide to make a procedure to make the [...]

%found after a bulk collect

Today I was reminded of a mistake I so often make:

OPEN c_somecursor;
FETCH c_somecursor BULK COLLECT INTO t_somecursor;
l_found := c_somecursor%FOUND;
CLOSE c_somecursor;

IF l_found
dbms_output.put_line(‘Jup, found something’);

The title of this post should give anough hints where this goes wrong. Even if there are records fetched %FOUND does not give back TRUE.
This does work:

OPEN c_somecursor;
FETCH c_somecursor BULK [...]

Upper-case double quoted objectnames

This script might come in handy if you also didn’t want the free double quoted upper-case object names in the object scripts on the DB, which you get importing and exporting a schema.

The script goes trough the user_source view and reconstructs a file with scripts to create or replace your current objects (packages, types, [...]

UTL_HTTP giving a nasty ORA-24247: network access denied

I got a question today from a colleague.

He had the following code:

l_response varchar2(4000);
l_response := UTL_HTTP.REQUEST(‘http://www.google.com’);

Executing this worked fine but incorporating it in a package function gave back an ORA-24247: network access denied by access control list (ACL).

I didn’t hear about ACLs before but listen to the [...]