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

Oracle Masters,

I have created a DB view with 15 columns and used CAST (Null as Number(20))  for a couple of numeric columns and CAST (Null as Date) to a couple of date columns and CAST(Null as varchar2(50)) to one column - in the format as below

create or replace view test_vw as
select
col1 ,
col2,
col3,
col4,
CAST (Null as Number(20))  as col5,
CAST (Null as Date)  as col6,  
CAST (Null as Number(20))  as col7,
CAST (Null as Date)  as col8,
CAST(Null as varchar2(50))  as col9,
..
....
...
col15
from tablename;

After the view creation -
I use the select below to retrieve the data

select col1, col2, col3,col4,col5, col6, col7, col8, col9 , col10
from test_vw
where col6 > to_date ('01-JAN-2010 12:00:15', 'DD-MON-YY HH24:MI:SS')
    and col6 <= to_date ('31-JAN-2018 12:00:15', 'DD-MON-YY HH24:MI:SS');

It does not retrieve any data.  How do I get the data in spite of casting the column as null ?

Please let me know,

Thanks,
Rads
0
Your Guide to Achieving IT Business Success
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Hi, i want to use DBMS Datapump Api to export some tables with some filter on the data.
in my .NET application i cal a store proc with a parameters name : in_liste_des_tables . i got somehing like this follow  : 'NAME_EXPR', value => 'IN (''MESSAGE_ENM'',''BIT'')'
i want to use in meta filter some option to do for exemple : select * from Message_Enm where Message_id = 100, select * from Bit where BIT_ID = 47

Here is this store proc i use and i want to update. Regards
.
PROCEDURE SP_PORTABLE_EXPORT_DUMP(in_liste_des_tables IN varchar2, in_dossier_reseau IN varchar2) IS

  tache_de_dump number;
  dir_name varchar2(200);
  etat_de_la_tache varchar2(30);
 
BEGIN

--  INITIALISATION DU DOSSIER DE SAUVEGARDE --  
    dir_name := in_dossier_reseau ;

--  INITIALISATION DE LA TACHE
    tache_de_dump := DBMS_DATAPUMP.open(operation=>'EXPORT',job_mode=>'TABLE',job_name=>'PORTABLE_EXPORT');

--  FICHIER D'EXPORT
    DBMS_DATAPUMP.add_file(handle=>tache_de_dump,filename=>'HP_EXPORT.dmp',directory=> dir_name, filetype=>1, reusefile=>1);

--  FICHIER DE LOG
    DBMS_DATAPUMP.add_file(handle=>tache_de_dump,filename=>'HP_EXPORT.log',directory=> dir_name, filetype=>3, reusefile=>1);
   
--  META_FILTER
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (' || in_liste_des_tables || ')', object_type=> 'TABLE');
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump,name =>'EXCLUDE_PATH_EXPR', value =>'IN …
0
I am using SQL-loader to load multiple .csv files in one go into my oracle table.
i have multiple files with same columns. Each file is having around 500 odd records.
I have 1 extra column "COLUMN5" in table which is not in .csv file. I need to update COLUMN5 with 1 unique number for each .csv file.

Example-
1st csv file is having 500 records, so for all 500 records it should be 1 Unique number.
2nd csv file having 300 records, so for all these 300 records, next unique number.

Like File1 is having record
ABC
DEF
GHI

And File2 is having record
JKL
MNO
PQR

i need to use NEXTVAL to get the ouput in table like
ABC,123
DEF,123
GHI,123
JKL,124
MNO,124
PQR,124

Is there any way to achieve this using NEXTVAL because requirement is to use NEXTVAL only??

I am using Number.Nextval sequence for COLUMN5 but it is putting one up number for each record.
Below is my control file.

LOAD DATA
INFILE 'sample.csv'
INFILE 'sample2.csv'

APPEND INTO TABLE TABLE1
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( 
COLUMN1, 
COLUMN2, 
COLUMN3, 
COLUMN4, 
COLUMN5 "NUMBER.NEXTVAL" 
)

Open in new window

0
ORACLE PL/SQL Updating a column with SUM query of another table
0
Any of you guys use TOAD to connect to an Oracle database? When starting a new connection you need to enter the following data:

*Host
*Port
*Service Name
*UserId/Password
 *Oracle driver (12g, 8i etc.,)

I am assuming there is a connection string being formed in the back end. Do you know what that is?

I have a sample connection string below that has all these components except the Oracle driver being used. Do you know what the connection string would look like if using 12g_Home?


Example:
Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=???)(PORT=???)))(CONNECT_DATA=(SERVICE_NAME=???)(SERVER=DEDICATED)));User Id=???;Password=???;
0
I use the below connection string in Excel VBA and it works great. Nonetheless, I came to a clients machine that had a bunch of different Oracle drivers installed. (I.e., 8i, 9i, 10g, 11g and 12g, 18c). What adjustment do I make to the connection string below to force it to always use the 12g driver

Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=???)(PORT=???)))(CONNECT_DATA=(SERVICE_NAME=???)(SERVER=DEDICATED)));User Id=???;Password=???;
0
Can someone please explain an editionable view  and syntax to alter the view or/and can you alter this view the same way you alter a simple view? I have already read Oracle documentation's but it is still not clear?
create or replace force editionable view "schema". "view_name_v"
("...aliases_col_name(s)
)
as
select  columns
from table(s)
where [ condition ]
;

Open in new window

0
Hi Experts,

Packga p42

PROCEDURE p1 -->need to invoke some other procedure procedure p2

package  p42
I have declared procdure p1
procedure p2 do i need to declare in package p42?

Could you pls suggest me.
If Possible provide a sample example.


create or replace
PACKAGE infoadm.p42 AS
  procedure p1;
END p42;
/


create or replace package body infoadm.p42 as


--procedure p2; --DO I NEED TO DECLARE OR NO NEED?

procedure p1 is
begin
  p2;
end;

procedure p2 is
begin
  null;
end;

end p42;
/

Open in new window

0
I'm a SQL DBA trying to do some querying on a ORACLE 12c Standard Edition machine. I have a field named DATETIME of the FLOAT datatype. Sample value is 41436.6514818518. How can I convert this to a value such as 2003-01-29 00:00:00.000? This way I can sort/filter by datetime. The 2003 value is an example of a datetime value in SQL Server. Not sure how to do this in Oracle. Need to be able to sort/filter by date and time. I tried multiple functions without luck.

Please let me know - thank you!
0
I found the below VBA code online that makes a connection to an Oracle database. How do I adjust the below code so no ODBC setup is required for it to work?


Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=Your Host Name)(PORT=Port Number))" & _
"(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"

con.Open (strCon)
End Sub
0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

DECLARE
DOS  KIMLIK.DOSYA_NO%TYPE:=&LALE;
ISIM  KIMLIK.ADI%TYPE;
ADRES_2  KIMLIK.EV_ADRESI%TYPE;
VERI_YOK  EXCEPTION;

BEGIN            
              IF DOS<0 THEN
              BEGIN
               DBMS_OUTPUT.PUT_LINE('EROR');
              RAISE VERI_YOK;
              ELSE
               
            

             FOR X IN (SELECT KIMLIK.ADI,KIMLIK.EV_ADRESI  FROM KIMLIK WHERE KIMLIK.DOSYA_NO=DOS) LOOP
             
             
             
             DBMS_OUTPUT.PUT_LINE('KISININ_ADI= '|| X.ADI ||' KISININ ADRESI= '|| X.EV_ADRESI );
             
             
             
             END LOOP;
               END IF;
EXCEPTION 
WHEN VERI_YOK THEN
DBMS_OUTPUT.PUT_LINE('DOSYTA_NO SIFIRDAN BUYUK GIR');
 WHEN no_data_found THEN 
 DBMS_OUTPUT.PUT_LINE('BOYLE BIR DOSYA_NO YOK');

END;

Open in new window

0
Trying to connect to an Oracle DB from Access 2013. I got the Oracle driver installed with help of a dba. I also created the System DSN and tested connection with success.

When I try to use the ODBC connection via Access to link/import tables or create a pass-through, I am getting the Reserved Error -7711. I have spent a few hours researching with no luck.

Would really appreciate any assistance to resolve this.

Below is my odbc dsn less connection string for reference.

strConnect = "ODBC;Driver={Oracle in OraClient12Home1_32bit};Dbq=servicename;UID=myuid;PWD=mypwd;"
0
Typically, we upgrade our application about every 16 months and reinstall at the customer site. This includes changes to the database schema, e.g. new tables, deletion of other tables, new columns, deletion of some column, modifying some data types, modifying some table data, etc.  We call this our database migration, and this is not associated with an Oracle upgrade or patching.

My question during our database migration, should we take advantage of what I would call some standard maintenance?  Perhaps this would be a good time to rebuild all indexes, updating statistics, etc. What else could we do at this customer scheduled down-time to make sure the database is running optimally and efficient?
0
oracle dbms_datapump export run correctly in an anonym bloc and fail in a store procedure

I have a database and i can execute dbms_datapump.open to create job and do an export of table directly in anonym mode in sqldevelopper,
but since i try to use the same code with a store procedure i got an error and there is no job start or exist.

You 'll find joinded the screen capture of th result

Execution of the Script in a anonym block and successfullyExecution in a Store Procedure Which Don't work
0
Q1:
In place of database (column or table or TDE) encryption, is encryption by application a good
substitute for it?

Q2:
What are some of the application encryption methods that are equally acceptable to
Transparent Data Encryption's  AES algorithm?

Q3:
Can suggest any tools/products for encryption by application?  We use Java & our
databases are Oracle, running on RHEL7 & Solaris 10?   I guess quite a fair bit of
key management is involved?
0
declare
v_match_count integer;
v_counter integer;

-- The owner of the tables to search through (case-sensitive)
v_owner varchar2(255) := 'HASTANE';
-- A string that is part of the data type(s) of the columns to search through (case-insensitive)
v_data_type varchar2(255) := 'NUMBER';
-- The string to be searched for (case-insensitive)
v_search_string varchar2(4000) := 1455671;

-- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a -----VARCHAR2 in PL/SQL
v_sql clob := '';
begin
for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in 
(select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%')
order by table_name) loop
v_counter := 0;
v_sql := '';


for cur_columns in (select column_name from all_tab_columns where 
owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
if v_counter > 0 then
v_sql := v_sql || ' or ';
end if;
v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
v_counter := v_counter + 1;
end loop;

v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;

execute immediate v_sql
into v_match_count;



if v_match_count > 0 then
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
end if;
end loop;

Open in new window

0
Hi Experts,

I want to get xml tag value from clob column.
this tag value contains alpha numeric example-abc1234

i have tried like this and it is not working for me

select (xmltype(requestxml).extract('/parameters/filters_multitext/text()')).getStringVal() as test from views

can some one suggest how to get the value

sample xml:
<?xml version="1.0" encoding="UTF-8"?><request><filters_multitext>abc - 098382</filters_multitext></request>

Thanks,
0
Hi Experts,

I want to execute couple of sql queries and fetch the count value.

SELECT COUNT(1) FROM MAP WHERE CODE = 'XX'
SELECT COUNT(1) FROM MAP WHERE CODE = 'YP'
SELECT COUNT(1) FROM FNAME WHERE COUNTRY = 'IN'; and  so on ----

I am expecting query in one column and count in one column

Example:
coulumn1                                                                                        Column2
SELECT COUNT(1) FROM MAP WHERE CODE = 'XX'                       304
0
Hi Guys,

I need to select  a count(*)  of records from a transactions table ( let's call it transaction_table)  by hour.  It sounds pretty simple
SELECT COUNT(*)  no_of_tr,
  TO_CHAR(dstamp,'DD/MM/YYYY HH24')  dstamp
FROM transaction_table GROUP TO_CHAR(dstamp,'DD/MM/YYYY HH24')

Open in new window


no_of_tr    dstamp
24              11/05/2019 00
1               11/05/2019 01
1               11/05/2019 02
118              11/05/2019 06
61               11/05/2019 07

What I want to achieve is to show  no_of_tr = 0 when there were no transactions in a given hour, It needs to look like this

no_of_tr    dstamp
24              11/05/2019 00
1               11/05/2019 01
1               11/05/2019 02
0               11/05/2019 03
0               11/05/2019 04
0               11/05/2019 05

118             11/05/2019 06
61              11/05/2019 07

Any idea how to do this ?
0
JavaScript Best Practices
LVL 13
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Hi Experts

Could you give me an overall strategie on how to mantain a centralized DB Server with its correspondnt databases receiving data from other decentrilized servers and databases from time to time?

The actualization between the decentrilized server and the centrilized server wouldn't need to be automatic, a D-1 actualization would be good (running when the resources comsumption is low).

The objective is to use Big Data analysis on the centralized DB.

Thanks in advance!
0
Hello expert friends!
this is Driving me crazy.  May be very simple for you guys/gals.
I can execute this command in toad (oracle) it gives me one row:
     SELECT *        
        FROM  DBA_USERS
       WHERE USERNAME = 'ALAD';--vUserId;

but when I use the above statement in pl sql it gives me compile error  table or view does not exist.
see my attached script please.
Thank youso much.
1MypckBody.sql
1Mypckspec.sql
0
Hello,

How do you express this expression as Microsoft SQL

 instr(',' || :3 || ',' , ',' ||  B.NC_CONTRACT_TYPE || ','))

This is being used in a prompt for Peoplesoft Query Manager.  Currently there is no way to express the prompt as a dynamic list.  This is the proposed workaround but I need this to be mssql.

Thanks,
Brock
0
hi,

 I am now testing any MariaDB dML and DDL operation when apply through maxscale, when failover during DDL and DML operation, the transaction will keep going even switch nodes, is it possible that connection do not drop during failover?

 what version of maxscale and mariadB can support this?

what parameter can make this happen in maxscale and mariaDB?

I just knew oracle TAF component can make it happen !

I want transcation level failover man and when failover, it is transparent to application.
0
I migrated data from oracle 11g to SQL server 2016 Standard using OPENQUERY. When complete, it says 129645385 rows affected but when I count the table, it shows only 637501. The statement I am using is below. Ran SQL profiler and provided no errors.


INSERT INTO AUDIT_TRAIL
SELECT * FROM OPENQUERY(QUANTUM, 'SELECT * FROM AUDIT_TRAIL')

Open in new window



Any ideas?
0
There's a regulator requirement to protect sensitive data-at-rest.  I'd like to comply with
minimal disruption/effort as our DB only  have a couple of columns containing Unique
Identification number (sort of social security #), contact person of client, Date of Birth.

We are running Oracle DB  (on Solaris & RHEL7) & don't plan to deploy DB Activity
Monitoring tools, DAM (like Imperva

Q1:
Are mobile tel#, name of companies (who are our clients) & their registered company
address considered PII??


Quoting the report:

"the attacker was able to view the full details of the medical records stored in the database, once he had gained access. This was so as there were no measures in place to secure the data-at-rest in the database.

Data-at-rest refers to information stored in databases in filesharing servers, in backup tapes etc, and generally includes any data that is not being transmitted.
(**We have secured fileshare servers & used TLS1.2 for data in motion).

a) Encrypting data-at-rest prevents unauthorised access by anyone who defeats normal system access controls.
    Encrypting all data-at-rest, where possible, to protect against both internal and external malicious actors.

b) Tokenisation also prevents unauthorised access to selected columns of data. Tokenisation can be used as an
    alternative to encryption on a column-by-column basis. Even if a database is compromised, tokenising PII
    (personally identifiable information, such as name & …
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.