Why is this Stored Procedure Inserting NULL values into the table? [closed]

Question

I am trying to load data (2 columns) from XML_HOURS_LOAD (columns: code,product) to a table called STAGING (columns: code, product) and am getting null values inserted for both columns:

So I have the following stored procedure:

Create or Replace Procedure Cascade_Load (
   p_code in XML_HOURS_LOAD.p_code%TYPE,
   p_product in XML_HOURS_LOAD.p_product%TYPE
)
AS
BEGIN
   INSERT INTO STAGING(code, product)
   VALUES(p_code, p_product)

   COMMIT;
END;

What am I doing wrong? Thanks in advance.

I am just running it (right click, execute). I don't know how i would be able to call the procedure when using a SELECT statement in place of "hard-coded" values. Any suggestions? — icerabbit

Sam M Sam M · Accepted Answer · 2018-02-08T03:09:52

To answer your question of why it's inserting nulls, that is because you aren't providing any values to the procedure parameters when you execute it.

Based on what you stated in the question and your comment above, it seems you are missing some fundamental skills in working with Oracle. The code you wrote is a procedure, not a function, so you can't call it in a SELECT statement. A procedure is called inside of a plsql block. Your procedure as written takes two arguments, which you must pass to the procedure call via the calling code. The procedure code you wrote does not look for data from the XML_HOURS_LOAD table.

We've all been the new person learning Oracle. You'll want to look at some tutorials to get you started on the fundamentals of pl/sql coding to help clear up the differences between functions and stored procedures and how to use parameter arguments.

From what you wrote in your question, I believe this is the code you want:

DECLARE
   p_code IS XML_HOURS_LOAD.code%TYPE,
   p_product IS XML_HOURS_LOAD.product%TYPE;
   CURSOR cXmlHoursLoadCursor IS (SELECT code, product FROM xml_hours_load); --You can add a WHERE condition to this cursor query
BEGIN
    FOR v IN cXmlHoursLoadCursor LOOP
       Cascade_Load(v.code, v.product);
       COMMIT; --I recommend calling commit here instead of inside your stored procedure so that the calling code has control of the transaction state
    END LOOP;
END;

Why is this Stored Procedure Inserting NULL values into the table? [closed]

1 Answers