Re: First attempt with Dynamic SQL

Marc Eggenberger <nw1_at_devnull.ch> wrote in message news:<MPG.1aec524dd43372ad989682_at_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;
> /
>

you bound the wrong thing!

identifiers cannot be bound.

open config_cursor for
  'select * from ' || v_name || ' where probid = :x' using intProbeId;

the rule with binds in DML is -- if you could go into sqlplus and use a character string literal, you can bind it.

Eg:

select * from emp where empno = 55;

in that query, 55 can be bound because:

select * from emp where empno = '55';

would be "valid" but -- emp itself (and in fact nothing else in that string) cannot be bound because:

select * from 'emp' where empno = 55;

would not be valid -- you cannot select from the string 'emp'.

> 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.
Received on Sun Apr 18 2004 - 12:41:28 CDT