Problems with characters executing a sentence SQL from JAVA

Question

I have store in a table in oracle data base this String:

CREATE OR replace PROCEDURE P_algorithm_ptcrb_2 ( 
s_result           IN OUT VARCHAR2, 
i_config           IN NUMBER, 
i_user             IN NUMBER, 
s_name             IN VARCHAR2, 
i_general_features IN NUMBER, 
i_pic              IN NUMBER) 
AS 
  s_aviso                VARCHAR2(2000); 
  /*TYPE array_varchar2 IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;*/ 
  l_categories           pkg_ptcrb.array_varchar2; 
  l_bands                pkg_ptcrb.array_varchar64; 
  l_test_requirements    VARCHAR2(256); 
  l_technologies         VARCHAR2(256); 
  i_test_plan_generation NUMBER; 
  i_error_pic            NUMBER; 
BEGIN /* Hora de inicio */ 
    SELECT 'Start_ ' 
           || To_char(SYSDATE, 'HH24_MI_SS') 
    INTO   s_aviso 
    FROM   dual; 

    dbms_output.Put_line(s_aviso); /* ********************* */ 
    /* STEP 0_ New test plan */ /* 
                                ********************* */ 

    s_aviso := '0.- New test plan.'; 
EXCEPTION 
  WHEN OTHERS THEN 
             Raise_application_error(-20001, 'An error was encountered - ' 
                                             ||SQLCODE 
                                             ||' -ERROR- ' 
                                             ||SQLERRM); 
END p_algorithm_ptcrb_2;

I need to execute this sentence from a java application to create dynamically a store procedure in oracle.

I'm using hibernate and before execute the sentence I replace the character ':' to '\:' and now I have this code:

CREATE OR replace PROCEDURE P_algorithm_ptcrb_2 ( s_result          IN OUT VARCHAR2, 
                                                 i_config           IN NUMBER, 
                                                 i_user             IN NUMBER, 
                                                 s_name             IN VARCHAR2, 
                                                 i_general_features IN NUMBER, 
                                                 i_pic              IN NUMBER ) AS s_aviso VARCHAR2(2000);
  /*TYPE array_varchar2 IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;*/ 
  l_categories pkg_ptcrb.array_varchar2; 
  l_bands pkg_ptcrb.array_varchar64; 
  l_test_requirements    VARCHAR2(256); 
  l_technologies         VARCHAR2(256); 
  i_test_plan_generation NUMBER; 
  i_error_pic            NUMBER; 
  BEGIN 
    /* Hora de inicio */ 
    SELECT 'Start_ ' 
                  || To_char(SYSDATE, 'HH24_MI_SS') 
    INTO   s_aviso 
    FROM   dual; 

    dbms_output.Put_line(s_aviso); 
    /* ********************* */ 
    /* STEP 0_ New test plan */ 
    /* 
********************* */ 
    s_aviso \\:= '0.- New test plan.'; 
  EXCEPTION 
  WHEN OTHERS THEN 
    Raise_application_error(-20001,'An error was encountered - ' 
    ||SQLCODE 
    ||' -ERROR- ' 
    ||SQLERRM); 
  END p_algorithm_ptcrb_2;

The only difference in the code isthe character ':' and '\:'.

Now I execute the java code:

Query q= session.createSQLQuery("create or replace PROCEDURE P_ALGORITHM_PTCRB_2 ( s_result in out VARCHAR2, i_config in NUMBER, i_user in NUMBER, s_name in VARCHAR2, i_general_features in NUMBER, i_pic in NUMBER ) AS   s_aviso VARCHAR2(2000); /*TYPE array_varchar2 IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;*/ l_categories PKG_PTCRB.array_varchar2; l_bands PKG_PTCRB.array_varchar64; l_test_requirements VARCHAR2(256); l_technologies VARCHAR2(256); i_test_plan_generation NUMBER; i_error_pic NUMBER; BEGIN /* Hora de inicio */ select 'Start_ '|| to_char(sysdate, 'HH24_MI_SS') into s_aviso from dual; dbms_output.put_line(s_aviso); /* ********************* */ /* STEP 0_ New test plan */ /* ********************* */ s_aviso \\:= '0.- New test plan.'; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END P_ALGORITHM_PTCRB_2;");

      String aux="create or replace PROCEDURE P_ALGORITHM_PTCRB_2 ( s_result in out VARCHAR2, i_config in NUMBER, i_user in NUMBER, s_name in VARCHAR2, i_general_features in NUMBER, i_pic in NUMBER ) AS   s_aviso VARCHAR2(2000); /*TYPE array_varchar2 IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;*/ l_categories PKG_PTCRB.array_varchar2; l_bands PKG_PTCRB.array_varchar64; l_test_requirements VARCHAR2(256); l_technologies VARCHAR2(256); i_test_plan_generation NUMBER; i_error_pic NUMBER; BEGIN /* Hora de inicio */ select 'Start_ '|| to_char(sysdate, 'HH24_MI_SS') into s_aviso from dual; dbms_output.put_line(s_aviso); /* ********************* */ /* STEP 0_ New test plan */ /* ********************* */ s_aviso \\:= '0.- New test plan.'; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END P_ALGORITHM_PTCRB_2;";

      new String(aux).equals(sourceCode); // <--false

      boolean isEquals=aux == sourceCode; // <--false
      log.debug(aux);
      log.debug(sourceCode);
      q= session.createSQLQuery(aux);
      q=session.createSQLQuery(sourceCode);
      q.executeUpdate();

With the String that I recover from database I obtains the error in q=session.createSQLQuery(sourceCode);

org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

But if the sentence I execute directly it works, why?? I think that could be some hidden characters, becuase if I compare using '==' the result is false, but I don't see this characters.

Thx :)

Try to replace "s_aviso := '0.- New test plan.';" with "select '0.- New test plan.' into s_aviso from dual;" — Multisync, Oct 24 '14 at 13:43
I'm using Hibernate 4.2.7. The problem doesn't the s_aviso var. I have more ':=' in the procedure which can be replace using that way. — , Oct 24 '14 at 22:17

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

From the error it seems like the : is causing an issue even though its escaped with \.

What version of hibernate are you on? Take a look at https://stackoverflow.com/a/11709475/883679 to see if this is your problem

edited May 23 '17 at 10:33

Community

1
1

answered Oct 24 '14 at 13:43

6ton

4,174
1
22
37

I'm using Hibernate 4.2.7. If I read from the text from the datase, it doen't work, but if I copy and paste the text and in put directly into the java code, it works. And both texts has the character '\\:=' q=session.createSQLQuery(sourceCode); <-- Not Work ||||| q=session.createSQLQuery("create procedure...."); <-- It Works – Oct 24 '14 at 22:18
session.createSQLQuery(sourceCode); - here use only 1 '\' – 6ton Oct 27 '14 at 13:14

Problems with characters executing a sentence SQL from JAVA

1 Answers1