Suppose that a_function_name returns an integer.

I want to do something like :

EXECUTE IMMEDIATE 'package_name.proc_name(a_function_name)';

I need this to remove code duplication dramatically.
EXECUTE IMMEDIATE says that this is not valid SQL. If EXECUTE IMMEDIATE cannot execute PL/SQL than do I need something else? I have a procedure name at runtime and want to call it. Is it possible?

Thank you.

The procedure that uses the dynamic-sql gets the procedure name to call as a parameter:

PROCEDURE my_procedure(procedure_call varchar2) IS
BEGIN

EXECUTE IMMEDIATE procedure_call;

END;

This is exactly what I need to do.
my_procedure will be a template-like method which does the common actions and calls the procedure which different clients pass as a parameter. I don't know if there is a tool like "function pointer" in PL/SQL. But it seems "execute immediate" cannot be used that way.

Hi,

Simply calling the procedure should do the task.

PROCEDURE my_procedure(procedure_call varchar2) IS
BEGIN

procedure_call;

END;

Just Try out.

Regards.

It is wrong.
Simply check your advise before post, please.

Rgds.

EXECUTE IMMEDIATE 'BEGIN proc; END;'
works just fine.


My intention was to use this feature on test procedures that exercise existing stored procedures. When the test fixtures became complex, code duplication became apperant.
No clients (as you mean) are going to call these sp's. Interface is limited to unit test procedures. In other words these are not going to be released.

What I can criticize is that there is too much logic in PL/SQL codes so I needed to write unit tests. My favorite approach would be to use SQL only for CRUD and just test CRUD behaviour.

> Consider this procedure:

Ah.. you are talking about creating a test harness. For example, the client application will typically make an application logon call (setting up a context for example to enable FGAC), set NLS settings and so on - and only then make a application process call to a specific procedure.

You want to test those procedures from a "pretend client" and needs to go through all those setups first before making the call.

Hmmm... yes, I can see why you want to use this approach. And if this is specifically going to do the job for you, I do not see why specifically you should not use this approach.

Practical experience tells me however that a test harness is never this simple and easy.

In today's development paradigm (XP/agile/etc), a great deal of emphasis is placed on test cases. And actually coding these test cases as units.

This is different as your approach seems to me to simply test a single procedure in isolation. Which is fine to simply validate the basics for that procedure - but for the business process.

I prefer to write my test cases as complete end-to-end unit tests of a business process. Something along the lines of:

create or replace procedure TestInvoiceRun is
begin
  TestHarness.ClientLogon;
  TestHarness.DoCommonStuff;
 
  -- starting invoice process
  ProcA; 
  TestHarness.EvaluateA; 
 
  ProcB; 
  TestHarness.EvaluateB;
 
  .. etc.
  -- process completed
 
  TestHarness.EvaluateInvoice;
 
exception when OTHERS then
  TestHarness.DumpState;
  raise;
end;

And then have a similiar test procedures/packages for stock orders, shipping orders, etc - whatever test cases demand.

Hi dnikiforov ,

I misunderstood the question. i meant

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Jun 5 18:14:38 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create or replace procedure proc1
2 is
3 begin
4 dbms_output.put_line('Inside Proc1');
5 end;
6 /

Procedure created.

SQL> create or replace procedure proc2
2 is
3 begin
4 dbms_output.put_line('Inside Proc2');
5 dbms_output.put_line('calling proc1');
6 proc1;
7 end;
8 /

Procedure created.

SQL> set serveroutput on;
SQL> execute proc2;
Inside Proc2
calling proc1
Inside Proc1

PL/SQL procedure successfully completed.

SQL>

Regards.

> But PL/SQL is not the right place to do this I think.

PL/SQL can do everything you can do in Java. My entire application tier is PL/SQL and resides in Oracle.

My systems do not use Java at all - except for the odd Java stored proc. The J2EE guys we have, have now too begun to code 90% of their applications in PL/SQL, only using Java to do the rendering. They use JBoss for rendering. I use APEX.

> Complex logic in stored procedures caused that. And that procedures are

That is the choice you as developer make - how to use PL/SQL. It does support o-o.

And alluding that procedural code is more difficult to test than o-o code.. I differ on that. In fact, o-o has a lot more hidden complexities that one does not have with procedural code.

> I see that, object oriented approaches encourage object to relational

Utter bs. That is the single primary reason for the failure of so many systems. The database is not a bit bucket. And J2EE is a failure because of this bit bucket approach.

If you only need a persistence layer for app code, then a RDBMS is not needed. Especially not Oracle.

But you do need a database. Why? Because the app server is incapable of being a database as good as what the database tier is.

What I really do not understand is that people who are o-o schooled, should be the very first to realise the benefits of importance of the data, the importance of encapsulating code with data - and yet, design the application in one tier and wants the data in another tier.

Fact is that PL/SQL is even more capable than Java or .Net in this regard. Fact is that Oracle is not just a bit bucket, but the most advance RDBMS product on this planet and very capable of encapsulating the COMPLETE app tier.

Fact is, it does this cheaper and faster and more scalable than an app server.

And ignoring these facts is why the majority of J2EE systems are failures at the end of the day.