Embed a string in a SQL-statement in dynamic PL/SQL

From: Jürg Schaufelberger <juerg.schaufelberger_at_econophone.ch>
Date: Thu, 23 Sep 2004 12:17:47 +0200
Message-ID: <4152a160_2@news.bluewin.ch>

Hello

I work with oracle 8.1.7 and I wrote the following procedure

CREATE OR REPLACE PROCEDURE SetPointDeleted (

   PointFeatureTable VARCHAR2, PunktFID NUMBER, MutID NUMBER) AS

   OS_User4Char VARCHAR2(6);
   aStatement VARCHAR(500);
...

BEGIN
   SELECT sys_context('userenv', 'os_user') INTO OS_User4Char FROM dual;
...

       aStatement := 'update '||T_geo||' LG set LG.deleted = 1,
         LG.mutation_id_deleted = '||MutID||',
         LG.mutation_status_deleted = 3,
         LG.user_deleted = '||OS_User4Char||',
         LG.date_deleted = SYSDATE
         where LG.deleted = 0 and exists (
           select 1
           from '||T||' L
           where L.fid = '||FID_T||' and L.gid = LG.gid)';
       EXECUTE IMMEDIATE aStatement;

    ...
END;
/
SHOW ERRORS;
COMMIT; It is compiled successfully, but if I execute it, I get the message: ErrorMessage : ORA-00904:

The column OS_User4Char does not exists ! How can I embed the value of OS_User4Char which contents for example 'mere' in the String 'aStatement' ? Thank you for help. Received on Thu Sep 23 2004 - 05:17:47 CDT