1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Insert values from select not working

Discussion in 'SQL PL/SQL' started by Girishr369, May 26, 2017.

  1. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad
    Hi ,

    can any one suggest me on my below issue

    I created one table and try to insert the log into the table by selecting the values from - v$session.
    I have proper authorities on v$session - table , and I am able to query from out side of the procedure.

    actually i am planning to use this process to capture the log when ever i have an issue in the program.


    CREATE TABLE err

    (

    Sid NUMBER ,

    OsUser VARCHAR(20),

    Machine VARCHAR(20),

    Process VARCHAR(12),

    Program VARCHAR(100),

    Module VARCHAR(100),

    Action VARCHAR(100),

    ErrorMsg VARCHAR2(4000),

    Backtrace CLOB ,

    Callstack CLOB

    );

    PROCEDURE
    create or replace procedure errproc as


    begin

    insert into err

    (

    Sid,

    OsUser,


    Machine ,

    Process ,


    Program ,


    Module,


    Action

    ErrorMsg ,


    Backtrace

    -- Callstack


    )


    select

    Sid ,


    OsUser,


    Machine,


    Process,


    Program,


    Module,


    Action

    DBMS_UTILITY.format_error_stack,


    DBMS_UTILITY.format_error_backtrace

    --DBMS_UTILITY.format_error_call_stack

    FROM v$session

    WHERE sid = sys_context('USERENV','SID');


    end errproc ;




    I am unable to create this procedure ,getting the below error


    [Warning] ORA-24344: success with compilation error


    30/12 PL/SQL: ORA-00942: table or view does not exist


    4/3 PL/SQL: SQL Statement ignored


    (1: 0): Warning: compiled but with compilation errors

    Thanks
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    In your code has errors:
    Code (Text):

    ....
    (
      Sid,
      OsUser,
      Machine ,
      Process ,
      Program ,
      Module,
      Action  -- absent ,
      ErrorMsg
    --  Backtrace
    )
    ..
     
    For logging DML-operation better to use : DBMS_ERRLOG
    Oracle not recomended to use RESERVED WORDS in naming user objects...
    Code (SQL):
    SET serveroutput ON
    CREATE TABLE err_tab
    (
      sid NUMBER ,
      osuser VARCHAR(20),
      machine VARCHAR(20),
      process VARCHAR(12),
      program VARCHAR(100),
      module VARCHAR(100),
      action VARCHAR(100),
      errormsg varchar2(4000),
      backtrace CLOB ,
      callstack CLOB
    );

    CREATE OR REPLACE PROCEDURE errproc AS
    BEGIN
      INSERT INTO err_tab
      (
        sid,
        osuser,
        machine ,
        process ,
        program ,
        module,
        action,
        errormsg ,
        backtrace,
        callstack
      )
      SELECT
        sid ,
        osuser,
        machine,
        process,
        program,
        module,
        action,
        dbms_utility.format_error_stack,
        dbms_utility.format_error_backtrace,
        dbms_utility.format_call_stack
      FROM v$session
      WHERE sid = sys_context('userenv','sid');
    END;
    /

    BEGIN errproc; END;
    /
    SELECT * FROM err_tab;
     
     
    Last edited: May 26, 2017
  3. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil