Oracle Database

78K

Solutions

104

Articles & Videos

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 all, I have some problem in execute immediate insert statement exception part..

--I have a table query_tb that contains two columns (DEPT and SOURCE_VALUE)

--The column contains data in below

CLERK                            select a.empno,a.ename,a.job,a.mgr,a.hiredate,b.deptno,b.dname,b.loc

                                       from emp a,dept b where a.deptno=b.deptno and a.empno= '#V_GCIF#'

SALESMAN                    select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept

                                       d where e.deptno=d.deptno and e.empno= '#V_GCIF#'

MANAGER                    select a.empno,a.ename,a.job,b.deptno,b.dname,b.loc from employee

                                       a,department b where a.deptno=b.deptno and a.empno= '#V_GCIF#'

ADMIN                           select a.empno,a.ename,a.job,b.deptno,b.dname,b.loc from employee

                                       a,department b where a.deptno=b.deptno and a.empno= '#V_GCIF#'


--if i pass the correct empno which is keep on the emp table it runs fine but if i pass the inccorrect empno(nodata) then exception part not working... can u pls try to solve and update me soon....


create or replace PROCEDURE test_emp_sp( p_id IN VARCHAR2) AS CURSOR rec IS SELECT dept, source_value FROM query_tb; v_query VARCHAR2(1000);

BEGIN

FOR rec IN (SELECT dept,source_value FROM query_tb ) LOOP IF rec.dept='CLERK' THEN v_query …
0
MS Dynamics Made Instantly Simpler
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

I need to create a primary key (number type) for a table that previously did not have one and then number the fields starting at 1.  Can somebody give me the ALTER STATEMENT sql code to to this?
0
Good Afternoon, I would like to get some assistance with something I'm trying to do. I have a query that queries a table and gets various project information from that table, and it works fine. I have another table that has many comments about each project, so I have a one to many situation. My problem is that all UI want to retrieve from that second table is the last comment entered. I store the date of the last comment so I thought I would be able to do some sort of retrieval with a max date but not sure how to go about it. Ultimately I'm building a spreadsheet so the query is being built to populate the spreadsheet. Everything works fine till I had the 'proj_reason_closed' field. That the field that has many entries for each project.

SELECT DISTINCT proj_no,
spl,
capces_location,
capces_state,
capces_description,
maxdate,
proj_closed_reason,
ob_date_sort

FROM  (SELECT DISTINCT

       current_bulk_filter.proj_no,
       current_bulk_filter.capces_location,
       current_bulk_filter.capces_description,
       current_bulk_filter.capces_state,
       current_bulk_filter.spl,
       capces_support_data.ob_date_sort,
       spend_plan_history.proj_closed_reason,
       x2.maxdate

      FROM   mca.current_bulk_filter

left join mca.spend_plan_history
        ON ( current_bulk_filter.proj_no = spend_plan_history.proj_no and
             )

WHERE ......
0
Good Morning Experts,

I'm looking to merge 2 Oracle queries.  I'm want to take the 1st query and use it's results for the where clause in the 2nd query.  I've got the quries below.  Any thoughts?  This should be doable right?

select * from suserflds susr, sample s, billing b where s.sampno = b.sampno and susr.sampno = s.sampno and s.sstatus = 'V' and s.invdate  to_date('12-30-1899','mm-dd-yyyy') and susr.comment is null;

--Changes the invoice date per the billing table
update sample set invdate = (Select max(invdate) from billing where sampno = sampno) where sampno in (' THIS WHERE THE RESULTS OF THE 1st query WILL GO');
0
I need to install the oracle.web.dll file into the assembly GAC_64 folder because my website will access Oracle.

I am looking for expert help from someone who might have installed it in the past in their environment.
I need to know where to get the 64 bit version of oracle.web.dll and also how to install it? Since the installation appears to be into a GAC folder, then I need to make sure the installation is performed properly.
0
HI Experts,

select configid, filtersql from test.export_table where productid='SM';


this query will return 100 records

filtersql(type is varchar2) will have a query like
"from test where dept_id=100"  [this is not static value will be changed,some of the queries will have additional table as well]

i have to execute this query by appending -->select count(*)

select count(*) from test where dept_id=100;

i have to execute all 100 records by appending select count(*)

some of the queries(filtersql) having syntax issue so those
queries i need to find out configid

procedure should return configid for queries having syntax issues.

can some one help me how to do i write procedure for this scenario.
0
Hello experts,


I have a table with id, k, v (id is is, k is key, and v is value)

like :

id           k            v                        <--- columns in a select * fron tempTable
------------------------
A            k1           1
A            k2           2
A            k3           3
B            k1           1
B            k2           2
B            k3           3

SO what I want to do is 

Return the data as a crosstable/PIVOR/something else:

id         k1       k2       k3
-------------------------------------     
A          1         2       3
B          1         2       3

Open in new window


PIVOT not working on earlier versions of oracle database and I want a simple still reliable solution.
I really don't want to use ms access to make a crosstable for this :D
0
CREATE OR REPLACE TRIGGER xxads_update_trigger
   AFTER INSERT
   ON ap_invoices_all  
   referencing new as new old as old
   FOR EACH ROW
   WHEN (source= 'RECEIVABLES')

   begin
    :NEW.description := REMITTANCE_MESSAGE1 || ' ' || REMITTANCE_MESSAGE2 || ' ' || REMITTANCE_MESSAGE3 ;
   EXCEPTION WHEN OTHERS THEN raise_application_error ( - 20000 , 'do not update' ) ;
END;
0
Hello,
I have a query (below) where I am trying to only pull the first 5 characters (alpha and numeric) of the ARINVT.DESCRIP field.  The field is a varchar in the Oracle database.  Any help ?


Select V_ORD_DTL_AVALIABILITY.CUSTNO,
  V_ORD_DTL_AVALIABILITY.COMPANY,
  ARINVT.ITEMNO,
  AKA.CUST_ITEMNO,
  V_ORD_DTL_AVALIABILITY.ORDERNO,
  ORDERS.PONO,
  V_ORD_DTL_AVALIABILITY.SHIP_QUAN As "QTY TO SHIP",
  (V_ORD_DTL_AVALIABILITY.UNIT_PRICE * V_ORD_DTL_AVALIABILITY.SHIP_QUAN) As
  "Sales $",
  V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE,
  ARINVT.DESCRIP,
  ARINVT.CUSER8,
  V_ORD_DTL_AVALIABILITY.ONHAND
From ((((V_ORD_DTL_AVALIABILITY V_ORD_DTL_AVALIABILITY
  Left Outer Join ORDERS ORDERS On V_ORD_DTL_AVALIABILITY.ORDERS_ID = ORDERS.ID)
  Left Outer Join RELEASES RELEASES On V_ORD_DTL_AVALIABILITY.RELEASE_ID =
    RELEASES.ID)
  Left Outer Join EPLANT EPLANT On V_ORD_DTL_AVALIABILITY.EPLANT_ID = EPLANT.ID)
  Left Outer Join ARINVT ARINVT On V_ORD_DTL_AVALIABILITY.ARINVT_ID = ARINVT.ID)
  Left Outer Join AKA AKA On ARINVT.ID = AKA.ARINVT_ID
Where V_ORD_DTL_AVALIABILITY.CUSTNO Is Not Null And
  V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE <= To_Date(SysDate + 43) And
  V_ORD_DTL_AVALIABILITY.EPLANT_ID = 2 And RELEASES.ID <> 0

Order By V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE
0
set serveroutput on;
       --DECLARE err_status NUMERIC;
       DECLARE v_REFERENCE_CODE varchar2(30);
       DECLARE v_status_lkp varchar2(30);
       DECLARE v_OBSOLETE_FLAG varchar2(30);
       DECLARE cursor cur_invalid_objects is
               select a.REFERENCE_CODE,a.status_lkp,a.OBSOLETE_FLAG from BASE_SALES_ORDER a,BASE_SALES_ORDER_LINE b where
                    a.SALES_ORDER_ID =b.SALES_ORDER_ID
                    and a.REFERENCE_CODE ='10132834';
        --rec_columns cur_invalid_objects%ROWTYPE;

BEGIN
        dbms_output.enable(10000);
        open cur_invalid_objects;
        loop
                fetch cur_invalid_objects into v_REFERENCE_CODE,v_status_lkp,v_OBSOLETE_FLAG;
                EXIT WHEN cur_invalid_objects%NOTFOUND;
                dbms_output.put_line('ROWTYPE ' || v_REFERENCE_CODE || '  ' || 'SalesOrder Status' || v_status_lkp || '  ' || ' SalesOrder Flag status' || v_OBSOLETE_FLAG);
               -- v_statement := 'alter index '|| rec_columns.index_name ||' rebuild partition '|| rec_columns.partition_name ||' online;' ;
                                                                execute immediate 'v_statement';
        end loop;
        close cur_invalid_objects;
               
end;
/



I am getting error like below.Can some one rectify it asap.

Error report -
ORA-06550: line 2, column 8:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

   begin function package pragma…
0
Instantly Create Instructional Tutorials
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Hi,

I need your help in Oracle sql. I am converting a Sql Server Stored Procedure to Oracle and here is my problem.

I am using this sql and I want the date format to be in "mm-dd-yyyy" but the output is different. Can you please help what should I do to fix it.

select to_date('08-29-2016','mm-dd-yyyy') - 7 from dual;

and the output is "22-AUG-16"

what should I do so the output is "08-22-2016"

Thanks in advance to you all... :-)
0
I am within my company firewall, and need to determine if I can connect to an Oracle database on a server within the firewall. How can I test the connection from my laptop to the server which has the Oracle database on it (A.K.A the Host).

Is there something I can do from the command line, or an application I can download that will perform the test?
0
I have an Oracle database table which has info like servername, application version, database version, URL, etc as columns.

I want to generate reports out of this table such that for example, when I click on DB version, I need to see a pie chart with version split and so on, the report must be pulled from the latest values which are in database table

What is the best way to achieve this?

I would like to use free tools if possible
0
Hi
I have a data set which has missing time date time values and these values need filling (this has been answered in a previous question) - however, the real tricky part is to fill missing values against the filled date times (ramp up or down accordingly). I have attached a spreadsheet which makes my requirement clear. No PLSQL please.
I did have a question most recent that filled values from last known value - but you will see in the spreadsheet the logic to fill missing values is totally different and not even sure it can be done.ee.xlsx
Regards
0
I have a sql query that groups a series of records by the class of the item.  I want to see if I can further group to combine some of the classes. For instance, I am looking for a total for the LS, HS, and AD classes combined.   File attached as an example.
class-query.pdf
0
How can I accomplish below with SQl  Developer--for Oracle database 11 G


;With CTE As
(Select Cust_ID, name, Results, Date, Row_number() Over(PARTITION  By Cust_ID + Name + Results Order By Date Desc) As Cust
From Customer)
Delete From CTE
Where Cust >1
0
i have this error
ORA-01722: invalid number

ther parameter is string k_visita_id but data type is NUMBER

when K_visita_id have one value it's OK
when K_visita_id have too many values show error: invalid number
visita_id  is NUMBER
K_visita_id IS varchar

i atached error

procedure Update_Ingreso(
                  K_visita_id IN VARCHAR2,
                  K_estado IN  VARCHAR2,
.
.
.
      UPDATE PORT_INGRESO
             SET estado = upper(K_estado),
                 observacion = upper(k_observacion),
                 MODIFIED_USER = upper(k_usumod),
                 entry_date  = sysdate(),
                 entry_hora_minuto = k_hora_minuto
      WHERE visita_id in (K_visita_id);
www.png
www.png
0
I have the following query

 SELECT hca_sold.account_number          sold_to_customer_number,
          hca_sold.account_name            sold_to_customer_name,
          rcta.trx_number,
          rcta.trx_date,
          rcta.interface_header_attribute1 order_number,
          rcta.interface_header_attribute2 order_type,
          rcta.purchase_order              customer_po,
          hca.account_number               ship_to_customer_number,
          hca.account_name                 ship_to_customer_name,
          hl.address1                      ship_to_address,
          hl.city,
          hl.state,
          hl.province,  /* Added as per TD# 1859774*/
          rtl.line_number,
          msi.segment1                     product_name,
          msi.description                  product_description,
          ROUND (
             ( (  1
                * CASE
                     WHEN rctta.TYPE = 'CM' THEN rtl.quantity_credited
                     ELSE rtl.quantity_invoiced
                  END)),
             0)
             line_quantity,
          DECODE (
             rtl.interface_line_attribute11,
             '0', ROUND (
                     ( ( (  apps.XXADS_COMMON_UTILS_INT.get_lb_conversion_rate (
                               muom.unit_of_measure,
                               msi.primary_unit_of_measure,
                               msi.inventory_item_id,
                               'Y',
                            …
0
Hi Experts,

Can some one explain the below trigger,
CREATE OR REPLACE TRIGGER "TR_PYTXN" AFTER
INSERT OR UPDATE ON PAY_TNX FOR EACH ROW

BEGIN
   IF (:new.STATUSCODE = 64)
  THEN
 IF inserting THEN
  TEST.pkg_inf_his.pr_history
      (
      p_CUSTOMERID_in=> :new.CUSTOMERID,
      p_TYPECODE_in=> :new.TYPECODE,
      p_STATUSCODE_in=> :new.STATUSCODE,
      p_BATCHREF_in=> :new.BATCHREF,
      p_CUSTREF_in=> :new.CUSTREF,
      p_PAYMTDATE_in=> :new.PAYMTDATE ,
      p_PROCESSDATE_in=> :new.PROCESSDATE ,
      p_CTRCD_in=> :new.CTRCD ,      
      p_ACCNO_in=> :new.ACCNO
      );
 END IF;

 if updating THEN

        IF    :new.CUSTOMERID   <>   :old.CUSTOMERID   or
              :new.TYPECODE   <>   :old.TYPECODE   or
              :new.STATUSCODE   <>   :old.STATUSCODE   or
              :new.BATCHREF   <>   :old.BATCHREF   or
              :new.CUSTREF   <>   :old.CUSTREF   or
              :new.PAYMTDATE    <>   :old.PAYMTDATE    or
              :new.PROCESSDATE    <>   :old.PROCESSDATE    or
              :new.CTRCD    <>   :old.CTRCD    or
              :new.ACCNO   <>   :old.ACCNO   or
              
        THEN
			TEST.pkg_inf_his.pr_history
             (
              p_CUSTOMERID_in                       => :new.CUSTOMERID,
              p_TYPECODE_in                           => :new.TYPECODE,
              p_STATUSCODE_in                         => :new.STATUSCODE,
              p_BATCHREF_in                           => :new.BATCHREF,
              

Open in new window

0
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Hii experts i have a query...

dense_rank() over (parttition by 1 group by colunm_name)   ---what dose this syntax means where exactly it's uses .

Thanks in Advance
0
I have a following select statement
SELECT         pv.vendor_id,
               pv.segment1 vendor_num,
               pv.vendor_name,
               pv.pay_group_lookup_code,
               MAX(DECODE(pvs.vendor_site_code,null,pm.payment_method_code,null)) Vendor_Default_Payment_Method,
               MAX(DECODE(SUBSTR(pvs.vendor_site_code,1,3),'EMP',pm.payment_method_code,null)) EMPL,
               MAX(DECODE(pvs.vendor_site_code,'TE',pm.payment_method_code,null)) TE
          FROM po_vendors              pv,
               iby_external_payees_all epa,
               iby_ext_party_pmt_mthds pm,
               po_vendor_sites_all     pvs
         WHERE     pv.party_id = epa.payee_party_id
               AND epa.ext_payee_id = pm.ext_pmt_party_id
               AND pm.primary_flag = 'Y'
               AND pm.payment_flow = 'DISBURSEMENTS'
               AND pvs.vendor_site_id (+)= epa.supplier_site_id
               --AND pv.segment1 = '56745'
               and vendor_type_lookup_code = 'EMPL'
               and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
      GROUP BY pv.vendor_id,
               pv.segment1 ,
               pv.vendor_name,
               pv.pay_group_lookup_code

how to use the the value &

    and pv.pay_group_lookup_code in ( 'T&EC', 'T&ED')
0
I have this following query which runs fine

SELECT UNIQUE pv.vendor_name,
              pv.vendor_id,
              pv.segment1,
              pv.party_id,
              ( select pvs.vendor_site_code from po_vendor_sites_all pvs
              where pvs.vendor_id=pvs.vendor_id
              and  pvs.vendor_site_id=epa.supplier_site_id
              ) vendor_site_code,
              pm.payment_method_code
              --epa.supplier_site_id
  FROM po_vendors              pv,
       iby_external_payees_all epa,
       iby_ext_party_pmt_mthds pm
 WHERE     pv.party_id = epa.payee_party_id
       AND epa.ext_payee_id = pm.ext_pmt_party_id
       AND pm.primary_flag = 'Y'
       AND pm.payment_flow = 'DISBURSEMENTS'
       AND pv.segment1 = '56745'

I need to convert the vendor_site_code into column and against that site code I want the payment method
0
Hi expert
I have a issue on validation of constraints and trigger..

Suppose a table contains both constraints and insert triggers. When we try to insert data into table, out of constraints and triggers
which will take precedence ??

Thanks a lot in Advance
0
Hi
I have a table as per the following .......... SELECT CPY, DATE_TIME, VALUE FROM MYTABLE
I only want to return values on the hour and half hour exactly. However, if there are no hourly or half hourly exact times then I want the nearest value.
Original data set .........
Cpy           Date_Time                           Value
A001        11/06/2017 16:00:00             25 -- on the hour  so ok
A001        11/06/2017 16:15:00             45  -- don't need this one
A001        11/06/2017 16:29:00             14 -- need this one as nearest to half hour as there is no exact  16:30:00
A001        11/06/2017 16:45:00             56  -- don't need this one
A001        11/06/2017 17:00:00              7  -- on the exact hour  so ok
A001        11/06/2017 17:30:00              8  -- on the exact half hour so ok
A001        11/06/2017 17:48:00              4  -- need this one as nearest to the hour    
A001        11/06/2017 18:01:00        etc        
,.................
So, this is what I would like to return ......
Cpy           Date_Time                           Value
A001        11/06/2017 16:00:00             25 -- on the hour  so ok
A001        11/06/2017 16:29:00             14 -- need this one as nearest to half hour as there is no exact  16:30:00
A001        11/06/2017 17:00:00              7  -- on the exact hour  so ok
A001        11/06/2017 17:30:00              8  -- on the exact half hour so ok
A001        11/06/2017 17:48:00              4  -- …
0
Hi
I had a similar question re my question below but I couldn't get it to work.
I have an output similar to below example and I want any gaps in datetime to be filled for every half hour - plus, I want the previous value carried forward grouped only by Company.  eg ..... Select Cpy, DateTime, Value from MyTable
Cpy         DateTime                               Value
A1           12/06/2017 07:00:00            100
A1           12/06/2017 08:00:00              45
A1           12/06/2017 09:00:00              64
B2           12/06/2017 09:30:00                0
B2           12/06/2017 10:30:00              14
B2           12/06/2017 11:30:00              18  
...........
Require Output
Cpy         DateTime                               Value
A1           12/06/2017 07:00:00            100
A1           12/06/2017 07:30:00            100 -- extra line
A1           12/06/2017 08:00:00              45
A1           12/06/2017 08:30:00             45 -- extra line
A1           12/06/2017 09:00:00              64
B2           12/06/2017 09:30:00                0
B2           12/06/2017 10:00:00                0 -- extra line
B2           12/06/2017 10:30:00              14
B2           12/06/2017 11:00:00              14 -- extra line
B2           12/06/2017 11:30:00              18
0

Oracle Database

78K

Solutions

104

Articles & Videos

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.