using PLSQL block in PreparedStatement

Question

I am trying to use a PL/SQL block in PreparedStatement in Java. The schema name will be coming externally. Here is the code:

PreparedStatement statement = null;
try {
    statement = connection.prepareStatement("
                        declare 
                        p_schema_name varchar2(400):= upper('" + schema + "'); 
                        l_owner varchar2(400);
                        l_object_name varchar2(400);
                        cursor c1_views is
                            select
                                owner,
                                object_name
                            from dba_objects
                            where object_type in (''VIEW'')
                            and status = ''INVALID''
                            and owner = p_schema_name;
                    begin
                        open c1_views;
                        loop
                            fetch c1_views into l_owner, l_object_name;  
                            if c1_views%notfound then
                                exit;
                            else
                                execute immediate 'alter view '||l_owner||'.'||l_object_name||' compile ';
                            end if;
                        end loop;
                        begin
                            dbms_utility.compile_schema(schema => p_schema_name);
                        end;
                    end;");
                statement.execute();
            } catch (SQLException e) {
                System.out.println("ERROR: Unable to compile schema " + schema + " in afterMigrate: " + e.getMessage());
            } finally {
                if (null != statement) {
                    try {
                        statement.close();
                    } catch (SQLException se) {
                        System.out.println("ERROR: Unable to close statement in afterMigrate: " + se.getMessage());
                    }
                }
            }

and these are the errors I got

javac example.java
example.java:25: error: unclosed string literal
                                statement = connection.prepareStatement("
                                                                        ^
example.java:25: error: ';' expected
                                statement = connection.prepareStatement("
                                                                         ^
example.java:27: error: ';' expected
                                                p_schema_name varchar2(400):= up
per('" + schema + "');
                                                                      ^
example.java:27: error: unclosed character literal
                                                p_schema_name varchar2(400):= up
per('" + schema + "');

    ^
example.java:27: error: unclosed string literal
                                                p_schema_name varchar2(400):= up
per('" + schema + "');

                  ^
example.java:27: error: not a statement
                                                p_schema_name varchar2(400):= up
per('" + schema + "');

                ^
example.java:28: error: ';' expected
                                                l_owner varchar2(400);
                                                                ^
example.java:29: error: ';' expected
                                                l_object_name varchar2(400);
                                                                      ^
example.java:30: error: ';' expected
                                                cursor c1_views is
                                                               ^
example.java:31: error: ';' expected
                                                        select
                                                              ^
example.java:32: error: not a statement
                                                                owner,
                                                                ^
example.java:32: error: ';' expected
                                                                owner,
                                                                     ^
example.java:34: error: ';' expected
                                                        from dba_objects
                                                            ^
example.java:35: error: ';' expected
                                                        where object_type in (''
VIEW'')
                                                             ^
example.java:35: error: ';' expected
                                                        where object_type in (''
VIEW'')
                                                                            ^
example.java:35: error: empty character literal
                                                        where object_type in (''
VIEW'')
                                                                              ^
example.java:35: error: unclosed character literal
                                                        where object_type in (''
VIEW'')
                                                                               ^

example.java:35: error: empty character literal
                                                        where object_type in (''
VIEW'')

    ^
example.java:35: error: not a statement
                                                        where object_type in (''
VIEW'')

 ^
example.java:35: error: unclosed character literal
                                                        where object_type in (''
VIEW'')

     ^
example.java:36: error: empty character literal
                                                        and status = ''INVALID''

                                                                     ^
example.java:36: error: unclosed character literal
                                                        and status = ''INVALID''

                                                                      ^
example.java:36: error: empty character literal
                                                        and status = ''INVALID''

                                                                              ^
example.java:36: error: illegal line end in character literal
                                                        and status = ''INVALID''

                                                                               ^

example.java:39: error: ';' expected
                                                open c1_views;
                                                    ^
example.java:39: error: not a statement
                                                open c1_views;
                                                     ^
example.java:41: error: ';' expected
                                                        fetch c1_views into l_ow
ner, l_object_name;
                                                             ^
example.java:41: error: ';' expected
                                                        fetch c1_views into l_ow
ner, l_object_name;
                                                                           ^
example.java:41: error: not a statement
                                                        fetch c1_views into l_ow
ner, l_object_name;
                                                                            ^
example.java:41: error: ';' expected
                                                        fetch c1_views into l_ow
ner, l_object_name;

   ^
example.java:41: error: not a statement
                                                        fetch c1_views into l_ow
ner, l_object_name;

     ^
example.java:42: error: '(' expected
                                                        if c1_views%notfound the
n
                                                          ^
example.java:42: error: ')' expected
                                                        if c1_views%notfound the
n
                                                                            ^
example.java:43: error: variable declaration not allowed here
                                                                exit;
                                                                ^
example.java:45: error: unclosed character literal
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

  ^
example.java:45: error: variable declaration not allowed here
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';
                                                                        ^
example.java:45: error: unclosed character literal
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

              ^
example.java:45: error: unclosed character literal
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

                                              ^
example.java:45: error: not a statement
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

                                            ^
example.java:45: error: unclosed character literal
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

                                                        ^
example.java:45: error: not a statement
                                                                execute immediat
e 'alter view '||l_owner||'.'||l_object_name||' compile ';

                                                ^
example.java:46: error: not a statement
                                                        end if;
                                                        ^
example.java:46: error: ';' expected
                                                        end if;
                                                           ^
example.java:46: error: '(' expected
                                                        end if;
                                                              ^
example.java:46: error: ')' expected
                                                        end if;
                                                               ^
example.java:47: error: not a statement
                                                end loop;
                                                    ^
example.java:49: error: ';' expected
                                                        dbms_utility.compile_sch
ema(schema => p_schema_name);
                                                                    ^
example.java:49: error: illegal start of expression
                                                        dbms_utility.compile_sch
ema(schema => p_schema_name);

            ^
example.java:50: error: not a statement
                                                end;
                                                ^
example.java:51: error: not a statement
                                        end;");
                                        ^
example.java:51: error: unclosed string literal
                                        end;");
                                            ^
51 errors

Any idea where I am making the mistake?

Thanks in advance.

Thanks. I just added the errors. Forgot to add them on first place — Jaanna, Apr 13 '16 at 09:04
Java doesn't have multi-line strings. You need to use `"Line 1" + "line 2" + ...` etc, not `"Line 1 line 2 ..."` (so each line needs to contain an open and close double quote) — Mark Rotteveel, Apr 13 '16 at 09:08
@Jaanna I think you are not using IDE like eclipse. If you use eclipse then this type of compile errors you can easily solve during coding — SpringLearner, Apr 13 '16 at 09:13
Thanks folks. I will certainly switch to an IDE. Now I have been using notepad++ :-) — Jaanna, Apr 13 '16 at 11:32

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

I would also suggest to test your code in PLSQL first. If it works there, you can utilize the function of Eclipse, which does all the escaping and formatting for you.

Take a look at the accepted answer here: Paste a multi-line Java String in Eclipse

edited May 23 '17 at 11:45

Community

1
1

answered Apr 13 '16 at 09:21

grange

582
5
17

1

Thanks. Yes, IDE is the way to go :-) – Jaanna Apr 13 '16 at 11:33

J. Chomel · Accepted Answer · 2016-04-13T09:14:55.570

First thing is to let the PLSQL code work, alone from java.

=> you do not need '' in the code. Change to this:

                        from dba_objects
                        where object_type in ('VIEW')
                        and status = 'INVALID'
                        and owner = p_schema_name;

Then from the error, you must not end lines within the java string. Try adding EOL manually like:

statement = connection.prepareStatement(" declare " + "\n" +
"                        p_schema_name varchar2(400):= upper('" + schema + "');  "+"\n" +
"                        l_owner varchar2(400); "+"\n" +
"                        l_object_name varchar2(400); "+"\n"+ 
"                        cursor c1_views is "+"\n" +
"                            select "+"\n" +
"                                owner, "+"\n"+ 
"                                object_name "+"\n" +
"                            from dba_objects "+"\n" +
"                            where object_type in ('VIEW') "+"\n" + 
"                            and status = 'INVALID' "+"\n" +
"                            and owner = p_schema_name; "+"\n" +
"                    begin "+"\n" +
"                        open c1_views; "+"\n" +
"                        loop "+"\n" +
"                            fetch c1_views into l_owner,l_object_name;  "+"\n" + 
"                            if c1_views%notfound then "+"\n" +
"                                exit; "+"\n" + 
"                            else "+"\n" +
"                                execute immediate 'alter view    '||l_owner||'.'||l_object_name||' compile '; "+"\n" +
"                            end if; "+"\n" +
"                        end loop; "+"\n" +
"                        begin "+"\n" +
"                            dbms_utility.compile_schema(schema =>    p_schema_name); "+"\n" +
"                        end;   "+"\n" +
"                    end;");

Thanks. This approached helped me for time being till I install an IDE :-) — Jaanna, Apr 13 '16 at 10:49

using PLSQL block in PreparedStatement

2 Answers2