how to reuse code in same script in oracle?

Question

I need to create a oracle sql script (not stored procedure) which has some sql statements (process_audit sql statements) that are getting used more than once in the script.

Pseudo code of my oracle script:

begin
open cursor1
loop(1)
open cursor2
loop(2)
if codition1 then
     process_audit sql statements;
 else
 sql statements;
 end if;
 end loop(2); 
 close cursor2;
  end loop(1);
   close cursor2;
    process_audit sql statements;
  end;

In Java/C we can create a function and put the reusable lines in it and then call the function wherever it is required. Can we do similar in oracle sql script? I cannot use function as I cannot create anything permanently in the database schema.

You can create functions in oracle also Refer docs.oracle.com/cd/B28359_01/appdev.111/b28370/…. — Tharunkumar Reddy
– Tharunkumar Reddy, Commented Aug 24, 2015 at 9:39
What do you mean with "not PL/SQL"? PL/SQL is the procedural language used for writing Oracle functions, procedures etc. (anything you cannot do with SQL alone). — Mick Mnemonic
– Mick Mnemonic, Commented Aug 24, 2015 at 9:41
If I used function then the funcion will be created permanently in the database, and I don't want create anything in the database. — PVJ
– PVJ, Commented Aug 24, 2015 at 9:43
You could create an anonymous PL/SQL block which you execute and wrap as a function in your client language (shell script, Java, etc.). — Mick Mnemonic
– Mick Mnemonic, Commented Aug 24, 2015 at 9:46
Why don't you want to create it in the database? You can expect performance issues if you don't — Nick.Mc
– Nick.Mc, Commented Aug 24, 2015 at 10:16

Ponder Stibbons · Accepted Answer · 2015-08-24 10:17:34Z

3

Put common part of code in inner procedure (or function) in declare section in PLSQL block and run it like here:

declare
  var number := 0;

  procedure process_audit is
  begin
    dbms_output.put_line(var);
  end;

begin
  var := var + 10;
  process_audit;
  var := var * 5;
  process_audit;
end;

answered Aug 24, 2015 at 10:17

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen · Accepted Answer · 2015-08-24 10:25:03Z

You can declare procedure and function in plsql block, which won't created in the schema: Demo

-- Declarative part of block begins
DECLARE
  -- variable declaration
  in_string  VARCHAR2(100);
  out_string VARCHAR2(200);

  -- Procedure declaration and definition begins
  PROCEDURE pseudo_proc(
     original IN VARCHAR2,
     new_string OUT VARCHAR2 
     --more params ...
  )
  IS
    BEGIN
      --your pseudo code
    EXCEPTION
      --handle exception
    END;
    -- Procedure declaration and definition ends
BEGIN
  pseudo_proc(in_string, out_string);  -- Procedure invocation
  pseudo_proc(in_string, out_string);  -- Procedure invocation
  pseudo_proc(in_string, out_string);  -- Procedure invocation
  -- any no of times.....
END;
-- Executable part of block ends
/

Please refer doc

Note: all other variables should be declared above procedure or function declaration.

Collectives™ on Stack Overflow

how to reuse code in same script in oracle?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related