Solved

PL/SQL: ORA-00984

Posted on 2006-07-13
24
1,824 Views
Last Modified: 2012-05-05

 In a procedure that is part of the package get the following error:

[QueueBridge]: Reading...
07/13/2006 10:40:20 Error 0 Low level InputterThread.cpp35

Comment     : enqueue
Message     : ORA-06550: line 1, column 174:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 1, column 9:
PL/SQL: SQL Statement ignored
ORA-06512: at "LBENN.FTI_QUEUE", line 2406
ORA-06512: at line 1

Procedure is below. It started when dinamic SQL was added with execute immediate part of it.

PROCEDURE Enqueue_Reply_Message
      (
          msg_typ                in VARCHAR2     DEFAULT 'REAL',
          job_id                     in VARCHAR2     DEFAULT NULL,
          trn_id                     in CHAR             DEFAULT NULL,
          orig_trn_id            in CHAR              DEFAULT NULL,

          msg_key                in VARCHAR2     DEFAULT NULL,
          grp_key                in VARCHAR2     DEFAULT NULL,

          msg_data               in BLOB            DEFAULT NULL,

          que_name               in VARCHAR2,
          consumer_name         in VARCHAR2     DEFAULT NULL,

          excep_que              in VARCHAR2     DEFAULT NULL,
          reply_que              in VARCHAR2     DEFAULT NULL,
          remote_que           in VARCHAR2     DEFAULT NULL,
          subscriber_que         in VARCHAR2     DEFAULT NULL,
          subscriber_name         in VARCHAR2     DEFAULT NULL,

          correlation            in VARCHAR2     DEFAULT NULL,
          priority               in INTEGER      DEFAULT 1,

          status            in VARCHAR2      DEFAULT 'DONE',

          reply_status           in BLOB         DEFAULT NULL,
          reply_msf_file_name in VARCHAR2     DEFAULT NULL,
          cmd_data               in BLOB         DEFAULT NULL,
          exp_msg_data        in BLOB         DEFAULT NULL,

          source_id           in VARCHAR2     DEFAULT NULL,
       severity            in NUMBER       DEFAULT 0,
       ext_source_id       in VARCHAR2     DEFAULT NULL,

          msg_id                 out RAW
      )
      AS
         eopt               dbms_aq.enqueue_options_t;
         mprop              dbms_aq.message_properties_t;
         dopt               dbms_aq.dequeue_options_t;
         msgid              dopt.msgid%TYPE;
         message            FTI_Message_t;
          rcpt_list          dbms_aq.aq$_recipient_list_t;
         rcpt_cnt            PLS_INTEGER;

               no_recipient         exception;
         pragma exception_init( no_recipient, -24033 );

         dummy            integer;
         stmt                  varchar2(250);

      BEGIN
            if (g_timing_on) then
                     TRACE('ENQUEUE_INPUT_MESSAGE: enqueue message time '||msgid,
                        elapsed_time(g_start_time));
               end if;
               begin
                  msg_id:= null;

                   -- if use table base input only reply rejected messages
                        if (g_use_input_table_be and status <>'REJECT') then
                                raise no_recipient;
                        end if;


                  mprop.exception_queue:= excep_que;
                  if (dbms_lob.getlength( msg_data ) <= 0) then
                        raise no_data_found;
                  end if;
                      if (g_timing_on) then g_start_time:=start_timer(); end if;
                      message:= FTI_Message_t(
                              FTI_Message_Trx_t( null,
                                          reply_que,
                                          subscriber_que,
                                          subscriber_name,
                                          job_id,
                                          trn_id,
                                          orig_trn_id),
                                         FTI_Message_Info_t( msg_typ,
                                                       msg_key,
                                           grp_key,
                                           reply_msf_file_name,
                                           cmd_data,
                                           exp_msg_data ),
                              msg_data,
                              reply_status,
                              status,
                              source_id,
                              severity,
                              ext_source_id);

                    if (g_use_input_table_qb) then

                               g_tablename := 'FT_O_'||upper(que_name);

                         if (g_ins_curs=0) then

                              g_ins_curs := 1;
                        --      g_ins_curs := dbms_sql.open_cursor;

                              g_session_id:= userenv('SESSIONID')* 100000000;

                          --      stmt:= ' insert into '|| g_tablename
                        --            || ' ( prio, correl, msg, msgid, enq_time, status )'
                        --            || ' values ( :1, :2, :3, '
                        --            || ' utl_raw.cast_from_number(g_session_id + bein_seq.nextval),'
                        --            || ' sysdate, g_new_status )'
                        --            || ' returning msgid into :4';

                        --      dbms_sql.parse(g_ins_curs, stmt, dbms_sql.native );
                        end if;

                        --dbms_sql.bind_variable(g_ins_curs, '1', priority);
                        --dbms_sql.bind_variable(g_ins_curs, '2', correlation);
                        --dbms_sql.bind_variable(g_ins_curs, '3', message); -- > Does not work!
                        --dbms_sql.bind_variable(g_ins_curs, '4', msg_id);

                        --dummy := dbms_sql.execute(g_ins_curs);
                        --dbms_sql.variable_value(g_ins_curs, '4', msg_id);

                          stmt:= ' begin '|| ' insert into '|| g_tablename
                                    || ' ( prio, correl, msg, msgid, enq_time, status )'
                                    || ' values ( :1, :2, :3, '
                                    || ' utl_raw.cast_from_number(g_session_id + bein_seq.nextval),'
                                    || ' sysdate, g_new_status )'
                                    || ' returning msgid into :4 ; end;';
                                execute immediate stmt USING priority, correlation, message, OUT msg_id;

                        if (g_put_count>=g_commit_cursor_size or g_put_count=0) then
                              g_put_count:=1;
                                     INSERT INTO FT_O_BERG
                              (s_id, proc_id, host_id, table_name, status,
                                     min_msgid, max_msgid, lock_tms)
                              values
                                     ( g_any_status, g_any_status, g_any_status, g_tablename,
                                    g_new_status, msg_id, msg_id, sysdate)
                              returning rowid into g_berg_rowid;
                        else
                              begin
                                g_put_count:=g_put_count+1;
                                       UPDATE FT_O_BERG
                                SET       max_msgid = msg_id,
                                    lock_tms=sysdate
                                where rowid = g_berg_rowid
                                and   status=g_new_status;  -- prevent engine lock
                                 exception
                              when no_data_found then
                                    -- Berg was already picked up by the engine.
                                    g_put_count:=1;
                                           INSERT INTO FT_O_BERG
                                    (s_id, proc_id, host_id, table_name, status,
                                          min_msgid, max_msgid, lock_tms)
                                    values
                                           ( g_any_status, g_any_status, g_any_status, g_tablename,
                                          g_new_status, msg_id, msg_id, sysdate)
                                    returning rowid into g_berg_rowid;
                              end;
                        end if;
             else -- no input table qb
                rcpt_cnt:=0;
                  -- default 2nd consumer-name = 'KEY'
                  if (consumer_name is not null) then
                      rcpt_list(rcpt_cnt):= sys.aq$_agent(consumer_name, remote_que, null);
                        mprop.recipient_list:= rcpt_list;
                  else
                      if (remote_que is not null) then
                                rcpt_list(rcpt_cnt):= sys.aq$_agent(null, remote_que, null);
                              mprop.recipient_list:= rcpt_list;
                      end if;
                end if;
                mprop.priority   := priority;
                mprop.correlation:= correlation;

                dbms_aq.enqueue
                (
                 queue_name         => que_name,
                 enqueue_options    => eopt,
                 message_properties => mprop,
                 payload            => message,
                 msgid              => msgid
                );
                if (g_timing_on) then
                  TRACE('ENQUEUE_INPUT_MESSAGE: enqueue message time '||msgid,elapsed_time(g_start_time));
                end if;

                TRACE('ENQUEUE_INPUT_MESSAGE: msg_len  '||dbms_lob.getlength( msg_data ), g_trace_level_info);
                if (reply_status is not null and
                    dbms_lob.getlength( reply_status )>0) then
                   TRACE('ENQUEUE_INPUT_MESSAGE: reply_len    '||dbms_lob.getlength( reply_status ), g_trace_level_info);
                end if;
                msg_id:= msgid;
            end if;
             if (g_timing_on) then
                  TRACE('ENQUEUE_INPUT_MESSAGE: time enqueue done '||msgid,elapsed_time(g_start_time));
            end if;
            -- Set action info
            DBMS_APPLICATION_INFO.SET_ACTION(g_idle_status);
         exception
            when no_recipient then
                        msg_id:= null;
                        -- Let the calling code handle this exception
                        raise;
            when others then
                        msg_id:= null;
                        TRACE('ENQUEUE_INPUT_MESSAGE: problem writing to queue '|| que_name, g_trace_level_error);
                        catch_error( SQLCODE, 'ENQUEUE_INPUT_MESSAGE: problem writing to queue '||que_name|| ' error: '  || substr(SQLERRM, 1, 200) );
                        raise;
         end;
      END;

thank you very much in advance,
Mia


0
Comment
Question by:milanushka
  • 15
  • 5
  • 2
  • +1
24 Comments
 

Author Comment

by:milanushka
ID: 17103452

Table that dinamic sql is inserting into is

SQL> desc ft_o_bein
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MSGID                                     NOT NULL RAW(16)
 ENQ_TIME                                  NOT NULL TIMESTAMP(6)
 PRIO                                               NUMBER(38)
 CORREL                                             VARCHAR2(132)
 MSG                                                FTI_MESSAGE_T
 STATUS                                    NOT NULL CHAR(1)
 DEQ_TIME                                           TIMESTAMP(6)
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17105732
What is the structure of table FT_O_BERG

SQL> desc FT_O_BERG
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17105741
0
 

Author Comment

by:milanushka
ID: 17107732

 It is a table version of a queue table. It gets populated from a text file
0
 

Author Comment

by:milanushka
ID: 17107755

 I know what that cause and fix for ORA-00984 is column name in VALUE section of insert statement. But as you can see this is not the case in our insert. It must be something else. It worked before we made insert into a dinamic SQL

Thanks,
Mia
0
 
LVL 31

Expert Comment

by:awking00
ID: 17108332
I'm thinking maybe the begin and end statements are not needed in the following section.
 stmt:= ' insert into '|| g_tablename
                              || ' ( prio, correl, msg, msgid, enq_time, status )'
                              || ' values ( :1, :2, :3, '
                              || ' utl_raw.cast_from_number(g_session_id + bein_seq.nextval),'
                              || ' sysdate, g_new_status )'
                              || ' returning msgid into :4';
                                execute immediate stmt USING priority, correlation, message, OUT msg_id;

0
 

Author Comment

by:milanushka
ID: 17108686


There is no BEGIN/END for that section. Also, I hardcoded all VALUES and still got same error. Is there any way to see which column is "column 174" that error message complains about?

This is a very importent to fix! Please help

Thanks,
Mia
0
 
LVL 31

Expert Comment

by:awking00
ID: 17108803
This is copied directly from your code above with the begin and end statements to which I was referring.
stmt:= ' begin '|| ' insert into '|| g_tablename
                              || ' ( prio, correl, msg, msgid, enq_time, status )'
                              || ' values ( :1, :2, :3, '
                              || ' utl_raw.cast_from_number(g_session_id + bein_seq.nextval),'
                              || ' sysdate, g_new_status )'
                              || ' returning msgid into :4 ; end;';
                                execute immediate stmt USING priority, correlation, message, OUT msg_id;

However, it doesn't appear that it has anything to do with line 1, column 174. Can you post the first half dozen or so lines from your package code using copy and paste to get an exact replica? There may be something there we're not seeing.
0
 

Author Comment

by:milanushka
ID: 17108840

 I removed begin and end and got invalid character.

                                stmt:= ' insert into '|| 'ft_o_bein'
                                    || ' ( prio, correl, msg, msgid, enq_time, status )'
                                    || ' values ( 10, XXX, MMM, 20) '
                                    || ' returning msgid into :4; ';
                                execute immediate stmt USING OUT msg_id;

Begining of the package looks like this

CREATE OR REPLACE PACKAGE BODY FTI_Queue AS
      -- ==============================
      -- Global variables
      -- ==============================
      g_lock_handle            varchar2(128);
      g_first_msg            pls_integer      :=0;
      g_queue_name             varchar2(64);
      g_queue_table            varchar2(64);
      g_put_count             number  :=0;
      g_commit_cursor_size    number  :=50;
      g_get_count             number  :=0;
      g_cursor_size           number  :=1000;
      g_start_time            number      :=0;
      g_bemk_row_id         rowid      :=null;
      g_timing_on           boolean := false;
      g_proc_start_time       date;

      g_unlock_status         varchar2(16)    :='DONE';  -- default lookahaed turned on

      g_active_status         varchar2(16)    :='ACTIVE';
      g_idle_status               varchar2(16)    :='IDLE';
      g_done_status               varchar2(16)    :='DONE';
      g_ready_status               varchar2(16)    :='READY';

      g_nokey_value               varchar2(16)    :='NOKEY';

      g_trace_level_error      number :=1;
      g_trace_level_warning      number :=2;
      g_trace_level_info      number :=3;

      -- ==============================
      -- for usage of Inputtable instead of queue
      -- ==============================
      g_use_input_table_qb       boolean := false;
      g_use_input_table_be       boolean := false;

      type ref_crsr_type      is REF CURSOR;
      type row_id_type  is table of ROWID index by binary_integer;

      row_id_tbl              row_id_type;
      row_id_tbl_index        number      :=0;
      row_id_tbl_index_nr       number      :=1;

      g_new_status               char(1) := 'N';
      g_run_status               char(1) := 'X';
      g_end_status         char(1) := 'D';
      g_any_status               char(3) := 'ANY';
      g_e_status               char(1) := ' ';
      g_min_msgid            raw(16);
      g_max_msgid            raw(16);

      g_berg_rowid            rowid;
      g_session_id            number      :=0;

      g_ins_curs               integer      :=0;
      g_upd_curs               integer      :=0;
      g_del_curs               integer      :=0;
      g_sel1_curs               integer      :=0;
      g_sel2_curs               integer      :=0;

      g_tablename            varchar2(16) := 'FT_O_BEIN';

      -- *****************************************
      -- ***  Common procs                     ***
      -- *****************************************

      PROCEDURE Version
      AS
            version_label char(100);
      BEGIN
           dbms_output.enable(1000000);

          select  sysdate
          into    g_proc_start_time
          from    dual;

                version_label := 'FTI_QUEUE: process start time'||g_proc_start_time;
                dbms_output.put_line( version_label);

            version_label := 'FTI_QUEUE: @(#)FTI CC USER: dstadler@(#)';
              dbms_output.put_line( version_label);
            TRACE(version_label,g_trace_level_info);

            version_label := 'FTI_QUEUE: @(#)ARCHIVE:/vobs/eServer/sql/FTIQPkg.sql@(#)';
              dbms_output.put_line( version_label);
            TRACE(version_label,g_trace_level_info);

            version_label := 'FTI_QUEUE: @(#)REVISION:/main/eserver_int/at_eserver_bugfix_7_3_general/2@(#)';
              dbms_output.put_line( version_label);
            TRACE(version_label,g_trace_level_info);

            version_label := 'FTI_QUEUE: @(#)CHECKED IN: Thu Mar 30 12:29:29 EDT 2005@(#)';
              dbms_output.put_line( version_label);
            TRACE(version_label,g_trace_level_info);

      END;

      -- ==============================
      -- Turn timing on
      -- ==============================

      PROCEDURE TurnTimingsOn
      AS
      BEGIN
            g_timing_on:=true;
      END;

      -- ==============================
      -- Turn timing off
      -- ==============================

      PROCEDURE TurnTimingsOff
      AS
      BEGIN
            g_timing_on:=false;
      END;

      -- ==============================
      -- Set Cursorsize
      -- ==============================

      PROCEDURE SetCursorSize
      (
            p_cursor_size      in number
      )
      AS
      BEGIN
            g_cursor_size:=p_cursor_size;
      END;

      -- ==============================
      -- Set Commit Cursorsize
      -- ==============================

      PROCEDURE SetCommitCursorSize
      (
            p_cursor_size      in number
      )
      AS
      BEGIN
            g_commit_cursor_size:=p_cursor_size;
      END;

        -- ==============================
      -- Enable Look ahaed
      -- ==============================

      PROCEDURE EnableLookAhaed
      AS
      BEGIN

          TRACE( 'EnableLookAhead',g_trace_level_info);
            g_unlock_status:='DONE';
      END;

      -- ==============================
      -- Disable Look ahaed
      -- ==============================

      PROCEDURE DisableLookAhaed
      AS
      BEGIN
            g_unlock_status:='IDLE';
      END;

      -- ==============================
      -- Set Use Input Table For QB
      -- ==============================
      PROCEDURE use_input_table_qb AS
      BEGIN
            g_use_input_table_qb := true;
               TRACE('USE TABLE INPUT!!!' ,g_trace_level_info);
      END;

      -- ==============================
      -- Set Use Input Table For BE
      -- ==============================
      PROCEDURE use_input_table_be AS
      BEGIN
            g_use_input_table_be := true;
               TRACE('USE TABLE INPUT!!!' ,g_trace_level_info);
      END;


      -- ==============================
      -- Error Handling Routine
      -- ==============================

      PROCEDURE catch_error
      (
          error_code   in number,
          error_string in varchar2
      )
      AS
      BEGIN
          dbms_output.enable(1000000);

          dbms_output.put_line( 'Oracle Error = ' || to_char (error_code) );
          dbms_output.put_line( error_string );
          TRACE( 'Oracle Error = ' || to_char (error_code), g_trace_level_error );
          TRACE( error_string, g_trace_level_error );
      END;

      -- ==============================
      -- Strip Exception Queue Name
      -- ==============================
      FUNCTION stripExceptQName (queue_name IN VARCHAR2)
         RETURN VARCHAR2
      IS
         /* Exception queue = _AQ$FT_Q_<Qname>_E */

         /* Location of third underscore */
         first_delim_loc  NUMBER := INSTR (queue_name, '_', 1, 3);

         /* Location of last underscore */
         second_delim_loc NUMBER := INSTR (queue_name, '_', -1);

         /* Return value of function, set by default to incoming string. */
         return_value VARCHAR2(1000) := queue_name;
      BEGIN
         /* Only switch words if two delimiters are found. */
         IF SUBSTR(queue_name,1,4) = '_AQ$' and second_delim_loc > 0
         THEN
            /* Pull out first and second words . */
           return_value :=
                     SUBSTR (queue_name, first_delim_loc +1, second_delim_loc - first_delim_loc -1);
         END IF;

         /* Return the stripped string */
         RETURN return_value;
      END stripExceptQName;

      -- ==============================
      -- Start timer
      -- ==============================
      FUNCTION start_timer
      RETURN NUMBER
      AS
            start_time       number;
      BEGIN
                select
                           to_number(to_char(systimestamp,'YYMMDDHH24MISS.FF6'),
                         '99999999999999.999999')
                     into start_time
                         from dual;
            return(start_time);
      END;

      -- ==============================
      -- Start timer
      -- ==============================
      FUNCTION elapsed_time
      (
            p_start_time       in number
      )
      RETURN NUMBER
      AS
            elapsed_time      number;
      BEGIN
                select
                           to_number(to_char(systimestamp,'YYMMDDHH24MISS.FF6'),
                         '99999999999999.999999') - p_start_time
                     into elapsed_time
                         from dual;
            --TRACE('ELAPSED_TIME: '||elapsed_time||' start '||p_start_time);
            return (elapsed_time);
      END;

      -- ==============================
      -- Register for Alert Signal
      -- ==============================
      PROCEDURE Register_For_Signal
      (
          service_id   in varchar2
      )
      AS
      BEGIN
               dbms_alert.register('SIGNAL_'||service_id);
      exception
         when others then
              TRACE('Register_for_signal: Problem register signal !',
                  g_trace_level_error);
              raise;
         END;

      -- ==============================
         -- Unregister for Alert Signal
      -- ==============================
      PROCEDURE Unregister_For_Signal
      (
          service_id   in varchar2
      )
      AS
      BEGIN
               dbms_alert.remove('SIGNAL_'||user||'_'||service_id);
         END;

      -- ==============================
      -- Wait for an Alert Signal to occur
      -- ==============================
      FUNCTION Wait_For_Signal
      (
          service_id   in varchar2,
          wait         in integer default 10
      )
      RETURN PLS_INTEGER
      AS
          alert_status integer;
          alert_msg        char(80);
      BEGIN
            dbms_alert.waitone('SIGNAL_'||user||'_'||service_id, alert_msg, alert_status, wait);
            return(alert_status);
      END;

      -- ==============================
         -- Signal Alert
      -- ==============================
      PROCEDURE Signal_Alert
      (
          service_id   in varchar2
      )
      AS
      BEGIN
            dbms_alert.signal('SIGNAL_'||user||'_'||service_id, 'alert message');
      END;

      -- ==============================
      -- Allocate Lock Handle
      -- ==============================
      --
      -- This does a commit !
      --
      PROCEDURE Allocate_Lock
      (
          service_id   in varchar2
      )
      AS
      begin
            dbms_lock.allocate_unique('LOCK_'||user||'_'||service_id,g_lock_handle);
      exception
            when others then
              catch_error( SQLCODE, 'ALLOCATE_LOCK: error ' || substr(SQLERRM, 1, 200) );
            raise;
      end;

      -- ==============================
      -- Request for an Lock
      -- ==============================
      -- Return values:
      --       0  - success
      --       1  - timeout
      --       2  - deadlock
      --       3  - parameter error
      --       4  - already owned lock specified by ?id? or ?lockhandle?
      --       5  - illegal lockhandle
      --       -1 - sql-error
      --
      FUNCTION Request_Lock
      (
          wait          in integer default 10
      )
      RETURN PLS_INTEGER
      AS
      begin
                    return dbms_lock.request(
                         lockhandle        => g_lock_handle,
                              lockmode          => dbms_lock.x_mode,
                              timeout           => wait,
                              release_on_commit => true);
      exception
            when others then
               catch_error( SQLCODE, 'REQUEST_LOCK: error ' || substr(SQLERRM, 1, 200) );
             return -1;
      end;

      -- ==============================
      -- Release Lock
      -- ==============================
      -- Lock is normally released on commit
      --
      -- Return values:
      --       0  - success
      --      3 - parameter error
      --      4 - don't own lock specified by 'id' or 'lockhandle'
      --      5 - illegal lockhandle

      FUNCTION Release_Lock
      RETURN PLS_INTEGER
      AS
      begin
               return dbms_lock.release(
                         lockhandle => g_lock_handle);
      exception
            when others then
               catch_error( SQLCODE, 'RELEASE_LOCK: error ' || substr(SQLERRM, 1, 200) );
             return -1;
      end;

      -- ****************************************************
      -- ********* API for the QAgen processes  *************
      -- ****************************************************

      -- ==========================================
      -- Put New Message Key in MQKY
      -- ==========================================
      PROCEDURE Put_New_Message_Key
      (
          p_service_id   in varchar2,
          p_input_que    in varchar2,
          p_job_id       in varchar2  DEFAULT NULL,
          p_trn_id       in char      DEFAULT NULL,
          p_msg_key      in varchar2 default 'NOKEY',
          p_grp_key      in varchar2 default 'NOKEY',
          p_msg_typ      in varchar2,
            p_msf_file     in varchar2,
          p_priority     in PLS_INTEGER,
          p_status       in varchar2 default 'READY',
          p_msg_id       in out raw
      )
      AS
         que_name            varchar2(64);

         duplicate_key  exception;
         pragma exception_init( duplicate_key, -1 );

      begin
            que_name      := stripExceptQName(p_input_que);

            insert into ft_o_mqky
                        ( que_name,
                    msg_id,
                    s_id,
                    job_id,
                    trn_id,
                    msg_key,
                    grp_key,
                    msf_file_name,
                    priority,
                    status,
                    created_tms,
                    row_seq )
            values (  que_name,
                    p_msg_id,
                    p_service_id,
                    p_job_id,
                    p_trn_id,
                    p_msg_key,
                    p_grp_key,
                    p_msf_file,
                    p_priority,
                    p_status,
                    sysdate,
                    mqky_seq.nextval );
            TRACE('PUT_NEW_MESSAGE_KEY (mqkyk):, values:'
                    || ' sid ' || p_service_id
                  || ',msg ' || p_msg_id
                  || ',msg_key ' || p_msg_key
                  || ',grp_key ' || p_grp_key
                  || ',status ' || p_status,
                  g_trace_level_info );
      exception
            when duplicate_key then
            -- ignore this message, it's already in the mqky table
            null;
            when others then
               catch_error( SQLCODE, 'PUT_NEW_MESSAGE_KEY: error inserting mqky ' || substr(SQLERRM, 1, 200) );
             p_msg_id:=null;
      end;

      -- ==========================================
      -- Change Status from Message Keys in MQKY
      -- ==========================================
      PROCEDURE Change_Message_Keys_Status
      (
          p_service_id         in varchar2,
          p_input_que          in varchar2  DEFAULT NULL,
          p_job_id             in varchar2  DEFAULT NULL,
          p_trn_id             in char      DEFAULT NULL,
          p_from_status         in varchar2 default 'HOLD',
          p_to_status         in varchar2 default 'READY'
        )
      AS
         pragma autonomous_transaction;
      begin
             update /*+ index(ft_o_mqky,ft_o_mqky_i001) */
              ft_o_mqky
              set status = p_to_status
              where s_id = p_service_id
              and (p_input_que is null or que_name = p_input_que )
                    and (p_job_id is null or job_id = p_job_id)
                    and (p_trn_id is null or trn_id = p_trn_id)
              and status = p_from_status;
            signal_alert(p_service_id);
            COMMIT WORK;
         exception
            when no_data_found then
                  null;
                  when others then
                  catch_error( SQLCODE, 'ACTIVATE_MESSAGE_KEYS: error update mqky ' || substr(SQLERRM, 1, 200) );
                raise;
      end;


      -- ==============================
      -- Get_Message_MSF_File_Name
      -- ==============================
      PROCEDURE Get_MSF_File_Name
      (
          p_queue_name       in VARCHAR2,
          p_msg_typ             in VARCHAR2,
          p_msf_file_name       out VARCHAR2
      )
      AS
            l_row_id             ROWID;
      BEGIN
        Get_Message_Type_Info_Row(
                p_queue_name,
                p_msg_typ,
                l_row_id);
         if (l_row_id is not null) then
         begin
            SELECT       msf_file_name
            INTO       p_msf_file_name
            FROM       ft_o_mstf
            WHERE       rowid = l_row_id;
         exception
                 when others then
                        catch_error( SQLCODE, 'GET_MSF_File_name Error select mstf ' || substr(SQLERRM, 1, 200) );
                p_msf_file_name:= null;
                raise;
         end;
      else
          p_msf_file_name:= null;
      end if;
      END;

      -- ==============================
      -- Get_Message_Key_Info
      -- ==============================

      PROCEDURE Get_Message_Key_Info
      (
          p_queue_name       in VARCHAR2,
          p_msg_typ             in VARCHAR2,
          p_msf_file_name       out VARCHAR2,
          p_use_key             out CHAR,
          p_ds_id              out CHAR,
          p_ds_num            out PLS_INTEGER,
          p_fld_id            out CHAR,
          p_segment_name       out VARCHAR2,
          p_segment_occur      out PLS_INTEGER,
          p_element_name       out VARCHAR2
      )
      AS
            l_row_id             ROWID;
      BEGIN
        Get_Message_Type_Info_Row(
                p_queue_name,
                p_msg_typ,
                l_row_id);
        if (l_row_id is not null) then
         begin
               SELECT       msf_file_name, use_key,
                  ds_id, ds_num, fld_id,
                            segment_name, segment_occurence, element_name
             INTO       p_msf_file_name, p_use_key,
                       p_ds_id, p_ds_num, p_fld_id,
                       p_segment_name, p_segment_occur, p_element_name
            FROM       ft_o_mstf
            WHERE       rowid = l_row_id;
         exception
                 when others then
                        catch_error( SQLCODE, 'GET_Message_Key_Info Error select mstf ' || substr(SQLERRM, 1, 200) );
                 p_msf_file_name:= null;
                 raise;
         end;
        else
          p_msf_file_name:= null;
        end if;
      END;
0
 

Author Comment

by:milanushka
ID: 17109078

 I cretaed trace to show what actual statement is produced and what I got was following

2006-07-14 10.48.04   Populating F_O_BEIN insert into ft_o_bein ( prio, correl, msg, msgid, enq_time, status ) values ( 10, XXX, MMM
,  20, returning msgid into :4 ;
2006-07-14 10.48.04   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 10.48.04   Oracle Error = -917
2006-07-14 10.48.04   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00917: missing comma
2006-07-14 10.58.28   USE TABLE INPUT!!!
2006-07-14 10.58.30   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 10.58.30   Oracle Error = -911
2006-07-14 10.58.30   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00911: invalid character
2006-07-14 11.12.07   USE TABLE INPUT!!!
2006-07-14 11.12.09   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 11.12.09   Oracle Error = -911
2006-07-14 11.12.09   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00911: invalid character
2006-07-14 11.18.11   USE TABLE INPUT!!!
2006-07-14 11.18.13   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 11.18.13   Oracle Error = -911
2006-07-14 11.18.13   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00911: invalid character
0
 

Author Comment

by:milanushka
ID: 17109422
2006-07-14 11.34.36   Populating F_O_BEIN insert into F_O_BEIN ( prio, correl, msg, msgid, enq_time, status ) values ( 10, XXX, MMM,
 20, sysdate, A ) returning msgid into msg_id ;
2006-07-14 11.34.36   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 11.34.36   Oracle Error = -911
2006-07-14 11.34.36   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00911: invalid character
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:milanushka
ID: 17109567

 Put higher level tracing and it looks like this particular insert is ok. Must be something else in the procedure

2006-07-14 12.02.16   Populating F_O_BEIN begin  insert into FT_O_BEIN ( prio, correl, msg, msgid, enq_time, status ) values ( :1, :
2, :3,  utl_raw.cast_from_number(g_session_id + bein_seq.nextval), sysdate, g_new_status ) returning msgid into :4 ; end;
2006-07-14 12.02.16   Oracle Error = 0
2006-07-14 12.02.16   ENQUEUE_INPUT_MESSAGE_AUTONOM: Problem Inserting into FT_O_BEIN error: ORA-0000: normal, successful completion
2006-07-14 12.02.16   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 12.02.16   Oracle Error = -6550
2006-07-14 12.02.16   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-06550: line 1, column 174:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 1, column 9:
PL/SQL: SQL Statement ignored
~
0
 
LVL 31

Expert Comment

by:awking00
ID: 17109577
>> I removed begin and end and got invalid character.<<
I think you also need to remove the semi-colon inside the string -
                           stmt:= ' insert into '|| 'ft_o_bein'
                              || ' ( prio, correl, msg, msgid, enq_time, status )'
                              || ' values ( 10, XXX, MMM, 20) '
                              || ' returning msgid into :4 ';
                                                                    ^
                                                      The one that was here

                                execute immediate stmt USING OUT msg_id;

0
 

Author Comment

by:milanushka
ID: 17109623


Thank you again for your responce.

 If I remove it, wouldn't  insert statement be generated without semi-colon and therefore not get executed. Plus, if you look at my last trace output it shows that that particular statement is OK. It must be something else in procedure then

Thanks,
mia
0
 

Author Comment

by:milanushka
ID: 17109667
Please desregard my previose message. i put tracing before execution of the insert so at  execute is where it mey still be breaking

Thanks
0
 
LVL 31

Expert Comment

by:awking00
ID: 17109894
Typically an execute immediate statement might take the form of
execute immediate
'insert into table select * from other_table';
In your case, when you assign those concatenated values to the string variable, stmt, you're just replacing what would typically go in between the quotes.
The way you have it now would be like this -
execute immediate 'insert into ... returning msgid into :4;' using out msg_id;
when it should be
execute immediate 'insert into ... returning msgid into :4 using out msg_id';

Try this
stmt:= ' insert into '|| 'ft_o_bein'
                              || ' ( prio, correl, msg, msgid, enq_time, status )'
                              || ' values ( 10, XXX, MMM, 20) '
                              || ' returning msgid into :4 '
                              || ' using out msg_id';   <= this semi-colon completes the string assignment
execute immediate stmt;   <= this semi-colon completes the execute immediate statement
0
 

Author Comment

by:milanushka
ID: 17110332

 When I do that I get "Command not properly ended"

2006-07-14 13.49.10   Populating F_O_BEIN insert into ft_o_bein ( prio, correl, msg, msgid, enq_time, status ) values ( 10, XXX, MMM
, 20, sysdate, A)  returning msgid into :4  using out msg_id
2006-07-14 13.49.10   Oracle Error = 0
2006-07-14 13.49.10   ENQUEUE_INPUT_MESSAGE_AUTONOM: Problem Inserting into FT_O_BEIN error: ORA-0000: normal, successful completion
2006-07-14 13.49.10   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 13.49.10   Oracle Error = -933
2006-07-14 13.49.10   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00933: SQL command not properly ended
0
 

Author Comment

by:milanushka
ID: 17110512

 now I am back to original error. Is there something about execute immediate that would not allow blobs or row types to insert?

2006-07-14 14.04.18   Populating F_O_BEIN insert into ft_o_bein ( prio, correl, msg, msgid, enq_time, status ) values ( 10, XXX, MMM
, 20, sysdate, A)  returning msgid into :4
2006-07-14 14.04.18   Oracle Error = 0
2006-07-14 14.04.18   ENQUEUE_INPUT_MESSAGE_AUTONOM: Problem Inserting into FT_O_BEIN error: ORA-0000: normal, successful completion
2006-07-14 14.04.18   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN
2006-07-14 14.04.18   Oracle Error = -984
2006-07-14 14.04.18   ENQUEUE_INPUT_MESSAGE: problem writing to queue BEIN error: ORA-00984: column not allowed here
~
0
 

Author Comment

by:milanushka
ID: 17110741

 When try to execute manually get error
SQL> insert into ft_o_bein ( prio, correl, msg, msgid, enq_time, status ) values ( 10, 'XXX', 'M', '20', sysdate, 'A')
  2  ;
insert into ft_o_bein ( prio, correl, msg, msgid, enq_time, status ) values ( 10, 'XXX', 'M', '20', sysdate, 'A')
                                                                                         *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected LBENN.FTI_MESSAGE_T got CHAR


SQL> desc FTI_MESSAGE_T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MSG_TRX                                            FTI_MESSAGE_TRX_T
 MSG_INFO                                           FTI_MESSAGE_INFO_T
 MSG_DATA                                           BLOB
 REPLY_DATA                                         BLOB
 STATUS                                             VARCHAR2(16)
 SOURCE_ID                                          VARCHAR2(256)
 SEVERITY                                           NUMBER
 EXT_SOURCE_ID                                      VARCHAR2(256)
0
 

Author Comment

by:milanushka
ID: 17111487


 Found solution

thank you all for your help!!!

Good weekend to all!
0
 
LVL 31

Expert Comment

by:awking00
ID: 17114620
Glad you got things resolved. Can you fill us in on the solution? Thanks.
0
 

Author Comment

by:milanushka
ID: 17121810

Dynamic sql only likes bind variables. I had to change the code from

stmt:= ' begin '|| ' insert into '|| g_tablename
                || ' ( prio, correl, msg, msgid, enq_time, status )'
                || ' values ( :1, :2, :3, '
                || ' utl_raw.cast_from_number(g_session_id +                                            bein_seq.nextval),'
                || ' sysdate, g_new_status )'
                || ' returning msgid into :4 ; end;';
  execute immediate stmt USING priority, correlation, message, OUT msg_id;

changed to


tmsg_id      RAW(16)

select utl_raw.cast_from_number(g_session_id + bein_seq.nextval) into msg_id                                                                from dual;
                        
stmt:= 'insert into '|| g_tablename
                        || ' ( prio, correl, msg, msgid, enq_time, status )'
                     || ' values ( :1, :2, :3, :5, :6, :7 )'
                     || ' returning msgid into :8 ';
               
execute immediate stmt USING priority, correlation, message, tmsg_id,                                             sysdate, g_new_status, OUT msg_id;
      

0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 17695620
PAQed with points refunded (200)

Computer101
EE Admin
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now