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

Hi Experts

Found one surprise when trying to load the data using sqlldr. In the .ctl file it seems sqlldr does not like specifying the datatype NUMBER.
My .ctl file looks like below:
LOAD DATA
INTO TABLE MYTABLE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
STATCODE      CHAR(2),
CLTDOB NUMBER
)

Then it gives below error: which says error in line no 7, expected comma or ) but found "NUMBER"

SQL*Loader-350: 行7に構文エラーがあります。
","または")"ではなく"NUMBER"が見つかりました。
CLTDOB NUMBER,

So, I removed the datatype NUMBER and then the sqlldr worked. Wondering why it gives error with this NUMBER data type?

If any one of you know kindly share it.
1
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have an oracle 12.2 database.. I set the audit trail setting to DB, EXTENDED to enable standard auditing and capture sql statements.

My understanding there are a dozen privileges that oracle will audit by default like CREATE TABLE, DROP TABLE, CREATE PROCEDURE, etc.

I have a main application admin account "hradmin" and I ran the sample schema script to create "OE" user and tables..

When I log in as "hradmin" and I created test table, dropped it, delete data, everything was recorded in the audit trail log.

When I logged in as "OE" and ran same steps nothing was recorded in the audit trail.

Do you know why?

I also tried logging in as SYS and running commands like this and still did not do it.

AUDIT CREATE ANY TABLE;
AUDIT DROP ANY TABLE;
AUDIT ALTER ANY TABLE;
0
Is there a need to shutdown all oracle 12c instances on a RHEL machine before the Linux team applies patches and reboots the machine?

I have those database registered with oracle restart so they should start automatically after reboot of machine. But I was wondering if the linux reboot will not
shutdown the database cleanly and may result in some database corruption.
0
hi,

I have the oracle 12c enterprise DB dump files to try importing to MariaDB/MySQL in order to see how many data and table structure can be convert to MySQL/MariaDB format.

I am not sure if it is a good idea to setup Oracle express and then import the dump files to it so that migration from Oracle can be easier, any idea?
0
How to call or execute below package using oracle sql developer


PACKAGE BODY "PKG_PRODUCT"
AS

PROCEDURE SP_LATEST_PRODUCT(
    I_ProductType     VARCHAR2,
    O_PRODUCT OUT SYS_REFCURSOR,
    O_PRODUCTCOUNT OUT NUMBER,
    O_RESULT OUT VARCHAR2 )
AS
BEGIN
      
        IF UPPER(I_ProductType)  = 'CLOTHS' THEN
                  O_PRODUCT OUT = 5;
        ELSE
                  O_PRODUCT OUT = 15;
        END IF;
 
      O_RESULT = 'SUCCESS';
      
END
1
What's the difference between Oracle GoldenGate and Oracle ODI? Which one is the ETL tool?

Why two tools? (Their product descriptions are fuzzy enough that it's hard for the layman to tell the difference).

if the two tools are appropriate, isn't that a big learning curve to learn two separate (albeit complementary) tools?

It seems to me GoldenGate is a "data replication tool", layman's view: it moves data from point A to point B.
- but it also says it does "transformations", so doesn't that translate to an E-T-L tool?

The ODI product home page doesn't say anything about transformation.

This is a good article but still my understanding on the differences is fuzzy ...

related article ...

===== background =====

I'm a data warehouse consumer and designer of a relational d.b. data warehouse. Never got into the multi-dimensional or star schema stuff.

So I'm not building the infrastructure for the data warehouse, but I might write the ETL specs. In other words, I know the data and know my reporting requirements, with a strong SQL knowledge and understanding of the business.

But I need to know some of the tools to best understand the environment and work with the builders of the data warehouse.
0
I need to search for a string in my Oracle database schema for a specific value, what is the best way to do that, or at least a doable way to do that?

For example, I have the following server name:
IMG-IT-AS004

I want to query the database to see what tables and columns the string is in.

Thank you much.
0
Hi Experts,

Below query is used LISTAGG() which is supporting in 11g .
same query is not executing in 10g  showing error -ORA00923-FROM Keyword not found.

what is the alternative for this query in 10g
i have tried with xmlagg(),and stringagg() but not succeeded.

Please provide the alternative solution.

query:
=====
SELECT DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE,
  LISTAGG (CHRG_AMT, ',') WITHIN GROUP (
ORDER BY CHRG_AMT) CHRG_AMT
FROM
  (SELECT S.DEAL_ID,
    MAX (DOC_ID) DOC_ID,
    SUM (CHRG_AMT)
    || ' '
    || CHRG_CURR_CODE CHRG_AMT,
    MAX (CHRG_MARGIN_RATE) CHRG_MARGIN_RATE
  FROM TR_CHARGES C,
    TR_SUMMARY S
  WHERE C.owner_cust_id IN ('10033')
  AND C.DEAL_ID      = S.DEAL_ID
  GROUP BY S.DEAL_ID,
    CHRG_CURR_CODE
  ) C
GROUP BY DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE;

Open in new window


Thanks,,
0
PORTNOI, MARK
ZANNIKOU, MARIA
SLINGER, ANDREW
DAVIES, LYDIA
ZANNIKOU, MARIA

The whole name (last name, first name) is in one column (column_name is assigned_to)
In oracle how can I get 1st alphabet of first name and full last name?

MPORTNOI
ASLINGER
LDAVIES
MZANNIKOU
0
Hi,
How can I make typeid as surrogated key

create table type
typeid number(10),---this as surrogated key
typecode varchar2(10 byte)
)

result as
typeid typecode

1         aa
2       bb
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Untitled.jpg errror display as not a valid month while executing the querry


/* Formatted on 2018/09/18 13:55 (Formatter Plus v4.8.8) */
SELECT a.dat, b.division, b.sm_name, b.dt, b.cust_code
  FROM (SELECT dat
          FROM (SELECT       TO_DATE (:r_from_cust_main_acnt_code,
                                      'DD/MM/RRRR'
                                     )
                           + ROWNUM
                           - 1 dat
                      FROM ot_job_exec_head
                CONNECT BY LEVEL <=
                                TO_DATE (:r_till_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              - TO_DATE (:r_from_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              + 1)
         WHERE ROWNUM <= 31) a
       LEFT JOIN
       (SELECT division, sm_name, dt, cust_code
          FROM (SELECT division, sm_name,
                       TO_CHAR (TRUNC (TO_DATE (dat, 'DD-MM-YYYY')),
                                'DD-Mm'
                               ) dt,
                       cust_code
                  FROM (SELECT vssv_field_02 division, vssv_name sm_name,
                               jeh_dt dat, jed_flex_01 cust_code,
                               jed_flex_03 purpose, jed_flex_11 next_action
                          FROM …
0
Need help figuring out why my dashboard continues to show the color white when the hex color I am specifying is green and orange.
SELECT WO_NUMBER AS WONUM
      ,PART_NUMBER AS PN
      ,DESCRIPTION AS DESCR
      ,CASE CONTRACT_TYPE WHEN 'PBH Pool' THEN 'PBH' ELSE 'TM' END AS CONTRACT
      ,DAYS_OPEN AS TAT
      ,CASE WARRANTY_REQUESTED WHEN 'F' THEN ' ' WHEN 'T' THEN 'W' END AS INFO
      ,TECH
,case when priority = 1 or priority = 2 then '#FFFF00'
when days_open > 3 or priority >= 1 then '#ff0000' 
when days_open < '-1' then '#66b65d'
when days_open in ('0', '-1') then '#f68835' 
end row_color
FROM BA_VIEW_WIP_SHOP_SCREENS
WHERE STATUS IN ('OPEN', 'AWT SHOP EVAL', 'SHOP EVALUATION') AND SHOP = 'SH ELE/FUE ARO'
order by days_open desc

Open in new window

Capture.JPG
0
Converting varchar(7 byte) 000.000
To_number(column,999.999) it gives me 0
1
Dear Experts,

 How do I know in Oracle SQL for specific table when ( the time ) it was last updated or changed ?

Thanks
0
hi am downloading the file using webutil how can i set the context to ASCII or Text when i download the file in my window is puting everything in one line am in oracle form 11gR2  the application is running in solaris
1
Hi Expert,

I want to insert multiple records on a single query and my records are in an external file.

Please any advice for me ...

I have 2 Approach:: Please suggest which one will work effectively.

1: Using external table ... "insert into table select * from exttbl;"
2: Using SQL loder .
0
Hi Experts

Suddenly sqldeveloper is not connecting to oracle instance. It is just trying to connect and hangs on like shown in the below image.
OralceConnection.png
Then I tried to connect using sqlplus and there also it remains unresponsive.


How to find the reason for this? Kindly help.
1
Hi,
Want to create a data model or tables for employee assessment in oracle

have table employee like below
empliid firstname last name

Then want to ask question to employee

question table
1) how many years of service
2) level of satisfaction  with manager
etc

And based on the answers they will give total assessment score

They also want the database to be dynamic so they can add question every quarter or so

They also want to keep versions of assessment for employee

please let me know what all tables i need and also how to do version design
0
In an Excel VBA procedure, I am retrieving data from an Oracle table and writing it to a worksheet. The procedure works fine, except for one oddity. I can filter the data on anything but a date column. I checked - this column is a DATE in the Oracle table, but the recordset result completely ignores any attempt to filter on it. No error, it just ignores it.

So, for example, if I run any of these three queries in an Oracle-connected UI (I use PL/SQL Developer), i get the exact same records:

select *
from myTable
where myDate >= '07SEP2018'
order by myDate desc

select *
from myTable
where myDate >= '07-Sep-2018'
order by myDate desc

select *
from myTable
where myDate >= to_date('07-SEP-18')
order by myDate desc
   
The results are filtered by the date 9/7/2018.

However, when I run the same query in ADO to fill a recordset using any of these formulations, I simply get the whole table - all dates, going back to 2016. Here is my code:

strSQL = "select * " & _
    "from myTable " & _
    "where myDate >= to_date('07-SEP-18') " & _
    "order by myDate desc"
   
    'create, configure, and populate recordset
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenForwardOnly
    rs.Open (strSQL), cn

I have tried all 3 of the above variations in ADO also, with the same result. I am using the ADO 6.1 library and Oracle 11g.

Any idea what the issue is?

Thanks.
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 Experts,

how to get the substring from the below query

SELECT ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1 start_date FROM DUAL
//01-AUG-18
expected o/p: AUG-18
1
Hi Database Experts,

Scenario:
As part of code deployment, we also execute Database DML and DDL. Recently a cursor statement (select / update) badly affected our PPTE environment. Since we have less data in lower environment. we actually couldnt catch this. however it took 3+ hrs in PPTE.

Is there any automated way to check this poor coding when executing in lower environment please?
E.g
  1. Can we add custom sql (as part of post sql on every deployment) to generate profile or gather stats when executing in lower env? So we check this report every time
  2. Anything with non-sys or non-system user. Basically with the user account who executes SQLs.
  3. SonarQ profile
0
I have been tasked with creating some reports out of VS and SSRS from an Oracle 12c database.  I am an Oracle developer but I don't know much about VS or SSRS.  I have both VS 2015 (14.0.25431.01 Update 3) and 2017 (15.7.5) installed and I have installed ODTwithODAC for 12c.  I have also installed SSDT for both VS 2015 and VS 2017.  

Using VS 2017, i have created a new project but I am unable to create a shared data source.  As soon as I select Oracle Database in the Type drop-down, the Edit button next to the connection string becomes grayed out.  

Using VS 2015, I have cloned an existing project that works fine for my colleague.  However, when I first tried to load the project I received a message that the version of the report server project is not supported and the project must be upgraded.  If I say no, then the project won't load.  If I say yes, the project loads and everything seems to be ok. However, when I try to Preview an existing report from the project, I receive the following error:
"An error has occurred during local report processing.  An error has occurred during report processing.  An attempt has been been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."  There is nothing in the Error List when this happens.  

The only difference between my colleague's setup and mine is that he has SQL Server Reporting Services 14.0.806.134 and I have …
1
I want to use Oracle regular expression to extract dates from a varchar2 field in Oracle.  
I need to convert the data into date format so that I can then evaluate it to filter out dates older than today.

regexp_like(batch_id,'[0-9]{2}.[0-9]{2}.[0-9]{2}') works in my where clause and brings back all the data, but I also need to convert it and compare it to SYSDATE to filter out old dates.

The data is in one varchar2 column and looks like this:


Student Term 01.31.19
Student Term 09.15.18
Student Term 09.30.18
Student Term 11.30.18
STAFF 08/31/18-08/15/19
EXTRA 8.31.18-12.21.18
EXTRAS END 08.31.18
AUGMENT END 08.31.18


All help is greatly appreciated.
0
AIRCRAFT_REG      PCDTM_CODE      EXCHANGE_INDICATOR      STARTDATE
N158AN                       102                           N                                     20-Mar-18
N157AA                       102                           N                                     20-Mar-18
N165NN                       102                           N                                     20-Mar-18

Doing script for dataload

Select 'N158AN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union
Select  'N157AA' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union
Select  'N165NN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union

=CONCATENATE("Select A2,"' ac_reg, ", B2, pcdtm_code,'N' ind,trunc(sysdate-177) stdt  from dual union")

I am getting error. Can someone hep me to fix this?
0
In Oracle SQL how could I do create this Query.  I would like to check for multiple conditions with the where clause.

select * from lawprod.WHSHIPLINE where company=10 location=98MDC update-date='01/20/2018'

It works if I do this

select * from lawprod.WHSHIPLINE where company=10  

However, I would like to check for more conditions like

I would like to include data of the company=10 and location=98MDC and update_date = 01/20/2018

Thanks for any help!
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.