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

Looking for learnings/advice of Oracle to Postgres conversion (SELECT queries in particular).

I'm aware of a few key differences (e.g. no "dual", no "sysdate", no "connect by ...") but I'm hoping someone has, or knows of, a good fact sheet of differences.

If it helps: Oracle 11 to "recent Postgres" (not sure of version as yet)
0
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

how can i update the records fast within hour i have got 7 million records,am in oracle 12c
i try this
DECLARE
       c_limit PLS_INTEGER := 3000000;

       CURSOR cur1
       IS
          SELECT item_no
            FROM noc_item_encoded_reply
           WHERE enc_encoded_reply  is not null
		   and enc_encoded_reply like '%*';

       TYPE curr_ids_t IS TABLE OF  noc_item_encoded_reply.item_no%TYPE;
	  

       l_curr_ids   curr_ids_t;
	   
    BEGIN
       OPEN cur1 ;

       LOOP
          FETCH cur1 
          BULK COLLECT INTO l_curr_ids
          LIMIT c_limit;      -- This will make sure that every iteration has 100 records selected

          EXIT WHEN l_curr_ids.COUNT = 0;           

        FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS   
		   update noc_item_encoded_reply re
         set   enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
         where enc_encoded_reply like '%*'          
		  and enc_encoded_reply  is not null
		  and re.item_no = l_curr_ids(indx);
      commit;  
        
	  FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
	  update noc_item_encoded_reply_jn re
         set   enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
         where enc_encoded_reply like '%*'          
		  and enc_encoded_reply  is not null
		  and re.item_no = l_curr_ids(indx);
		  commit;
	 
	  FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
	  

Open in new window

0
hi expert
Values ​​in column "FATURA_FATURA_NO" in table "FATURADETAIL"
I want to update the column "ISLEMYAP_FATURA_NO" in the "ISLEMYAP" table
Rowid1 columns common in two tables.
I connected these tables with each other rowid1 column.
I tried to explain what I wanted as a picture.
The information in the image is the result of the following query.
 SELECT IY.FATURA_NO AS ISLEMYAP_FATURA_NO, IY.ROWID1 as ISLEMYAP_rowid1 ,FT.ROWID1 as FATURADETAIL_rowid1,
 FT.FATURA_NO AS FATURA_FATURA_NO
  FROM FATURADETAIL FT,ISLEMYAP IY WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL 

Open in new window

The update I wrote is as follows
update ISLEMYAP IY
set 
   IY.FATURA_NO=( SELECT FT.FATURA_NO
                                     FROM FATURADETAIL FT
                                      WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL)
WHERE EXISTS (
    SELECT 1
      FROM FATURADETAIL FT
     WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL )

Open in new window

I think the update goes into an infinite loop
the process does not stop...
I did update by typing plsql.
but I could not update with this code.
how can i fix it.?
how should I fix this code
thanks
deneme_calismalari2.png
0
Hi Experts,

I have a query that is built into an interactive report. Everything works great and i get an email every day.

I would like to modify the subscription so that when the query in the report returns no records, an email is not sent.

Is there anyway to do this?

Asim
0
Hi,

I have a OC4J application running in oracle application server 10g.  It currently connects to an 11Gr2 database using an oracle thin jdbc connection.  I'm trying to connect the same app to a 12C data again using the thin jdbc connection with no success.  this is the error.

Unable to establish connection for one or more OC4J instances in the group. Below are the results for each OC4J instance.
"my OC4J: on "my site" - Failed due to error: "Exception occurred testing connection. Exception: java.sql.SQLException: Io exception: Got minus one from a read call."

I'm assuming it's due to a jdbc version issue.  I can connect via sqldeveloper to both dbs.  Any thoughts with updating the jdbc on the application server side would be appreciated.  thanks
0
Experts,
In the below query, sometimes the sub-select will have no matching row returned.
When this happens the main query returns no result, even when the Else is called.

Ideally i use a default value of 1 when there is no matching row.
I tried a Coalese in the sub-select, and on its own that works. But the main query starts to complain about the table names

SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
TO_CHAR (TO_NUMBER(:Qty)/TO_NUMBER(CA_QTY))
 ELSE :Qty
 END as QTY
FROM
(select Item_qty_Bkd_qty CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = :item
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window

0
Hi, i have query where i joined 4 tables and i am getting 3 records which is correct
but as soon as i joined 5th table records getting duplicated. i am getting 9 records now
can someone help me how can we eliminate duplicate records in multiple joins
all joins i am using is inner join
i am running query in oracle db but this should be same in sql db as well.
i referred some forums but that didn;t helped me much
any help is much appreciated.
thank you
0
hi how can i import data to database table using oracle form i got the following screen
loaddatai got the following code
begin
	upload_db;
	  exception
  	when others
  	then
  	  message('File upload failed: '||sqlerrm);

end;

PROCEDURE UPLOAD_DB IS
  l_success boolean;
BEGIN
  l_success := webutil_file_transfer.Client_To_DB_with_progress
                                    (clientFile      => :upload.file_name
                                    ,tableName       => 'wu_fileupdown_demo'
                                    ,columnName      => 'BLOB'
                                    ,whereClause     => 'ID = 1'
                                    ,progressTitle   => 'Upload to Database in progress'
                                    ,progressSubTitle=> 'Please wait'
                                    ,asynchronous    => false
                                    ,callbackTrigger => null
                                    );
  if l_success
  then
  --
	-- Force commit to save file in db.                                    
	-- This is not in the original Demo 
	-- which causes the Demo to fail if 
	-- you exit and return later to download.
  	:System.Message_Level := '15';                                   
		commit;
  	:System.Message_Level := '0';	
  	
    message('File uploaded successfully into the Database');
    
-- Added to make demo more user friendly.
-- If file is successfully uploaded, move to "Download" tab.        
    

Open in new window

0
Hello Experts :
I need an SQL query check some values to avoid duplication or interference for time values, the data saved as number field in oracle DB 8.1 but considered as times, two fields are being used as start time and stop time to register labor , check below example :

- Saved Value :

Start_time        Stop_Time
 13:00                  17:00

- Some Entered Values (using an application) will raise an error because it will be interfered with saved Start & Stop Times :

13:00                   17:00
13:30                   16:00
14:00                   17:00
13:00                   18:00
11:00                   18:00
11:00                   14:00

Thank You
0
Oracle Update Statement.
Iam trying to update a column in a table

update SC_SERIAL_NO set po_line_id = (

select CL.C_ORDERLINE_ID
 
   

from sc_serial_no sc

left JOIN M_ATTRIBUTESETINSTANCE ASI ON sc.LOT_NAME = ASI.LOT --and sc.ad_org_id = asi.ad_org_id
left JOIN M_TRANSACTION MT ON ASI.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
left JOIN M_INOUTLINE ML ON MT.M_INOUTLINE_ID = ML.M_INOUTLINE_ID and MT.M_PRODUCT_ID = ML.M_PRODUCT_ID AND MT.AD_ORG_ID = ML.AD_ORG_ID
left JOIN C_ORDERLINE CL ON ML.C_ORDERLINE_ID = CL.C_ORDERLINE_ID
left JOIN C_ORDER C ON CL.C_ORDER_ID = C.C_ORDER_ID


WHERE  
MT.MOVEMENTTYPE ='V+'
)
The above is erroring out with :
"single-row subquery returns more than one row"
Please help me on how to do an update on a column
0
Angular Fundamentals
LVL 19
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Hi,
On oracle EBS login form, randomly some users getting another users credentials autofilled (autocompleted) and able to log in!

It is not always, but rarely. I am sure that, the second user never logged in to the first user's laptop, if you think that it is a cache issue. It is not public laptop.

I appreciate any support. Thank you.
0
Hi I am a Oracle JDE developer. I want to learn JDE CNC. is there anyone who can teach me on line.
0
We are using Client 11.2.0.4 (because we need oo4o for now) and occasionally when we insert an image file as a BLOB, the image gets corrupted. A picture will be a black box with a thin white strip at the top is what we see if we display the image in our program. If we save the image back to a file (TIFF in this case) the image information is all correct (we store text information about the image). This happens in a very small # of transfers (maybe 10 out of 1000). We've tested this on Oracle 10,11 and 12. The only constant is the client at 11.2.0.4


Any ideas?
0
Oracle BI Publisher Template : The template is working fine in 11g but failing with OBIEE 12C.
Template file and data is attached.
11G-template-orig.rtf
12c_MGAGMonthlyFinancialPacket_.xml
0
Dear EE,

My Web server stuck and i restarted it. In event log below is the error i see.

1.      Message : ORA-03113: end-of-file on communication channel

Details:
Message: HandlingInstanceID: 12d6bbb6-8560-4a7d-9eb7-2a5ea0adfdd0
An exception of type 'Oracle.DataAccess.Client.OracleException' occurred and was caught.
----------------------------------------------------------------------------------------
10/16/2019 09:13:18
Type : Oracle.DataAccess.Client.OracleException, Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342
Message : ORA-03113: end-of-file on communication channel
Process ID: 144215
Session ID: 554 Serial number: 15831
Source : Oracle Data Provider for .NET
Help link :
Errors : Oracle.DataAccess.Client.OracleErrorCollection
DataSource : NETSOLprod
Procedure : PAC_SER_ChangePOSStatus
Number : 3113
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite : Void HandleErrorHelper(Int32, Oracle.DataAccess.Client.OracleConnection, IntPtr, Oracle.DataAccess.Client.OpoSqlValCtx*, System.Object, System.String, Boolean)
HResult : -2147467259
Stack Trace :    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx,
0
I can update this one column UWI but I would like to turn this into a procedure, where if there is a new uwi in the table which  meet the requirement, it updates automatically.

UPDATE well_core_analysis a
SET    a.active_ind = 'N'
WHERE  active_ind = 'Y'
       AND a.analysis_source = 'GOVT'
       AND uwi IN (SELECT uwi
                   FROM  (SELECT DISTINCT e.string02 AS License_num,
                                          b.uwi,
                                          e.string03 AS Province,
                                          b.analysis_source,
                                          b.row_quality,
                                          b.active_ind
                          FROM   data_entry.well_core_analysis b
                                 join well a
                                   ON b.uwi = a.uwi
                                 join well_license c
                                   ON a.uwi = c.uwi
                                 join license l
                                   ON c.license_id = l.license_id
                                 join publicdata.entity e
                                   ON l.license_id = e.string03  || '|'|| e.string02
                                 join publicdata.l_entity_document led
                                   ON e.entity_id = led.entity_id
                                 join publicdata.document d
                                   ON led.document_id = d.document_id
  …
0
I have been haggling with this (seemingly) simple stored procedure (In the left margin, I have provided line numbers where errors occur):

Replace PROCEDURE MyProc()
BEGIN
	Insert into 
		MyTable
	Select
		*
    From
        ReferenceTable;
		
	Declare cur1 Cursor with return only For
	    Select
			*
	    From
	        MyTable;

	Open cur1;
END;

Open in new window


When I try to run this, I get the following error, between the two statements:

Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'Declare' keyword.'.

Open in new window


I have run the two processes individually, outside of a stored procedure, and they work.  Please help explain this mysterious error.  Thank you.
0
hi,

Now I have convert Oracle to MariaDB/MySQL and we have a convern, how can we know the number of table is the same! but the main idea should be what tables is missed from MariaDB/MySQL side?

any easy way to compare what table is missed on MySQL/mariaDB side ?
0
hi am trying to download file in oracle form using webutil am geting the following error
Application server file INT.REQ-4805-18152.TXT does not exist or is of zero length
this si the log Java Plug-in 11.211.2.12 x86
Using JRE version 1.8.0_211-b12 Java HotSpot(TM) Client VM
User home directory = C:\Users\username
----------------------------------------------------
c:   clear console window
f:   finalize objects on finalization queue
g:   garbage collect
h:   display this help message
l:   dump classloader list
m:   print memory usage
o:   trigger logging
q:   hide console
r:   reload policy configuration
s:   dump system and deployment properties
t:   dump thread list
v:   dump thread stack
x:   clear classloader cache
0-5: set trace level to <n>
----------------------------------------------------
network: Connecting http://hostname:9001/forms/lservlet;jsessionid=CDhmdLLV7Wc2WgSJhyfxshNgpT263YnF3dGfGRTGgyKnbmwgpfq5!2075368613 with proxy=DIRECT
network: Connecting http://hostname:9001/ with proxy=DIRECT
network: Connecting http://hostname:9001/forms/lservlet;jsessionid=CDhmdLLV7Wc2WgSJhyfxshNgpT263YnF3dGfGRTGgyKnbmwgpfq5!2075368613 with proxy=DIRECT
2019-Sep-25  08:42:43.580 WUF[setProperty()] Setting property WUF_GFN_DIRNAME to /usr2/dev/maint/apps/ncs/appfiles/arc/
2019-Sep-25  08:42:43.580 WUF[setProperty()] Setting property WUF_FILENAME to INT.REQ-4805-18152.TXT
2019-Sep-25  08:42:43.580 WUF[setProperty()] Setting property WUF_FILTER to
0
HTML5 and CSS3 Fundamentals
LVL 19
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Hi,
Where can I get recent sample questions and answers for Oracle exam: 1Z0-071 - Oracle Database SQL.

I don't mind having it from a pay site so long as I can be assured they can help to pass it.


Thanks
0
Can anyone share a sample full contents of recommended / hardened settings
of postgresql.conf ?

What's indicated in CIS benchmark for Postgresql 10 is unclear & in bits & pieces;
some of the extracts from the benchmarks are posted below:

- configuration file enumerates all tunable parameters and even though most of them are
commented out it is understood that they are in fact active and at those very same
documented values.

- shared_preload_libraries = 'pgaudit'
OR
shared_preload_libraries = 'pgaudit,somethingelse'

- $ vi ~postgres/10/data/postgresql.conf
# load set_user libs before anything else
shared_preload_libraries = 'set_user, other_libs'
0
Hello Experts,
I am facing an issue while exporting one schema which is taking huge time without any rows.

Command used:
exp username/password@instance file=user_name.dmp log=username_exp.log GRANTS=y CONSTRAINTS=y  ROWS=n statistics=none

Open in new window



Cannot use data pump as directory access to servers are restricted.
0
Hello,

I'm trying to make this Crystal Report like the Excel report.  I'm having trouble with making it like the Excel report.

I've attached both files...  Thanks for your help.

JoelEE.xlsx

LG.rpt
0
Why is Oracle 12c db installation stuck at 91%?
I have added a screen capture of the installation information on UI.
Kind regards
Erreur-Oracle-Issue.PNG
0
hi,

I am reading:

https://www.oracle.com/technetwork/testcontent/jdbc-ch5-131209.pdf

https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28767

for Oracle prepared statement.

and I do not understand what it means as it says:

Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.

Open in new window


when I check standard JDBC batching it says:

The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching for Statement and CallableStatement objects, you are[b][u] unlikely[/u][/b] to see performance improvement.

Open in new window


so in order to use a  REAL batch mode for PL/SQL prepare statement, what should I do ?

or it means must be handle in java library when calling:

...
Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");
stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");
...

Open in new window


?

then it still related to prepared statement ?
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.