How can you access a user-defined function from Sql Developer

[bobt_1234]

Hi Everybody,


I use Sql Developer to connect to Microsoft Access databases, but I have a problem.

I have a query that needs to find the *last occurrence* of a character in a string, and I had to write a user-function to perform this.

My problem is that when I try to execute that function from a query in my SqlDeveloper session, I still get "[my functoin name] is undefined".

Is there a way to expose a user function to the "outside world" from within Access? Alternatively, is there a built-in function that will allow me to find the last occurrence of a character in a string?

Thanks in advance.

[RuralGuy]

Have you looked at InStrRev() yet?

[orange]

Is the SQL Developer you're talking about, the Oracle SQL Developer. If so, I think Access has no knowledge of Sql Developer/your function, and quits with errors.
Perhaps you could tell us WHAT you are trying to do and someone may offer some alternatives.
As Allan said InStrRev() is an Access function

see http://www.techonthenet.com/access/f...g/instrrev.php

[bobt_1234]

Is the SQL Developer you're talking about, the Oracle SQL Developer. If so, I think Access has no knowledge of Sql Developer/your function, and quits with errors.
Perhaps you could tell us WHAT you are trying to do and someone may offer some alternatives.
As Allan said InStrRev() is an Access function

see http://www.techonthenet.com/access/f...g/instrrev.php

Here's what I don't get.

When I use MS Access on my work computer (Windows 7 professional, Office 2010 professional), I can call InStrRev just fine.

select InstrRev('This is a test.txt', '.');

Result:
Expr001: 15.

When I call the same function from my computer at home (Windows 8 home edition, Office 2010 professional). I get this:

select InstrRev('This is a test.txt', '.');

-- [Microsoft][ODBC Microsoft Access Driver] Undefined function 'InstrRev' in expression.

So why does it work on one computer and not the other? If it works at work, that means you CAN call InstrRev from SqlDeveloper.exe. There is something different about my home computer that is causing it not to work there. My thoughts are: 1) A configuration setting that makes InstrRev "global" so it can be called from anywhere? 2) A registry setting that allows it to be called from outside? 3) A setting within SqlDeveloper? Any thoughts are welcome, this is really bugging me.

[RuralGuy]

Is this the SQL Developer you are talking about?
http://sqldeveloper.solyp.com/

[ItsMe]

Don't you need to make an ODBC connection from your W8 machine? It has been quite a while for me so I could be wrong. I think you need to make a connection in your W8 machine using the correct server name and I think a "connection" name that may be hardcoded in your VBA.

Another thing, your W8 Machine may not have that component installed. You may have to install it and the drivers specific to the type of ODBC you need. I don't know if it is or is not available for W8 home.

Anyway, the way I remember it is you need to make the connection on the machine where the application resides. In this case you have it on your home computer. But I don't always think straight or have a good memory. Maybe someone can correct me if I am wrong.

[RuralGuy]

If the answer to my question in my post # 5 is YES then I did not see any access to MS Access in the spec.

[bobt_1234]

Yes, it is SQL Developer (the application file is SqlDeveloper.exe). It's basically a GUI for interacting with Oracle databases, but it has an option to connect to an MS Access database. In the connect option, you simply specify an alias and a path to the .mdb (or .accdb) file name and it automagically behind the scenes makes the connection. So I don't have to go into the ODBC dialog and create a system alias, etc., that all happens within the app.

What's frustrating for me is that it works at work and not at home. I explored the connection parameters and arguments in SqlDeveloper (they're very basic!), so there is no difference in how I'm connecting at home and at work. The only thing I can think is there must be some difference between Windows 7 and Windows 8 that makes it work on one and not the other. I'm still baffled.

[bobt_1234]

Ok, I installed the latest version of Sql Developer (version 4.0) and it works now. I probably had a more recent version of 3.x installed on my computer at work and it allowed the use of functions like InsStrRev.

How do I mark this thread as "solved"?

[RuralGuy]

Ok, I installed the latest version of Sql Developer (version 4.0) and it works now. I probably had a more recent version of 3.x installed on my computer at work and it allowed the use of functions like InsStrRev.

How do I mark this thread as "solved"?

At the top of the thread under Thread Tools. Great to hear it is solved.