ReadMore

I really like unit testing, and nowadays there is an helping framework for almost every environment and/or programming language. For PL/SQL this is utPLSQL, which consists of a bunch of packages that you need to put on your database and then you should be good to test your could. You should.

I just spent some hours debugging my test code, so I want to share this with you, maybe I can save someone out there some time.

My setting

• Schema A: utPLSQL package
• Schema B: package XY (code to test), package UT_XY (test code)

I have two schemas A and B. In schema A utPLSQL is installed, and in B is the code to test. It is recommended to have your code to test and your test code in the same schema.

The following script is used to run the tests:

 begin
   for c in (select object_name,owner
               from all_objects
              where object_name like 'UT\_%' escape '\'
                and object_type ='PACKAGE')
   loop
     begin
     utPLSQL.test(
       package_in => substr(c.object_name,4),
       prefix_in => 'UT_',
       owner_in => c.owner
     );
   exception
     -- in case of a faulty unittest continue with the others.
     when others then
       null;
   end;
   end loop;
 end;
 /

But I kept getting the error:

 Program named "XY" does not exist.

So I thought that somehow the schema A does not “see” the packages in the schema B and created public synonyms. Unfortunately this didn’t help. Then I tried to move the “UT_” package to schema A, which worked, but now I got an error that the code to test and the test code are in different schemas.

There must be something obvious I’m missing!

And there it was: A had not “execute” grant on the packages in B.

  grant execute on b.xy to A;

Now everything just worked fine.

I needed to generate exactly n rows and then this is very handy.

select * from (
select level lvl
from dual
connect by level <= N
)

Found at: http://awads.net/wp/2005/07/01/generating-n-rows-from-dual/

It is so easy: I have a testing environement where an OCI8-Driver is installed. Therefore I can use the OCI-Function if PHP to Bind Variables, get return values and everything is just fine.

Then on the production I needed to switch to ODBC, and thought: Easy just write this stuff for ODBC and you’re done.

Unfortunately the driver does simply not support return values of functions. The problem is, that there are updates in my function, so I can’t solve the problem using:

select function_name(param1,param2,param3,...) from dual

After giving up my return value, I wanted to run the function like that (in TOAD this is no problem):

begin :retval := function_name(?,?,?,?); end;

Afterwards you are giving an array of parameter to the PHP function odbc_execute, and this should work alright.

I tried hundrets of possibilities to run my function. Finally I gave up, made a procdure (which has no return value and vor sure ODBC has no support for OUT or INOUT-Vars). I resigned to have a return value (which should only be the number of updated rows).

I tried: begin procedure_name(?,?,?,?); end;

There was no reaction. My last try:

call procedure_name(param1,param2,param3,..);

This woked! So I need to use “call” and not using the parameter-array of ODBC but giving it directly into the call. I’m happy to get it working, but this costs me one day.

Is there any really working ODBC-Driver for PHP?

I gave up.

PS: Yes I know, there is a solution for my return value. In my procedure I could write the value into a temporary table. But I rather want to use a good driver (like the OCI8) than using such a temporary solution. I’m open to new ways of doing this.

I know what I write is quite newbieish, but I needed information about a directory I’ve created, means which path it’s pointing to:

select * from all_directories dir;

There simply is no user_directories, and with all_objects I didn’t get what I wanted.

Don’t ask me why I needed to to that, but I had a lot (about 14 Mio.) duplicate records in a table.

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

helped me out. (thanks to www.devx.com)

Next time I write my queries to filter those out before inserting them…

I just searched the web for this simple task, and it was difficult to find.

Maybe anyone working a bit with Oracle knows this, but it helped me, maybe I can help someone out there too, so here it is:

select sys_guid() from dual

This generates a 32 character GUID (fits best in a RAW(16) field).