Object Types

From: Oliver Stratmann <yxcsstratmoqwert_at_gmx.de>
Date: Fri, 13 Feb 2004 21:30:15 +0100
Message-ID: <c0jc4o$17rflq$1@ID-180535.news.uni-berlin.de>

Hello All!

I tried to create an Object Type SQL_Stmnt which is listed below. (It's purpose is to store, execute and (optionally) write debug-infos for dynamically created SQL-Statements.)
In this version it compiles an runs.
If I uncomment the row marked with "--XXXX", I get the Compile Error "PLS-00363: expression 'SELF' can not be used as an assignment target" though I don't see the assignment in the procedure DEBUG_STMNT.

Any hints are appreciated.
Thanks in advance!

Bye!
Oli

create or replace type sql_stmnt
as object (

      shorttext      varchar2(32000)


      ,calling_module varchar2(1000)

      ,debug_flag     varchar2(100)

      ,member procedure debug_stmnt

      ,member procedure exec_stmnt

      ,member function exec_stmnt

      return number

);
/

create or replace type body sql_stmnt
as

   member procedure debug_stmnt
   as

      lc_sql2 VARCHAR2(2000);

   BEGIN

      if debug_flag is not null
      then
       IF shorttext IS NOT NULL
       THEN
        FOR ln_count IN 0..trunc(length(shorttext)/2000)  LOOP
         lc_sql2:=substr(shorttext,ln_count*2000,2000);
         INSERT INTO IMP_SQL_DEBUG
          (PROZEDUR,SQL_ANWEISUNG)
         VALUES
          (calling_module ,lc_sql2);
         COMMIT;
        END LOOP;
         END IF;

    end if;
    null;

   EXCEPTION

       WHEN OTHERS
       THEN
       null;

   end debug_stmnt;

   member procedure exec_stmnt
   is
    lc_cursor integer;
    ln_return integer;
   begin

    debug_stmnt;

  lc_cursor := dbms_sql.open_cursor;

• DDL statements are executed by the parse call, which
• performs the implied commit dbms_sql.parse (lc_cursor, shorttext, dbms_sql.native); ln_return := dbms_sql.execute (lc_cursor); dbms_sql.close_cursor (lc_cursor); exception when others then
  • Schließen des Cursors im Fehlerfall if dbms_sql.is_open (lc_cursor) then dbms_sql.close_cursor (lc_cursor); end if; end exec_stmnt;

 member function exec_stmnt
   return number
   is

      lc_cursor                     integer;   -- Cursor für Ausführung
      ln_return                     number;   -- Return Wert
      ln_rows_proc      number;

   begin

• TODO: Debugging für die Funktion EXEC_STMNT;
  • Fehlermeldung: "(1): PLS-00363: Ausdruck 'SELF' kann nicht als Zuweisungsziel benutzt werden"
• Debug Infos schreiben
• debug_stmnt; --XXXX
  • Stmnt ausführen und Ergebnis in einer Varchar-Variable zurückgeben lc_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(lc_cursor, shorttext, DBMS_SQL.NATIVE); DBMS_SQL.define_column( lc_cursor, 1, ln_return ); ln_rows_proc := DBMS_SQL.EXECUTE(lc_cursor); IF DBMS_SQL.fetch_rows (lc_cursor) > 0 THEN DBMS_SQL.column_value(lc_cursor, 1, ln_return); END IF; DBMS_SQL.CLOSE_CURSOR(lc_cursor);

      return ln_return;
   exception

      when others
      then
         -- Schließen des Cursors im Fehlerfall
         if dbms_sql.is_open (lc_cursor)
         then
            dbms_sql.close_cursor (lc_cursor);
         end if;

         return null;

   end exec_stmnt;

end;
/ Received on Fri Feb 13 2004 - 14:30:15 CST