Re: Dynamic SQL in Stored Procedure

From: Ing. Martin Gamperl <mgampi_at_ibm.net>
Date: 1998/01/31
Message-ID: <34D3356F.666D553E@ibm.net>#1/1

Hi Mark,

try to use the DBMS_SQL.xxxxxx package to solve your problem. With this package you can generate SQL-Statements dynamically. For example we used this fragment of code to insert values into a table which rows are changed arbitrary.

• Eintragen des Record in die Tabelle vCommand := 'INSERT INTO PWEV.TA_MINVALUES VALUES(:Datum'; FOR i IN 0..iMaxFields LOOP IF WerteFeld(i) IS NULL then vCommand := vCommand || ',NULL'; else vCommand := vCommand || ',' || TRANSLATE(TO_CHAR(WerteFeld(i)), ',', '.'); end if; END LOOP;

            vCommand := vCommand || ')';

• Cursor für dynamische SQL-Anweisung öffnen iCursor:=DBMS_SQL.Open_Cursor; DBMS_SQL.Parse(iCursor, vCommand, DBMS_SQL.NATIVE); DBMS_SQL.Bind_Variable(iCursor, ':Datum', TO_DATE(CurDate, 'dd.mm.yyyy,hh24:mi')); iReturned:= DBMS_SQL.Execute(iCursor); DBMS_SQL.Close_Cursor(iCursor);

Mark Aurit schrieb:

> Im using the Oracle Web Server, and I want to give the customer the
> ability choose a sort; in other words, affect the ORDER BY. Their
> request will be passed to a stored procedure, and Im wondering how
> (and if) I can change the SQL dynamically. I could do something like
> IF var1 = 'A' THEN
> .. do some cursor stuff and issue a SELECT..
> ORDER BY colA
> ELSE
> ORDER BY colB
> END IF;
> But I hate the idea of all that code Ill have to duplicate. Can I say
> something like:
> IF var1 = 'A' then
> var2 := 'emp_id';
> else
> var2 := 'city';
> end if;
> Followed by:
> .. cursor stuff and a SELECT ...
> ORDER BY var2

>

> Or some such construct?

>

> Any ideas greatly appreciated.
> Mark_Aurit @ mail.northgrum.com

--
---------------------------------------------------
*  Ing. Martin Gamperl
*  MGS Softwareentwicklung
*  Klosterwiesgasse 31/IV
*  A-8010 GRAZ
*  Tel.    : +43-316-83 58 26
*  Fax     : +43-316-83 58 26
*  Mobil   : +43-664-40 03 032
*  EMail   : mgampi_at_ibm.net
---------------------------------------------------

Received on Sat Jan 31 1998 - 00:00:00 CST