Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select v
from (
  SELECT 'A' column1,'B' column2,'C' column3,'D' column4  from dual
) t
unpivot
(
  v  for val in (column1,column2,column3,column4)
) u;



V


A

B

C

D


select * from docs;
ORA-00942: table or view does not exist

--------------------------------------------------------------------------------
-- ① Gather stats
--------------------------------------------------------------------------------
begin
  dbms_stats.gather_schema_stats(null);
end;
/
1 rows affected

--------------------------------------------------------------------------------
-- ② Execution Plans 
--------------------------------------------------------------------------------
-- see https://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g;
--------------------------------------------------------------------------------
select /*+ gather_plan_statistics */
       'Do this to get execution plans and actual execution stats' "Execution Plans"
from docs cross join docs
group by 'A';
ORA-00942: table or view does not exist

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


PLAN_TABLE_OUTPUT


SQL_ID  fynpctqfpuszk, child number 0

                                     

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

-------- -- ① Gather stats ---------------------------------------------

----------------------------------- begin

dbms_stats.gather_schema_stats(null); end;

 

NOTE: cannot fetch plan for SQL_ID: fynpctqfpuszk, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

 


--------------------------------------------------------------------------------
-- ③ PL/SQL Compilation Errors
--------------------------------------------------------------------------------
create function f(x in integer, y in integer) returns integer as
begin
  return x*y;
end;
/
ORA-24344: success with compilation error

select * from user_errors;


NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
ATTRIBUTE
MESSAGE_NUMBER


F
FUNCTION
1
1
40
PLS-00103: Encountered the symbol "RETURNS" when expecting one of the following:

   return
The symbol "return was inserted before "RETURNS" to continue.

ERROR
103


--------------------------------------------------------------------------------
-- ④ PL/SQL Functions
--------------------------------------------------------------------------------
create or replace function f(x in integer, y in integer) return integer as
begin
  return x*y;
end;
/
select f(7,6) from dual;


F(7,6)


42