Needed some clever SQL in PL/SQL!

[drkestrel]

I need to have a PL/SQL procedure of the following signature

 lookup(item IN VARCHAR2(255), value IN VARCHAR2(255),ans OUT VARCHAR2(255)))

Item turns out to be actually a dynamicfield name in a DB table. The procedure, not suprisingly, do

SELECT blah INTO ans FROM MyTable

The trap is-
blah could be of type VARCHAR(x),CHAR(x),INTEGER.

Can I do any kind of clever ordinary SQL92 stuff without using TO_NUMBER, catching Exceptions, etc. etc. as The catching of exception doesn't work!
I tried to catch the exception using

EXCEPTION 
WHEN OTHERS THEN
  -- do the handling for when blah=VARCHAR/CHAR
END;

but it failed!!
PS: I also have local variables defined of different types used for the SELECT , but that doesn't explain why WHEN OTHERS is no good.... I thought Others mean everything?? Also tried ERR_CATCH_ALL, but that appears to be an invalid constant.... Where about in the documentation could I find these funny codes... I tried, but no luck!

 

[MikeJones]

before you do the "select balh into ans from MyTable" you could look up the type of the MyTable.blah in dba_tab_columns (Or all_tab_columns if you don't have select privs on dba_tables). Based on this you could open one of many cursors were each cursor works on a differnt data type.

HTH,

Mike.

 

[THoey]

I can't remember the term that is used for it, but couldn't you create three functions, each identical in name, the only difference being the number and/or datatype of the input parameters? Example:

fncLookup(item IN CHAR(255), value IN VARCHAR2(255),ans OUT VARCHAR2(255)))

fncLookup(item IN VARCHAR2(255), value IN VARCHAR2(255),ans OUT VARCHAR2(255)))

fncLookup(item IN NUMBER, value IN VARCHAR2(255),ans OUT VARCHAR2(255)))

At the time that you call the function, a test would be run on the type of the ITEM and then the correct function would be called. Oracle already does this with the TO_CHAR function. It looks to see if the parameter passed is a DATE field or a number field...

I think it is called function overloading (???)...
Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[MikeLacey]

This could be done with dynamic sql couldn't it? Seems made for it. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[drkestrel]

Certainly Dynamic SQL would work. I am just not very good at ProC (or the Java/Cobol,etc. version)

I am not sure whether the Cursor method would work. My original problem is that because of the nature of Stored procedure, my current code would be translated into something like

 SELECT * FROM MyTable WHERE 'AFieldName'='AFieldValue'

or even more interestingly

 SELECT * FROM MyTable WHERE 'AFieldName'=9999

which is not the required behaviour. To the best of my knowledge dba_tab_columns only return me the field name in VARCHAR2 format? So i think the

FETCH

could result in the same error, though more difficult to debug :-(

Hmm...... performance wise, what sort of figures are we talking about here comparing-
1) JDBC Oracle connection with single row lookup using Java Statement.
2) Embedded SQL (Pro Whatever) called using a Java Callable statement execution also with single row lookup.

assuming
a) Client and server on same machine or
b) Client and server on different machine

 

[ljspop]

Hi,

I think by dynamic SQL Mike might have been refering to the DBMS_SQL pl/sql package. It has a variety of functions that allow you to parse, execute and fetch SQL built on the fly in PL/SQL. It's performance is not bad in later releases. (IMHO)

I like Terry's overloading idea though...

Bob Bob Lowell
ljspop@yahoo.com

 

[MikeJones]

Do you need to do the overloading?

if the stored proc received a varchar2 you could call it with either of the three types without any errors. Then maybe you could use native dynamic SQL (Would be easier to code) rather than DBMS_SQL which is a bit nasty to use.

BTW, I didn't understand your problem at first, ignore the different cursors suggestion. If you got the datatype back from dba_tab_columns as a string literal then you could decide which cursor to use as each cursor would work on a different datatype. I don't think this is a problem though as the only ones you are using will all be implicetly converted by Oracle for you, i.e. you can store either a VARCHAR, CHAR or NUMBER in a varchar2 field.