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

Hi Team,

Below is my query
select LISTAGG(name, chr(10)) WITHIN GROUP (ORDER BY name),ORGANIZER_EMAIL from community_call_details where RECORDING_LINK is null and trunc(sysdate )  > call_date + 2
group by ORGANIZER_EMAIL

This retrieves results like below. I have ignored the other column just posting the results of first column

Cloud Accelerate Community Call Competitive Update for PaaS for SaaS Content & Experience Cloud (CEC) 17.3.5 & 17.4.1 Proficiency Webcast Graph Enthusiast Community Call NATD Cloud Community Call NATD Cloud Community Call NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD iPaaS Office Hours Call OOW 2017 Announcements to Accelerate Sales & Create New Opportunities Office Hours - Chatbots Personal Assistants - Cutting Edge Content Delivery w/ CEC Oracle Management Cloud - OOW 2017 Key Insights & Highlights PaaS OOW highlights from AppDev, Integration, and much more Preview of Oracle Autonomous Database SE Community Call for Architects SE Community Call for Architects Town Hall for Oracle Detroit and Ann Arbor Employees teast teast

But I want the results to be numbered. Is this possible?

like below

1.Cloud Accelerate Community Call Competitive Update for PaaS for SaaS Content & Experience Cloud (CEC) 17.3.5 & 17.4.1 2.Proficiency Webcast Graph …
0
Learn Veeam advantages over legacy backup
LVL 1
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

I have a very large Oracle Stored procedure. I wold like to be able to test it in sections. Is there a way deliberately exit an Oracle stored procedure and avoiding any type of rollback? So for example if I just want to test the first 1/3 of the stored procedure, and then exit the stored procedure committing any changes, is there a way to do that? If so, how do I do it?
0
Hi Experts,

I have below data in CLOB column, and I want to get xyz.com from below data. DOMAIN NAME LIST will not change and it will be same all the time

{
    "OBJECT ID":"71864756",
    "OBJECT NAME":"MESSENGER",
    "PARENT ID":"71865529",
    "OBJECT STATUS":"O",
    "WHITE LISTED":"N",
    "LAST UPLOAD":"",
    "PARENT CLASS":"ORDER",
    "IS A LINK OBJECT IND":"N",
    "AOL IM FEDERATION":"false",
    "ATTRIBUTE 1":"0",
    "ATTRIBUTE 2":"0",
    "ATTRIBUTE 3":"0",
    "ATTRIBUTE 4":"0",
    "ATTRIBUTE 5":"0",
    "CLASSIC CENTER INTEGRATION URL":"",
    "COMMENTS":"",
    "DISPLAY NAME":"",
    "DOMAIN NAME LIST":"xyz.com",
    "ICON NAME":"SERVICE_16.PNG",
    "IM LICENSES":"500",
}

Please help me by using dbms_lob.substr.....
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
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
I have a letter America
how may letter s of A we have in this
how to find it using a select statement
0
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
Windows Server 2016: All you need to know
LVL 1
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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
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
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
Need help with the correct syntax to use a named subquery into another query.

AS VSI  
  SELECT VENDOR_SITE_ID
  FROM tblAll
WHERE creation_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -24)
    and STATE = 'IL' ;
 
  SELECT *
  FROM VSI
  --INNER JOIN A.VENDOR_ID as
  WHERE (A.VENDOR_NAME like '%DEPARTMENT%'
  or A.VENDOR_NAME like '%ILLINOIS%');

what am I missing to call VSI as the datasource for 2nd query?

Thanks,

Karen
0
hi,

for Oracle, can a user or process within Oracle can run in unprivileged manner?
0
Hello,
I have a broad question so please bare with me on this. I posted a question regarding how to calculate a working days using a holidays table. So now I have a function working.

But my question, how to call/execute this function so that I can run it against a table. (Kinda similar to MS Access queries work).
I've been searching the web but I just don't how what's the best practice. Example:
I have a function that accepts 2 dates and returns an integer number. (e.g., function_name (a date, b date) and returns the a number.
My table columns: id, start_date, end_date.
My expected results if possible: id, start date, end_date, "the number returned from the function"

Thank you.
0
I am new to Perl (5.10.0) and creating a Perl script, connect to Oracle database (11.2.0.4) and output a csv file after reading columns from a table.

The below code selects data from an Oracle table inside a stored procedure/package.

Select all_cols  from
emp 
where emp_name = 'SMITH' and partition_key  = '20170101_20170331'

Open in new window



Here's my first time perl script that I put together. Not sure of what changes I need to make to the script to get my desired output in a csv format.

use strict;
use warnings;
use 5.27;

use DBI;
use Text::CSV;

my $dbi = DBI->connect( $dsn, $user, $pass );
Date_Init('TZ=US/Central','Internal=1');

my $sth = $dbi->prepare("SELECT columns from EMP table" );
$sth->execute;
WHILE (my $row = $sth->fetchrow_arrayref )
 {
    dump_table($row->[0] );
 }

sub dump_table 
  {
    my ( $table_name ) = 'Schema_name.table_name';

    print "Extracting $table_name...\n";

    my @column_names;
    my $column_name_sth = $dbi->prepare(
        "SELECT column_name FROM USER_TAB_COLUMNS"
        . "WHERE table_name = '$table_name' AND PARTITION_KEY = '20170101_20170331'"
    );
    $ column_name_sth->execute;
    while ( my $row = $column_name_sth-> fetchrow_arrayref ) {
        push @column_names, $row->[0];
    }

    my $csv = Text::CSV->new ( { binary => 1 } );
    $csv-> eol ("\n");

    Open ( my $fh, 
        ">: encoding(utf8)", 
        "$table_name.csv" 
    ) or die "$table_name.csv: $!”;

    $csv->print ($fh, \@column_names);

Open in new window

0
[Webinar] Cloud and Mobile-First Strategy
LVL 11
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Hello,
I am trying to spool a simple select statement to a text file.  The problem I am running into is that additional spaces get added  my data.  For eg

set pages 0;
set echo off;
set trim on;
set linesize 250;
set trims on;
set heading off;
set space 0;

select 'KIT-' || trim(field1) || '-' || trim(field2) || '-PACK' as data from table
where trim(field3)='xxx'
order by field1;

spool off;
quit

My data should be
KIT-data1-data2-PACK

However, I am getting
K I T - d a t a 1 - d a t a 2 - P A C K

Please help
0
I have written an SQL query (Oracle) to pull some data from our database to find the number of registrations created by item type but based on a range of dates (basically each week).    The dates are for sample because we haven't finished the first week.  

SELECT
 EVT_CFG_ITEM.DESCRIPTION,
  sum(EVT_REG_ITEM.QTY),
  cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '03-11-2017 00:00:00'  AND  '03-11-2017 23:59:00' THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '04-11-2017 00:00:00'  AND  '04-11-2017 23:59:00' THEN 'Week 2'
                    ELSE 'Unknown'
               END) as VARCHAR(100)) as rangedate   FROM
  EVT_REG_ITEM INNER JOIN EVT_CFG_ITEM ON (EVT_CFG_ITEM.EVENT_ID=EVT_REG_ITEM.EVENT_ID and EVT_CFG_ITEM.ITEM_ID=EVT_REG_ITEM.ITEM_ID)
  
WHERE
  (
   EVT_CFG_ITEM.EVENT_ID  IN  ( '1081'  )
   AND
   EVT_REG_ITEM.CANCEL_REASON  Is Null  
     )
GROUP BY
 EVT_CFG_ITEM.DESCRIPTION, 
   cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '03-11-2017 00:00:00'  AND  '03-11-2017 23:59:00' THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '04-11-2017 00:00:00'  AND  '04-11-2017 23:59:00' THEN 'Week 2'
                    ELSE 'Unknown'
               END) as VARCHAR(100)) 

Open in new window


I get the message "ORA-01830: date format picture ends before converting entire input string" even though it validates.   Ideas on a better way to do this or to fix my syntax?
0
Hi

I can't get my MTD column to work when using the Model functionality
I want the MTD to reset when any of the columns differ from columns 1 to 5
Any help appreciated - see below for my example code.

Thanks


SELECT *
FROM
(
SELECT 
'Corporate' AS COL1,'CS' AS COL2,'Global' AS COL3,'ZZZ' AS COL4,'USD' AS COL5,TO_DATE('5/28/2013' ,'mm/dd/YYYY')AS DATES,48927664 AS NAVS
FROM
DUAL
UNION
SELECT 
'Corporate','CS','Global','ZZZ' ,'USD' ,TO_DATE('7/31/2013' ,'mm/dd/YYYY'),49255158.97 
FROM
DUAL
UNION
SELECT 
'Corporate','CS' ,'Global','ZZZ','USD',TO_DATE('8/30/2013' ,'mm/dd/YYYY'),48145696.3 
FROM
DUAL
UNION
SELECT 
'Corporate','CS','Global','ZZZ','USD',TO_DATE('9/30/2013' ,'mm/dd/YYYY'),4915349
FROM
DUAL
-----------------------------------------------------------------------------------
UNION
SELECT 
'Test' AS COL1,'CS' AS COL2,'Global' AS COL3,'AA' AS COL4,'USD' AS COL5,TO_DATE('5/28/2013' ,'mm/dd/YYYY')AS DATES,48927664 AS NAVS
FROM
DUAL
UNION
SELECT 
'Test','CS','Global','AA' ,'USD' ,TO_DATE('7/31/2013' ,'mm/dd/YYYY'),49255158.97 
FROM
DUAL
UNION
SELECT 
'Test','CS' ,'Global','AA','USD',TO_DATE('8/30/2013' ,'mm/dd/YYYY'),48145696.3 
FROM
DUAL
UNION
SELECT 
'Test','CS','Global','AA','USD',TO_DATE('9/30/2013' ,'mm/dd/YYYY'),4915349
FROM
DUAL
)
MODEL
DIMENSION BY (COL1,COL2,COL3,COL4,COL5,DATES)
MEASURES (0 MTD, NAVS)
RULES SEQUENTIAL ORDER
( -- MTD = ThisMonth / PrevMonth - 1
-- If monthly performance is worse -100% (impossible) then assume bad data and return 

Open in new window

0
Hi,

How to update the table column which are matched with reference table columns.

I have four rows in main table. on 2 rows are matching and updated the specified columns. I need to update other two columns which are not matched with the reference table.
0
I want stripout numeric data from the column value

ex :: Column name = abc
abc having the values like
INV242436,
APPY246578,
PO141423,
FSO897623,
APAC126756
I want like this..
INV,
APPY,
PO,
FSO,
APAC
0
Is there any way to rollback a committed transaction in oracle 11g

I have made a delete from table in db and committed it, now I want to rollback the committed change. Is there any way to do it?

Please share a optimal solution .

thanks in Advance
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.