I have a scenario of editing the definition of Oracle Procedures from the web page (build on Java) and compile it. Will it be possible? If so, please let me know your thoughts. The procedures might contains simple business logic with a select/update statements.
Thanks in advance.
This does not seem to be a standard requirement but yes, you should be able to do it. For fetching the code of the stored procedure from the database which you intend to modify you can use the following command.
SELECT text FROM user_source WHERE name = 'procedure_name';
Now, you can display the lines returned by this statement in a editable text box. Once, the user performs the changes you can now invoke a stored procedure in the database which will accept the string. The stored procedure can be something like this.
CREATE OR REPLACE PROCEDURE compile_proc
(
v_str IN VARCHAR2
)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE ' || v_str;
END;
/
Now, you will have to invoke this stored procedure from your webpage and pass the string. I can try to show how it can be done via PLSQL.
DECLARE
v_proc_text VARCHAR2(30000);
BEGIN
v_proc_text := 'sample_procedure AS
v_count NUMBER(1);
BEGIN
SELECT count(1) INTO v_count FROM dual;
END;';
compile_proc (v_proc_text);
END;
Needless to say you will have to take care of all the semantic and syntactic errors which might be thrown when you will compile the changes. You can possibly fetch the return codes from Oracle and show them to the user.
Something similar to your requirement is implemented on the Oracle apex cloud. You can create a free account and check it out.