Hi,
I'm trying to create procedure that will multiply records in a table in Oracle Apex. Table was created using the following code:
CREATE TABLE Produkty (
Id NUMBER(10) NOT NULL,
Nazwa VARCHAR2(50) NOT NULL,
Status VARCHAR2(50) NOT NULL,
CONSTRAINT CHK_Status CHECK (Status IN ('czynny', 'zakonczony')),
CONSTRAINT PK_Id PRIMARY KEY (Id)
);
I'm using the following code:
CREATE OR REPLACE PROCEDURE testy_wydajnosciowe (
p_table_name IN VARCHAR2
) AS
v_sql NUMBER;
top_sql NUMBER;
BEGIN
-- Create query to count records
v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
top_sql := v_sql * 3;
FOR i IN v_sql..top_sql LOOP
INSERT INTO Produkty (id, nazwa, status)
VALUES (i,'test'||TO_CHAR(i),'czynny');
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Handle exceptions (e.g., table not found)
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END testy_wydajnosciowe;
/
Currently I have 9 records in table. The procedure compiles successfully with no errors. Then I run it from SQL Script section with the following command:
set serveroutput on
BEGIN
testy_wydajnosciowe(p_table_name => 'Produkty');
END;
/
I got message “statement processed” and no errors. But the records are not added to table Produkty it still has 9 records. What do I do wrong and how to fix it? Thanks.