Skip to Main Content
Forums

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to execute statement with duplicate parameters?

610683May 7 2013 — edited Oct 15 2014
Hello.

Is it possible to execute PL\SQL with contains two or more duplicate name parameters?

Example: begin select *:v* ,*:v* into :v1, :v2 from dual; end;

I tried to execute it using follow code:

String plsql = "begin select :v ,:v into :v1, :v2 from dual; end;";
OracleCallableStatement callableStatement = (OracleCallableStatement) connection_.prepareCall(plsql);
callableStatement.setInt(1, 1);
callableStatement.setInt(2, 2);
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.registerOutParameter(4, Types.INTEGER);
callableStatement.execute();

result of executing it, is error: java.sql.SQLException: ORA-01036: illegal variable name/number

If I register parameters by name:

String plsql = "begin select :v ,:v into :v1, :v2 from dual; end;";
OracleCallableStatement callableStatement = (OracleCallableStatement) connection_.prepareCall(plsql);
callableStatement.setIntAtName("v1", 0);
callableStatement.setIntAtName("v", 1);
callableStatement.setIntAtName("v2", 0);
callableStatement.execute();

, i have same error: java.sql.SQLException: ORA-01036: illegal variable name/number

I'm porting Delphi application to Java, and can not change PL\SQL code. In Delphi this pl\sql execute successfully.
This post has been answered by jschellSomeoneStoleMyAlias on May 8 2013
Jump to Answer

Comments

xilan-Oracle
Answer

Hi,

To develop RESTful Services in SQLDev, you have to create an ORDS user using its user command, for example:

java -jar ords.war user ords_sqldev "SQL Developer"

The role Listener Administrator is used for Administration not for REST Development.

Once you have the user, in SQLDev:

1. Right click on a database connection, for example scott, enable REST on it

2. Click View -> REST Data Services -> Development

3. Click the Connect icon, enter the following fields:

Username: ords_sqldev

Hostname: your_host (localhost for example)

Port: 8080

Server Path: /ords

Schema/Workspace: scott

4. Do what you did to define a service above

5. Click the Validate RESTful Services icon to validate the service

6. Right click on the module you defined and upload it

7. Access the endpoint from a web browser

Hope this helps.

Thanks,

Xiaohe

Marked as Answer by itshak · Sep 27 2020
1 person found this helpful
itshak

Hi Xilan,

Your answer help me very much ! I success to crate and test the RESTful service.

There is any limitation about select statement that return an user defined object type?

My statement:

select lib_patient.wsget( '18', null) from dual

return one record based on some DB user defined object.

At the browser, I see Capture.JPG

Thanks

Isaac

xilan-Oracle

Hi Isaac,

Currently, seems you cannot use a complex data type.

Thanks,

Xiaohe

itshak

Hi Xilan,

Thanks very much again.

We found an workaround:

select *

from table( cast( tya_patient( lib_patient.wsget( '18', null) ) as tya_patient ) )

Capture.JPG

Even, Hebrew characters are displayed selecting "Unicode" at Browser Text Encoding.

Do you have an RESTful example, If I want to run a pl/sql block that return some value?

Isaac

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 5 2013
Added on May 7 2013
#11g, #database-connectivity-discussions, #jdbc
8 comments
1,017 views