First attempt with Dynamic SQL

From: Marc Eggenberger <nw1_at_devnull.ch>
Date: Sat, 17 Apr 2004 20:05:55 +0200
Message-ID: <MPG.1aeb907129823c0c989681@news.individual.net>

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
)

-- 
mfg
Marc Eggenberger

Received on Sat Apr 17 2004 - 13:05:55 CDT