Re: Object Types

Hello Oliver,

  See, if modifying exec_stmnt as below (both in type and body) helps

   member function exec_stmnt (SELF IN OUT sql_stmnt ) return number.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Oliver Stratmann" <yxcsstratmoqwert_at_gmx.de> wrote in message news:c0jc4o$17rflq$1_at_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 Sat Feb 14 2004 - 16:58:19 CST