Oracle SQL Developer – Creating Procedures for a Session

oracleoracle-sql-developer

Is it possible to create a bunch of procedures just for the SQL developer session, so when the session ends the procedures are deleted?

Best Answer

The closest thing you can do is defining procedures in anonymous blocks, for example:

Declare
    val number;
    Procedure P1(abc in number, def out number) is
    begin
         def := abc*abc;
    end;
begin
    For i in 1..10 loop
    P1(i,val);
    dbms_output.put_line(val);
    End loop;
end;
/

Another feature is defining PL/SQL functions for a single SQL call in 12c.

Using a PL/SQL Function in the WITH Clause

For example:

WITH
 FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
   pos BINARY_INTEGER;
   len BINARY_INTEGER;
 BEGIN
   pos := INSTR(url, 'www.');
   len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
   RETURN SUBSTR(url, pos + 4, len);
 END;
SELECT DISTINCT get_domain(catalog_url)
  FROM product_information;
/
Related Question