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

Hi Team,

Iam written the below code , In one block , i written the code to put data into buffer , in another block iam reading the buffer , iam not getting the expected output.

dbms_output.put(' Welcome to DBMS_OUTPUT package');
dbms_output.put_line('Its used to communicate between program units in the server');

v_line varchar2(100);
v_lines dbms_output.chararr;
v_status integer;

Iam not getting the output, Can any one help what is wrong with the code.
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

i have a table where i need to look up .... and then update a single field..
table A
c1 c2 c3 c4 c5
table B
c1 c2 c6 c7 c8 c9

in these two we have c1 and c2 common ....
will populate c9 based on c4 if (c1,c2 are same and c4 not null)
else update c9 to value Base for c7 equal B
 or update c9 to Dollar for c7 equal D
or update c9 to Sed for c7 equal S

my query does this
if c9 not null then
check in table A for C4 not null (c1 and c2 found)
else set based on c7 works

but when an update happens to table and c7 gets set then it does not reflect ....
how to solve this ?
We are working on the requirement.
We are getting transaction data to one staging db ( The table having clob column and store this data as xml). we need to parse this xml and insert/update data in three different tables.

xml  like this::

       Field information...

Based on the xml.. we need to update
Batch Table
Document Table
Field Table


I created an oracle external table, but got error when select from the table.
Please see the attached file.

Any idea would be appreciated.

Thanks a lot,

I have to determine which spec_int have a tests where all have any result.  The other group would be spec_int which have tests where one or more does not have a result_id

Have tried to put together inner queries to get counts of tests with results, then bring it out to an outer query that would then list the group where all tests have result, and/or the groups of spec_int in which one or more tests have no result_id.

Table spec_int contains spec_int_id and hold.
table test3 contains spec_int_id and  test_ids,
table result4 contains test_ids and result_id.
Table 5 contains results.

I need to determine which spec_int's are not on hold and have tests that all have result_id's.   Does not a matter what the result is, just whether there is a result for every test contained in a spec_int.

Spec_int_id         hold
       1                    N
       2                    N
       3                     N
       4                    Y

Spec_int_id     Test   result
       1                69
       1                55       5
       2                87       8
       2                44       6
       3                45

Desired results:           
Groups  status
   1           open
   2          closed
  3           open
  4           on hold

Open in new window

  HOLD         CHAR(1 BYTE)

  TEST_ID       NUMBER(10)

Open in new window

I have a table Tb1 as follows:

Col1 Col2 MCol1 MCol2   MCol3
3       4        1         1            X
3       4        1         3            X
3       4        1         6            X
4       5        1         1            X
4       5        1         2            X

I need to update MCol3 to Z where MCol2 is not the minimum value for a certain Col1 and Col2. So the values I want after the update are:

Col1 Col2 MCol1 MCol2   MCol3
3       4        1         1            X
3       4        1         3            Z
3       4        1         6            Z
4       5        1         1            X
4       5        1         2            Z

I have a pretty convoluted way of doing this with a lot of different SELECTs but I was wondering if anybody has any lean solution.

I have set up my connection to an Oracle Database in Excel and the Command Text in the definition tab of the Properties window says 'SELECT * FROM "PS_OWNER"."PS_JOB"' which returns too much data for an excel spreadsheet. So my question is, can I use VBA to return the data the same way I return it in SQL Developer?
I am developing an application that needs to connect to an Oracle database. I've added the required Nuget packages to connect to the database. If I have the DBA credentials to a schema, I can connect fine and expand the tables in the schema to add them to my model. However, on another schema I only have permissions to SELECT from a table. So, when I go through the wizard to set up my EF model I can get to the page to select the Tables I want to add to my model. However, I can't get the Tables node to expand in order to select the table that I have SELECT permissions on. The first screenshot show the screen I am stuck on. I Cannot click the check-box or expand the node to show the tables. The second screenshot shows the same behavior in VS2015 Server Explorer in that the node under 'Tables' can't be expanded to show that table I have select access to.

As I said before, there are other schemas where I have DBA privileges and these work fine (I can expand all the nodes) . Surely we are not restricted to users with DBA privileges when  using EF?

Any help, much appreciated
I'm using for Windows 64bit. It comes with Java 5 as part of the install (I'm assuming so you don't need to have Java installed to use Oracle). How do I update that version of Java? I'm running into an error where I need the unlimited crypto package but I can't use it on Java 5 (I tried and it grenaded parts of Windows if I update the security files). I did then install Java 8 on the system but Oracle seems to look at it's own vs what is installed outside of Oracle.
We have a small java program that connects to an Oracle ( Windows) DB. There is a jks file that has the certs in it (4096 key size). When we try to connect we get: PKIX path validation failed: Algorithm constraints check failed: MD5withRSA

If I make changes to entries below in the file in Java:


and remove MD5 and MD5withRSA parameters it works. From what I've read this is supposed to be an issue in JRE 7.4 and above but it is only supposed to happen when they key length is 1024 or less. Not sure why it's happening with a 4096 key length cert.
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Db: Oracle 11g

We have an etl job run via a third party scheduler that connects to oracle database via db link and fetches the data into a file.

The query is as follows:

<bunch of colums>

This fails on the first run with the following error:

Error code: 8103, Error message: ORA-08103: object no longer exists.

On the re-run it runs just fine.

The object always exists on the remote database. Cannot understand why it fails on the first run?

Any thoughts? Please let me know if you need more information
I had this question after viewing Create Function  which returns records from multiple tables..

I see this query from the above question:

select * from table(DepartmentEmployees('07/09/2017','07/20/2017'));

how to change the above to get the parameters from other table.

TableA  ==> This table will only have one record as below, and I need to get those into above query.

I tried something like below but I'm getting error ORA--00936 Missing Expression.

select (select start_Date from tableA where rownum = 1) as start_date,
       (select end_Date from tableA where rownum = 1) as end_date,
  from table(DepartmentEmployees(start_date - 30, end_date) );
Regards All,

What options are out there if I am to make something to export a quesry result to tab-separated file, and store it on network place.
Size can be about 100Mb, so quite amount of data.

I'd rather not make it Integration service/job but perhaps directly from the Oracle Database.

1. run a query, generates 500k rows
2. export the result to a network place as data.txt (tab-separated)

Maybe so instead,
1. run scheduled query to store data into a temp table
2. run export from temp table to text file.

Preferably as scheduled work, cronjob, anything similar so one doesn't have to do this manually.

If we have "-" in between postal code, like "N6L-1A1"
We need to replace the "-" by an empty space, making it:
"N6L 1A1"

* But we have some exceptions, like this: "NOB 1-E0"
If we apply the code on this, it would make = "NOB 1 E0"
and we have to make it  "NOB 1E0".
We have a system in place which is hosted (SaaS) by the software manufacturer/supplier.

We have a small internal team in place to run the day-to-day admin and management of the system, like setting up new users, running reports etc.

However I’ve become concerned as I’ve been informed that the host/supplier can access the database, setup users within the database and general write directly to the tables.  This is obviously bypassing all the application controls that are in place.

We can, and probably will run reports on users so we should be able to identify any news users that might have been set up in this way.  However this is only a small fraction of what could possibly be done via the tables, for example changing the bank details for payments to your own etc.

We can try and monitor this as much as possible but it has its limits and it is detective rather then preventative controls.
The problem I think we have is that they developed this system and now they host it, so in theory they can do whatever they want.  Has anyone else been in this situation, is there anything we can put in place to prevent this happening rather than just detecting it?  At the moment the only thing I can think of is making them promise they won’t do this, which is pretty useless.

They do provide some system support so we can’t just remove their access all together and as we don’t have control over the database, we can’t insist access is disabled until they come through us to enable …
I have some users trying to access tables / views via Excel.

Server: Oracle Standard Edition on Linux OEL6
Client: Windows 10, Oracle 32 bit Client
Excel 2016 from MS Office 32 bit

I've tried this on a couple of different servers accessing a couple of different databases.  Results are consistent across all.

Define a new ODBC dataset and verify connection using the 32 bit ODBC administrator

Open a new worksheet
Select Data Tab
New Query
From Other Sources
Enter the ODBC connection, select any Schema, any table or view (even a single column view)

Generates the following error:
DataSource.Error: ODBC: ERROR [07006][Oracle][ODBC] Restricted data type attribute violation

It appears to be an ODBC thing.  You can go through the same procedure - Data / New Query / From Oracle Database / Enter the TNS Alias / Select Schema / Select table or view - preview of the data appears.

I've spent a couple of hours so far researching ODBC configuration options, Oracle Support and everything else I can think of and have come up short.  It seems to be a fairly common thing in PowerBI, but I'm not using PowerBI.

Any help appreciated.
Hi ,


I have a question regarding inserting values to the table..i have like 2 table T1 and T2..

T1 has 80 columns and T2 82..

both have same 79 columns ....
i need to insert data into T2 from T1 ..

since so many columns are their ..

i used this code..




type col is table of varchar2(200)

index by BINARY_integer;

col_name col;

v_record T1%rowtype;


select column_name bulk collect into col_name from cols where table_name like 'T2' where column_name NOT LIKE 'NAME'; --excluding the dissimilar one

select * into v_record from T1;

insert into  T2 col_name values v_record;




but it throws error as insufficient value to resolve this?
I'm trying to set up a trigger that will execute a stored procedure when the row being inserted contains certain verbiage.

The procedure is only sending an email notification.
I'd like to pass a value from the row to the procedure so that it can be included in the email.   It's a date value.

I keep getting the following error when I try to create the trigger:

SP2-0552: Bind variable "NEW" not declared.

Here is the trigger code:

FOR EACH ROW WHEN (new.USER_MESSAGE='java.sql.SQLRecoverableException: Closed Connection')


Here is the table:
SQL> desc schema_name.interface_log
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 LOG_ID                                    NOT NULL NUMBER(10)
 LOG_DATE                                           DATE
 USER_MESSAGE                                       VARCHAR2(4000)

I could sure use an expert eye.
I'm new to Oracle functions. Need help for the following  task.

I need to write a function which accepts 2 parameters and it would return records from 2 different tables by joining them.

For Example:

Employee - EMP table

1                 Sam               1                 July 18th 2017
2                 Andrew         2                July 10th 2017

Department - Dept Table

1                Arts
2                Science

I need a function - DepartmentEmployees( Start_Date, End_Date ) this function returns all the employees whose join date is in between the Start_Date and End_Date. with the selective no. of columns like empno, empname, deptname, stardate

something like this query

select * from emp e join dept d on e.deptno=d.deptno where Start_date >= e.startdate and End_Date <=e.startdate.

so if I do this query select * from DepartmentEmployees('07/09/2017','07/20/2017'); then i should get the below output:

1                 Sam               Arts                 July 18th 2017
2                 Andrew         Science           July 10th 2017
[Live Webinar] The Cloud Skills Gap
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Hello Team,

Can you please help me in inserting below query with n number of OrderId at a time. Below is query for single order insert statement.















    'orderId', --Internal OrderId

    'orderId'  --Internal OrderId


I have mistakely delete Oracle tables and views like 'DMRS_%´ and 'DM' that automaticly generated.After that,when I am trying to connect that user ,It show error something like this.

ORA-12541: TNS:no listener
I have an XML file extract with names of people and other details. How do I make this data available on a form of some sorts so end users can perform all kinds of searches on the data. i. e. First Name/Last name text search or even filter the data further using drop down that users can choose from?

I am thinking of loading the XML file into an Oracle database and then display the data as a form on Oracle Apex. However I have no prior experience in Oracle Apex, so I'm assuming it has text search, drilling down features etc.

Ps - this XML data load could end up being a weekly /monthly data load.

Really appreciate your help/suggestions.

Thanks, Ben
Not tested but you might try -
INFILE 'yourdatafile'
desc "substr(:desc,1,1)"

first i would like to say thanks

i tried this my ctl script was successful

but when iam triying to retriving the data from the table it will show no rows selected

can u tell me the reason
i have 2 tables like

table 1                                       table2
--------                                         ---------      

id       s_name                id        s_name  
1         null                        1        tirpur  
2         null                        2        jaipur
3         null                        3         nagpur
4          null                       4         kocchi

so, iwant to updaet table 1 at at a time
if 1 then tirpur
if to 2 jaipur like

can any one help me plsee
Hi Team ,

I need to create a table partition which can store the employees who have joined in a given month and year
Can I achieve it using range partition
Iam trying the below command.
Create table emp_part1
  employee_id number,
  hire_date date


Actually i want to create partitions like below


create partitions like above and store the records , but  iam getting an error

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.