stored procedure in Oracle 8.1.7.0
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: stored procedure in Oracle 8.1.7.0

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you are correct, have to use execute immediate to get that to work.

    they start working in plsql from 9i

  4. #4
    Join Date
    Jul 2002
    Posts
    228
    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?

  5. #5
    Join Date
    May 2005
    Posts
    31
    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.

  6. #6
    Join Date
    Jul 2002
    Posts
    228
    "where a.COD_RITMI not in (''04''))"

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

  7. #7
    Join Date
    May 2005
    Posts
    31
    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
  •  



Click Here to Expand Forum to Full Width