Re: First attempt with Dynamic SQL

From: Marc Eggenberger <nw1_at_devnull.ch>
Date: Sun, 18 Apr 2004 09:53:03 +0200
Message-ID: <MPG.1aec524dd43372ad989682@news.individual.net>

In article <5j5380d61he60hkjciihms9nrlt9n7bt87_at_4ax.com>, gooiditweg_at_sybrandb.verwijderdit.demon.nl says...

[...]

Hi Sybrand.

> Your commandline wraps. You can't wrap a string literal across a line.

It only wraps in the newspost ... my newsclient wraps it, in the code it doesnt.

> 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.

OK, so using bind variables ...

CREATE OR REPLACE FUNCTION sp_getProbeConfig2(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;
	 v_Name := 'tblProbe_' || v_Name;
	 
	 OPEN Config_Cursor FOR
	 	  'SELECT * FROM :TableName WHERE ProbeID = ' || intProbeID 

using v_Name;                   

        RETURN Config_Cursor;
END;
/

btw
The 'SELECT * ... Part in the OPEN Config_Cursor is not wrapped in the original source code, thats my newsclient.

That doesnt work .. how would I have to rewrite the OPEN Config_Cursor FOR part?

You say that I dont need dynamic SQL. But I only know the ProbeID not the Tablename, where the information is in which I need to know. So I have to construct the Tablename on runtime. How should this be possible with non dynamic sql?

Thanks for any help.

-- 
mfg
Marc Eggenberger

Received on Sun Apr 18 2004 - 02:53:03 CDT