Trying to insert data into various tables via stored procedure, what am i doing wrong?

Question

I am trying to get a JAVA program (Pega to be exact) to call the procedure to load XML data by calling a stored procedure, but it is not working, what am i doing wrong? I know it has something to do with my variable definitions but i am not sure how i would be able to specify that they will be provided parameters by the java file. below is my stored procedure. Thanks in advance

The error messages I'm getting are:

Error(2,16): PLS-00103: Encountered the symbol ";" when expecting one of the following: := . ) , @ % default character

and

Error(16,1): PLS-00103: Encountered the symbol ")" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

Create or Replace Procedure Cascade_Load (
    Value_ID Number,
    pValue_ID Number,
    pCalculation_ID Number,
    Calculation_ID Number,
    Calculation_Value_ID Number,   
    p_Entity_Address_ID Varchar2(50),
    New_Value_ID Number,
    New_Calculation_ID Number,
    New_Calculation_Value_ID Number
) AS
    BEGIN
        IF code is not null
        THEN
            INSERT INTO Value (Value_ID, energy_product_id, data_source_id, unit_cd, 
                value_tx, padd_cd, supply_type_id, country_cd, state_cd, county_cd, 
                entity_address_id,  series_id, energy_process_cd, result_type_cd, 
                geo_area_cd, create_dt, create_user_id)
            VALUES ( 
                Value_ID, 
                Get_energy_product_id(:NEW.Product_Name_Cd),
                Get_Data_Source_Id(:NEW.Data_Source_Tx),
                :NEW.UNIT_CD ,
                :NEW.Value_Tx,
                :NEW.PADD_CD,
                Get_Supply_Type_Id(:NEW.Supply_Type_Tx),
                :NEW.COUNTRY_CD,
                Get_State_CD(Get_entity_Id(p_Entity_Address_ID)),
                'NA',
                Get_Entity_Address_ID(Get_Entity_ID(p_Entity_Address_ID)),
                0,
                :NEW.Energy_Process_CD,
                :NEW.Result_Type_CD,
                :NEW.Geo_Area_Cd,
                Sysdate,
                '15'
            );
            Commit;
            END IF;
            END;

It looks like you are using trigger code in a stored procedure? — RBarryYoung, Feb 14 '18 at 19:43
yes, i am very lost on how i can simply have JAVA call my stored procedure with very specific parameters and have my stored procedure run aftger being called. DO you know any useful guides out there that can give me better direction on this? — icerabbit, Feb 14 '18 at 19:50
You cannot use :NEW.UNIT_CD in a procedure. You need to replace all such values with the variables that you created at the top of the procedure. `:NEW` is only for triggers where you have a new row of data coming in. — clinomaniac, Feb 14 '18 at 20:42

score 0 · Answer 1 · answered Feb 14 '18 at 19:26

0

@icerabbit

Am not sure if it is a syntax error for having semicolons to separate parameters instead of comas. If this was the case You would've got an error message while compiling itself. What is that code in the body of the SP, is that a variable which is not defined? If Value is a table name, try enclosing it in the braces. If you are seeing an error message, please post that too, it'll give some idea for debugging further.

Thank you

answered Feb 14 '18 at 19:26

Gururaj Bachu

3
3

I fixed what you suggested, now i am getting bad bind variable errors. I assumed if NEW is a pre-existing function/class in the system how can i import in at the beginning of the code? (if that makes any sense) – icerabbit Feb 14 '18 at 19:33
If your intending to just make an insert into the table with values you receiving as parameter plus values you generate from different stored procedures it is always good to follow few standards. First define all parameters with p_ as prefixes just to remove ambiguity between parameters and column names. Secondly declare variables - number of variables should be equal to the number of columns you are trying to insert in this SP. Thirdly define all these variables with values you wanting to insert them with like v_col1 = p_col1 and v_col2 = getCol2(p_col2) and then use these just defined variab – Gururaj Bachu Feb 14 '18 at 19:44
➕ Just defined variables in your insert statement in the SP – Gururaj Bachu Feb 14 '18 at 19:45
if java / pega is calling my stored procedure do i just leave the variables declared but not defined? (I.e. Value_ID Number) Not quite sure how this works or if there are any guides out there. – icerabbit Feb 14 '18 at 21:27

score 0 · Answer 2 · edited Feb 14 '18 at 21:18

0

Replace the semicolon with comma in your stored procedure's parameters

edited Feb 14 '18 at 21:18

Gururaj Bachu

3
3

answered Feb 14 '18 at 19:32

pandi

15
1
7

score 0 · Answer 3 · answered Feb 14 '18 at 19:52

0

:NEW and :OLD variables are used just in TRIGGERS.

You can't BIND then inside a stored procedure.

answered Feb 14 '18 at 19:52

Humberto de Carvalho

147
6

Trying to insert data into various tables via stored procedure, what am i doing wrong?

3 Answers3