Oracle Database

79K

Solutions

26K

Contributors

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

Hello expert,

Using Oracle SQL Developer ver 18.1
working on learning about PL/SQL Loops.
At
https://www.tutorialspoint.com/plsql/plsql_loops.htm
The code
DECLARE
   i number(1);
   j number(1);
BEGIN
   << outer_loop >> 
   FOR i IN 1..3 LOOP
      << inner_loop >> 
      FOR j IN 1..3 LOOP
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
      END loop inner_loop;
   END loop outer_loop;
END;
/

is offered and says the result will be

i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3  

PL/SQL procedure successfully completed.

But all that is output is

 PL/SQL procedure successfully completed.

Is there something that needs to be changed to get the
more complete result?

Thanks.

Allen in Dallas
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

hi ,

When i try to do an insert statement from Java to Oracle table i am getting below error
ORA-01693: max # extents (1000) reached in lob segment.

Is there any way i can overwrite my insert statement from Oracle side . So that it will not break the code .
I can't ALTER the table in near future . But i can remove the BLOB data in the table . If i create a trigger like before INSERT Update BLOB column to EMPTY String ,will these solve the issue ?  . The issue happening only in production .
0
hi,
I'm trying to execute this query and the following error happens

select a.abc ,bdf ,cgh , b.xyz
from tablea a , tableb b
where a.abc in ( select distinct xyz from tableb);

this gives error-
ora - 01652 unable to extend temp segment by 640 in tablespace oratemp
0
Hello All,
I have to print the below dates as report rows based on the sys date and going back to 30 days as the data rows.Using Oracle as the database.Please let me know how can I do it in the select statement.
Report
6/5/2018
6/6/2018
6/7/2018
6/8/2018
6/9/2018
6/10/2018
6/11/2018
6/12/2018
6/13/2018
6/14/2018
6/15/2018
6/16/2018
6/17/2018
6/18/2018
6/19/2018
6/20/2018
6/21/2018
6/22/2018
6/23/2018
6/24/2018
6/25/2018
6/26/2018
6/27/2018
6/28/2018
6/29/2018
6/30/2018
7/1/2018
7/2/2018
7/3/2018
7/4/2018
7/5/2018
7/6/2018
0
I have a package. The procedure in the package is called in the trigger. This procedure has a commit. So what I am creating a waybill and saving. I get this error.
Enclosed print shot of the error.

Need help to fix it. How can I not use a commit but still save the waybill that calls the package?
screen-print.jpg
0
SQl for Oracle SQL Developer.  I am trying to count the number of items by a date field between two dates but it does not like my date syntax.
and EVENT_DT > TO_DATE('01-JAN-2017', 'DD-MON-YYYY') and
and EVENT_DT > fROM_DATE('31-DEC-2017', 'DD-MON-YYYY')

The first date works but not when I add the FROM Date.
0
SCOPE :
The following question deals with the bare metal restore of a Microsoft Hyper-V server running (amongst others) two virtual machines on which is installed a licensed Oracle 12c (Standard). I am not interested in an “Oracle for DR” installation scheme. There is exactly one backup scenario: the entire Hyper-V server is backup up to local media using Windows Server Backup. There are exactly two restore scenarios: (1) the entire server is restored on the original equipment  from backup media using the appropriate Microsoft Distribution Media and (2) a single VM is restored from backup media using Windows Server Backup.

Comment:
I understand that Oracle’s default position is that all installations require licensing. The first scenario above implies that more than one physical machine may hold the Hyper-V server; the second scenario implies that restoring VMs could create more than the two licensed installations. To quote a post by Brian Lowinger (https://www.softwareone.com/en/blog/licensing-oracle-database-for-disaster-recovery) : “Oracle uses the term ‘failover’ to describe a scenario where, within a cluster of servers, a database running on a primary server could move to a secondary server when the primary server fails. Oracle allows for the database to run on the secondary server for up to 10 days without additional licensing requirements.” If we stick to the scope of the question and the 10 days grace period, all licensing is honored.

Constraints:
-      The OS is…
0
I have 1 java web service and 1 spring batch job process which access same Oracle database server. Oracle DB is installed on Linux VM. Both use spring JDBC template for database operations. When I run both simultaneously I start getting "The Network Adapter could not establish the connection" error. We also see "ORA-00018.Maximum number of sessions exceed" exception after few seconds.  If I run them standalone, I don't get these error. Earlier everything was working fine even while running them together. What might have changed? What could be the reason for this error? What should I check?
0
This is to migrate the existing SuSE Linux Enterprise server 11 (think with SP1) to another newly-setup SuSE Linux Enterprise 11 with SP4. This existing server is hosting apache, and supporting an oracle via PHP. The main thing is, we also have to configure the new SuSE to have PHP supporting Oracle with the following working descriptions from the application owner as follows,

           - Apache connecting to PHP accessing a Oracle table through Oracle instant client

with this requirement:
1)      the PHP has to be compiled to enable Oracle instant client
2)      apache has to have the connector configured (not shown here ) and recompiled

So far, on this new SuSE, a apache and associated modules were installed. When typing "rpm -qa |grep apache", the results shows,

      apache2-prefork-2.2.10-2.18
      apache2-utils-2.2.10-2.18
      apache2-mod_python-3.3.1-147.19
      apache2-mod_php5-5.2.6-50.17
      apache2-2.2.10-2.18
      apache2-mod_perl-2.0.4-40.19

How should we proceed from here?

thanks in advance.
0
Hi,
i have two tables to be loaded from flat file and i am trying to figure how much space they might need to get loaded

First file has 615908 rows  other file has 1611660 rows
first file has 150 columns and other file has 25 columns

Thanks
0
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I am trying to generate reports from Oracle, and I am having trouble with the following.

I have 3 tools that can run 3 types of products. Tool A, B, C and Prod1, 2, 3.

Sample data:
ToolID     ProductType    Qty
ToolA       Prod1                1
ToolA       Prod1                1
ToolA       Prod1                1
ToolA       Prod3                1
ToolC       Prod1                1
ToolC       Prod2                1
ToolC       Prod3                1
ToolC       Prod3                1

How do I write a PLSQL query to present the data into a table like below?

               ToolA   ToolB   ToolC
Prod1           3           0            1
Prod2           0           0            0
Prod3           1           1            2
0
Hello Experts,

I have a table contain 2 columns Customer & product
Example:
Customer  |   Product
Cust1                  xx
Cust1                  ab
Cust1                  mu
Cust1                   ty
Cust2                   re
Cust2                   ws

I need help to create Oracle view, which display each customer in one row with all the products separated by "," & sorted by abc

Example for the view
Customer   |   Product
Cust1                 ab,mu,ty,xx
Cust2                  re,ws


Thanks
0
Enterprise system HTML can be modified ("hacked"), allowing a user to see data they shouldn't see, by inspecting the page.

I'm no web programmer but generally familiar with HTML. I was really surprised that the HTML could be modified so easily.

The HTML is generated from Oracle Designer, a really old tool that is outdated but we're still using it.

Isn't there some way to mask the code? or make it unchangeable? We've also had issues in the past where SQL code / values are exposed and were able to be changed. That too surprised me. That was fixed with some back-end programming (the HTML could still be modified).

In the screen shot, the ID # can be changed and so the user can see another user's info, by either guessing another's ID or sleuthing to find someone specific's ID.
Inspect-HTML-and-change.png
0
Hi,
i have cursor like below

CURSOR  crus_stage
   IS
  SELECT
*
from stg a
where  flag='A'

then i have

temptable                  crus_stage%ROWTYPE;

i don't want temptable get value from crus_stage as it has filter so can i do

CURSOR  crus_stageall
   IS
  SELECT
*
from stg a
and then do
temptable                  crus_stageall%ROWTYPE;


can i have two cursor in package
0
Hi,

Right now want to research about how vulnerability each major DB has :

1) MS SQL
2) MySQL
3) MariaDB
4) Oracle
5) DB2.

Other than NIST database : https://nvd.nist.gov/vuln/search.

What other source I can search on the number of vulnerability of each product in last 3 years and the result make sense ?  the source has to be trust able !
0
I have noticed a new user has been added to a table within Oracle DB with Select grant.

Is there as way to see at what time this user was given permission?
0
Hi Experts,

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);
I have created table customers.
when i try to insert with data which is greater than of column data type length.
i mean customerid number(10)
if i try to insert with 11 numbers is there any way to insert data with out throwing an error
it should insert customer id with 10 digits even if i pass 11 digits.
0
Here's my formula in figuring out the Age of a customer, the MyTable.CustAge field is numeric, in most cases the formula behaves properly, but in other cases, I get negative years for the age (e.g -27, or -39 ).

Your thoughts.
Thx

trunc(months_between(sysdate,to_char(to_date(MyTable.CustAge,'YYYYMMDD')))/12) Age
0
hi ,

Due to some latency issues in the network some duplicate records are getting inserted in our T_MONTHLY_INCOME table .
So we are running below query to get duplicate records and checking the amounts manually and deleting row by row .
If i say duplicate record either its having same amount(INCOME_AMOUNT)twice  or the amount is 0.0 for the same income code .

Can we create a delete query which will identify the duplicate records and delete those ?

SELECT COUNT(*), i.loan_id,i.INCOME_AMOUNT,
  i.INCOME_CODE
FROM UW.T_MONTHLY_INCOME i
join WC.LOANS l on l.loan_id = i.loan_id
 where
i.loan_id = 1234567 and
 i.INCOME_CODE in ('08','09','10','11','12','13','AB')
GROUP BY i.loan_id, i.INCOME_CODE,INCOME_AMOUNT
having count(*) > 1
0
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

hi ppl,

I'm trying to execute the below query and the script goes on executing and the results are not shown.. i guess it just goes into some kinda loop but the table1 just has 4 columns. data in table2 and table3 is ~700000 each. please suggest. TIA.

insert into table1
select seq.nextval, 'Bus', a.col1, b.col1
from table2 a, table3 b;
0
This is a question that I have little understanding of. I understand what a Equal, Left Outer, Right Outer and Union Join is. As for the rest I am confused. This document  is straight out of Crystal Reports Help file. As you can see there are literally no definitions or examples for it. I do not know how hard this would be to give an example and give some definitive answer to what they mean but I thought I would try. Here it is straight out of the help....

Linking options
Crystal Reports enables you to specify the type of join and type of link you want to use when linking tables. You can also enforce the use of tables in your joins. Joins and links indicates how linked fields in two tables are compared when records are read. Join, enforce, and link options can be specified in the Link Options dialog box. Using the various join enforcement options can ensure that linked tables are included in the SQL query, even when none of the fields in the table are used in the report.

Note
 When you link fields using joins, no indexed fields are required.
The join types are:

Inner join
Left Outer join
Right Outer join
Full Outer join
The enforce join options are:

Not Enforced
Enforced From
Enforced To
Enforced Both
The link types are:

Equal [=] link
Greater Than [>] link
Greater Than Or Equal [>=] link
Less Than [<] link
Less Than Or Equal [<=] link
Not Equal [!=] link
0
Hello experts,

Need to insert date into Oracle DB.

MERGE INTO tmp_case_insrt_fnl_b tmpb
    USING tmp_case_insrt_fnl tmp
    ON (tmpb.crm_case_id = tmp.crm_case_id)
  WHEN MATCHED THEN
    UPDATE SET tmpb.CRM_REQUEST_DATE = sysdate

This inserts 26-JUN-18.
Other tables in the schema have dates like
26-JUN-18 11.29.01.033000000 AM

Have found some tracts  on the net
that use 'DD-MON-YYYY HH24:MI:SS'
This gives military, twenty-four hour
for the HH.

But none that provide the format with
fractions of a second out to nine digits and
the meridian (AM or PM) indicator.
Can't use TOCHAR because the field wants
a date format.

Thanks.

Allen in Dallas
0
what are differences between correlated vs non-correlated sub query

when we use each one of them
what is moving range date and how correlated sub query useful there?

also when we use Cartesian product with cross join
please advise
0
Hello Experts,
I have a column based table of million of rows.  I am trying to convert the columns to rows for better readability.  See the attachment for details.  Please let me know how to get the output.  Thank you very much in advance.

Thank you!
Pivot.png
0
DIFFERENCES BETWEEN

1. FUL OUTER JOIN
2. LEFT OUTER JOUN
3. RIGHT OUTER JOIN
4. LEFT INNER JOIN
5. RIGHT INNER JOIN
6. FULL INNER JOIN

WHEN TO USE WHICH ONE

please advise
0

Oracle Database

79K

Solutions

26K

Contributors

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.