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,

right now we are analysis the need of data warehouse and data mart as well as ETL.

our oracle database has some ETL by batch process, we don't know what it is but in system diagram we have ETL.

and we use replicate technology to replicate data from OLTP to 2x other DB for reporting purpose but we do not use the oracle data warehouse builder to create a data warehouse.

question now is, why and when should we use mariaDB AX to create a separate data warehouse for reporting purpose as existing model already can handle loading, why need to redesign one using MariaDB AX to do reporting ?

data mart on MySQL/MariaDB is REALLY necessary ? can it be done on MySQL and MariaDB?

please share your thought.
0
Experts,
I need a hand with an Oracle CASE statement.

In My subquery I am returning a distinct count of records as NumADR.
In each CASE I am evaluating NumADR

The result requires Joins to 3 tables.
The same joins already exist in the subquery
unsure how to make them available to the CASE statements.

Select 
E.CON_CODE,
CASE WHEN NumADR > 1 THEN 'MULTIPLE DESTINATIONS' ELSE decode (E.CON_CODE, '/', D.DOC_MAN_NAME, C.CON_NAME) END as ST_NAME,
CASE WHEN NumADR > 1 THEN 'SEE INDIVIDUAL BOL' ELSE decode (E.CON_CODE, '/', D.DOC_MAN_ADD1, C.CON_ADD1) END as ST_ADD1
FROM (   
  Select 
  COUNT(DISTINCT(decode (E.CON_CODE, '/', D.DOC_MAN_ADD1, C.CON_ADD1))) As NumADR
  From E_ORD_H E
  left JOIN M_CON_H C on C.CON_CODE = E.CON_CODE and C.COMP_CODE = E.COMP_CODE 
  left JOIN C_DOC_MAN D on D.DOC_NUM = E.ORD_NUM and D.COMP_CODE = E.COMP_CODE 
  and D.DOC_TP_FLAG = 'O' and D.DOC_MAN_CODE_TP_FLAG = 'C'
  left JOIN M_ZIP Z on Z.ZIP_CODE = C.ZIP_CODE and Z.COUNTRY_CODE = C.COUNTRY_CODE and Z.ZIP_STAT = 'A'
  where E.COMP_CODE = :p_comp_code
  AND E.ORD_NUM in (
      SELECT D4.ORD_NUM from E_FRT_ORD_D4 D4
      where D4.load_num = :p_load_num
      and D4.comp_code_Actual = :p_comp_code)
  )

Open in new window

0
These are the tables and the data in the tables is attached to the csv files.
 CREATE TABLE TABLE1 
  ( UNITCODE	VARCHAR2(20 BYTE),
PROVINCE	VARCHAR2(20 BYTE),
UNITNAME	VARCHAR2(60 BYTE),
ORIGINALEFFECTIVE	DATE,
REVISIONDATE	DATE,
UPDATEDATE	DATE,
INTERESTDATE1	DATE,
INTERESTDATE2	DATE,
INTERESTDATE3	DATE,
INTERESTDATE4	DATE,
INTERESTDATE5	DATE,
TERMINATED	VARCHAR2(20 BYTE));

     
     
     CREATE TABLE Table2
   (  UNITBOUNDARYID	NUMBER,
UNITCODE	VARCHAR2(20 BYTE),
DLSORNTS	VARCHAR2(1 BYTE),
LOWDESC	VARCHAR2(50 BYTE),
DLS_MERIDIAN	NUMBER(1,0),
DLS_RANGE	NUMBER(2,0),
DLS_TOWNSHIP	NUMBER(3,0),
DLS_SECTION	NUMBER(2,0),
NTS_PRIMARY_QUAD	NUMBER(3,0),
NTS_LETTER_QUAD	VARCHAR2(1 CHAR),
NTS_SIXTEENTH	NUMBER(2,0),
NTS_BLOCK	VARCHAR2(1 CHAR),
HIGHDESC	VARCHAR2(20 BYTE)
);


CREATE TABLE MAIN
(STREAM_ID	VARCHAR2(50 BYTE),
UNIT_SHORT_NAME	VARCHAR2(100 BYTE),
UNIT_LONG_NAME	VARCHAR2(150 BYTE),
EFFECTIVE_DATE	DATE,
STRAT_NUINT_NAME	VARCHAR2(150 BYTE),
UNIT_TYPE	VARCHAR2(30 BYTE),
TRACT_ID	NUMBER(4,0),
LAST_BOUNDARY_CHANGE	DATE,
BA_NAME	VARCHAR2(100 BYTE),
BA_ID	NUMBER(10,0),
OPERATOR_DATE	DATE,
COMPUTED_LOCATION_DESCRIPTION	VARCHAR2(20 BYTE),
TRACT_STATUS	VARCHAR2(20 BYTE),
UNIT_ID	NUMBER(2,0));

Open in new window



Here is my code that is giving me the error attached below.  All I am trying to do is to merge data from the Main Table to the rest of the tables if the data exists great just update if not then insert into Table1 and Table 2

SET SERVEROUTPUT ON;
 
 DECLARE 
 
    

Open in new window

1
Hi,

We are doing impact analysis on the new type AFTER convert from Oracle 10g/12c to to MariaDB, any information on

1) Is there any impact on the change of type ( in terms of RAM, disk space and CPU) ?
2) What to do to get ride of the impact?
3) URL for the proof?

For the following type conversion:

NUMBER(10,0)  and NUMBER (9,0) convert to BIGINT
NUMBER(3,0) convert to SMALLINT
NUMBER(1,0) convert to TINYINT.
NUMBER without () (e.g. NUMBER) to DOUBLEe
      DATE  to DATETIME
      VARCHAR2() to VARCHAR()  (e.g. VARCHAR2(1 CHAR) will become VARCHAR(1). VARCHAR2(100 CHAR) will become VARCHAR(100)
0
hi,

right now planning the last stage of oracle to mariaDB migration and here are some BI related question, hope you all can give a hand.

On the change on the DB and data column type. (e.g. Oracle varchar2() to MariadB varchar()),  usually any impact on GUI and report design?
0
Hi APEX masters,

Can I know how to display a message as "NO DATA FOUND" in oracle apex , when the query is not returning any data from a SQL.

As when I give a message under the attributes of the report - it would display a message as "No Data Found", even before the user selects the parameters. I want to display "No Data Found" message only when there is no data.

Please let me know how to approach this issue.

Thanks,
R
0
This is only for two tables, but I would like to merger from one source into 6 target tables, can someone assist please?

CREATE TABLE source_table (
    col1 NUMBER,
    col2 VARCHAR2(10),
    col3 VARCHAR2(10)
);

INSERT INTO source_table (col1, col2, col3) VALUES (1, 'a', 'w');
INSERT INTO source_table (col1, col2, col3) VALUES (1, 'b', 'x');
INSERT INTO source_table (col1, col2, col3) VALUES (2, 'c', 'y');
INSERT INTO source_table (col1, col2, col3) VALUES (3, 'c', 'z');

COMMIT;

CREATE TABLE target_table (
    col1 NUMBER,
    col2 VARCHAR2(10),
    col3 VARCHAR2(10)
);

INSERT INTO target_table (col1, col2, col3) VALUES (1, 'b', 'z');
INSERT INTO target_table (col1, col2, col3) VALUES (3, 'd', 'w');

COMMIT;

Open in new window


Now we are going to merge two table.

MERGE INTO target_table trg
USING source_table src --Now I simply write the table name:)
ON (
    trg.col1 = src.col1 AND
    trg.col2 = src.col2
   )
WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause
    trg.col3 = src.col3
WHEN NOT MATCHED THEN INSERT
    (
        col1,
        col2,
        col3
    )
    VALUES
    (
        src.col1,
        src.col2,
        src.col3
    );

COMMIT;

Open in new window



CAN YOU MERGE DATA FROM ONE SOURCE TABLE TO 6 TARGET TABLES?
0
i am trying to load  the attached csv file to oracle database, but I get the error message  because I have duplicate date though they are in different column name.  I formatted the date but still get this error : - --EFFECTIVE_DATE GDK-05030: The date format pattern ends before converting entire input string. LAST_BOUNDARY_CHANGE GDK-05030:
The date format pattern ends before converting entire input string. OPERATOR_DATE GDK-05040: input value not long enough for the date format
How can I load this csv file after formatting date as is?r
SAMPLE.csv
0
I have setup 11G database on Windows server 2012R2 machine and I am able to access 11G from same machine through SQLPLUS but When I am trying to access that from different server then it gives ERROR: [ORA-12170: TNS:Connect timeout occurred]
How I can fix that, Please help me.
0
Any free tool or site like draw.me to sketch database entity relationship diagrams like many-many, one-many type of relationships between tables

please advise
0
What are the options to execute remote procedures in apex , can it be done over dblink?
0
Steps to refresh a region every 5 minutes in Oracle APEX. I have created the dynamic action to refresh but not sure about for every 5 minutes
0
I am looking for a list of password policy management commands (including list commands) that I can run in sqlplus in Oracle.

Thanks
0
I have a region with a button on it that is right-aligned. I want to left-align it or center aligned but not sure how. Here is the region:

 

  <div class="t-Region-body">
       <div class="container">...</div>
       <div class="title-container">...</div>
       <div class="t-Region-buttons-right">
          <button>Test</button>
       </div>
    </div>

Open in new window


I tried changing `t-Region-buttons-right` to `t-Region-buttons-left` but nothing changed. Is there any other way?

The region gets generated by PL/SQL code:
htp.p(..);

Open in new window

0
I have a bash shell script that I want to call a Oracle DB stored procedure with (procedure is in a package with public interface), I am running it on Cygwin:

#!/bin/bash

if [[ $1 = '' ]]; then

  echo
  echo "usage : copy_client_div.sh <master conect string> "
  exit 0
fi

test_db1=`$ORACLE_HOME/bin/sqlplus -s $1 << EOF
set heading off
set pages 0
set feedback off
select 999 from dual;

EOF`

test_db=`echo $test_db1`
echo test_db: ${test_db::-1}
echo ${test_db}


if [[ ${test_db::-1} != 999 ]]; then

  echo "Can NOT connect to database with the given connect string"
  echo
  exit 0

fi

CONNECT_STRING=$1

`$ORACLE_HOME/bin/sqlplus -s $CONNECT_STRING << EOF > /c/temp2/log.txt

set heading off
set pages 0
set feedback off

begin
      --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
end;
EOF
`

If I run the procedure by itself in a sql script, it works.  In the bash script, nothing happens and no error gets returned.  Can someone tell me what I am missing?  Thanks.
0
Convert a .txt file to a .csv file through coding using 1.plsql oracle and 2.java
0
Hi APEX masters,

I am using Oracle APEX 18.1 and can anyone let me know how to implement hours in the charts X-axis , please find attached.

Thanks,
R
88C341A5-BDA1-4C47-B563-C624248BC17.jpeg
0
I am a novice to Oracle Administration and am using it primarily as a backend resource to demonstrate an Auditing application.

Oracle 12c, installed on Windows 2012R2.  I can logon to the Windows server as the local administrator but while I can logon to Oracle locally, I cannot logon remotely.

ORA-01017: invalid username/password; logon denied.

ORA-28000: the account is locked.
0
Hi Expert,

If there anyone working on Oracle Retail V16 SaaS Interface here,
Cloud anyone please guide how to prevent Duplicate file processing to SaaS using PL/SQL or UNIX?

Thank You!
0
hi am having this error when starting database
SQL> startup
ORACLE instance started.

Total System Global Area 1728053248 bytes
Fixed Size                  3004520 bytes
Variable Size            1056966552 bytes
Database Buffers          654311424 bytes
Redo Buffers               13770752 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1:
'/u01/app/oracle/oradata/dbname/datafile/o1_mf_system_fbj5km7b_.dbf'
ORA-01207: file is more recent than control file - old control file

Open in new window


the database is 12c in solaris
0
hi am in database 12c am having this error when starting my database in solaris

SQL> startup

ORACLE instance started.

 

 

Total System Global Area 1728053248 bytes

Fixed Size                  3004520 bytes

Variable Size            1023412120 bytes

Database Buffers          687865856 bytes

Redo Buffers               13770752 bytes

ORA-00205: error in identifying control file, check alert log for more info
0
Hi Expert,

Could anyone please share how to create REST web service using ORDS ?

Please share with some real-time demo scenario.

Your comment is always Appreciate ..

Thanks..
0
I have a cursor that extract data then compares it with the ones on the directory. If they don't exists in the directory, then delete from listed tables starting with the child then parent.

Files are stored in the directory like this

c:\temp\"12345/john doe/1 2nd street/ Louisville/ TX/ 75091/2 accts _ chq and svg"
c:\temp\"12367\jay doe\2 22nd street\ Louisville\ TX\ 75099\2 accts _ chq and svg"

How can I read files in a directory with different formats like shown above from those two files in the c drive


DECLARE
      v_load_status  VARCHAR2(400);
      v_missing_acct NUMBER;
       
    BEGIN
      FOR c1 IN
      (  ---extracting data from tables
             SELECT c.acct_no ,
                    a.acct_holder_name ,
                    a.address ,
                    a.phone_number ,
                    c.bf ,
                    c.acct_path ,
                    c.acct_id
             FROM   customer_inf a
             join   accounts c on c.account_id = c.acct_id)
      LOOP
        v_load_status := NULL;
       
        BEGIN
       
IF DBMS_LOB.FILEEXISTS(c1.bf) = 0 THEN

            v_load_status := 'MISSING';
            INSERT INTO activity_log
                        (
                                    acct_no ,
                                    acct_holder_name ,
                                    address ,
                                    phone_number ,
                         …
0
I need to get Microsoft SQL Server Mgmt Studio to connect to an Oracle database. I am having issues w/ getting the Oracle data access component -    OraOLEDB.Oracle available and showing on the SQL client under Linked Servers and Providers. Ticket-Details.docx
0
Hi All Experts,

I want an oracle pl/sql scripts/procedure/package anything compatible to oracle 12c, which can help me to send the result of a query over my email.

For example: Select * from employee;

1. If this query returns any rows, the count will be emailed to a@b.com
2. The result of this query will stored in excel and the excel file will mailed to a@b.com
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.