Oracle 12c – How to Analyze Table in Procedure

oracle-12cplsql

I have many partitions to analyze and don't want to paste every single line to terminal so I'm trying to run a procedure for it, but I got complitaion error:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    END;
    /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANALYZE_TABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         ( ; is with default authid as cluster compress order using
         compiled wrapped external deterministic parallel_enable
         pipelined result_cache accessible rewrite

What is reason for that?

Best Answer

You can not place DDL commands directly in PL/SQL code. You can run them as dynamic SQL, for example:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    END;
    /

Related Solutions

Within a PL/SQL procedure, wrap a query or refcursor in HTML table

It is possible to generate xml with dbms_xmlgen, eg:

select dbms_xmlgen.getxml('select * from dual') as xml from dual;

XML
---
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

Using getxmltype instead, it is then possible to apply an xslt transform, perhaps like this:

select dbms_xmlgen.getxmltype('select * from dual').transform(XMLType(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
  <body>
   <table border="1">
     <tr bgcolor="cyan">
      <xsl:for-each select="/ROWSET/ROW[1]/*">
       <th><xsl:value-of select="name()"/></th>
      </xsl:for-each>
     </tr>
     <xsl:for-each select="/ROWSET/*">
      <tr>
       <xsl:for-each select="./*">
        <td><xsl:value-of select="text()"/> </td>
       </xsl:for-each>
      </tr>
     </xsl:for-each>
   </table>
   </body>
</html>
  </xsl:template>
</xsl:stylesheet>')).getclobval() as html from dual;

HTML
----
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<th>DUMMY</th>
</tr>
<tr>
<td>X</td>
</tr>
</table>
</body>
</html>

_{nb credit to Tom Kyte for the xslt from his blog. See also this other post for a slightly more advanced version.}

Oracle – How to Detect in Which Schema a Procedure is Called?

You can use one of the functions of Oracles owa_util's package named WHO_CALLED_ME.

To demonstrate this feature, we can create a table which will store the results of the call to Oracle's procedure.

create table T_CALLSTACK (
    OWNER_NAME      varchar2(30),
    OBJECT_NAME     varchar2(30),
    LINE_NUMBER     number,
    CALLER_INFO     varchar2(100),
    INSERTDATE      date
);

As a test procedure which will insert some values we use:

create or replace procedure P_CALLED_BY_T is

    v_Owner          varchar2(30);
    v_PackageName    varchar2(30);
    v_LineNumber     number;
    v_CallerT        varchar2(100);

begin

    owa_util.WHO_CALLED_ME (v_Owner, v_PackageName, v_LineNumber, v_CallerT);

    -- v_Owner will contain the schema name of the procedure which
    -- called this procedure itself
    insert
      into T_CALLSTACK (OWNER_NAME, OBJECT_NAME, LINE_NUMBER, CALLER_INFO, INSERTDATE)
    values (v_Owner, v_PackageName, v_LineNumber, v_CallerT, sysdate);

end P_CALLED_BY_T;

The procedure WHO_CALLED_ME will return the schema's name as well as the package name and the line number inside the calling package body, caller-T is the type of database object which called our custom procedure P_CALLED_BY_T. If the caller itself is just a procedure, the variable v_PackageName will hold the procedure's name instead of a package name.

Best Answer

Related Solutions

Within a PL/SQL procedure, wrap a query or refcursor in HTML table

Oracle – How to Detect in Which Schema a Procedure is Called?

Related Question