CODEWITHSUNDEEP
oracle-databasestored-procedures
Lukasz Kisicki
Lukasz Kisicki

Reputation: 43

Compile error oracle procedure

Hej. I have a task that says to create a procedure that adds column "BRUTTO" to a table "TABELA_1, then fills that column with values based on values from column "NETTO" and output all records from TABLE_1, including newly created BRUTTO. It works without commented out code but doesn't otherwise. Apparently it doesn't see column BRUTTO yet so I can't reference it like that. Any help appreciated.

CREATE OR REPLACE PROCEDURE WSTAW_BRUTTO_I_WYSWIETL
AS
    --CURSOR C IS
        --SELECT NAZWISKO, NETTO, BRUTTO FROM TABELA_1;
    V_VAT NUMBER(9,2) := 24;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE TABELA_1 ADD BRUTTO NUMBER';
    EXECUTE IMMEDIATE 'UPDATE TABELA_1 SET BRUTTO = NETTO * (1 + :1 /100)' USING V_VAT;
    --FOR V_REC IN C 
    --LOOP
        --DBMS_OUTPUT.PUT_LINE('| '||V_REC.NETTO||' | '||V_REC.BRUTTO);
    --END LOOP;
END WSTAW_BRUTTO_I_WYSWIETL;

Upvotes: 0

Views: 69

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

Your procedure won't compile because you can't access a column before it is added to the table. Not sure why you wrote a procedure with dynamic SQL for this. A plain SQL statement should work. Moreover, you can't use bind variable in a DDL, It would have raised

ORA-01027: bind variables not allowed for data definition operations

during run time.

You should also consider using BRUTTO as a VIRTUAL COLUMN, rather than a column itself.

ALTER TABLE TABELA_1 ADD BRUTTO NUMBER AS ( NETTO * (1 + 24 /100) );

Demo

If you still think you want a procedure and it has to compile, you should put the block inside EXECUTE IMMEDIATE, but it's not recommended. 

CREATE OR REPLACE PROCEDURE wstaw_brutto_i_wyswietl AS
    v_vat   NUMBER(9,2) := 24;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE TABELA_1 ADD BRUTTO NUMBER AS ( NETTO * (1 + '
                      || v_vat
                      || ' /100) )';
    EXECUTE IMMEDIATE q'{BEGIN
      FOR V_REC IN ( SELECT NETTO,BRUTTO FROM TABELA_1 )
      LOOP
        DBMS_OUTPUT.PUT_LINE(V_REC.NETTO||','||V_REC.BRUTTO);
      END LOOP;
    END;}'
;
END wstaw_brutto_i_wyswietl;
/

Demo2

Upvotes: 2

Related Questions