Can I compile a large stored procedure over a database link?

Question

I am working in an Oracle database and do not have direct access to our production database. I do have indirect access via a database link. The link is with the same user that all custom code runs under and has the same permissions as if I were to login directly as that user.

My question is, given this access, is it possible to compile a large stored procedure or package over the link/?

I found the function

dbms_utility.EXEC_DDL_STATEMENT

I am successful in using this to compile a procedure to the target database, however if the procedure is large, over 32k b, I get an error

[Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 21

Is it possible to send more than 32k b over to a target database and have the database compile the code? Here is a snippet of what I am trying to achieve

declare vsql  NCLOB ;
i   NUMBER := 0;

begin

FOR rec IN (
            select * from all_source@dblink
              where owner = :schema_owner
              and name = :procedure_name
            order by  line asc
            )
    LOOP

        vsql := vsql ||  rec.text;
        --this outputs fine
        DBMS_OUTPUT.put_line ('Record ' || i || ': ' || rec.text);

    END LOOP;
--line errors with  ORA-06502
dbms_utility.EXEC_DDL_STATEMENT@dblink(vsql);

end;

[`EXEC_DDL_STATEMENT`](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_UTILITY.html#GUID-5DFAACBB-7897-4BF9-9069-F09D34F6D811) takes a `varchar2` argument, not a CLOB, so the error makes sense. But your code makes it look like you're trying to compile the source of the remote procedure locally, not over a link. And if you are actually calling `dbms_utility` remotely, if you're using the current definition, then why - are you just recompiling with no changes? (I'd still wonder why; and why you're doing anything ad hoc against a production DB...) — Alex Poole, Dec 13 '18 at 18:24
You are right @AlexPoole, i left out the link name in the function call but had it on my TOAD window. Still tho, with the link set right and using varchar, is there a way to do this? varchar can not hold a string that is as large as I need. DO you know of any other method? — Brian Adam, Dec 13 '18 at 18:31
Error at line 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 14 — Brian Adam, Dec 13 '18 at 18:33
I'm still unclear whether you're just recompiling the procedure with its existing code, for some reason; or are trying to compile new code that doesn't already exist on the remote DB. — Alex Poole, Dec 13 '18 at 18:34
Interesting idea. Do you have any special requirements regarding the Oracle version? — wolφi, Dec 13 '18 at 18:39
In this example I am pulling the sql from the target dB into the source dB then trying to send it back over the Dublin k to the target dB to compile it anew. This was my test case. My actual use case is to pull the procedure from the source dB or a source db2 and compile to target dB. — Brian Adam, Dec 14 '18 at 20:43
I know the file structure of all servers in the all environments. Maybe it’s possible to write out, line by line, to a file then have the file read to compile the code back into the dB? I’m unsure if such a function exists from oracle or toad or sql plus. Some of the procedures I need to compile are upwards of 120k bytes. Which varchar only can hold around 32k byte which is where I’m running into the issue of being able to send the string over to be run on the target machine. I tested and can compile a new procedure with the above method as long as it fits within a varchar object. — Brian Adam, Dec 14 '18 at 20:46

score 0 · Answer 1 · answered Dec 28 '18 at 15:24

You're probably in luck.

Since Oracle 11g Execute Immediate can accept CLOB data. Just create a small procedure like this example:

CREATE OR REPLACE PROCEDURE generate_from_clob( p_source IN OUT CLOB )
    AUTHID CURRENT_USER
IS
BEGIN
    EXECUTE IMMEDIATE p_source;
END;
/

Due to the Holidays I cannot test this now.

You might need the info from this link overcome 32K limit when inserting oracle clob.... to actually pass a CLOB over a DB-Link.

I expect something like this to work however:

DECLARE
   l_source  CLOB := 'create or replace Package very_long is
  .
  .
  .
  end very_long;';
BEGIN
   generate_clob@remote_DB_Link( l_source );
END;
/

This looks promising . I’ll give it a try later this evening. Thanks for the input! — Brian Adam, Jan 02 '19 at 20:57

Can I compile a large stored procedure over a database link?

1 Answers1