Validate in PL-SQL

Question

Validate in PL-SQL

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I'm in a PL / SQL database and I need to put together an anonymous script to insert into a configuration table (config and value fields). Something like this:

Config: ‘country’       Value: ‘Argentina’
Config: ‘moneysign’     Value: ‘$’
Config: ‘dateformat’    Value: ‘DD/MM/YYYY’

The problem is that I need to validate that the 3 inserts are correct and also, in case of failure, you must identify which failed and leave the base in the consistent state before the start of the anonymous script (as I understand it is done with < em> ROLLBACK or something similar).

How can I do?

sql oracle plsql

asked by Juan Manuel Barreda Fafasuli 21.09.2016 в 03:43

source

3 answers

Display information depending on what the user chooses Publish project in Python 3.5 with heroku

score 3 · Answer 1

Since PL / SQL is a transaction-oriented language, then you can encompass those three inserts within a transaction. That is:

DECLARE
  …
BEGIN
  INSERT INTO mi_table (campo_1, campo_ 2)
      VALUES('country', 'Argentina');
  INSERT INTO mi_table (campo_1, campo_ 2)
      VALUES('moneysign', '$');
  INSERT INTO mi_table (campo_1, campo_ 2)
      VALUES('dateformat', 'DD/MM/YYYY');
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' :''( ' || SQLERRM);
    ROLLBACK;
END;

To learn more:

score 1 · Answer 2

The following code makes use of a cursor with the data of interest and, through a FOR LOOP , controls the insertion of data. If a registry fails, the variable v_CodeResp changes its value and in the end it will not perform the COMMIT . Instead, perform a ROLLBACK , returning the state of the table to how it was before executing the anonymous block.

Greetings!

SET SERVEROUTPUT ON;
DECLARE
    v_CodeResp INTEGER := 0;
    v_MsgResp  VARCHAR2(300);
    v_Log      VARCHAR2(4000);
BEGIN
    FOR Datos IN (
            SELECT 'Country' AS CONFIG,  'Argentina' AS VALUE FROM DUAL
            UNION
            SELECT 'moneysign' AS CONFIG, '$' AS VALUE FROM DUAL
            UNION
            SELECT 'dateformat' AS CONFIG, 'DD/MM/YYYY' AS VALUE FROM DUAL)
    LOOP
        BEGIN
        INSERT INTO TABLA(A, B) VALUES(Datos.CONFIG, Datos.VALUE);

    EXCEPTION
        WHEN OTHERS THEN
            v_MsgResp := 'Fallo al insertar: '|| Datos.CONFIG || ' ' || Datos.VALUE ||' ';
            v_Log := v_Log || v_MsgResp;
            v_CodeResp := 1;
    END;
END LOOP;

IF v_CodeResp <> 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
    v_MsgResp := 'Registros insertados correctamente';
    v_Log := v_Log || v_MsgResp;
END IF;



DBMS_OUTPUT.PUT_LINE(v_Log);
END;

score 0 · Answer 3

You can do everything in the same transaction and in the case of an exception when you insert any of the configuration values, capture and rollback. This will only save the transaction if all records have been inserted successfully

BEGIN
  --
  SAVEPOINT SP_CONFIGURATION;
  --
  INSERT INTO CONFIGURATION(config, value) VALUES ('country', 'Argentina');
  INSERT INTO CONFIGURATION(config, value) VALUES ('moneysign', '$');
  INSERT INTO CONFIGURATION(config, value) VALUES ('dateformat', 'DD/MM/YYYY');
  --  
  COMMIT;
  --  
EXCEPTION
WHEN OTHERS THEN
  --
  ROLLBACK TO SP_CONFIGURATION;
  --  
END;