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

Keep receiving two errors when attempting to compile a function in SQL Developer. Errors are attached. Any help will be appreciated.

Errors:
- PL/SQL: SQL Statement ignored
- PL/SQL: ORA-00933: SQL command not properly ended


create or replace FUNCTION BA_REC_EXCUSABLE_DELAY_V2(v_rc_number IN NUMBER, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)
RETURN NUMBER
IS v_days NUMBER(11,2);
BEGIN
-- THIS FUNCTION RETURNS THE NUMBER OF DAYS A RC SPENT IN QUARANTINE

select round(sum(case when time_in_quarantine.start_time>nvl(p_before,sysdate) then 0
               else case when time_in_quarantine.stop_time<=nvl(p_before,sysdate) then time_in_quarantine.stop_time-time_in_quarantine.start_time
               else nvl(p_before,sysdate)-time_in_quarantine.start_time end end))
into v_days

from(
               select RC_NUMBER,
                              case when rca.time_stamp>=nvl(v_wo_receiving_date,rca.time_stamp) then 
                                             case when rca.time_stamp<=nvl(v_wo_entry_date,rca.time_stamp) then rca.time_stamp else v_wo_entry_date end
                                             else case when v_wo_receiving_date<=nvl(v_wo_entry_date,rca.time_stamp) then v_wo_receiving_date else v_wo_entry_date end
                              end start_time,
                              (select case when nvl(min(stamptime),sysdate)>=nvl(v_wo_receiving_date,nvl(min(stamptime),sysdate)) then
                             

Open in new window

0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Hello Experts,

I need Regex function in Oracle PL/SQL to check for Decimal Number(with or without decimal points) and Date(MM/DD/YYYYY), so I can validate the Parameter values in the pkg.

Below I found for Number(having greater than 0)
SELECT 'Number' FROM DUAL
WHERE regexp_like('111111111111111111110','^[0-9]+$')
AND NOT regexp_like('11111111111111111111','^0+$');

Open in new window


Thanks,
ASPDEV
0
I am using Oracle and I need to create a temp table and declare it in my pl/sql script and fill the table of one column with "id".

CREATE TABLE #temp_paystubids (Paystubid NUMBER NOT NULL);

I need to be able to use the temp table later in my script.
0
I would like to select ALL the table names where employer_code exist and data_precision from all schemas  in a database.
0
hi expert
I wrote a query like this
select DRADI.ADI_SOYADI as namee,count(*) as count_x from protokol,dradi where PROTOKOL.DR_KODU=DRADI.DR_KODU group by DRADI.ADI_SOYADI  
the output of this query is as follows.

Şule ŞİMŞEK      5440
Check Up Polikliniği      847
Aslıhan Çakır      38
Furkan KARALOĞLU      4
Yıldız EKMEKÇİ      1267

I want to set the given names as columns.
I want to bring number values ​​under names.

the template I want is below

Şule ŞİMŞEK    Check Up Polikliniği   Aslıhan Çakır  Furkan KARALOĞLU   Yıldız EKMEKÇİ
5440                  847                                38                      4                                      1267

how can I do that.?
thanks
0
Experts,
My goal in the Query below is to return a default value when the subquery has no matching rows.
as-is, it works fine if there is a match, but returns nothing if there is no match

Select 
COALESCE(Cart_ID,'MyValue')
from (
  select Cart_ID from C_Cart_D 
  where Comp_code = 'T2'
  and ORD_NUM = 181985
  and INVT_LEV3 = '000554878547'
  )

Open in new window



How can I correct this ?
0
How to deactivate Oracle EBS "Internal Monitor" from the command line or sqlplus

I tried this but the "Internal Monitor" was still running. (even after restarting the apps)
CONCSUB apps/<apps passwd> SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FNDIMON DEACTIVATE FNDIM_SRVRNAME

Linux SRVRNAME 3.0.101-108.52-default #1 SMP Tue May 29 19:42:53 UTC 2018 (80e6815) x86_64 x86_64 x86_64 GNU/Linux

 Internal Monitor
0
hi expert
the following code gives an error
ORA-00984: column not allowed here
how can i fix?

declare
yazi varchar2(2000);
sayi number;
adxx varchar2(200);
begin
        for tablo_adi in (select table_name from user_tables where table_name not in ('112HASTANE_PERSONEL','112INI','112LOG','112SONLANMAMIS_VAKALAR' ) )loop 
           begin
                   yazi:='select  count(*) from '||tablo_adi.table_name;
                   adxx:=tablo_adi.table_name;     
                   execute immediate yazi into sayi;
                   
                 -- execute immediate ' insert into tablo_deneme(tablo_adi) values('||tablo_adi.table_name||','||sayi||')';
                 execute immediate ' insert into tablo_deneme(kayit_sayisi,tablo_adi) values('||sayi||','||adxx||')';--error line addx? ORA-00984 Error Message
                  dbms_output.put_line(tablo_adi.table_name||' = '||sayi);
                   
           end;
        end loop;
 
end;

Open in new window

0
hello expert
I want to save a long type value in another table.
But that's a mistake.
how to fix this error.

In the 'RAD_SONUC' table, 'RAPORX' has the value 'LONG' of the column.

I want to insert the values ​​of the 'raporx' column of the rad_sonuc table into the rad_tani table.

code is below

create or replace trigger raporxx
before  update  of RAPORX
   ON RAD_SONUC
FOR EACH ROW
declare
  VAR1 clob;
  VAR2 VARCHAR2(4000);
  var3 LONG;
begin
      var3:=:new.RAPORX;
      insert into rad_tani(istek_no,rowid1,yazi) values(:new.istek_no,:new.ROWID1,to_char(var3)); 

dbms_output.put_line('istek_no= '||:new.istek_no||' giris= '||VAR3);
end;

Open in new window

The error in the code is as follows.
ORA-04093: references to columns of type LONG are not allowed in triggers

Open in new window

how can i fix it.?
thanks
0
Recently upgraded to Oracle 12c 4 node RAC exadata DbaaS. We are facing issue with insert statement when pulling the data via DBlink, it is just hanging. There are other parallel sessions to insert into one single table that run @ the same time. All the sessions are complete and this one is still hanging. When we checked with source team, they confirmed there is no active session in their DB.

We see one Node is taking more load than the rest of the nodes.

 Any idea what we can do..
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

top depth  base depth  perforation    account Nmber                completion type         perforation density         changed date      source
1067.5          1071.5         JET                100010107207W600      PERFORATION          17                                      12-AUG-04            by phone
1074          1077                 JET                100010107207W600      PERFORATION          17                                      04-OCT-04            by phone
100.5          101.5                 JET                100010107217W400      PERFORATION          17                                      13-MAR-03            Website
416                   417                 JET                100010107217W400      PERFORATION          17                                      11-MAR-03            Called
417.5           418.5                 JET                100010107217W403      PERFORATION          17                                      13-MAR-03            Called
416                    417                 JET                100010107217W402      PERFORATION          17                                      11-MAR-03            Called
416                    417                 JET                100010107217W402      PERFORATION          17                                      11-MAR-03            Website
417.5            418.5         JET                100010107217W403      PERFORATION          17                                      13-MAR-03            Website
416                    417                 JET                100010107217W402      PERFORATION          17                                      11-MAR-03            Website
416                    417                 JET                100010107217W400      PERFORATION          17                                      11-MAR-03            Website
416                    417                 JET                …
0
I have 2 scripts one is the Master_UpgradeScript.sql and the other is the scriptFileToRun.sql that I need to run to alter several schemas and add indexes and constraints to them. I feel like I am missing this: DEFINE schema_name=&1; from the scriptFileToRun.sql file and I don't know who this ties into the Master_UpgradeScript.sql.....


Could someone please verify if I need this (DEFINE schema_name=&1;) or not and if so please explain what it is doing and where it comes from? And how does it get into the ALTER script that needs to run?
Master_UpgradeScript.sql
scriptFileToRun.sql
0
hi how do i add oracle sql data source when creating spring boot application my database in oracle database 12c
spring
0
Experts,
Below is a lengthy query with joins into multi tables.
select O.COMP_CODE, O.CUST_CODE, O.ORD_NUM, O.ORD_CUST_ORD_NUM, O.Ord_PO_Num, O.Ord_to_ship_date, O.Ord_to_Arr_date, O.CARR_CODE, O.Frt_term_code, O.Parcel_carr_acc_Num,
COALESCE(C.Carr_Add3,'X') S_CARR, COALESCE(C.Carr_Add4,'X') S_SVC,
CU.CUST_NAME, 
CASE WHEN O.CON_CODE = '/' THEN DM.DOC_MAN_NAME ELSE CN.CON_NAME END ST_NAME,
CASE WHEN O.CON_CODE = '/' THEN DM.DOC_MAN_ADD1 ELSE CN.CON_ADD1 END ST_ADD1,
CASE WHEN O.CON_CODE = '/' THEN DM.DOC_MAN_ADD2 ELSE CN.CON_ADD2 END ST_ADD2,
--CASE WHEN O.CON_CODE = '/' THEN DM.ZIP_CITY_DOC_MAN ELSE ZP.ZIP_CITY END ST_CITY,
--CASE WHEN O.CON_CODE = '/' THEN DM.STATE_CODE_DOC_MAN ELSE ZP.STATE_CODE END ST_PROV,
CASE WHEN O.CON_CODE = '/' THEN DM.ZIP_CODE_DOC_MAN ELSE CN.ZIP_CODE END ST_POSTAL,
--CASE WHEN O.CON_CODE = '/' THEN DECODE(ZP.COUNTRY_CODE, 'CAN', 'CA', 'USA', 'US', ZP.COUNTRY_CODE) ELSE CN.COUNTRY_CODE END ST_COUNTRY
D3.ORD_CON_CONTACT_NAME, COALESCE(D3.ORD_CON_TEL_NUM,'555-5555'), D3.ORD_CON_EMAIL_ADD, D3.ORD_PARCEL_RESIDENTIAL_FLAG, D3.ORD_PARCEL_SIGNATURE_REQ_TP, D3.ORD_PARCEL_SATURDAY,
CH.CART_ID, CH.Cart_Ucc128_num, SUBSTR(CH.CART_ID,-10,10) Cart_10, CH.Cart_Inx_num, Count(*) over () TotalCart, Coalesce(Decode(CH.Cart_Wgt,0,1,CH.Cart_Wgt),1),
DECODE(CH.Cart_Size_Code,'VAR','',CH.Cart_Size_Code)SIZE_CODE, 
CH.Cart_Size_Var_Len, CH.Cart_Size_Var_Wid, CH.Cart_Size_Var_Hgt,
(CH.Cart_Size_Var_Len*12)LEN_IN, (CH.Cart_Size_Var_Wid*12)WID_IN, 

Open in new window

0
I would like to update every unitcode which is not in the sk_units_temp_table but in those two tables
   unitboundary and unitheader showing  terminated = 'True'   and set the terminated to  'Flase'.

Here is my code below:
   
   SELECT *
             FROM   unitboundary
                    join unitheader USING (unitcode)
             WHERE  terminated = 'False'
               AND  unitcode IN (SELECT unit_code
                                 FROM   sk_units_temp_table)
                    AND province = 'SK'
                    AND ( unitcode, highdesc ) NOT IN (SELECT unit_code, highdesc
                                                       FROM sk_units_temp_table);
0
Never saw this syntax before, it was generated from Oracle Discoverer (v 11.1.1.3.0), but it won't run in SQL.

What is this syntax:
FROM Student_Vw S52_313113
                                {oj HIGH_SCHOOLS S52_118794 RIGHT OUTER JOIN Student_DW S52_170368 ON S52_170368.HS_ID = S52_118794.HS_ID}

Pseudo-query
SELECT ****
FROM Table A,
            Table B
WHERE A.id = B.student_id
    AND   A.id in (SELECT S52_313113.PRMI_ID
                          FROM Student_Vw S52_313113
                                {oj HIGH_SCHOOLS S52_118794 RIGHT OUTER JOIN Student_DW S52_170368 ON S52_170368.HS_ID = S52_118794.HS_ID}
                          WHERE ( ( S52_313113.ID = S52_170368.Student_ID ) )
                            AND ( S52_313113.ITL = 'N' )
                            AND ( S52_313113.CLASS BETWEEN 2019 AND 2023 )
                            AND ( S52_170368.STATUS_CODE NOT IN ('31','32','35','36') )
                            AND ( S52_313113.CODE NOT IN ('19','20','21','22','23','34') )
                            AND ( S52_313113.CODE NOT IN ('96','97','98','99') ) )
0
Using Oracle SQL Developer - I want to pull back only claims that meet a certain criteria, multiple lines on the same claim number where one line is SURGTG and the other SURGDAY with a copayment  > 0.  ignore everything else.  SURGTG and SURGDAY have to exist together on the same claim and only pull in those lines.

The code i have below pulls back single and multiple line items but dosnt focus on pulling back the above scenario only. See attachment for example. I only want to pull back what is highlighted in yellow, if a single line is SURGTG or SURGDAY ignore, If multiple claims are SURGTG or SURGDAY, ignore. I need to pull back only the claims that are SURGTG and SURGDAY together.

WHERE c.adjst_clm_id is not null and
              ST.NAME in ('Clean','Pending')                
               and C.SERVICE_START_DATE between TO_DATE('08/01/2018','MM/DD/YYYY') and TO_DATE('08/31/2019','MM/DD/YYYY')    
               and (cml.adj_table_name like 'SURGDAY%' or cml.adj_table_name like 'SURGTG%') and PX.COPAYMENT > 0

Open in new window

Scenario-example.xlsm
0
I am using PL/SQL (Oracle) and I have a case statement that is dependent upon 1 field, but it drives the results of 2 other fields and possibly 3 fields in another table.  Here is the pseudo code:

case
  when mbr10.ssn is not null then mbr10.ssn as SSN, mbr10.eff_date as Eff_Date, mbr10.end_date as End_Date
  when mbr10.ssn is null and mbr20.ssn is not null then mbr20.ssn as SSN, mbr20.eff_date as Eff_Date, mbr20.end_date as End_Date
end

Can this be done and if yes what is the syntax?

Thanks,
Scott
0
Hi Experts,

table1-->txns
table2-->Feed1
table3-->Feed2

select * from feed1;

FEEDID	DESCRIPTIONSHORT	TXN_AMOUNT		TXN_DATE				CREATED_DATE
1001	H000xxxxx	       2000				28-SEP-19				28-SEP-19
1002	DVP xxxxx	       2000				28-SEP-19				28-SEP-19
1003	successful	       2000				28-SEP-19				28-SEP-19
1004	failed	           2000				28-SEP-19				28-SEP-19

select * from feed2;

FEEDID	DESCRIPTIONSHORT	TXN_AMOUNT			TXN_DATE	CREATED_DATE
2001	INTRxxxxx				4000			28-SEP-19	28-SEP-19
2002	REDMxxxxx				5000			28-SEP-19	28-SEP-19
2003	successful				6000			28-SEP-19	28-SEP-19
2004	failed					2000			28-SEP-19	28-SEP-19


select * from txns:
TXNSEQ	TXNCODE	TRANSDATE	DESCRIPTIONSHORT	TRANSAMOUNT	PROCESSINGTIME
15460	8666	19-JUL-13	H000xxxxx09272019		3425.96		19-JUL-13
16260	8871	19-JUL-13	H000ZZxxxxx09272019		52105.53	19-JUL-13
64731	9111	21-NOV-16	REDMxxxxx09282019		40100000	21-NOV-16
89551	9771	21-NOV-16	EXWAZZZxxxxx09282019	30082.8		21-NOV-16


relation between 3 tables is DESCRIPTIONSHORT

Open in new window


main table-->txns
from this table based on input(TXNCODE) i have to fetch the data from other tables
query input will TXNCODE

Feed1 table data i have to fetch with -->First 16 charcters of txns.DESCRIPTIONSHORT
condition if the txncode IN ( 8666, 8228, 8881 ) then only i have to fetch the data  else all columns data from the feed1 will be empty
Feed2 table data i have to fetch with -->Last 16 charcters of txns.DESCRIPTIONSHORT
condition if the txncode IN ( 9111, 9089, 9087 )  then only i have to fetch the data  else all columns data from the feed1 will be empty
 
if the txn code choosen from any other code
let us 9999 i have chosen as transaction code then it should not fetch any of the data from Feed1 and Feed2
it should fetch only txns table data alone other table columns data should display as empty.
below are the columns need to fetch from all 3 tables:
SELECT txns.txncode,
       txns.descriptionshort,
         txns.CREDITAMOUNT,
         txns.PROCESSINGTIME,
         Feed1.TXN_AMOUNT,Feed1.TXN_DATE
         Feed2.TXN_AMOUNT,Feed2.TXN_DATE


please suggest how to acheive this.
0
OWASP: Forgery and Phishing
LVL 13
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

hi i what to disable the button when old_tii_code is =2  i have attach the code am in oracle 12c
disable.sql
0
Our team typically uses Perl in our Solaris Oracle database environment.  We need a Perl script, that will receive parameter, such as, “-keep 30, -keep 60, -keep 90”.

Script called (example):  CleanUpOraDbFiles -keep 60
 
Also, PERL script will move gzip up files older than 60 days, and move to a larger file share of the filesystems. Need good example what this script would look like.  Looking to archive-off ( listener.log, audit logs, alert logs, etc.. )
0
hello expert

I need to print the Icd value as the number of boys and girls.
E: meaning of the boy
K: meaning of the girl
I want to print these 'E' and 'K' values ​​in the columns.
I think the pivot is used for this operation.
but i couldn't :(

select 
   TANI.ICD,KIMLIK.CINS,count(*) as sayi
from protokol,tani,kimlik,fmcocuk where PROTOKOL.DOSYA_NO=KIMLIK.DOSYA_NO and 
                                                 PROTOKOL.DOSYA_NO=TANI.DOSYA_NO and
                                                 PROTOKOL.PROTOKOL_NO=TANI.PROTOKOL_NO and
                                                 PROTOKOL.BOLUM=TANI.BOLUM and
                                                 PROTOKOL.PROTOKOL_NO=FMCOCUK.PROTOKOL_NO AND
                                                 PROTOKOL.DOSYA_NO=FMCOCUK.DOSYA_NO and
                                                 PROTOKOL.HASTA_A_Y=1 and
                                                 PROTOKOL.BOLUM in ('120') 
group by
TANI.ICD,
KIMLIK.CINS
order by 
TANI.ICD ,
 KIMLIK.CINS
 asc
 ;

Open in new window


the result of the query is as follows
ICD                  CINS   COUNT(*)
-------------------- ---- ----------
W19                  E             3
W19                  K             4
Z00.0                E            15
Z00.0                K             7

Open in new window


but
the query result I want to have is below
ICD                   E          K
-------------------- ---- ----------
W19                  3             4
Z00.0                15            7

Open in new window

how can I solve this problem.
0
Hi Experts,

select TXNCODE,DESCRIPTIONSHORT,1st 16characters of DESCRIPTIONSHORT,last 16 characters of DESCRIPTIONSHORT   from TXNS
where TXNCODE in(?)

input for the query will be any of the below steps 1,2,3


1.
txn code will be the input for this query
if txn code choosen from these 3-->8666, 8228, 8881
i have to get 1st 16characters of DESCRIPTIONSHORT( last 16 characters of DESCRIPTIONSHORT  should be empty)

2.
if txn code choosen from these 3-->9111,9089,9087
i have to get last 16 characters of DESCRIPTIONSHORT (1st 16characters of DESCRIPTIONSHORT should be empty)

3.
if txn code choossen any other i mean not from the above txn codes
i have to display these two fields as empty-->1st 16characters ofDESCRIPTIONSHORT,last 16 characters of DESCRIPTIONSHORT

select TXNCODE,DESCRIPTIONSHORT,1st 16characters of DESCRIPTIONSHORT,last 16 characters of DESCRIPTIONSHORT   from TXNS
where TXNCODE in(any of the above)

please suggest how do i get the data.
0
I'm new to oracle , if one has  to save XML in Oracle datatype ( 11 or higher) , is there a XML datatype in oracle?
0
Hi Team,

I need to write  a query to fetch the top 3 departments in oracle 12c where there are maximum number of employees working . Below is the query i have written
Select
*
from
(
Select
department_name,cnt, dense_rank() over (order by cnt desc) rk
from
(
select d.department_name , count(*) cnt
from departments d left outer join employees e ON
(d.department_id=e.department_id)
group by d.department_name)
) where rk<=3;


My question is there a better way to write this query?
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.