Go Premium for a chance to win a PS4. Enter to Win

x

Oracle Database

78K

Solutions

25K

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

The attached snippet is from a BASH script where I use Oracle SQL*Plus Ver. 11.2 to extract the last 6 hours of values for the KEYs listed.  The variable $DATA_VALUES_CURRMM is the database table for the current month that I pass to sqlplus.  What I would like to do is put the KEYs in a .csv file and pass the file to sqlplus using a BASH script.  The sqlplus portion of the script is attached.  The Experts helped me with this script before.  Any other suggestions would be appreciated.
SQLSnippet.sql
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

hi i have upgrade report to 11gR2 from 6i i what to download my txt report in user worksatation i what a dialog where user can select a location to download report have anyone done that
0
I am getting an ORA-00984: column not allowed here error message?
I just have a table named "SIMPLE" with one 30 character column named "SM" can someone tell me why I am getting the error message when I run the code below?

declare
lvQuery varchar2(100);
lala varachar2(10);
begin
lala:='Smile';

lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  ('   || lala || ')' ;

END;
0
In Nov 4, I exported a full database from an old database (oracle 10gr2), and import the data file into a new database (oracle 12c). After a week, we found that some application still insert the data into the old database. So I give myself a task to find the missing data (about 159 records in a specific table )from the old database and put it into the new database.

First, I tried the COPY command:

COPY FROM someuser/oldpaswd@//192.168.1.46:1521/OLDORCL to someuser/newpaswd@//192.168.1.26:1521/NEWORCL
 CREATE plus_circular_profile_new USING (select * from plus_circular_profile where sys_date >= to_date('05-NOV-17','DD-MON-YY' ));

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

SQL> desc plus_circular_profile;
 
 ID_OF_ROW              NOT NULL RAW(16)         <------ this is the culpit
 ID_OF_CIRCULAR    NOT NULL NUMBER(18)
 ID_OF_CONSUMER NOT NULL NUMBER(18)
 FIRST_NAME            VARCHAR2(50)
 .........

Open in new window


Due to I have the raw type in the table, this is certainly not possible to do.

Then I turn around to use imp/exp utilites.
exp tables=plus_circular_profile query="""where sys_date > to_date('04-NOV-17','DD-MON-YY' )"""
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table          PLUS_CIRCULAR_PROFILE        159 rows exported

Open in new window


But I have trouble to import the data into the new table:

imp someuser/newpaswd fromuser=someuser touser=someuser file=EXPDAT.DMP rows=Y ignore=Y log=import111617.log

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_PK) violated
Column 1 EA6AEC75335D4B1F9DCECF852D2165ED
...........

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_UK) violated
Column 1 DDA0613DE4354DC5B03C4FCC47FF3726

Open in new window


For the primary key violation:
ID_OF_ROW                       NOT NULL RAW(16)         <------ this is the primary key

For the Unique Key violation:
ID_OF_CIRCULAR              NOT NULL NUMBER(18)  <------- this is the unique key

I know the RAW type record could be possibly duplicated from one physical server to another after the database migration.
For the duplicated ID_OF_CIRCULAR it is because the production of the past week had generated the same id number because it always find the currently biggest number in the table and increment by 1 when needed. so there must have some record in the new database have exactly same ID_OF_CIRCULAR.

My question is: How can I get these 159 records into the new database?

Thank you for any help.
0
Hi Experts,

I need help on update the existing sql based on below requirement.

I have a three tables

1) Shield
2) OL
3) Cross_Ref

I need to create the new table ' Result' by using above table data. Here i am sharing the table data, and the SQL i have created .
Basically i need help on update that sql to get final output showed in the excel file.

Please let me know if  any questions.
test_data.xlsx
0
hi,
What is normalization concept


if a table called Student as below with 4 columns and data as below

Student table

StudentName YearOfStudy Subject TeacherName
john                    10thGrade    Maths    Ashley

why it is better to move away Teacher information to separate table as below

Teacher Table

TeacherName TeachingSubject
Ashley                 Maths


what is various types of normalizations?
Please advise
0
Write an update statement to remove double-quotes and carriage-return, line-feeds out of a columns data.

--I thought this would work to remove the carriage-returns, line feeds but get a
--ora-01722:invalid number error where the datatype for column1 is varchar2(50).
Update mytable
set column1 = replace(column1, chr(13) + chr(10), '')

--tried for removing the double-quotes and did not remove them.
Update mytable
set column1 = replace(column1, '"', '')

Using Oracle 11g

Thanks
0
I am calling a stored from within another stored procedure. When I execute the calling stored procedure I get the error message:

Error PLS-00222: no function with name 'Generator' exists in this scope

I don't have a function. My stored procedure is named "Generator". Why does it think I am executing a function?


EXECUTE IMMEDIATE Generator ('tTable',inParam) INTO newVal;
0
Hi,
I have date_created datatype date showing in sql developer as 06-09-29 can I display it in yyyy-mm-dd format.

Thanks
0
I have a letter America
how may letter s of A we have in this
how to find it using a select statement
0
Free Tool: SSL Checker
LVL 11
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Suppose I am in Sql developer and open a new window to perform a sql query. How should I declare a variable? My first line of code below is causing the issue and I don't know why? All I need is to declare a variable on the fly, and then use it. Apparently the Declare statement is not liked by the compiler.


DECLARE MY_NUMBER(10);

SELECT COUNT(1) FROM THE_TABLE;
0
The following errors appear when running backups and Oracle instance must be restarted:

Wed Nov 15 03:08:01 Hora estándar romance 2017
Thread 1 advanced to log sequence 10606 (LGWR switch)
  Current log# 4 seq# 10606 mem# 0: K:\SORANA1\SORANA1\REDO04.LOG
Wed Nov 15 04:00:03 Hora estándar romance 2017
Errors in file k:\sorana1\admin\sorana1\bdump\sorana1_lgwr_25960.trc:
ORA-00345: redo log write error block 4295 count 1
ORA-00312: online log 4 thread 1: 'K:\SORANA1\SORANA1\REDO04.LOG'
ORA-27070: async read/write failed
OSD-04016: Error al poner en la cola una solicitud de E/S asíncrona.
O/S-Error: (OS 1784) El búfer del usuario no es válido para esta operación.

Wed Nov 15 04:00:03 Hora estándar romance 2017
Errors in file k:\sorana1\admin\sorana1\bdump\sorana1_lgwr_25960.trc:
ORA-00340: IO error processing online log 4 of thread 1
ORA-00345: redo log write error block 4295 count 1
ORA-00312: online log 4 thread 1: 'K:\SORANA1\SORANA1\REDO04.LOG'
ORA-27070: async read/write failed
OSD-04016: Error al poner en la cola una solicitud de E/S asíncrona.
O/S-Error: (OS 1784) El búfer del usuario no es válido para esta operación.

Wed Nov 15 04:00:03 Hora estándar romance 2017
LGWR: terminating instance due to error 340
Instance terminated by LGWR, pid = 25960
Wed Nov 15 06:44:02 Hora estándar romance 2017
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Wed Nov 15 06:44:02 Hora …
0
How can I store my count value when I execute the dynamic query at line 6 below?

1  queryString VARCHAR(200);
2  FINALVALUE int;
3  COUNTER int;

4   SELECT COUNT(1) INTO COUNTER FROM CLIENT -- gets the number of records -- Hurrah This works !!!!


5   queryString:='SELECT COUNT(1) INTO COUNTER FROM CLIENT';
6   FINALVALUE := execute immediate queryString;
0
Hi,
what is best way to find dups data looks like below:

id   cntry
123 ca
123 ca
123 us
456  ca
456 ca
657  mx
657  mx
657 us
657 us

i want results like
123 ca
123 us
657  mx
657 us
0
Hello experts,

Can you please suggest any good performance tuning tools for oracle database which will also help sql and plsql code tuning too.
0
I need to print a screen or oracle database 12c security settings.

Is there a tool or a screen that shows that? if not is there a view or table that can show the settings.
0
I want to convert the date to a year/month.  An example would be the date is 01/10/2017 and converted to 201701.

And so any date within January would convert to 201701.  Any date within February would be 201702 etc.

Can this be done and if yes, how?

Thanks, Scott
0
does it go for full table scan or what?
0
I have the following SQL which really well to pull the sum of the registrations by week

SELECT
 EVT_CFG_ITEM.DESCRIPTION,
  sum(EVT_REG_ITEM.QTY),
  cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-09-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-16-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 2'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-23-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 3'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-30-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 4'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-07-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 5'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-14-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 6'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-21-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 7'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   

Open in new window

0
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Hi,

We have one matrix report created using oracle Reports builder. We are trying to generate the report with data from Jan-Oct, we are getting error in application server as "Engine rwEng-0 crashed, job id: 28". The same report is working for Feb-Oct and also for oct month only.

Our application is working on Oracle Forms & Reports 11g. Your immediate response is highly appreciated.

Thanks,
Mary
0
Need to select the slip with the largest number when grouped by stock from objects below:

create table tab1(horse number, stock number);
insert into tab1 values(100,4);
insert into tab1 values(101,5);

create table tab2(stock number, slip number);
insert into tab2 values(4, 1);
insert into tab2 values(4, 5);
insert into tab2 values(4, 8);
insert into tab2 values(5, 1);
insert into tab2 values(5, 5);
insert into tab2 values(5, 6);
insert into tab2 values(5, 7);
commit;

Open in new window

desired results
horse  stock  slip
 100       4        8
 101       5        7
 
 select t1.horse, t2.stock, t2.slip,
   rank ()
                   over (partition by horse, slip
                         order by slip desc)
                       rnk
  from tab1 t1
  join tab2 t2
  on t1.stock = t2.stock
  where rnk = 1

Open in new window

returns error:  "rnk" invalid identifier....

all pointers & suggestions appreciated...
0
Hi,

I have a two tables and one reference table.
STABLE
OTABLE
REFERENCE

We need to create the sql that should create  a new table based on the above table.
in the given data
stable have 10 rows
otable have 10 rows
and matched 3 rows in reference table

in the final output table , I need the all the rows from the stable and otable columns along with one new column
will show like this
matched = Y  OR ' IN STABLE'  OR  'IN OTABLE'
0
I have some Sql server code, and I need it converted to Oracle

SET @tmp = (SELECT TOP 1 tmpColzer FROM tecoTable);

Can someone show me how this is done in Oracle?
0
I have created the stored procedure below. If I try to execute it, I am getting an insufficient priviledges error message.
Does anyone have any idea why?


CREATE OR REPLACE PROCEDURE SIMPLE
IS
 BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE ELIX (ID INT NULL);
  EXECUTE IMMEDIATE 'GRANT INSERT ON B1';
  EXECUTE IMMEDIATE 'INSERT INTO ELIX (ID) VALUES (1)';
END;

EXECUTE SIMPLE

Error starting at line : 3 in command -
EXECUTE SIMPLE
Error report -
ORA-01031: "insufficen
*Cause: An attempt was made to perform a database operation without the necessary privileges
0
Dear All,

Iam working on oracle form personalization.

It has a block RES_PRICING_MODIFIERS and it has max 1 or more rows. Some time no rows.

The block name is RES_PRICING_MODIFIERS and it has

list_line_id, list_name, discount_type
and so on till last records.

I have a requirement .

There is a button in the form. If i press the button .it has to loop through the block and get the value of the list_line_id field and store the values in a global variable

like g_temp := 1,2,3,...;

You help is highly appreciated .

Thanks
0

Oracle Database

78K

Solutions

25K

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.