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 |