URL string format for connecting to Oracle database with JDBC

There are two ways to set this up. If you have an SID, use this (older) format: jdbc:oracle:thin:@[HOST][:PORT]:SID If you have an Oracle service name, use this (newer) format: jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE Source: this OraFAQ page The call to getConnection() is correct. Also, as duffymo said, make sure the actual driver code is present by including ojdbc6.jar … Read more

How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?

If GetQuestions is a function returning a refcursor, which seems to be what you have in the SQL Server version, then rather you may be able to do something like this: select * from table(MyPackage.GetQuestions(‘OMG Ponies’)); Or if you need it in a PL/SQL block then you can use the same select in a cursor. … Read more

Dynamically pivoting a table Oracle

This can be done dynamically the following way. First, here is the static version of the query so you can see the final sql: select c_id, p_id, max(case when r_key= ‘KEY1’ then r_value end) KEY1, max(case when r_key= ‘KEY2’ then r_value end) KEY2, max(case when r_key= ‘KEY3’ then r_value end) KEY3, max(case when r_key= ‘KEY4’ … Read more

Oracle Age calculation from Date of birth and Today

SQL> select trunc(months_between(sysdate,dob)/12) year, 2 trunc(mod(months_between(sysdate,dob),12)) month, 3 trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day 4 from (Select to_date(‘15122000′,’DDMMYYYY’) dob from dual); YEAR MONTH DAY ———- ———- ———- 9 5 26 SQL>

Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

Bind variables are not allowed in DDL statements. So following statements will cause errors: Example #1: DDL statement. Will cause ORA-01027: bind variables not allowed for data definition operations EXECUTE IMMEDIATE ‘CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )’ USING 42; Example #2: DDL statement. Will cause ORA-00904: : invalid identifier EXECUTE IMMEDIATE ‘CREATE … Read more