how to pass formatted string to column and same fetch it in package in PL/SQL-CodePudding

I have error master table which contain description like 'Error in table abc in xyz column.' I need to format string for column name which is xyz here. Where ever I need to call this table I will pass column name and then I will get expected description. Ex - Insert into errorTabl values(01,There is error in {0}) Whenever inside package I need to retrieve value of 01 then I will pass column name col1 so then expected value will be as below : 01 There is error in col1

Request you to please help me for insert and select both statements.

CodePudding user response：

Though this doesn't make sense, maybe the code below could help you to start with something or to clarify your problem.
NOTE: the code below is here just to show you the basics - there is no sense in it for any kind of production. You are the one to adjust it to your context.
So, the package to put and get things into or from errors table:

CREATE OR REPLACE PACKAGE ERRS AS 
  Procedure putError(p_table IN VarChar2, p_column IN VarChar2);
  Function getError(p_table VarChar2, p_column VarChar2) RETURN VarChar2;
END ERRS;
--  ---------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ERRS AS
    Procedure putError(p_table IN VarChar2, p_column IN VarChar2) AS
    BEGIN
      Declare
          mSql        VarChar2(512) := '';
          sq          VarChar2(1) := '''';
      Begin
          mSql := 'Insert Into ERRORTABLE values( ' || sq || '01' || sq ||  ', ' || sq || 
                  'There is error in table ' ||  p_table || ' in ' || p_column || ' column' || sq || ')';
          Execute Immediate mSql;
          Commit;
      End;
    END putError;
    -- -------------------------------------------------------------------------------
    Function getError(p_table VarChar2, p_column VarChar2) RETURN VarChar2 IS 
    BEGIN
      Declare
          Cursor c IS
              Select ERR_DESC From ERRORTABLE Where ERR_DESC Like('%table ' || p_table || ' in ' || p_column || '%');
          mRet      VarChar2(512) := '';
          mDesc      VarChar2(512) := '';
      Begin
          Open c;
          LOOP
              FETCH c into mDesc;
              EXIT WHEN c%NOTFOUND;
              mRet := '01 ' || mDesc || Chr(10);
          END LOOP;      
          Close c;
          RETURN RTRIM(mRet, Chr(10));
      End;
    END getError;
END ERRS;

Now the calling code to insert 5 records (once more - this is senseless) and to get you one of them...

set serveroutput on
DECLARE
    errMsg    VarChar2(512);
BEGIN
    ERRS.putError('T_ABC', 'C_XYZ');
    ERRS.putError('T_ABC', 'C_MNO');
    ERRS.putError('T_ABC', 'C_PQR');
    ERRS.putError('T_DEF', 'C_MNO');
    ERRS.putError('T_DEF', 'C_XYZ');
    --
    errMsg := ERRS.getError('T_ABC', 'C_XYZ');
    dbms_output.put_line(errMsg);
END;
/*  R e s u l t :
anonymous block completed
01There is error in table T_ABC in C_XYZ column
*/

CodePudding user response：

Just needed to pass double colon in insert query so then it will take single colon in table.

Ex - Insert into errorTabl values(01,There is error in ''{0}'') In table it will be look like

  **Id**   **Description**
    01   There is error in'{0}'.