-
stored procedure in Oracle 8.1.7.0
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!
-
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.)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
you are correct, have to use execute immediate to get that to work.
they start working in plsql from 9i
-
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?
-
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;
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
"where a.COD_RITMI not in (''04''))"
this is incorrect because Oracle see "04" as new column
-
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.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|