Oracle Database





Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

In oracle, I want to see people who have the same Course Description this year as they did last year

So students where  Last years Course = This Years Couse

Data is stored as follows:

Student ID: 111000
Course Code: 100
Course Year: 2018
Student ID: 111000
Course Code: 100
Course Year: 2017

Any help much appreciated.

Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hello expert,

Using Oracle SQL Developer to import large data set from Excel Office 365.
The way it has been done before is to right click on the Tables icon in Connections
navigator, then Import Data. This goes to dialogue box titled 'Open' where a browse
thru the file structure leads to the Excel spreadsheet. Then the import dialogue
goes away and comes back in a few seconds (directly proportional to the number
of records in the Excel file). Then it goes thru a five step process.

The problem is the Excel file is 76K rows  with 13 columns.
The process described above is followed but when it goes to the import dialogue
after choosing the file at the Open screen it never comes back.

But smaller files have been imported many times. So it is surmised that the size
of the file is the issue. So the file is cut in half to 38k. Same result.
File halved again to 19k. No luck. Files halved again to 9k. The
table was created but the SQL Developer says the data failed to load
and the INSERT statement was rolled back. Finally got a file of 4.7k rows to load.

This means after the initial load of 4.7k, fifteen additional files would be
appended. Not an optimum operation.

Is there some way of doing this without 16 loads? Will the number of rows
loadable in a single pass increase by using CSV or other file type?

Is there another process that can be used to import data?


Allen Pitts, Dallas Texas
Using an Oracle Data Guard with a Primary and Standby database instance, is there a way to use a JDBC driver to send to the correct DB instance (or more specifically, to allow the application to fail over to the Standby  database instance if the primary is down)

I examined the documentation and the JDBC drivers appear to be for a RAC cluster as opposed to the Data Guard Primary and Standby

Can some one please help clarify ?

i want to disable all user from access my oracle database includig user with admin privilage like system  ;
Below is the code I have run to schedule the job in oracle using scheduler in oracle.  I want to run the procedure every 12 hours. But the job does not run automatically , I need to run it manually. Any help is really appreciated.

  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'Load_OMR_data',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN P_LOAD_INVENTORY_DATA( ''' || 'today' || ''') END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=HOURLY;Interval=12',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job to load inventory data for host database instance');
I have written a SQL query where I am getting all the required fields but the update timestamp in not in order

Though I have given as group by and order by in the script. Could someone tell what I need to change in the script below

 SELECT b.cust_id,
    SUM(DECODE(a.status_cd,'pending',1,0))  AS Pending,
    SUM(DECODE(a.status_cd,'Error',1,0))    AS Error,
    SUM(DECODE(a.status_cd,'success',1,0))  AS success,
    SUM(DECODE(a.status_cd,'existing',1,0)) AS Existing,
    SUM(DECODE(a.status_cd,'canceled',1,0)) AS Cancelled,
    SUM(DECODE(a.status_cd,'',1,0))         AS Null_Values
    FROM table a,
    table b
    WHERE A.UPDT_TS BETWEEN TO_DATE('28-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS') AND sysdate
    AND b.cust_id=a.cust_id
    GROUP BY b.cust_id,
    ORDER BY b.cust_id,
    a.updt_ts DESC;
Recently our Storage Array LUN was extended by our Sys Admin group, to allow my DBA team to increase the size of “OCR_VOTE” disk group.  Apparently, this is a new requirement for Oracle Database 12c.  Unfortunately, the Sys Admin group did not format this new extended LUN.

The DBA team has determined we need 50Gig, and this has been confirmed running the Solaris, “format -e” command, and also the “prtvtoc” command.  So, am I totally wrong thinking, my DBA team could just run “format”, select the correct disk, partition, then label, then save?

# format<cr>
Searching for disks ... done

       0. c0t0d0 <SUN146G cyl 14087 alt 2 hd 24 sec 848>
       1. c0t1d0 <SUN146G cyl 14087 alt 2 hd 24 sec 848>
       2. c3t60A98000572D577465346D3936706348d0 <NETAPP-LUN-7330-200.00GB>
i have a requirement of copying three tables from an Oracle database and dump it into another Oracle database.

Copy tables A, B, C from the source database and put it into Destination database as copies of master table A1, B1, day A2, B2, C2..and so on. This needs to happen daily.

The destination database may have these tables as unique copies for each day, to prevent overwriting..

What is the best way to do it? Should we use datapump exp/imp? if so how to write the query so that the destination database table will not be overwritten.

I am totally new to Oracle sorry.. I can share the table details if needed.
How do you think your previous experiences will help you succeed in as a master's data analyst position?
hi Team ,
I have a scenario where a Oracle stored procedure have around 20 select Queries as a sing varchar input argument . I need to execute all these 20 select statement and return 20 results sets .

create or replace PROCEDURE POC_PROJECT
  SELECT_QUERIES IN VARCHAR2 -- All select Queries will come in this Ex:- SELECT BORROWER FROM TABLE WHERE LOANID = 123456;
) AS
sql_stmt  VARCHAR2(4000);
  dbms_output.put_line(SELECT_QUERIES);-- Here i am seeing the query
  EXECUTE IMMEDIATE sql_stmt; -- How will i return the result set of this query ? If there are 10 select queries i need 10 result set

Please help me Thanks.
Never miss a deadline with
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


I have this data from an external company and after I import it into a table they send time stamps like this '2017-06-01T09:40:58.520+0000'

how can I convert it to 'DD-MON-YYYY HH24:MI:SS'

Many Thanks
I have a Oracle table with >10M (referencing other child tables) of record without date, and i want partitioning it  by date.
There is a workaround ?

 ID               NUMBER(19),
  KEY           VARCHAR2(32 BYTE) NOT NULL
this query is taking forever to return it is hitting 60 million rows can we make it better

select whb1.*

FROM e_payroll whb1 
 WHERE whb1.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99) 
       AND whb1.date_loaded = 
              (SELECT                                    /*+ parallel(whb2) */ 
                     MAX (whb2.date_loaded) AS date_loaded 
                 FROM e_payroll whb2 
                WHERE whb2.bnf_sin = whb1.bnf_sin 
                      AND whb2.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_creation = 
              (SELECT                                    /*+ parallel(whb3) */ 
                     MAX (whb3.date_creation) AS date_creation 
                 FROM e_payroll whb3 
                WHERE whb3.bnf_sin = whb1.bnf_sin 
                      AND whb3.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_loaded >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1 
       AND whb1.date_creation >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1;

Open in new window

I need to modify the following query to limit the data by quarters.  1 row of data for the previous & 1 row per current quarters.  I currently am using a Union query  (this is only 1 part of the Union qry) to create the unique rows of data, but I need help limiting the data by previous & current quarters.

I have tried using ranking and using the sysdate to create the quarter, however how do I limit the date I am using (gpa_prd_enddt) in the WHERE clause to display a row of data for each quarter requied?

          , (  SELECT to_char(sysdate,'YYYY-"Q"Q') FROM dual) as CurQtr
         -- , (  SELECT to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') FROM dual)as PrevQtr 
         , to_char(A.gpa_prd_enddt,'YYYY-"Q"Q') ENDDATE
          , C.SUPPLIER_NAME
         , Null GPA_Cost
         , Null GPA_Mgmt
         , Null GPA_Qual
         , Null GPA_Sch
         , GPA_Tech
         , Null Cost_CMT
         , Null     Mgmt_CMT
         , Null     Qual_CMT
         , Null     Sch_CMT
         , Eval_CMT Tech_CMT
        INNER JOIN gpa_evaluation B  ON A.GPA_ID = B.GPA_ID
    WHERE Evaluation_Cat =  'T'
       -- AND to_char(A.gpa_prd_enddt,'YYYY-"Q"Q')  = (SELECT to_char(sysdate,'YYYY-"Q"Q') FROM dual)

Open in new window

How can I find specific records in a large database if...?
I do not have access to any DB schema.
I do not know even which (1000) table(s) to choose.
I do not know what any of the relationships are in the tables.

"There used to be this great tool called SQL Grep that you could plug in any value integer, string, date, boolean etc.
The tool would search a large database in a few minutes identifying and determining what Table and Field the record is in.
Thus giving the ability to reverse engineer the tables and find all the relationships but that products owner shut all the products down closing the entire company.
Not sure why."

Is there a tool that can do this. This question was asked 7 years ago but the only tool was SQL Grep at the time.
I do not even know what classification this product is or what to search Google for? Would it be called a data search tool?
No idea...
Hi Team,

I have an urgent requirement , I need to write a stored procedure which makes an rest api call and it gets an 100's of inerts statement as output. My question is
how to use the UTL_HTTP package to make this call and get the output. Any help is really appreciated. I am not able to figure out should i use XML soap or json .

I have this table where 3 columns could have same information where the last 4 columns can sometime be the same and other times can have different values.

In below sample that you will also have in the attachment, you see that Brampton John Carson have 3 records but when you look at the columns Result1 to Result4, 2 rows are the same versus the other is different.

As for Ajax Bruce Saxton, Result1 to Result4 have the same values on both rows.

The objective for me is to only extract from the table when i have the same value from City, Name and Family name and when i have more then 1 difference in columns Reult1 to Result4.

Example. If my table got:
The query result would return:
Query return
How can i do that?

Thank you for your help
Oracle DB 12c Multitenant

I'm trying to combine two tasks in to one pl/sql block. I have the task of removing privs on certain tables from public then granting them to all the database users besides public. The issue is having two FOR statements inside the block. how can I accomplish this?

   -- prevent any further connections
      IN (SELECT username
            FROM dba_users
           WHERE  username <>'PUBLIC');

FOR r IN (SELECT table_name
                FROM dba_tab_privs
               WHERE table_name IN ('DBMS_LOB',
      -- grant execute
    EXECUTE IMMEDIATE  'Grant execute ON ' || r.table_name || ' to user.username
   EXECUTE IMMEDIATE 'revoke execute on <Priv> from Public';
I have a Crystal report with one field based on a Subreport, in the subreport I do a count and only pull that COUNT  into the main report, replacing the whole report with SQL is straightforward, but I'm questioning my approach to replacing the subreport part with a subquery as the results I got this far are sketchy. How would you approach it?
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

There is a table test created by info user.
To provide permissions to other user below is the syntax i have used.

create or replace synonym info1.test for info.test

is it mandatory to create synonym while granting permissions to other user?
suggest me how it helps.
I have 5 Quiries which will pull the data from the oracle database and show the results

Now I am planning to work on a project like as below

1. JAVA program to use the Quries and pull those information and
2. Write into a spreadsheet with individual sheet names and
3. Finally send out an mail to the team with the link where I have copied the sheets.

Please share your thoughts

I am looking for following query  in Oracle
    I have a table Tbl1
    I need fetch all the data from Tbl1
    But where Tbl1.Fld1 = Tbl2.Name  return Tbl2.UID as Tbl2UID
          where Tbl1.Fld2 = Tbl3.Name  return Tbl3.UID as Tbl3UID
        .. a fewmore till  Tbl1.Fld10 = Tbl10.Name  return Tbl10.UID as Tbl10UID
    I may be able to write it with Case statement . But is there a more compact way of doing it?
hi How i can avoid the NULL in the result set while using the CASE Statement in ORACLE.


I suppose to get only one row of data but its bringing  two rows  but one with NULL.

44      DAVID      data Analyst III    (null)        1000      7
44      DAVID      data Analyst III      1              1000      7

If i remove the CASE Statement from the select query i am getting only one row.
I dont want to add condition in the WHERE Clause because there are other CASE Statements as well. Please help. Thanks.
In a time column like below, how can I find the occurrence count of all the times?

2018-02-25 23:30:52
2018-02-25 23:30:56
2018-02-25 23:30:56
2018-02-25 23:31:00
2018-02-25 23:31:00
2018-02-25 23:31:04
2018-02-25 23:31:04
2018-02-25 23:31:08
2018-02-25 23:31:08
2018-02-25 23:31:12
2018-02-25 23:31:12
2018-02-25 23:31:15
2018-02-25 23:31:15
2018-02-25 23:31:15

For the above time entries column 'TIME RECORD', I would need something like:

2018-02-25 23:30:52    1
2018-02-25 23:30:56    2
2018-02-25 23:31:15    3

Thanks in advance!
Help with syntax issue in the group by  I need to create a query that will return the total contract_amount by Supplier Name.  What am I doing wrong?

SELECT DISTINCT B.supplier_legal_name                     supplier_name
      , SUM(NVL( p.blanket_total_amount * NVL(p.rate, 1), 0) ) contract_amount
      , Sum(x.invoiced_amount)                            invoiced_amount
FROM po.po_headers_all p
      Left Outer JOIN   per_all_people_mv             a ON p.agent_id = a.person_id 
      Left Outer JOIN   best_supplier_data_mv         b ON p.vendor_site_id = b.vendor_site_id
      Left Outer Join    dss_agreement_amounts_total_v   x on p.po_header_id = x.po_header_id
WHERE p.type_lookup_code IN ( 'BLANKET', 'CONTRACT' )
      AND TRUNC( NVL( p.start_date, p.approved_date ) ) <= TRUNC(SYSDATE) 
      AND TRUNC( NVL( p.end_date, SYSDATE ) ) >= TRUNC(SYSDATE) 
      AND NVL( p.closed_code, 'OPEN' ) = 'OPEN'
      AND NVL( p.cancel_flag, 'N' ) = 'N'
      AND p.approved_flag IN ( 'Y', 'R' ) 
Group By b.supplier_legal_name
      , SUM(NVL( p.blanket_total_amount * NVL(p.rate, 1), 0))
      , sum(invoiced_amount)

Open in new window


Oracle Database





Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Vendor Experts

monday.comMonday Learn more about Monday