Enlightensoft's Blog

Helping in your each step

  • Categories

  • Authors

Archive for the ‘Oracle’ Category

Oracle

Pass String (or Custom Object) List into Store Procedure from Java

Posted by Pankil Patel on November 1, 2012

  1. Create type:create or replace
    TYPE STRING_LIST_TYPE AS TABLE OF VARCHAR2(7);
  2. Java Code:ARRAY sqlArrayOfString = getSqlArray(arrayListOfString.toArray(), “STRING_LIST_TYPE”,
    tssPortalDataSource);
  3. Store Procedure input param:P_NAME in STRING_LIST_TYPE,
  4. Use list into IN parameter of query in Store Procedure:

select * from EMPLOY where EMPLOY_NAME in (select column_value from (Table(CAST(P_NAME AS STRING_LIST_TYPE))))

Posted in Core Java, Database, Oracle | Tagged: , | Leave a Comment »

Aggregate functions with extra column in select

Posted by Pankil Patel on October 31, 2012

How to select extra column(s) with aggregate function in query?

  1. Rollback script:
    ALTER TABLE EMP_DEPARTMENT DROP CONSTRAINT UNIQUE_1;
    DROP TABLE EMP_DEPARTMENT;
    DROP TABLE EMPLOY;
    DROP TABLE DEPARTMENT;
  2. Create script:
    CREATE TABLE “TSSRISKAPPDBO”.”EMPLOY”
    (
    “EMPLOY_ID”   NUMBER(10,0),
    “EMPLOY_NAME” VARCHAR2(100 BYTE),
    “SALARY”      NUMBER(10,2),
    CONSTRAINT “PK_EMPLOY” PRIMARY KEY (“EMPLOY_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

    CREATE TABLE “TSSRISKAPPDBO”.”DEPARTMENT”
    (
    “DEPARTMENT_ID”   NUMBER(10,0),
    “DEPARTMENT_NAME” VARCHAR2(100 BYTE),
    CONSTRAINT “PK_DEPARTMENT” PRIMARY KEY (“DEPARTMENT_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

    CREATE TABLE “TSSRISKAPPDBO”.”EMP_DEPARTMENT”
    (
    “EMPLOY_ID”     NUMBER(10,0),
    “DEPARTMENT_ID” NUMBER(10,0),
    CONSTRAINT “UNIQUE_1” UNIQUE (“EMPLOY_ID”, “DEPARTMENT_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE,
    CONSTRAINT “FK_EMP” FOREIGN KEY (“EMPLOY_ID”) REFERENCES “TSSRISKAPPDBO”.”EMPLOY” (“EMPLOY_ID”) ENABLE,
    CONSTRAINT “FK_DEPT” FOREIGN KEY (“DEPARTMENT_ID”) REFERENCES “TSSRISKAPPDBO”.”DEPARTMENT” (“DEPARTMENT_ID”) ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

  3. Data creation:
    INSERT INTO EMPLOY VALUES (1,’VIBHA’,21);
    INSERT INTO EMPLOY VALUES (2,’PANKIL’,10.2);
    INSERT INTO EMPLOY VALUES (3,’MAYANK’,15);
    INSERT INTO EMPLOY VALUES (4,’NAMRATA’,12);
    INSERT INTO EMPLOY VALUES (5,’NISHA’,9);
    INSERT INTO EMPLOY VALUES (6,’SHAILESH’,12);

    INSERT INTO DEPARTMENT VALUES (1,’APPS’);
    INSERT INTO DEPARTMENT VALUES (2,’UET’);
    INSERT INTO DEPARTMENT VALUES (3,’PROCESS’);
    INSERT INTO DEPARTMENT VALUES (4,’BPO’);

    INSERT INTO EMP_DEPARTMENT VALUES (1,1);
    INSERT INTO EMP_DEPARTMENT VALUES (2,2);
    INSERT INTO EMP_DEPARTMENT VALUES (3,3);
    INSERT INTO EMP_DEPARTMENT VALUES (4,4);
    INSERT INTO EMP_DEPARTMENT VALUES (5,4);
    INSERT INTO EMP_DEPARTMENT VALUES (6,1);
    INSERT INTO EMP_DEPARTMENT VALUES (6,3);
    INSERT INTO EMP_DEPARTMENT VALUES (4,1);
    INSERT INTO EMP_DEPARTMENT VALUES (4,2);
    INSERT INTO EMP_DEPARTMENT VALUES (6,2);

  4. Query: Get Employee(s) data of employee(s) who has the highest Salary in “BPO” department

    SELECT *
    FROM EMPLOY E,
    (SELECT MAX(SALARY) AS MAX_SALARY
    FROM EMPLOY
    WHERE EMPLOY_ID IN
    (SELECT EMPLOY_ID
    FROM EMP_DEPARTMENT
    WHERE DEPARTMENT_ID =
    (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT_NAME = ‘BPO’
    )
    )
    ) MAX_RESULT
    WHERE MAX_RESULT.MAX_SALARY = E.SALARY
    AND E.EMPLOY_ID            IN
    (SELECT EMPLOY_ID
    FROM EMP_DEPARTMENT
    WHERE DEPARTMENT_ID =
    (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT_NAME = ‘BPO’
    )
    );

 

Note: Summary of above code is: get inner join of any aggregate function’s result with the table field

Apply where condition at both places (In inner query having aggregate function  and In outer query with table)

Posted in Database, Oracle | 2 Comments »

“printf” in Oracle SQL Procedure

Posted by Pankil Patel on October 31, 2012

How you would print data to DBMS OUTPUT. during execution of Store Procedure?

OR

How to debug Oracle Store Procedure?

Below is the syntax to print data to DBMS OUTPUT:

DBMS_OUTPUT.PUT_LINE (‘PANKIL—————:’ || v_sql || ‘:’);

Posted in Database, Oracle | Tagged: , , , | Leave a Comment »

How to find dependent tables on my table?

Posted by Pankil Patel on October 3, 2012

Based on constraints on my table, how to find dependent tables?

Below is the query which will give me the tables name & constraint name those are related to MY_TABLE:

select table_name, constraint_name, status, owner
from all_constraints
where  r_constraint_name in
 (
   select CONSTRAINT_NAME from ALL_CONSTRAINTS
   WHERE upper(table_name) = ‘MY_TABLE’   
 )
order by table_name, constraint_name;

Posted in Database, Oracle | Tagged: , , , , , , | Leave a Comment »

Pure JDBC Connection for Oracle Database in Core JAVA using Service name & SID

Posted by Pankil Patel on June 22, 2012

Required JAR: classes12.jar

Pure JDBC Connection for Oracle Database in Core JAVA using :  Service name :

Connection con = null;
Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection(
“jdbc:oracle:thin:@Hostname:Port/Service name”,
“username”, “password”);

Statement s = con.createStatement();

Pure JDBC Connection for Oracle Database in Core JAVA using :  SID :

Connection con = null;
Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection(
“jdbc:oracle:thin:@Hostname:Port:SID”,
“username”, “password”);

Statement s = con.createStatement();

Posted in Core Java, Oracle | Tagged: , , , , , | Leave a Comment »

Nth Highest record in Oracle

Posted by Pankil Patel on April 18, 2012

First solution:

nth highest record from Bv_Category table based on Indvl_Rank_Id

select * From
(
Select C.*, Dense_Rank() Over (Order By C.Indvl_Rank_Id Desc) Ranking From Bv_Category C
)
where Ranking = (nth index)

Example: Record with Second Highest Indvl_Rank_Id

select * From
(
Select C.*, Dense_Rank() Over (Order By C.Indvl_Rank_Id Desc) Ranking From Bv_Category C
)
where Ranking = 2

For more detail visit:

http://www.oratable.com/nth-highest-salary-in-oracle/

Second solution:

Example: Second Highest Indvl_Rank_Id

Select Min(Indvl_Rank_Id) From (
select * from (
Select Unique Indvl_Rank_Id From
Bv_Category
ORDER BY Indvl_Rank_Id DESC) where ROWNUM <= 2)

Posted in Database, Oracle | Tagged: , , , | Leave a Comment »

jPub Object Generation Command

Posted by Pankil Patel on January 11, 2012

set path=%path%.;C:\oracle\product\10.2.0\client_1\BIN;

set classpath=%classpath%;.;C:\oracle\product\10.2.0\client_1\sqlj\lib\runtime12.jar;C:\oracle\product\10.2.0\client_1\sqlj\lib\translator.jar;C:\oracle\product\10.2.0\client_2\jdbc\lib\ojdbc14.jar;c:\xmlparserv2.jar;C:\oracle\product\10.2.0\client_1\sqlj\lib\runtime12ee

jpub -url=jdbc:oracle:thin:@<HostName>:15000/<service name> -user=username/password -omit_schema_names -package=co.cc.enlightensoft.demo.vote.db.dto -sql=BV_OBJ_FIRM_ARRARY_TAB:UserDBDto -tostring=true -dir=C:\jpub

Posted in Database, Oracle | Leave a Comment »

Command to copy table in same database schema

Posted by Pankil Patel on April 8, 2011

create table ABC_COPY as select * from ABC;

Note: It will create new table ABC_COPY in same schema as table ABC, with all data in new table same as original table data.

But new table doesn’t having any constrains created as they are in original table.

You need to add/execute alter script yourself to add constrain in new table.

Posted in Database, Oracle | Leave a Comment »