Thanks for the question.

Asked: April 02, 2001 - 9:30 am UTC

Last updated: February 04, 2008 - 3:01 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

hi tom,

we are using jdbc to access the database via stored procedures. however, we intend on developing our product across multiple DB platforms such as SQL Server and DB2. This poses a problem of sorts for us. Since we are heavily dependent on stored procedures, this will mean coding the same business logic in stored procedures for SQL Server and DB2. This could mean a maintenance/logistical nightmare for us.

Someone here has recommended converting all the stored procedures to standard SQL so that we can have the portability we desire. However, another issue confronts me and that is performance of SQL versus stored procedures. I need to prove that PL/SQL stored procs are much faster than SQL and are preferrable for enterprise scale applications.
Can you please shed some light on my dilemma and what would you do in my situation. Please be mindful of the fact that we do get some really huge volumes of data. Please give pros and cons of both i.e. SQL and stored procedures.

Many Thanks

Rajat

and Tom said...

Well, I've always found the porting issue between various databases to be trivial when compared to the "logic" issues. the same exact set of sql executed in the same exact sequence on the same exact data will produce two entirely different results when executed in different databases.

How will you code your application so that

- it is independent of the fact that Oracle gives you non-blocking queries, whereas the others will not. The application that works fine in Oracle may/may not work find in the others.

- Oracle gives you consistent reads, others will not. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:431163879856 <code>
for a somewhat amusing story with regards to that.

Basically, you may well get different answers to the same queries run in the SAME exact set of circumstances on different database engines.

- it is independent of the locking model employed by the database? Concurrency control is the major differentiator between databases.

And the list goes on. Stored procedures will not only POSSIBLY provide a performance boost (you can write bad code in any language), but since you will need to implement them for each database -- they will give you the opportunity to exploit the underlying database fully. They will give you the opportunity to solve some of the above issues (the logic you need will be different in different databases for different things).

Unless you are building a READ ONLY application, your best bet would be stored procedures for portability of the rest of the appication. There will be subtle differences, with different solutions, for each RDBMS you use. You will be able to exploit the underlying RDBMS to its fullest by using stored procedures as there will be no hesitation to implement a function as it should be given the target RDBMS. Not as it has to be in order to be generic.

Just my 2cents worth. I've been there, done that. The right way to do things varies by database. Sybase -- they need you to use temp tables, Oracle -- we don't like you to (not necessary). DB2 -- if you want a consistent result set, you must use repeatable read isolationn, Oracle not so. And so on.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Markova, April 03, 2001 - 3:18 am UTC

OK

Raju, March 23, 2004 - 1:20 am UTC

Dear Sir,

If I have a procedure with arguments like
sql>create or replace procedure p(x in varchar2 default'Oracle',y
out varchar2) as
begin

/* procedure body*/
end;
/
Then how to make a call to the procedure?
I tried options like
sql>exec p(default,:a)
sql>exec p(' ',:a)
sql>exec p((),:a)
sql>exec p(,:a)-- Does the work but throws errors.
How to get around with this?Could you please provide a solution?
Bye!

March 23, 2004 - 7:09 am UTC

exec p( y => :a );

use named notation if you want X to be defaulted.

Thanks

Raju, March 23, 2004 - 12:58 pm UTC

Dear Sir,
Thanks for your reply.But I tried the other option using 
Call like

SQL> call rev( y => :a);
call rev( y => :a)
            *
ERROR at line 1:
ORA-00907: missing right parenthesis
I think it must be some sort of
 SQL> Call rev() into :a
But how we can pass the arguments in the Call method of
invoking a procedure?
Please do reply.
Bye!

March 23, 2004 - 5:36 pm UTC

SQL> EXEC p(....

SQL> begin p( y=> :a); end;
  2> /

I never use call myself.  I've given two ways known to work....  Not sure if call supports that or not.

ok

Raj, June 05, 2005 - 1:20 pm UTC

Hi Tom,
How to execute this procedure??

SQL> create or replace procedure test_p(dno in emp.deptno%type default 10,rec out emp%rowt
  2  as
  3  r emp%rowtype;
  4  begin
  5  select * into r from emp where deptno = dno;
  6  rec := r;
  7* end;

I am getting errors when executed.like
Exact fetch returns more than requested number of rows.

How to fix this??

SQL> /

Procedure created.

June 05, 2005 - 2:22 pm UTC

well, in order to FIX we need to understand what is BROKEN.

What are you trying to do or accomplish? What is the "goal" here.

If the goal is to get the single record for deptno "x", then you cannot achieve your goal as there are many records for deptno "x"

if your goal is to return a result set of all employees in deptno "x" to the client, we need to recode the application.

so, whats the goal.

OK

Raj, June 06, 2005 - 10:49 am UTC

Hi Tom,
I would like to get all the rows equal to the deptno
entered.What modification should I do??

June 06, 2005 - 11:20 am UTC

and what would you like to have happen to these rows?

do you want to process them IN the procedure:

create or replace procedure test_p( p_dno in emp.deptno%type default 10 )
as
begin
for x in ( select * into r from emp where deptno = p_dno )
loop
process record x here
end loop;
end;

returned as a result set to client program:

create or replace procedure test_p( p_dno in emp.deptno%type default 10,
p_result in out sys_refcursor )
as
begin
open p_result for
select * into r from emp where deptno = p_dno;
end;

ORA-00907 error, with rigth sintax

Bruno, January 31, 2008 - 6:09 am UTC

Hello,

Sorry for my bad english.

I have a stored procedure that runs a statement like:

Proc(Val1 in VARCHAR2, Val2 In VARCHAR2) return VARHCAR2 is

...---Var declarations
TYPE CCURSOR IS REF CURSOR;
C CCursor;
Begin
statement:='SELECT tbl1.col1, tbl2.clo1 bulk collect into FROM tbl1 ';
statement:=statement||'INNER JOIN tbl2 ON(tbl1.id=tbl2.ID)
statement:=statement||'WHERE tbl1.col2= '||val1|| and';
statement:=statement||'tbl2.col2= '||val2|| ;';
Open C for statement;
....
END;

I have ORA-00907 error when i execute the statement (Open).
I am sure that the sintax are rigth.

I will be in serious troubles if i cant solve this in this week.
Please someone can help me?

Thank you very much.

Bruno

February 04, 2008 - 3:01 pm UTC

... I will be in serious troubles if i cant solve this in this week. ...

you already are in serious trouble.

No bind variables, you lose.

I see no need for dynamic sql here at all. please simply code:

begin
   open c for select tbl1.col1, tbl2.clo1 
                from tbl1 inner join tbl2 
                  on (tbl1.id = tbl2.id) 
               where table1.col2 = VAL1 and tbl2.col2 = VAL2

your syntax error is that "BULK COLLECT INTO" is not valid SQL, it is something a host language using STATIC SQL can "see" and "do", but it is not part of SQL.

You would

a) open cursor
b) fetch c bulk collect into some_variable, some_variable;
c) close cursor

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

OK

Thanks

ok

OK

ORA-00907 error, with rigth sintax

More to Explore

PL/SQL demos

PL/SQL docs

Performance comparison between SQL and PL/SQL stored procedures

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

OK

Thanks

ok

OK

ORA-00907 error, with rigth sintax

More to Explore

PL/SQL demos

PL/SQL docs