0 Replies Latest reply on Oct 27, 2015 5:23 AM by HarbourGhost

    create view and DESC failures

    HarbourGhost

      Got a few issues that crop up in both sqlcl and SQL Developer

       

      1. 12c WITH PL/SQL

      When you have a query featuring the new 12c WITH using a PL/SQL function, the query alone can execute fine, but it fails when you try a CREATE VIEW (the semicolons in the PL/SQL function send the statement off prematurely).

      The Syntax works in the 12c sqlplus instant client

       

      create view y as

      WITH

      FUNCTION strip_space(i_name VARCHAR2) RETURN VARCHAR2 IS

      BEGIN

         RETURN replace(i_name,' ');

      END;

      SELECT strip_space('Force Awakens') x from dual

      /

       

      2. DESC package

      When you use the DESC command on a package, it appears to drive from the *_ARGUMENTS dictionary view. As such, if the procedures/functions don't have arguments, they don't get reported.

      Info works fine

       

      create package jedi is

          procedure toggle_force;

          procedure set_force (p_ind in boolean);

          function get_force return varchar2;

      end jedi;

      /

       

      >desc jedi

      PROCEDURE Argument Name Type           IN/OUT Default

      --------- ------------- -------------- ------ -------

      SET_FORCE P_IND         PL/SQL BOOLEAN IN

       

      >info jedi

      Package

       

      /* Package DEV.JEDI */

      /*  PROCEDURE  DEV.JEDI.TOGGLE_FORCE  */

          DEV.JEDI.TOGGLE_FORCE();

      /*  PROCEDURE  DEV.JEDI.SET_FORCE  */

          DEV.JEDI.SET_FORCE(   P_IND   =>  p_IN_param0  /*   PL/SQL BOOLEAN   */);

      /*  FUNCTION  DEV.JEDI.GET_FORCE  */

          /*   RETURN VARCHAR2   */

           v_ret := DEV.JEDI.GET_FORCE( );

       

       

      Again, sqlplus works fine

      FUNCTION GET_FORCE RETURNS VARCHAR2

      PROCEDURE SET_FORCE

      Argument Name                  Type                    In/Out Default?

      ------------------------------ ----------------------- ------ --------

      P_IND                          BOOLEAN                 IN

      PROCEDURE TOGGLE_FORCE