Re: First attempt with Dynamic SQL

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 18 Apr 2004 08:30:05 -0400
Message-ID: <xOidndnuDZt56R_d4p2dnA@comcast.com>

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

...

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

you can't use the bind variable for the table name, but you should use the bind variable for search criteria

SQL> @bind-sample
SQL> var tname varchar2(3)
SQL> var dnum  number;
SQL>
SQL> begin

  2 :tname := 'EMP';
  3 :dnum := 20;
  4 end;
  5 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2 type refcur is ref cursor;
  3 rc refcur;
  4 begin
  5 open rc for

  6           'select empno from :t where deptno = '
  7           || :dnum
  8           using :tname;

  9 end;
 10 /
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 5

SQL>
SQL> declare
  2 type refcur is ref cursor;
  3 rc refcur;
  4 begin
  5 open rc for

  6           'select empno from '||:tname||' where deptno = :dn'
  7           using :dnum;

  8 end;
  9 /

PL/SQL procedure successfully completed.

;-{ mcs Received on Sun Apr 18 2004 - 07:30:05 CDT