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

I have a number column whose values I will like to convert into a number with 2 decimal places.
For example the number 9200 should become 92.00
I tried the formula
SELECT TO_NUMBER(TO_CHAR(9200/100, '999.00')) FROM DUAL
      Output is 92
I would have prefer it to be 92.00
The output should be in number format.
Any ideas
0
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I’ve data in a table column that looks like this:
9201#CCCC
18183#DDD

I’m looking for output as:

9201
18183
I’ve tried substr and instr and I’m getting desired result. Can someone please help? Thanks
0
GIVE A PROMPT FOR A SPECIFC NUMBER

Is there a way to chnage this give a specific prompt for a project ID ?

WHERE I.PROJECTID = R.PROJECTID
AND I.LINEITEMID = R.LINEITEMID
AND I.PROJECTID = '20181227111629816' /*give project id */
ORDER BY R.LINEITEMID) TEMP

THANKS
fordraidersa
0
I am using PL SQL with an Oracle database
I am trying to make a primary key from two fields in a table.

The table is  dhcs_contstat_hn2017_TEMP
The new primary key field is called "PrimaryKey".  But it is not set as a primary key yet
The fields I am using to make the field are "record_id" and "main_sgmnt_id_no".
The primary key needs to be inserted on the line that matches the "record_id" and "main_sgmnt_id_no".

I have the psuedo code below, .    

INSERT INTO dhcs_contstat_hn2017_TEMP c
(PrimaryKey)
SELECT concat(t.record_id, t.main_sgmnt_id_no)
FROM dhcs_contract_status_hn2017 t
WHERE c.main_sgmnt_id_no = t.main_sgmnt_id_no
;
How do I do this?

Thanks,
Scott
0
Is there any process to load jpeg images into oracle database using a procedure ? The database column will be a blob. I googled, but could not find any that I can use.
0
Oracle

I have an sql that asks me for a date
and when the box comes up its displays:
oracle pic for date

WHERE
    trunc(r.lastmodified) IN (
        :dateseplist

I'm oracle sql developer.
nd i need to amend/change this
so it ask for a date range ?  not just one day ?

I dont know what ":dateseplist"

so i can change it ? if so where in oracle developer ?

Thanks
fordraiders
0
oracle dates:

I have a select statement for some date fields:
SELECT
    p.projectid,
    p.startdate     submitdate,

The dates are being displayed as :

13-NOV-18 02.17.44.000000000 PM

Is there a way to cast them as :

yyyy-mm-dd hh:mm:ss  AM/PM   ?

I do not work in oracle...so a little vague to me as to why time stamps  have a  .   and  :

Thanks
fordraiders
0
how to insert the image in the tables using BFILE or BLOB ??
Tell me the easy way to insert
Please give the detailed programs for the above questions
0
How to concatenate dblink which will change dynamically in a Oracle SQL Statement at the end of the table name
For ex.
Select * from gv$session@:<dblink>
0
Hello Experts,

I have a table that contains 546471 rows out of which I need to delete 386201 rows based on date column.

Now I tried two ways where it is taking huge time to remove the rows.


First the table has a primary key c_key1  and c_dt is a date column based on which I need to delete.

The delete statement is :

DELETE FROM MY_TABLE 
WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ;

Open in new window


The date column is not having any indexes and even after creating the index it takes more then 15 mins to execute the delete statement.


So adding the index on c_dt , tired using forall mapping the primary key i.e. c_key1 column.


Here is the sample code which is taking again huge time:

DECLARE
  TYPE t_chk_tab IS TABLE OF my_table.C_KEY1%TYPE;

  l_chk_tab    t_chk_tab   := t_chk_tab();
  l_start     NUMBER;

BEGIN
  -- Populate collections.
  FOR i IN (
  select C_KEY1 from my_table
  WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ) LOOP
    l_chk_tab.extend;
    l_chk_tab(l_chk_tab.last)     := i.C_KEY1;
    
  END LOOP;

  l_start := DBMS_UTILITY.get_time;

  -- Time bulk delete.

  FORALL i IN l_chk_tab.first .. l_chk_tab.last
    DELETE FROM my_table
    WHERE  C_KEY1   = l_chk_tab(i); 
    
  DBMS_OUTPUT.put_line('Bulk Deletes   : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  ---commit;
END;

Open in new window



Any suggestion on to make it faster, the only issue is I cannot move the data to another table and delete the rows and rename it. As the same table is been used in multiple application for the delete operation all the application will not be using till weekend but still cannot drop the original table
0
Introduction to Web Design
LVL 12
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Hello Expert,

A large web-based application is connected to an Oracle 12c database.
The application has a feature which puts a record in Draft status to isolate the record while changes are made.
The Draft record is deleted when the record is Saved and Approved. Or it is supposed to be.
For some reason, under investigation, the Draft records are sometimes not deleted.
This create an invalid data state we lovingly call a Stray Draft.
A Stray Draft causes an error if a user ever tries to edit the record again, I think
because the new Draft record ties to use the same primary key as the old, undeleted draft.

That is the context.

So the boss says 'Delete all old (last_update_date > than timestamp - 90) drafts.
The only fly in the ointment is there are about eighty draft tables with name syntax
D_tablename and it is known by this writer that some of them have parent-child
relationships. That is, for instance, the D_PAYER table is Parent to the Child D_PAYER_ADDRESS
table and the D_PAYER_ADDRESS table is Parent to the Child D_PAYER_ADDRESS_TYPE and
the  D_PAYER_ADDRESS_USAGE tables.
One cannot delete Child records that have a foreign key relationship to Parent
records.

With eight tables getting everything in order empirically is pretty tough. So
visiting with one of the developers, he said 'If you look at table meta data (Columns, Data Model,
Constraints, Grants, Statistics, Triggers, Flashback, Dependencies,
Details, Partitions, Indexes, SQL) for the…
0
Hello Experts,

I want to update multiple row with same sequence.

Here is my object:

create table mytab (id number, name varchar2(400), source varchar2(400), key_val number );

insert into mytab
select 1 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 2 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 3 as ID, 'name2' as name, 'source2' as source , null as key from dual
union
select 4 as ID, 'name3' as name, 'source3' as source , null as key from dual
;
commit;

create sequence key_seq  increment by 6666 start with 1;

Open in new window


Now if I run my select statement then I can see that the first and second row have the same values so I need to update the key column with same sequence number to make a track.

select 
id, name, source, key_val,dense_rank() over (order by name, source) dr 
from mytab;

Open in new window


 Result:
1	name1	source1		1
2	name1	source1		1
3	name2	source2		2
4	name3	source3		3

Open in new window


Now the expected result would be getting same sequence to row 1 and 2 and follow nextval to other 2 rows.
0
I need to run a update query with a join with multiple matching criteria.  Relevant tables and columns for reference are as follows.

OPMAT TABLE
arinvt_id
stdcost
backflush

ARINVT TABLE
id
itemno

I am provided with a list of itemno values that I need to locate the corresponding ID for in ARINVT.  Join that ID to the opmat.arinvt_id and update the values of stdcost and backflush.

Normally, I accomplish this by running multiple select statements against arinvt using the itemno's provided to locate the id's
select id from arinvt where itemno ='12312312321'
I run this over and over in a begin / end loop and get a giant list of arinvt.id's returned.

I then run a update statement update opmat set stdcost = 'y', backflush = 'y' where arinvt_id = '232132123' (the values returned) and I repeat all of that in a big update loop.


This requires a lot of excel junk to make my statements.


I'm trying to simplify this with the update and join combination with multiple case statements so I'm more efficient with this.
0
I'm executing a query inside SSRS and receiving an ORA-00933: SQL command not properly ended.  It's just a SELECT statement but the error appears to be in the Where clause.  Am I specifying the parameter, @PaperBill, incorrectly?

Select ACCTUSAGE_MAIN_V.BILLING_DATE
,ACCTUSAGE_MAIN_V.BILLING_CYCE
,ACCTUSAGE_MAIN_V.BILLING_MONTH
,ACCTUSAGE_MAIN_V.BILLING_YEAR
,ACCTUSAGE_MAIN_V.INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER
,ACCTUSAGE_MAIN_V.DESCRIPTION
,ACCTUSAGE_MAIN_V.CNT_LAST_NAME
,ACCTUSAGE_MAIN_V.CNT_FIRST_NAME
,ACCTUSAGE_MAIN_V.CNT_ADDR_1
,ACCTUSAGE_MAIN_V.CNT_ADDR_2
,ACCTUSAGE_MAIN_V.CNT_CITY
,ACCTUSAGE_MAIN_V.CNT_STATE
,ACCTUSAGE_MAIN_V.CNT_ZIP
,ACCTUSAGE_MAIN_V.CNT_ZIP_EXTEN
,ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL
,ACCTUSAGE_MAIN_V.EMAIL
,ACCTUSAGE_MAIN_V.MIN_INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.MAX_INVOICE_NUMBER
From COMIT_CUSTOM.TAMU_ACCTUSAGE_MAIN_V as ACCTUSAGE_MAIN_V
Where ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL = @PaperBill
Order by ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER,ACCTUSAGE_MAIN_V.BILLING_CYCE
0
I need some help with a CASE statement.

I am looking at sales order lines and linking out to a reference table. I want the case to output 'MY STRING' If at least 1 line on the order has the value 'WILLIAMS'. Nothing output if no lines contain this value.

So far I wrote a query to return the reference, then added a count where the reference equals Williams.

Select count(ITEM) from (
    select D5.ORD_NUM, D5.ORD_LEV1 ITEM, D3.ALT_INVT_REP_CODE BRAND
    from E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 on D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE = D5.ORD_LEV1
    where D5.comp_code = 'W1' and D5.ord_num = 935373 
    )
WHERE BRAND = 'WILLIAMS'

Open in new window


Not sure how to make this part of a CASE. (of if CASE is even the best approach)
0
Need best websites (e.g. plus utube video if possible) for upgrading an Oracle (3) node RAC database under Solaris 11.  Need to upgrade 12.1 RAC database to 12.2.
0
Hi,
I'm looking for a regexp_substr result = HIGH and LOW from the following two strings

1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR
2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR

Output:
HIGH
LOW

This can be 2 separate SQL statement regexp_substr.

Thanks,
0
Need to create an Oracle database privileged database user account, but don't want this user to be able to drop database.

     Sqlplus>  create user  InfSysAd IDENTIFIED BY xxx_XXX;

     SqlPlus>  grant connect, resource, DBA to SInfysAd;
     SqlPlus>  revoke drop database;

Is this good enough to all this new InfSysAd to create other database users, tables, etc?
0
Oracle Sql Developer.

I'm new to  Oracle sql  developer and i keep getting this error message everytime i want to run a query.
even on a simple sql statement.

select * from  mmmm

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 3,310


Any guidance would be appreciated.
Thanks
fordraiders
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

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

hi am having the folowing eror in my code i have attach the code
error report -
ORA-06550: line 19, column 4:
PLS-00103: Encountered the symbol "FUNCTION"
ORA-06550: line 19, column 31:
PLS-00103: Encountered the symbol "RAW" when expecting one of the following:

   (
ORA-06550: line 78, column 7:


procedure val_pswd(in_paswd in varchar2, out_msg out varchar2) is
passwd         varchar2(30);

  raw_key raw(128):= hextoraw('0123456789ABCDEF');

  raw_ip raw(128);
  pwd_hash varchar2(16);

  cursor c_user (cp_name in varchar2) is
  select   password
 from sys.user$
  where password is not null
  and name=cp_name;
  
  procedure unicode_str(userpwd in varchar2, unistr out raw)
  is
    enc_str varchar2(124):='';
    tot_len number;
    curr_char char(1);
    padd_len number;
    ch char(1);
    mod_len number;
    debugp varchar2(256);
  begin
    tot_len:=length(userpwd);
    for i in 1..tot_len loop
      curr_char:=substr(userpwd,i,1);
      enc_str:=enc_str||chr(0)||curr_char;
    end loop;
    mod_len:= mod((tot_len*2),8);
    if (mod_len = 0) then
      padd_len:= 0;
    else
      padd_len:=8 - mod_len;
    end if;
    for i in 1..padd_len loop
      enc_str:=enc_str||chr(0);
    end loop;
    unistr:=utl_raw.cast_to_raw(enc_str);
  end;
  
   function crack (userpwd in raw) return varchar2
  is
    enc_raw raw(2048);
    --
    raw_key2 raw(128);
    pwd_hash raw(2048);
    --
    hexstr varchar2(2048);
    len number;
    password_hash varchar2(16);
 

Open in new window

0
I have two tables. One called EMAILQUEUE and another called EMAILQUEUE_STATUS. EMAILQUEUE contains information about emails sent or to be sent. EMAILQUEUE_STATUS contains a record of the status of each item in EMAILQUEUE. I need to get a list of all items in EMAILQUEUE whose most recent status from the EMAILQUEUE_STATUS table is "Ready to send". If I was using an Oracle database I could use something like this:
SELECT ... FROM EMAILQUEUE_STATUS WHERE STATUS = 'Ready' AND ROWNUM = 1 ORDER BY TIME_STAMP DESC

Open in new window

For Microsoft SQL Server I could use something similar but different due to differences in syntax:
SELECT TOP(1) ... WHERE STATUS = 'READY' ORDER BY TIME_STAMP DESC

Open in new window

Here's the catch. I need a single query statement that will work with the same syntax for both Oracle and Microsoft SQL Server. I'm thinking some kind of group by or partition might be the way to go but I'm not quite figuring it out. Anyone have any ideas?

Oh and by the way, this needs to be compatible with Oracle 11g or later and Microsoft SQL Server 2012 or later. Unfortunately, Oracle 11g doesn't support CROSS APPLY so that option is out. :(
0
hi am in database 12c am having this error when connecting to my database in solaris

Enter user-name: /as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 9925
ORA-09925: Unable to create audit trail file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 9925
0
A dblinked schema-remapping Oracle 12c DataPump operation is failing to capture views from the source schema.  The receiving schema has import full database.

The needed views combine data from tables in a (3rd) separate schema, landing in the target schema.  I am wondering if the target schema needs read privileges on the 3rd schema's tables.

The desired results are that the views in the source schema be included in the export/import with duplicate views landing in the target schema.

Any suggestions/pointer by resident experts deeply appreciated!!
0
Struggling with some SQL in Oracle 11g.  

I have a result set with group by and order by along with a MAX on a particular column, however results are not filtering out the results based on the max.  I assume this is because of how I have the select statement set up, possibly the group by and order by.  Maybe need to use a nested select or such.   The query is as follows:

SELECT LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, MAX(TU.TURNOUT_ID) AS TURNOUT_ID, TU.ASSET_TAG, TU.CLASS, TU.GPS_LONG, TU.GPS_LAT
FROM CERREJON.V_LINES_ZONES_TRACKS LV, CERREJON.V_TURNOUTS TU
WHERE TU.TRACK_ID = LV.TRACK_ID
AND (TU.TURNOUT_ID IS NOT NULL) AND (TU.TURNOUT_ID > 0)
--AND LV.ZONE = 'TRAMO 2'
GROUP BY LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, TU.ASSET_TAG, TU.CLASS, TU.GPS_LONG, TU.GPS_LAT, TU.IMPORT_DATE  
ORDER BY LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, TU.ASSET_TAG DESC, TU.IMPORT_DATE DESC

The results are attached.   The item in yellow is the MAX value for that result set (line, zone, track, start_marker, start_offset and asset_tag.  This is the only row out of this result set I want to be included.  the two rows following should not be included.
SI_SQL_Results_Capture.JPG
0
I'm trying to set the connection timeout property for an OleDbConnection object. I read in docs that the property is read-only but can be set by adding a parameter to the connection string. My connection string looks like this:
Provider=MSDAORA;Data Source=[myTNSEntry];User ID=[myUser];Password=[myPassword];Connect Timeout=30;

Open in new window

Inspecting the OleDbConnection object reveals that the connection timeout is, in fact 30 seconds which is good. Unfortunately, as soon as I try to open the connection I get an OleDbException error saying "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." If I do not add the "Connect Timeout=30" argument in the connection string then the ConnectionTimeout property of the OleDbConnection object is the default 15 seconds but the Open() method works without errors. Am I missing something or am I just running into some kind of limitation with OleDb or with the Oracle provider?
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.