CODEWITHSUNDEEP
oracle-databaseprocedure
Gustavo Menezes
Gustavo Menezes

Reputation: 137

How to execute Oracle procedure with clob parameter in?

I have a procedure

create or replace PROCEDURE PROC_PROJPREL_TEMPL_SERV_MAT(
  P_TABELA IN VARCHAR2,
  P_COLUNAS IN VARCHAR2,
  P_DADOS IN CLOB,
  O_CODIGO OUT NUMBER,
  O_MENSAGEM OUT VARCHAR2
) IS 
BEGIN
  o_codigo := 0;
  o_mensagem := '';
  -- no implementation coded yet
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20101, 'erro ao executar procedure ');    
END PROC_PROJPREL_TEMPL_SERV_MAT;

And I need to execute this in SQL Developer.

I tried using anonymous block

declare
  i_tabela varchar2(30);
  i_colunas varchar2(4000);
  i_dados clob;
  o_codigo number;
  o_mensagem varchar2(4000); 
begin
  i_tabela := 'table_name'; -- max 30 characters
  i_colunas := 'columns_names'; -- less 4000 characters
  i_dados := '45000 characters';
  proc_projprel_templ_serv_mat(i_tabela, i_colunas, i_dados, o_codigo, o_mensagem);
end;

But it returns an error "string literal too long"

and I tried using "call" command too.

call proc_projprel_templ_serv_mat('table_name', 'columns_names', &DATAS);

But it returns an error ORA-00972 identifier is too long, Cause: An identifier with more than 30 characters was specified, Action: Specify at most 30 characters.

Somebody can help me?

Upvotes: 1

Views: 2142

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9775

The maximum length of a string literal in PL/SQL is 32,767 characters. As the error "string literal too long" is saying, you're blowing out this limit here:

i_dados := '45000 characters';

You have to break up that string into sections up to 32,767 characters long and concatenate them together, e.g.:

i_dados := 'first 32767 characters' || 
           'remaining 12233 characters';

Upvotes: 1

Related Questions