Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

In my query, we are using the following code:
spool c:\Work\PS PR Register 2018.csv

Select /*csv*/prd.EMPLOYEE, etc...

The query is working fine, but I wanted to know if there was a way to omit the sql that always shows ups at the beginning of the code? Also, is there a way to remove the column headings as well?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 5
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Hi,

Is there a way to set up a variable that is stored and used again and again in the database (Oracle).  I want to set the financial Year to 2017.  It's used over and over again in stored procedures and views etc...  When it's changed, it should be changed in everything.

Like a global variable but where do you declare it etc...


Kind regards,
Caoimhe
0
HI,

we try to run Mcafee DB scanner to scan for Oracle DB security related issue.

and the permission for the account connect to the Oracle DB is control by this script:

-- necessary to be able to connect to the database
grant create session to &vausername;

-- necessary for DML triggers delay time
grant execute on dbms_lock to &vausername;

-- the following grants is needed for checks:
-- ORACONF353, ORACONF347
create or replace view sys.x_$ksppi as select * from sys.x$ksppi;
create or replace view sys.x_$ksppcv as select * from sys.x$ksppcv;

grant select on sys.x_$ksppi to &vausername;
grant select on sys.x_$ksppcv to &vausername;

-- grant the privileges to be able to read database link passwords
-- and old database passwords stored in user_history$ (optional)
grant select on sys.link$ to &vausername;
grant select on sys.user_history$ to &vausername;
-- required for several CIS benchmark checks
grant execute on sys.dbms_crypto to &vausername;

Open in new window


do you think the above permission allow writing anything to the Oracle DB?
0
This is related to earlier question on "How-to-get-output-displaying-as-rows-to-columns".

The query below is using pivot function to display out columns and performing a SUM on interval data.

The pivot function works alright, but the SUMing appears to not work as the values are not adding up correctly.

Query:
WITH opdays as
       ( --
         select opday as opdaystart,                           -- opday is derived from the inner Select query
                (opday + 1) - (1 / 86400) as opdaystop,       -- opdaystop is derived by adding a day and subtract a second from that day
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('1/1/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('1/3/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 

Open in new window

0
Hello Experts.
Here's what I would like.   It's probably possible, but you guys can show me.
I have one small table called Food.
It has two columns  food_type and item.
Here are sample entries:

      Food table
Food_type,      item
=========      ====
fruit                   apple
fruit                   mango
fruit                   cherry
grain                  rice
grain                 wheat
grain                  barley

I would like to display my items as follows:

Food_type,     items
=========    =====
fruit                 apple, mango, cherry
grain                rice, wheat, barely

Is it possible to show result like that?  If so
can you please help me to write a script  to display such result.
Thanks a bunch.
Al
0
Need to expand the tablespace or delete contents from CCTBLS(screenshot attached). Help wil be greatly appreciated.
Capture.PNG
0
I'm getting the following error ... thoughts

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number  
Select distinct TO_CHAR(coalesce(C_DECISION_LETTER_MAILED_DT,C_DECISION_LETTER_MAILED_DT),'MM-DD-YYYY')as "Decision Lt Mail Dt" from T_MAS_APL
0
Hi Expert,

I have live transaction table called t1,t2,t3,t4 which contains data from last 2 years.

my requrement is i want to delete the data which is older than 14 days

and want to backup the deleted data into bkp_t1, bkp_t2, bkp_t3, bkp_t4.

please guide me how can i perform these action in a plsql procedure .

thanks in advance .
0
Virtual desktop has the basic Oracle 11g client installed and needs to upgrade to the full client for application/web server oracle client installs.  I have both installations in batch files.  To install the full client version, does the original(basic client) version need to be uninstalled first?

The user needs expdp, impdp, sql loader, etc.  It is a virtual desktop running Windows 10.   The Oracle client does show the net manager, config assistant, and wallet manager.  TOAD is heavily used for database admin, etc.

What steps should be taken to most cleanly install the full client on this win 10 virtual machine that has the basic client installed?

All pointers & suggestions appreciated.
0
With
         
    if REGEXP_LIKE(newChar, '^[0-9]+$') then      
        curChar := 'Y';      
    else      
        curChar := 'N';      
    end if;
      
    return newChar || '=' || curChar;      

Open in new window

Result is
 14=N

Open in new window

Why??
0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

There are a few custom object types in our database.  This is a new area for me.
Is there anything a DBA should be aware of when these are trickled throughout the database?

Thanks for feedback! Theresa
0
I have the  code which produces the output format below (Output A).  

Looking for suggestions on how to modify the SQL below to produce the desired output B ..

Note: The columns INTV1 = First interval_number  , INTV2 = 2nd interval number.. et al.  The values below INTV1 is the interval_value column for 1st interval.

 WITH opdays as
       ( --
         select opday as opdaystart,                           -- opday is derived from the inner Select query
                (opday + 1) - (1 / 86400) as opdaystop,       -- opdaystop is derived by adding a day and subtract a second from that day
                trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
     

Open in new window

0
 if :freight_estimates.reg_id_to is null and :freight_estimates.dsp_region_to is not null  THEN
	       select count(*) into v_count 
      	  from dss.regions
	       where upper(region) like upper(:freight_estimates.dsp_region_to)||'%'
	         and region_type = 'S';
	         if v_count = 0 or v_count > 1 then   
	            v_lov := show_lov('lov_region_to'); 
	         else    
	              select region into :freight_estimates.dsp_region_to
	         	      from dss.regions 
	         	     where region like :freight_estimates.dsp_region_to||'%'; 
	         end if;     
	              if not v_lov then
	    	         :freight_estimates.dsp_region_to := null;
	                 msg_alert('Please enter a valid region.','E',true);
	              end if;
   End if;

Open in new window


I am having this code in when-validate-item trigger. ON the property of the item LOV- validate from list is YES.
If I enter North for region from it works fine. Since I have only 1 that starts with N, puts the value North America in the field. If there are many then it pulls the LOV list. This is working fine when I am inserting

If I want to query, I do F7 (enter query) and when i type Nor or anything with N cursor moves to the next field. Does not validate or puts the value North America. What am I missing here? What additional code is needed to achieve this functionality.
reg_id_from (id) is database column. Lov is on a non database field dsp_region_from
0
on running the below query, i am getting the error ORA-00933: SQL command not properly ended

select to_char(cast(A.xx as date),'dd/mm/yyyy')as clnt_date_of_birth from t2 A join
t2 B on A.clnt_id=B.exrf_table_id
where B.column='1004620399';
0
In my sql query I am pulling all the email address from my company; example john.smith@abc.com
We are changing our domain to john.smith@hospital.org

Want to know if there is a way for me to search for the string @abc.com and replace it with @hospital.org in my query?
0
example input :
col1 col2 col3 col4 etc..
A        null    3   null
A         5        null  4
A        6          3      2
B         2         4     1

I should expect output just with record "B". group by  A should be there only if col3 has no NULLS
0
The query gets ESIID interval data from lschannel cut datasource and has gives out kilo watt hour daily totals by esiid.  I am wanting to know if a single mega watt hour total for all ESIIDs can be achieved, using the below query?

The value for Mega Watt hour total = kilo watt hour total / 1000.

What I have in mind is to add a SUM(V.interval_value/1000) in the outer select after daily kwh totals are computed and call it MWH total?  Would that be correct assumption?

   WITH opdays as
       ( --
         select opday as opdaystart,                           -- opday is derived from the inner Select query
                (opday + 1) - (1 / 86400) as opdaystop,       -- opdaystop is derived by adding a day and subtract a second from that day
                gv_firstDaystarttime as FirstDayStarttime,    -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
                gv_firstDaystoptime as FirstDayStoptime,      -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
                gv_lastdaystarttime as LastDayStarttime,      -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
                gv_lastdaystoptime as LastDayStoptime         -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
         from (--  
                 select gv_firstdaystarttime + (LEVEL - 1)

Open in new window

0
I create below statement in a SQL Stored Procedure in order to extract data from Oracle tables using open query and insert into another SQL table.

SET @MyString = 'SELECT  SUBSTR(GPCOMP1.GPRECL.CUSTNO,1,15) as CUSTNO,  GPCOMP1.GPRECL.AMOUNT, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.TRANTYPE, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD4,1,10) as FLEXFIELD4,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD2,1,5) as FLEXFIELD2, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD14,1,35) as FLEXFIELD14, GPCOMP1.GPRECL.RCLDATE, GPCOMP1.GPRECL.PROMISED,
            SUBSTR(GPCOMP1.GPRECL.INVNO,1,10) as INVNO, SUBSTR(GPCOMP1.GPRECL.INVREFNO,1,25) as INVREFNO, GPCOMP1.GPRECL.TRANORIG, GPCOMP1.GPRECL.FLEXNUM3, SUBSTR(GPCOMP1.GPRECL.TRANCURR,1,5) as TRANCURR,
            SUBSTR(GPCOMP1.GPRECL.INVPONUM,1,15) as INVPONUM, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD1,1,10) as FLEXFIELD1, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD9,1,25) as FLEXFIELD9, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD10,1,25) as FLEXFIELD10,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD11,1,25) as FLEXFIELD11, SUBSTR(GPCOMP1.GPCUST.COLLECTOR,1,10) as COLLECTOR, SUBSTR(GPCOMP1.GPCOLL.COLLNAME,1,30) as COLLNAME, SUBSTR(GPCOMP1.GPCOLL.TEAM,1,10) as TEAM,
            SUBSTR(GPCOMP1.GPRECL.CUSTNO, 2, 12) AS CUSTNO13, SUBSTR(GPCOMP1.GPRECL.CUSTNO, 2, 6) AS CUSTNO6, SUBSTR(GPCOMP1.GPCUST.COMPANY,1,40) as COMPANY, sysdate as processdate,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD5,1,10) as FLEXFIELD5, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD6,1,35) as FLEXFIELD6, …
0
database:  Oracle 12c

Hi I need to create pl/sql to drop and create table, so that I don't have to do it manually each time.
Here are the scripts.
can I combine somehow in pl/sql.  It will me lot of time to run it manually each time!
Thanks.
--------------
DROP TABLE My_schema.TEMP_Report_1 CASCADE CONSTRAINTS;
 
CREATE TABLE My_schema.TEMP_Report_1
(
  POLICY_NUMBER                   VARCHAR2(9 BYTE),
  SEQUENCE_NUMBER                 NUMBER(5)     NOT NULL,
  EFFECTIVE_DATE                  DATE          NOT NULL,
  COMPANYID                       VARCHAR2(1 BYTE),
  BRANCHID                        VARCHAR2(1 BYTE),
  NAME                            VARCHAR2(80 BYTE),
  FULL_TERM_PREM                  NUMBER(12,2),
  TXN_DATE                        DATE,
  Report_1                      NUMBER(12,2)
)
TABLESPACE SIMLOOK
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          520K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


GRANT DELETE, INSERT, SELECT, UPDATE ON My_schema.TEMP_Report_1 TO BA_SPECIAL_UPD;

GRANT SELECT ON My_schema.TEMP_Report_1 TO …
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

function CF_AMOUNTFormula return Number is
CURSOR C1 IS
SELECT TERM_SHORT_NAME
FROM OM_TERM
WHERE TERM_CODE=:JEH_TERM_CODE;            

begin
      IF C1%ISOPEN THEN
            CLOSE C1;
      END IF;
      OPEN C1;
      FETCH C1 INTO :CP_TERM_NAME;
      CLOSE C1;
            
 IF  :JEH_RETENTION_PERC >0 THEN
 :CP_RETENTION_AMT :=NVL((:JEH_RETENTION_PERC/100) * :CS_GROSS_FC_VAL,0);
 END IF;
   :CP_GROSS_AMT := :CS_GROSS_FC_VAL + NVL(:CS_EXP_SUM,0);
 -- :CP_NET_AMOUNT := :CS_GROSS_FC_VAL + NVL(:CP_RETENTION_AMT,0) + NVL(:CS_EXP_SUM,0);
    :CP_NET_AMOUNT := :CS_TOTAL -  (NVL(:JEH_DISC_VAL,0)+(:CF_TAX));
  RETURN(1);
  EXCEPTION
        WHEN OTHERS THEN
        RETURN(0);
        
end;
0
Suppose I have a table with two columns named ID, and UserName. When the table is first created ID will have all zeroes in it.
Example:
0     Barry
0     Mary
0     Jane
0     Dan

How would I update this table so that is would add 1 to each value in the ID column starting from the first record in the table. So the end result would look like below.

1    Barry
2    Mary
3    Jane
4    Dan
0
Greetings, experts!

I was given a query that's running out of space in the TEMP tablespace. (see error-message below) As a quick solution to get the user back up and running, I simply added a TEMPFILE to the tablespace. (see alter statement below)  But, when I ran the query again, it still ran out of space. I can see from querying dba_temp_files that the tablespace is pretty big, and it does contain both datafiles, but I thought the MAXSIZE UNLIMITED clause would have solved my problem by not allowing the new datafile to run out of space.

Besides rewriting the query, what would you recommend?

Thanks!
DaveSlash

SQL Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

ALTER TABLESPACE temp
  ADD TEMPFILE '+DATAC1/cop3/tempfile/temp01.dbf' SIZE 10g
  AUTOEXTEND ON
  NEXT 1g
  MAXSIZE UNLIMITED;

Open in new window


SQL> SELECT substr(tablespace_name,1,4) as tbls,
  2         substr(file_name,1,40) as file_name,
  3         bytes/1000000000 as gigabytes
  4    FROM dba_temp_files
  5   WHERE tablespace_name = 'TEMP'
  6  ;

TBLS FILE_NAME                                 GIGABYTES
---- ---------------------------------------- ----------
TEMP +DATAC1/cop3/tempfile/temp.771.915115045 21.4748365
TEMP +DATAC1/cop3/tempfile/temp01.dbf          34.359722

Open in new window

0
As you can see form the attachment, my results are more than two decimal places for DAYS_SINCE_REVIEW. How can I cast this column two display only the first two decimals? ex. 30 not 30.79595864855

Current SQL:
select
WO_NUMBER
, CUSTOMER_PO
, CUSTOMER_NAME
, CUSTOMER_CODE
, CSM
, DEPT_NAME SHOP
, PN
, SERIAL_NUMBER
, DESCRIPTION
, ENTRY_DATE WO_CREATION
, STATUS WO_STATUS
, ENTRY_DATE + 5 as ESTIMATED_QUOTE_DATE
, LAST_QUOTE_SENT
, LAST_QUOTE_STATUS QUOTE_STATUS
, LAST_QUOTE_APPROVED
, LAST_QUOTE QUOTE_NUMBER
, QUOTED_AMOUNT
, SHIP_DATE as ESTIMATED_SHIP_DATE
, LAST_QUOTE_SENT - ENTRY_DATE AS DAYS_SINCE_REVIEW
FROM ba_view_wo_summary
where syscm_auto_key=1 and pbh_customer='N' and status not in ('CLOSED','CANCELLED','CLS INVOICE') and dept_name not like '%GSE%'
order by due_date asc

Open in new window



Thank you
Capture.PNG
0
I have a script and need to use bulk collect

DECLARE

   CURSOR c_progpcon 
   IS
      SELECT cust_id
            ,div_no
        FROM dss.program_processing_controls;

   CURSOR c_custord(in_orgrole_id_customer IN dss.orders.orgrole_id_customer%TYPE)
   IS
      SELECT id order_id
            ,order_type
            ,order_number
            ,customer_po
        FROM dss.orders
        WHERE order_type = 'CUST'
          AND orgrole_id_customer = in_orgrole_id_customer;

   CURSOR c_outbound(in_order_id IN dss.orders.id%TYPE)
   IS
      SELECT ship.id ship_id
            ,ship.shipper_no
            ,shptrk.id shptrk_id
            ,shptrk.waybill
            ,shptrk.estimated_freight
            ,shptrk.actual_freight
            ,shptrk.dt_created
        FROM dss.shipments ship
            ,dss.shipment_trackings shptrk
        WHERE ship.order_id = in_order_id
          AND shptrk.ship_id = ship.id;

   v_cust_processed NUMBER := 0;
   v_custord_processed NUMBER := 0;
   v_orgrole_id_customer dss.org_roles.id%TYPE;
   v_estimated_freight_custord adwaram.order_freight.estimated_freight%TYPE;
   v_actual_freight_custord adwaram.order_freight.actual_freight%TYPE;
   v_calc_freight number:=0;
   v_method varchar2(4000);

BEGIN

   FOR c_progpcon_rec IN c_progpcon
   LOOP

      v_cust_processed := v_cust_processed + 1;

      SELECT orgrole_id
        INTO v_orgrole_id_customer
        FROM dss.customers
       WHERE id = c_progpcon_rec.cust_id;

      FOR 

Open in new window

0
I have a column containing data as below

data
aaaaa.2
bbbbb.4/5
aaaaa.4/6, ccccc.6/10
aaaaa.1/2/10, bbbbb.7

I need to parse the data into separate columns per below

data1         data2        data3        data4      
aaaaa.2
bbbbb.4    bbbbb.5
aaaaa.4     aaaaa.6     ccccc.6       ccccc.10
aaaaa.1     aaaaa.2     aaaaa.10   bbbbb.7

Can anyone help?
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.