What is the recommended practice for writing procedures with re-usable code?

Question

I want to check with some of the more experienced Oracle developers here on the best-practices for backend development.

I write a lot of packages that print data in XML format and are used by HTTP services.

For this purpose, I loop through cursors and print the data using htp.p.

e.g.

for i in c_my_cursor loop
  htp.p('<element>', i.data_field, '</element>');
end loop;

Now I've heard that cursors are bad for performance (is this true?). Moreover, there are similar cursors used in different packages, which I feel from a maintenance perspective would be better to switch to functions.

But what can I return from the function? I don't think a cursor would work. What do you folks use?

Answer 1

Cursors are not inherently bad. What is bad is processing Row By Agonizing Row, rather than using set processing. SQL is all about The Joy Of Sets. The problem with cursors is that PL/SQL developers often reach for them automatically; this frequently leads us down the RBAR route, when a straight SQL statement would be more efficient.

Functions are no more efficient than procedures. Choose functions or procedures accoring to whether you're doing something or retrieving something.

In your case I would consider whether Oracle's built-in XML functionality will work in your particular case. This partly depends on which version of Oracle you're using, but pretty much any version since 8i would work with the specific example you posted. Find out more.