CODEWITHSUNDEEP
oracle-databaseschema
Chad
Chad

Reputation: 24679

Can DBMS_METADATA.GET_DDL procedure correct poor indentation?

Here's the command to get the DDL or the procedure and make it pretty:

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM , 'PRETTY' , TRUE);
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', UPPER('LOOPPROC'), 'MYSCHEMA') FROM DUAL;

This is the output which is exactly like the input and the same as if PRETTY was set to FALSE above.

  CREATE OR REPLACE EDITIONABLE PROCEDURE "MYLANID"."LOOPPROC" (inval NUMBER) 

IS

  tmpvar   NUMBER;
  tmpvar2   NUMBER;
  total     NUMBER;

BEGIN

  tmpvar := 0;
  tmpvar2 := 0;
  total := 0;

  FOR lcv IN 1 .. inval

  LOOP

      total := 2 * total + 1 - tmpvar2;
      tmpvar2 := tmpvar;
      tmpvar := total;

  END LOOP;

  IF inval = 1 THEN
                        DBMS_OUTPUT.put_line ('IN IF TRUE branch, inval = ' || inval);
DBMS_OUTPUT.put_line ('IN IF TRUE branch, inval is still = ' || inval);
  ELSE
     DBMS_OUTPUT.put_line ('IN ELSE, inval = ' || inval);
  END IF;
   

  DBMS_OUTPUT.put_line ('TOTAL IS: ' || total);

END loopproc;

Note how the IF - THE - ELSE clause is indented like the Snake River.

Is there a way to get the procedure to indent more conventionally?

Upvotes: 3

Views: 240

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

I’m also open to open source that can be called from .net. I’m considering as a last resort to use Selenium to take advantage of free web sites that do sql formatting but only as a last resort.

Idea: SQL Developer has already built-in formatting capabilities and allow to set multiple options.

Then using: Command-Line Interface for SQL Developer:

For certain operations, you can invoke SQL Developer from the command line as an alternative to the graphical user interface. To use the command-line interface, go to the sqldeveloper\sqldeveloper\bin folder or sqldeveloper/sqldeveloper/bin directory under the location where you installed SQL Developer, and enter sdcli.

C:\sqldeveloper\sqldeveloper\bin>sdcli

Available features:

format: Format Task

Invocation:

 sdcli format input=<here goes file>

I could imagine building following batch script/pipeline:

  1. get object definition from Oracle
  2. save it as file
  3. use sdcli format
  4. read the file content back

Upvotes: 1

Related Questions