Thanks for your views and quick response.

in a c/s app (powerbuilder/oracle) we have some processes which read a .txt file (n number of records). each record could be an Add(A)/Delete(D) or Update (U).
each record potentially affects multiple tables ie A/D/U to x number of tables. i find that all the sqls to do this has been embedded within PB. so there will be x number of trips per record (totalling x & n trips) between the client and server. and some processes run for about 6 hrs !

1)won't a proc/function that does this in the db be more efficient ? <your opinion about this would be most helpful.>
2) its quite difficult to maintain code that is a total mix of pb code and sqls.
3) how to determine the approximate benefit in performance after moving the code to plsql. now i know the current time taken is about 6 hours. but how to 'guess' that after putting the code in modules of sp's/functions the process will take x time ?

i am sure it will be a better approach but my being 'sure' doesnt help me. i need to show & prove that it will run so much times faster. and for that i need to develop it.
any idea how to pre-judge it ?

thank you.

ok. almost what i was thinking of. (more than half way through your book + this site and i guess i have started thinking on the right lines :))

but i doubt whether i will be able to use the BULK feature. reason: then input values (for each column) needs to be validated, sometimes cross checking against the db. moreover, inserting/updating/deleting from n tables for 1 particular record makes up a transaction unit.

"ripping apart the code to see what it does"...hmmm.
you might find this hard to believe ( or maybe you have seen much "better" coding ) but in one place i actually saw code like this:
if variable > 0 then
if variable = 0 then (=> whatever can cause this to happen is beyond my understanding ! )
.....
end if ;
end if ;

Hi tom,

I wrote a stored procedure as follows...


create or replace sp
( p_message out varchar2,p_status out varchar2)
as

var1 varchar2(20);
var2 number;
var3 varchar2(30);
begin
select id_val into var2 from t;

begin
select id_name int var1 from t2
where id_val = var2;

exception
when no_data_found then
-- dbms_output.put_line('we need to insert...');
RAISE;
when too_many_rows then
-- dbms_output.put_line('we need to insert...');
null;
when others then
-- dbms_output.put_line('trap the error ...');
p_message := sqlerrm;
p_status := 'F';
RAISE;
end;
/****************************/
begin
select username into var3 from user;
raise too_many_rows;
exception
when no_data_found then
--dbms_output.put_line('insert again..');
RAISE;
when too_many_rows
-- dbms_output.put_line('row already exists');
null;
when others then
-- dbms_output.put_line('trap the error ...');
p_message := sqlerrm;
p_status := 'F';
RAISE;
end;

exception
when no_data_found then
my_pkg.insert_t(var1,var2,p_status);
when others then
-- dbms_output.put_line('trap the error ...');
p_status := 'F';
insert into error_table(err.nextval,p_status,p_message);

end sp;

/*****************************************************/

do you think the way this script is written is not good ?
is the script writer uses the exception as the " label " ??

like "go to " statements in c or other languages ?

if it is bad what would be the right approach ?


Thanks,

Tom,

I have audit trail implemented on the table t and users
so when atleast one row exists we need to update(but because of audit trail we insert )
so in the above procedure whereever I am raising too_many_rows
it is update ( I just worte null; to illustrate)

but my question is the use of the exeception in above
procedure is like label ? like go to stmt ?

this is exactly what our dba said.
it is, however, not what the instructor of the oracle class i'm taking said. i think this is because they said the problem was impossible to solve using plain sql, and it was not, and that made him cranky.

The original question claimed there was "more complexity in SQL than in PL/SQL. Examples could be DECODE/CASE statement in SQL VS nested IF statements in PL/SQL." I'm still attempting to fathom that.

Here's one of the more challenging PL/SQL code snippets I inherited (which I've abbreviated and annotated):

v_protection_class number;
CURSOR c is SELECT pnf.protection_class, pnf.location_code
','||pnf.Risk_Codes_1||','...
pnf.Risk_Codes_20||',' -- YES, CONCATENTATED!
as Risk_Code_ALL
from SOURCE_TABLE pnf;
FOR recSourceRecord IN c LOOP
v_protection_class := rec.protection_class;
IF v_Protection_Class IS NULL THEN
BEGIN
SELECT protection_class INTO v_Protection_Class
FROM risk_code_tbl
WHERE recSourceRecord.Risk_Code_ALL LIKE '%,'||risk_code||',%'
EXCEPTION
WHEN OTHERS THEN -- ARG!!!
v_Protection_Class := NULL;
END;
END IF;
IF v_Protection_Class IS NULL THEN
BEGIN
SELECT protection_class INTO v_Protection_Class
FROM location_code_tbl
WHERE location_code = trim(recSourceRecord.location_code)
AND protection_class is not null;
EXCEPTION
WHEN OTHERS THEN
v_Protection_Class := NULL;
END;
END LOOP;

Data profiling found that the subqueries sometimes returned multiple (identical) rows, which threw an exception so the result was incorrectly set to NULL! After some evaluation, the PL/SQL was re-written into SQL with explicitly nested conditions:

SELECT ( CASE WHEN pnf.Protection_Class IS NOT NULL THEN pnf.Protection_class
ELSE CASE WHEN ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2,...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 ) IS NOT NULL
THEN ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2,...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 )
ELSE (
SELECT TO_CHAR(L.protection_class)
FROM location_code_tbl L
WHERE L.location_code = trim(pnf.location_code)
AND L.protection_class IS NOT NULL
)
END
END ) AS protection_class
FROM source_table pnf;

Of course, this could be revised to be shorter and mildly more cryptic to procedural thinkers:

SELECT ( COALESCE( pnf.Protection_Class
, ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 ) , (
, ( SELECT TO_CHAR(L.protection_class)
FROM location_code_tbl L
WHERE L.location_code = trim(pnf.location_code)
AND L.protection_class IS NOT NULL
)
) AS protection_class
FROM source_table pnf;

Do you know of data-oriented procedural code that cannot be rendered declaratively? The era before analytic functions provided some, I'm sure, but nowdays?

Of course, I concur: "many procedural algorithms should die an instant death and be done in a single sql statement" :))

With the advent of subquery factoring (named subqueries), see </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8120272301765#44118532883998 <code>I'm very pleased with how modular SQL can be!

BTW: When did you first uncover the mantra ?