stored procedure in Oracle 8.1.7.0

[raf]

HI,
my oracle version is 8.1.7.0

I've created this stored procedure:

CREATE OR REPLACE procedure CREATE_TAB1
IS
SVUOTA VARCHAR2(64);
PRAGMA AUTONOMOUS_TRANSACTION;
err_num number;
err_msg varchar2(100);
BEGIN
SVUOTA := 'TRUNCATE TABLE TAB1 REUSE STORAGE';
EXECUTE IMMEDIATE SVUOTA;

INSERT INTO TAB1
SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in ('04'))
WHERE max_area = total_area;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
err_msg:= SUBSTR(SQLERRM, 1, 100);
err_num:= SQLCODE;
INSERT INTO tab_error (proc_name, err_code, err_msg, err_date)
VALUES ('CREATE_TAB1', err_num, err_msg, sysdate);
COMMIT;

END CREATE_TAB1;

but when I compile I get this error

PROCEDURE AFM.CREATE_TAB1
On line: 14
PLS-00103: Encountered the symbol "(" when expecting one of the following:

, from

the procedure stop on line:
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area

I think that oracle version 8.1.7.0 not allow the code "OVER (PARTITION BY"

I tried also with:
EXECUTE IMMEDIATE 'INSERT INTO TAB1
SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in ('04'))
WHERE max_area = total_area';

but I get:
PROCEDURE AFM.CREATE_TAB1
On line: 16
PLS-00103: Encountered the symbol "04" when expecting one of the following:

How can I create my stored procedure with Oracle version 8.1.7.0

Thank in advance!

[DaPi]

In haste: I think Analytic Functions are not recognised in PL/SQL - but, sorry, do not have time to check the doc. (In 8i SQLPlus and PL/SQL use different engines.)

[davey23uk]

you are correct, have to use execute immediate to get that to work.

they start working in plsql from 9i

[raf]

Originally posted by davey23uk
you are correct, have to use execute immediate to get that to work.

they start working in plsql from 9i

ok, but with execute immediate I get an error:

PROCEDURE CREATE_TAB1
On line: 16
PLS-00103: Encountered the symbol "04" when expecting one of the following:.............
I think because
EXECUTE IMMEDIATE '(here I open)INSERT INTO TAB1
SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in ('(here I close)04'))
WHERE max_area = total_area';

How can I write correctly this query in pl/sql?

[Bonker]

try like this

EXECUTE IMMEDIATE 'INSERT INTO TAB1
SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in (''04''))
WHERE max_area = total_area';

or create a view for analytical function and then use the view to insert into the table
i.e create or replace view v
("COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA)
as
SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in ('04')
/

and then in plsql
insert into TAB1
SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
from v where total_area = max_area;

[raf]

"where a.COD_RITMI not in (''04''))"

this is incorrect because Oracle see "04" as new column

[Bonker]

You see 04 being literal you have to surround the ' with "'" so that Oracle understands it is literal and not end of the string.

ie something like this
execute immediate
'SELECT COD_IMM,PIANO,COD_RITMI,TOTAL_AREA,MAX_AREA
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM REP_02_TEST1 a
where a.COD_RITMI not in ('||chr(39)||'04'|| chr(39)||'))
WHERE max_area = total_area';

otherwise try the other option that I suggested of creating a view for analytical function and then use the view to insert into table in plsql.