Re: First attempt with Dynamic SQL

On Sat, 17 Apr 2004 20:05:55 +0200, Marc Eggenberger <nw1_at_devnull.ch> wrote:

>Hi there.
>
>My system is Oracle 9.2.0.5 on Windows.
>I have 3 tables tblProbe, tblProbeType, tblProbe_HTTP (see at the end of
>this message)
>
>with
>SELECT pt.Name INTO v_Name
> FROM netprobe.tblProbe p
> INNER JOIN netprobe.tblProbeType pt
> ON p.PROBETYPE = pt.ProbeTypeID
> WHERE p.ProbeID = intProbeID;
>I get the Name, which I use for the Table.
>
>Then I need to do:
>
>SELECT * FROM tblProbe_HTTP
>
>I tried to create a function which I can call from java later
>I tried:
>
>CREATE OR REPLACE FUNCTION sp_getProbeConfig(intProbeID IN NUMBER)
> RETURN types.ref_cursor
>AS
> Config_Cursor types.ref_cursor;
> v_Name varchar2(45);
>BEGIN
> SELECT pt.Name INTO v_Name
> FROM netprobe.tblProbe p
> INNER JOIN netprobe.tblProbeType pt
> ON p.PROBETYPE = pt.ProbeTypeID
> WHERE p.ProbeID = intProbeID;
>
> OPEN Config_Cursor FOR
> 'SELECT * FROM tblProbe_' || v_Name || 'WHERE ProbeID =
>' || intProbeID;
>
> RETURN Config_Cursor;
>END;
>/
>
>This compiles but when executed I get
>ORA-00933: SQL command not properly ended
>ORA-06512: at "NETPROBE.SP_GETPROBECONFIG", line 13
>
>What am I doing wrong here?
>
>
>
>CREATE TABLE TBLPROBE
>(
> PROBEID INTEGER NOT NULL,
> PROBETYPE INTEGER NOT NULL,
> CREATEDDATE DATE
>)
>
>CREATE TABLE TBLPROBETYPE
>(
> PROBETYPEID INTEGER NOT NULL,
> NAME VARCHAR2(35) NOT NULL,
> DESCRIPTION_SHORT VARCHAR2(50),
> DESCRIPTION_LONG VARCHAR2(255)
>)
>
>CREATE TABLE TBLPROBE_HTTP
>(
> PROBEID INTEGER NOT NULL,
> SERVERNAME VARCHAR2(255) NOT NULL,
> URL VARCHAR2(255),
> PORT INTEGER,
> INTERVAL INTEGER
>)

Your commandline wraps. You can't wrap a string literal across a line. The string literal needs to end in a '|| and continue with ' on the next line.
However, in this particular case, you don't need dynamic sql at all, as you are referring to a formal parameter in your procedure, which is a number variable.
And if you insist on using dynamic sql, you would better code 'select ... from ... where probeid = :n' using <your variable> Otherwise you will end up with an unscalable application, as every call to your statement would need to be hard parsed. As Thomas Kyte says in his 'Expert one on one' If I want to write a book on building non-scalable applications, the first and last chapter would be entitled 'Do not use bind variables'

NB: Even Java supports bind variables, provided you use PrepareStatement calls.

--
Sybrand Bakker, Senior Oracle DBA