[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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,

 any way to find out isolation level  of oracle 10g/12c ?

 will export feature export isolation level  setting from 10g and can be import to 12c  ?
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Dear Experts, I'm testing Oracle 11g on Centos 7 64b.

I installed Oracle but could not connect to the globalDB by sqlplus, it keeps showing error: ORA-12514: TNS:listener does not currently know of service requested in connect even I start the Oracle Database

ora1.PNG
ora2.PNG
I also attached the response file from Installation process. These are the details of listener and tnsname files:

ora3.PNG
lsnrctl status:

ora4.PNG
Can you please help? Many thanks!
db.rsp
0
Hi Experts
As I am currently extract data in excel file via MSSQL from local computer in which there is linked server with Oracle Database, I got errors message for excel -2147417484 (80010108).
But I am successfully extract data if small dataset. What should I do? Transfer to sever side instead of local computer? Please kindly advise

Many thanks
1
I am creating a report in Oracle Developer and usually do not like to add my Schema name to each line in my From Clause for each Join. HCCLSC.Pat_enc so in Oracle Developer I can use ALTER SESSION SET CURRENT_SCHEMA=HCCLSC


and this defines the schema so I do not have to add it to each line of my From Clause.

My question is, how do I use this code in Crystal Reports? Is there a way to setup the SCHEMA without having to add it to each line?  We have switched from Teradata to ExaData.

 FROM HCCLSC.pat_enc pat_enc
        LEFT OUTER JOIN HCCLSC.patient
                ON pat_enc.pat_id  = patient.pat_id
0
Hi,

Hi Experts,

Not sure I have this correct as my source field has a space in it.  That might be problem, but not sure if I did this correctly.  I'm trying to change '100' to 'KABOOM'.  I'm not sure if 100 is stored as a CHAR or NUM though.  So I think that matters?


Case Statement:

CASE WHEN (Document Number) IN '100' THEN 'KABOOM ' ELSE (Document Number) END

Getting this error:    Oracle SQL Error:  ORA-00920: invalid relational operator

SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
1
Hi Experts,
create table BIT_BANK_CODES
(
COUNTRYCODE VARCHAR2(2),
ACCTNUMBER VARCHAR2(34),
ACCTIDENTIFIER  VARCHAR2(34),
GROUP_ID    VARCHAR2(15) NOT NULL,
BANK_ID VARCHAR2(20) NOT NULL,
DELETEFLAG  VARCHAR2(1) NOT NULL) ;


INSERT INTO BIT_BANK_CODES VALUES('SG','22205251117',null,'testgrp','CBLSGS0XXX','N');
INSERT INTO BIT_BANK_CODES VALUES('SG','0100879756',null,'testgrp','CBLSGS0XXX','N');

select bank_id from BIT_BANK_CODES where group_id='testgrp' and (acctnumber='0100879756' OR acctidentifier='0100879756');

Open in new window


I HAVE TO WRITE SAME QUERY USING FUNCTION
THIS FUNCTION SHOULD HAVE THREE INPUTS -->group_id,acctnumber,acctidentifier
AND IT WILL RETURN bank_id

I HAVE TO CREATE A PACKAGE-->THEN I HAVE TO CREATE A FUNCTION



created package:
================
create or replace
PACKAGE PKG_INF_BANKCODE AS

  FUNCTION GET_BIT_BANKCD(group_id IN varchar2,acctnumber IN varchar2,acctidentifier IN varchar2) RETURN  varchar2;

END PKG_INF_BANKCODE;

created function:
=================
create or replace FUNCTION GET_BIT_BANKCD(in_group_id IN varchar2,in_acctnumber IN varchar2,in_acctidentifier IN varchar2)
RETURN  varchar2
IS BANKCODE VARCHAR2(20);
BEGIN
select bank_id INTO BANKCODE from BIT_BANK_CODES where group_id= in_group_id
and (acctnumber=in_acctnumber OR acctidentifier=in_acctidentifier);
RETURN BANKCODE;
END GET_BIT_BANKCD;

CAN SOME HELP ABOVE CODE IS CORRECT?
DO I NEED TO CREAT PACKGAGE BODY?
HOW CALL THIS PACKAGE and function BY PASSING INPUTS
I AM USING SQL DEVELOPER.
0
Hi Expert,

I wants to archive and purge my table data based on following condition, could you please guide me how to do this using rowid .

--This is based on create_daytime column
Archive_criteria  --365*3    
Purge_criteria  -- 365*8    

Please let me know if you need more info.

Thanks in Advance
Mihir
0
Hi Expert,

I have a issue with a job, i'm trying to fetch and insert into common_error_log table.

FYI.. I have mentioned the code chunk.
This is failing before insert into Common_Error_Log table as the cursor has no record to fetch .
   OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE; 
   END IF;

Open in new window


But after checked the value from table it's there, could you please throw an light if entry is there than why going this failure.

Please find the following attachment select stmt for cursor.

CEL
Please let me know if there need more info.
Thanks,
Mihir
1
hi i got the following record

 

abc*** i what it to be update to abc*###

 

or if i have abc** to be  abc##

 
am in oracle database 11gr2
this is my query
update noc_t_mrc set enc_encoded_reply=substr(enc_encoded_reply,1,length(enc_encoded_reply)-1)||'#' where enc_encoded_reply like '%*';

Open in new window

0
Hello,

I search a script to compare number of lines on all tables between two schémas.

Thank you

Best regards
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

https://community.oracle.com/thread/686026

Extracted fr above, "According to documentation, a Private Database Link is more secure than a public or global link";
we have less critical systems that use DB links to a very critical system (Oracle).

I'm new to the organization & DBA told me there's a number of "Public DB links" including some with RW & without
SSL cert.

Q1:
Is it a lot of work or feasible to convert from public to private?


Q2:
Can self-signed SSL certs be used?


Q3:
Any other mitigations if we leave it as public?
0
I have stored my xml in a table. Select and insert in a new table works.

I save the xml file with sqloader as xmltype as HUGECLOB. I can select with xpath the data and insert it in a new table.

My Table structure is:
MAINPART | MAINPART2 | MAINPART3 | MAINPARTEXTENSION1 | MAINPARTEXTENSION2 | MAINPARTEXTENSION3 | MAINPARTEXTENSION4

I can map everything from the xml file except the mainpartextension. The select gives me a singleton error. How can I handle with Select or PL/SQL with a Loop or another xpath xpression to laod the xml tag MAINPARTEXTENSION into MAINPARTEXTENSION1 , MAINPARTEXTENSION2, MAINPARTEXTENSION3,....

Please help.
0
https://www.michalsons.com/blog/what-is-a-national-critical-information-infrastructure/17701
https://publicwiki-01.fraunhofer.de/CIPedia/index.php/Critical_Information_Infrastructure

I have to draft a guideline for systems that interface with a CII system & need inputs:
currently, the interfaces concerned are limited to 3 types only:

1. files transfer
============
I can only think that the generally practices ie:
 a) encryption of data in transit (eg: using sftp instead of ftp/mapping a drive or NFS)
 b) encryption of data at rest if it's sensitive (tampered with)

2. API
=====
how do we secure these (in particular APIs using microServices)??
I've heard of API needs to be certified so before requesting for it, need to be certain
else applications developers may question its relevance/usefulness

3. DBLink
========
Those sqlconnect  esp Oracle links to extract / update data.
Will need to define if the non-CII system is
   a) updating into CII, will have to be extra stringent but how?
   b) extracting from CII, just encrypting the sql calls
 

Oracle databases, weblogic are involved in the critical systems
while the less-critical systems may be Windows, Linux on
various apps (including mobile apps).

Editing thread to add Oracle as it relates to DBLink.
0
HI,

I am comparing a data warehouse solution for MySQL/MariaDB, we are looking at :

1) Oracle Database 12c Data Warehouse
2) IBM BigInsights
3) Microsoft Azure
4) Pentaho Kettle / Other OSS

any idea?
0
Hi,
I have a temp table like below with clause.

I want to create a procedure where we should have the ability to

do execute sp_employee (enter empid '')
and below code pass that  parameter in where employee_id  ='000006190'

I need help in store procedure.

WITH T_employee  (employe_ALL)

     AS (

select  distinct employee_id as employee1 from employee_1

union

select  distinct employee_id as employee1 from employee_2

) A

where employee_id  ='000006190'

)

 

SELECT

  SUBSTR( tt. employe_ALL,2,3) AS SEQ_EMP,

                CASE

                   WHEN SUBSTR (tt.employe_ALL,1, 1) = 1

ELSE

                         'no valid info in em[;oyee****'

                           END

                   AS employe_ALL

                  FROM T_employee  tt

Open in new window

0
i want to change password_lifspan_days for all user to 180
0
the attached is the sample data im using.

I need some sql/stored procedure that:
searches for all tables that contain a named column,
queries those tables to see if any of the rows in the named column have a specific value.
outputs the result into a new table.


I’m using this  query to look for all tables that contain the column BASE_ID.

SELECT table_name,
column_name
FROM   all_tab_cols
where
column_name  =  'BASE_ID'

Open in new window

this outputs a number of tables and columns. Using the sample data, this query would return 5 rows.

img0
I then want to run a query that lists all of the tables that have row where the base_id is a certain value (in this example, BASE_ID = 100001), and a column with counts the rows that meet the condition

the sql would probably be something like:

Select Count(*), 'ANT_GIS_PROCESS_ERROR' As Table_Name, 'BASE_ID' As Column_Name From Ant_Gis_Process_Error Where Base_Id = 100001 Union
Select Count(*), 'Alternative_Allocations_Bases' As Table_Name, 'BASE_ID' As Column_Name From Alternative_Allocations_Bases Where Base_Id = 100001 Union
Select Count(*), 'Ant_Report_Selection' As Table_Name, 'BASE_ID' As Column_Name From Ant_Report_Selection Where Base_Id = 100001 Union
Select Count(*), 'Appeals_Dw' As Table_Name, 'BASE_ID' As Column_Name From Appeals_Dw Where Base_Id = 100001 Union
Select Count(*), 'CHOICES_BCK' As Table_Name, 'BASE_ID' As Column_Name From Choices_Bck Where Base_Id = 100001

Open in new window



The output would be something like:

img2

I would like this result to go into a table that looks like the above output.
sampledata.xlsx
0
HI There! I have two schemas (A & B) having multiple tables(Huge no. of records), and have to load data into schema (B) tables by capturing table names from all_tables & all_tab_columns of (A).  The table names are same in source and the target schemas but in target there're some tables has extra columns with null constraint. Need Help to INSERT the data automatically.

Oracle 11gR2 Release 4.
Source Schema Names: A
 Target Schema Names: B
Tables: Same in both schemas and in some tables more NULL columns datatypes are Number, Varchar2, Char, Date & Timestamp.

Need Help.
0
HI,
I can access the oracle tables and stored procedures using C#, but i am getting error when trying to use select oracle VIEW.
Below code i am using. I have access to that view also.
I don't even understand what mistake i did, please do the needful, very urgent.
If you have any piece of code to connect oracle view, please post it.

Error:Operation is not valid due to the current state of the object."

OracleConnection con = null;
OracleCommand cmd = new OracleCommand();
OracleDataReader dr = null;

try
{
    con = new OracleConnection("COnstring");
    con.Open();
    string strQuery = string.Empty;

    strQuery = "select col1,col2 from VW_test_view where col1 ='" + COL1 + "'";
    cmd.CommandText = strQuery;
    cmd.CommandType = CommandType.Text;
    dr =cmd.ExecuteReader(); 

    if (dr.HasRows)
    {
        while (dr.Read())
        {
            statusCode = dr["col2"].ToString();
        }
    } 
}

Open in new window

0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Hi ,
I am creating table like below:

CREATE TABLE ANSWER
  (
  ANSWER_ID NUMBER (8) GENERATED ALWAYS AS IDENTITY (
    START WITH 1 CACHE 20 ) NOT NULL ,
    QUESTION_ID NUMBER (8) NOT NULL ,
  answer_desc
)

i have data in that table

2,101 testa
3,101 testb
4 102 fana
5  102 fanb

Now i want to move code in test

how can i disable  ANSWER_ID  in test and just insert the data from dev and then enable it lets say from 500
0
Hello Expert,

Could anybody please through light on following query. How json table works here !

SELECT ext.legacy_id        legacy_id,
                               ext.record_type      record_type,
                               ext.tran_type        tran_type,
                               ext.corp_id          corp_id,
                               ext.mdse_co_id       mdse_co_id,
                               json.loc             location,
                               ext.sty_cd           sty_cd,
                               ext.sty_clr_cd       sty_clr_cd,
                               ext.mkt_cd           mkt_cd,
                               ext.unit_price       unit_price,
                               ext.effective_date   effective_date
                          FROM risprc.ris_legacy_price_stg ext,
                               json_table('['||ext.excep_str_nbr||']', '$[*]' COLUMNS loc  NUMBER PATH '$') json,   --How it works?
                               TABLE (CAST (L_loc_tbl AS NUMBER_TBL)) tbl
                         WHERE ext.record_type   = LP_corp_rec_type
                           AND ext.excep_str_nbr IS NOT NULL
                           AND ext.location      = VALUE(tbl) ;

Open in new window

0
I'm having a performance issue with an Oracle SQL query. When I add a subselect, the query takes 5 min, 40 sec.

SELECT DISTINCT
    prokeyi,
    probezc,
    provf2c,
    artbezc,
    artkeyi,
    artanrc,
    katbezc,
    etybezc,
    stabezc,
    vgbbezc,
    abpbezc,
    kvtbezc,
    kvtinac, 
    rafbezc,
    rfavf1c,
    rfavf2c,
    rfavf3c,
    rfavf4c,
    rfavf5c,
    depbezc,
    MIN((select distinct pflfldc from pfl,pty where pflptykeyi = 17 and pflobjkeyi = prokeyi and prokavkeyi = pflvarkeyi))
FROM
    pro,
    art,
    aez,
    kat,
    ety,
    dok,
    wob,
    abp,
    vgb,
    sta,
    kvt,
    kav,
    dep,
    rfa,
    raf
WHERE
    prokeyi = aezprokeyi (+)
    AND   aezkavkeyi (+) = prokavkeyi
    AND   artkeyi (+) = aezartkeyi
    AND   artkavkeyi (+) = aezkavkeyi
    AND   prokatkeyi = katkeyi
    AND   etykeyi (+) = proetykeyi
    AND   dokkatkeyi = katkeyi (+)
    AND   doksnei = prosnei (+)
    AND   wobabpkeyi = abpkeyi
    AND   wobobjkeyi (+) = dokkeyi
    AND   wobvarkeyi (+) = dokkavkeyi
    AND   wobstakeys = stakeys
    AND   dokvgbkeyi = vgbkeyi (+)
    AND   staabpkeyi = abpkeyi
    AND   abptyps = 150
    AND   wobtyps = 150
    AND   kavkatkeyi = katkeyi
    AND   kavkvtkeyi = kvtkeyi
    AND   kavkeyi (+) = prokavkeyi
    AND   rfakavkeyi (+) = dokkavkeyi
    AND   rfadokkeyi (+) = dokkeyi
    AND   rfadepkeyi = depkeyi
    AND   prorafkeyi = rafkeyi (+)
    AND   doksnei = prosnei (+)
    AND   probezc <> '---Page element---'
  

Open in new window

0
I have 2 tables  xxeis_Cost_function
it has the columns

employee_name, cost_center, payroll_group function  


and table2 xxeis_job_function
has column

cost_center, payroll_group, function

I have to find the employee
for the payroll_group, cost_center in second table,  dosent have the correct function value in the first table


table 2 has the correct fiunction value

and display the correct function_value and the employee name and wrong function_value

my query

select a.employee_full_name, substr(a.payroll_name,1,3) payroll_group, a.employee_number , a.pj#job_function
, regexp_substr(organization_name, '[0-9.]+') cost_center from xxeis_cost_function a
           where not exists( select null from xxeis_job_function b
                where substr(a.payroll_name,1,3)= b.payroll_name
                 and  regexp_substr(a.organization_name, '[0-9.]+')=b.cost_center
                 and a.pj#job_function=b.function )
0
Hi,

as we found data type after convert from Oracle to MariaDB there are diff, I'd like to know:

1) does MySQL/MariaDB has DATE only type ? Why MariaDB use datetime? MariaDB only has datatime?
2)  Why varchar2(75) become TEXT
3) why varchar2(100) become TEXT

any information about what is the space occupied by both type ?
0
Keep getting invalid identifier when running the following statement. Need to color rows based on criteria
within the SQL code in a third party dashboard.

With N1 As (
SELECT 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
  BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, 
  DEPARTMENT.DEPT_NAME, 
  BA_VIEW_SHIPPING_ORDERS.PN, 
  BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE,
  BA_VIEW_WO_SUMMARY.WO_TYPE,
  TRUNC(CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - BA_VIEW_WO_SUMMARY.NET_TAT) AS WO_DAYS_LEFT, 
  TRUNC(BA_VIEW_SHIPPING_ORDERS.DUE_DATE - SYSDATE) AS SO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'RO' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE NULL END) AS RO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'WHS' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE NULL END) AS WHS_DAYS_LEFT
FROM 
  BA_VIEW_SHIPPING_ORDERS 
  LEFT OUTER JOIN BA_VIEW_WO_SUMMARY ON BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = BA_VIEW_WO_SUMMARY.WO_NUMBER 
  LEFT OUTER JOIN DEPARTMENT ON BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = DEPARTMENT.DPT_AUTO_KEY 
WHERE 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%' 
  AND BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED')
  )
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||NVL(RO_DAYS_LEFT,'')||NVL(WHS_DAYS_LEFT,'')) 

Open in new window

1

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.