SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

conditional incremensum (condition is on the sum value of the previous row)

518921Jan 28 2010 — edited Feb 2 2010

Hi,
i know is a little bit tricky but i need to know the credit of a client after each invoice.
so if sum(invoice_val-previous_credit) >0 then the credit is 0 else credit is previous_credit-invoice_val.
As you can see to dermine the credit value of the current row it is needed to check the same value of the previous row.Is this possible in some way?

EX.
so the situation in the table is the following:

ID_CLIENT	ID_invoice	invoice_VAL
8.789	220.227	120,47
8.789	238.342	109,76
8.789	246.388	121,69
8.789	258.163	137,45
8.789	268.969	138,67
8.789	295.455	145,16
8.789	311.395	138,92
8.789	327.104	138,96
8.789	340.793	-335,18
8.789	375.451	129,14
8.789	386.650	125,57
8.789	398.606	124,18
8.789	428.166	31,66
8.789	435.844	25,93
8.789	447.639	34,32
8.789	462.137	-43,64
8.789	475.613	-110,39
8.789	485.022	92,29
8.789	495.807	91,67

i need something like this:

ID_CLIENT	ID_invoice	invoice_VAL credit
8.789	220.227	120,47	0,00
8.789	238.342	109,76	0,00
8.789	246.388	121,69	0,00
8.789	258.163	137,45	0,00
8.789	268.969	138,67	0,00
8.789	295.455	145,16	0,00
8.789	311.395	138,92	0,00
8.789	327.104	138,96	0,00
8.789	340.793	-335,18	335,18
8.789	375.451	129,14	206,04
8.789	386.650	125,57	80,47
8.789	398.606	124,18	0,00
8.789	428.166	31,66	0,00
8.789	435.844	25,93	0,00
8.789	447.639	34,32	0,00
8.789	462.137	-43,64	43,64
8.789	475.613	-110,39	154,03
8.789	485.022	92,29	61,74
8.789	495.807	91,67	0,00

thanks very much

Edited by: 4ndr34 on Jan 28, 2010 1:38 PM

This post has been answered by Frank Kulash on Jan 29 2010

Jump to Answer

BluShadow

The majority of those commands are SQL*Plus commands. That means that they are specific to SQL*Plus and are not part of SQL or PL/SQL which is what you write your procedures/packages with.

Tell us more about what you are trying to achieve and we can advise the best way, because this may be that you put everything in a procedure and code the requirements to generate output or you put everything in a script and execute it via a job etc.

221158

You can include the select statement in a cursor within a database procedure and use dynamic SQL (execute immediate to execute the DDL).

Christopher Soza
Oracle BI DBA
Orix Consultancy Services Ltd
b: http://sozaman.blogspot.com

519811

The system setup is that from Oracle 10g database, data will be copied to locally deployed Oracle XE databases. In order to do that, tables in Oracle XE should be truncated. But before truncating the table, constraints should be disabled first.Then after inserting records from Oracle 10g, constraints shall be enabled again.

I hope you could help me with this one. Thanks in advance.

Marwim

Hello sidkaterin,

here is a package I use for development. Never give access to this package to a normal user!

Regards
Marcus

CREATE OR REPLACE PACKAGE  my_trunc
IS
PROCEDURE disable_triggers;
PROCEDURE enable_triggers;
PROCEDURE disable_constraints;
PROCEDURE enable_constraints;
PROCEDURE truncate_table(p_tabelle IN VARCHAR2);

END my_trunc;
/

CREATE OR REPLACE PACKAGE BODY my_trunc
IS

PROCEDURE disable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN(
        SELECT  'ALTER TRIGGER ' || trigger_name || ' DISABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Trigger: '||SUBSTR(SQLERRM, 1, 100));
END disable_triggers;

PROCEDURE enable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN (
        SELECT  'ALTER TRIGGER ' || trigger_name || ' ENABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Trigger: '||SUBSTR(SQLERRM, 1, 100)
                );
END enable_triggers;

PROCEDURE disable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END disable_constraints;

PROCEDURE enable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END enable_constraints;

PROCEDURE truncate_table
    (
     p_tabelle              IN VARCHAR2
    )
IS
    cur_name                INTEGER;
    v_statement             VARCHAR2(500);
    rows_processed          INTEGER;
BEGIN
    v_statement     := 'TRUNCATE TABLE ' || p_tabelle || ' ';
    cur_name        := dbms_sql.open_cursor;
    dbms_sql.parse (
             cur_name
           ,v_statement
            ,dbms_sql.v7
            );
    rows_processed  := dbms_sql.execute (cur_name);
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Truncate Table ' || p_tabelle || ': ' ||
                SUBSTR(SQLERRM, 1, 100)
                );
END truncate_table;

END my_trunc;
/
SHO ERR

Edited by: Marwim on 18.09.2008 07:33
Replaced lessThan greaterThan with != because using code-markup does not hinder the forum software from changing the characters.

519811

Thank you. I can review this and maybe would revise a few statements to fit my requirements.

247514

sidkaterin wrote:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql;
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
--and c.constraint_type IN ('P','R','U');
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;

Or run your procedure here.
exec myprocedure ;

exit
/

however, there are other procedures that shall be done after disabling the constraints. I want to put them in one package. How do I call this from a procedure? When I tried to simply enclose it in a PROCEDURE format, the spool file command is not recognized. It returned error message. Hope anyone can help me with this. Thanks in advance.

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 2 2010

Added on Jan 28 2010

11 comments

2,623 views

SQL & PL/SQL

conditional incremensum (condition is on the sum value of the previous row)

Comments

Post Details