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 ,

SQL> /

COMP_ID    COMP_NAME                      VERSION                        STATUS
---------- ------------------------------ ------------------------------ --------------------------------------------
DV         Oracle Database Vault          11.2.0.3.0                     INVALID
OLS        Oracle Label Security          11.2.0.3.0                     VALID
XDB        Oracle XML Database            11.2.0.3.0                     VALID
OWM        Oracle Workspace Manager       11.2.0.3.0                     VALID
CATALOG    Oracle Database Catalog Views  11.2.0.3.0                     VALID
CATPROC    Oracle Database Packages and T 11.2.0.3.0                     VALID
           ypes


How do i make my vault component to valid state . I tried with multiple options .

OS - Solaris

Thanks,
VInay Palaparthy
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 All,

I wrote small query where select query will fetch the status of one ORder . If the order status is not 1 it will update the status to another status. If not found it will handle the exception.
I am made one script.In the comment can you help in recitfiying the script asap. Need this script in this format only.



DECLARE
       
        v_REFERENCE_CODE varchar2(30);
        v_status_lkp varchar2(30);
        cursor ObsoleteSalesOrder is
               select a.REFERENCE_CODE,a.status_lkp from BASE_SALES_ORDER a,BASE_SALES_ORDER_LINE b where
                    a.SALES_ORDER_ID =b.SALES_ORDER_ID
                    and a.REFERENCE_CODE ='10132834';
       
BEGIN
open ObsoleteSalesOrder;
loop
fetch ObsoleteSalesOrder into v_REFERENCE_CODE,v_status_lkp;
exit when ObsoleteSalesOrder%notfound;
loop
if v_status_lkp !=1

--Do Below update
update base_sales_order set obsolete_flag = 1 where reference_code IN ('10132834');
update base_sales_order_line set obsolete_flag = 1 where sales_order_id
IN (select sales_order_id from base_sales_order where reference_code IN ('10132834'));
commit;
DBMS_OUTPUT.PUT_LINE('SalesOrder MArked it obsolete');

else if v_status = 1
DBMS_OUTPUT.PUT_LINE('SalesOrder is in obsolete');

else
DBMS_OUTPUT.PUT_LINE ('No sales ORder Found');

--Exception handling
end loop;
--Exception handling
else
end loop;
--Exception Handling
close ObsoleteSalesOrder;            
end;
/
0
hi i got the following report where i add two column to summary the total of two column
1 after i edit the report am now geting more page than original report
2 the report is showing wrong total
beforeedit.jpg
afteredit.jpg
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
On Demand Webinar - Networking for the Cloud Era
LVL 8
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

pdate ra_customer_trx_all tr1
set interface_header_attribute3= (select unique asg.delivery_id
from ra_customer_trx_lines_all ral, wsh_delivery_details det, wsh_delivery_assignments asg
where ral.interface_line_context='ORDER ENTRY' and ral.interface_line_attribute2='US Delivery Order Type'
and ral.interface_line_attribute3='0' and ral.interface_line_attribute6 is not null
and det.source_line_id=ral.interface_line_attribute6
and det.delivery_detail_id=asg.delivery_detail_id
and tr1.customer_trx_id= ral.customer_trx_id
)
where customer_trx_id in(SELECT unique ral.customer_trx_id
  FROM ra_customer_trx_lines_all ral,
       wsh_delivery_details      det,
       wsh_delivery_assignments  asg
 WHERE     ral.interface_line_context = 'ORDER ENTRY'
       AND ral.interface_line_attribute2 = 'US Delivery Order Type'
       AND ral.interface_line_attribute3 = '0'
       AND ral.interface_line_attribute6 IS NOT NULL
       AND det.source_line_id = ral.interface_line_attribute6
       AND det.delivery_detail_id = asg.delivery_detail_id)
and org_id= 151;  


this update is not working
single row subquerry returns more than 1 row
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
Hello All,

I am in a situation to push database audit XML logs to syslog server. Audit logs are getting generated when Audit_Trail is set to OS. The same is not happening when Audit_Trail is set to XML,EXTENDED. Please advice on this.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/appldrch/DRCH_DB_audit_lo
                                                 g
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      LOCAL1
audit_trail                          string      XML, EXTENDED

[root@appsdr ~]# cat /etc/syslog.conf
local1.warning /var/log/audit.log


Thank You.

regards,
John Marshal.A
0
Hi,
I have .rep file in 10 G. is there is any converter available to convert it to .rdf.
Or any other option to open .rep file using oracle 10g report builder
0
Hi expert,
my Oracle version is 12.1 and during Oracle import datapump I get the folowing error:

. . imported "SYS"."NACL$_IMP"                        0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"RAD_OWN"."TAB_RAD" failed to create with error:
ORA-01882: timezone region not found
Failing sql is:
CREATE TABLE "RAD_OWN"."TAB_RAD" ("RADAC" NUMBER(*,0) NOT NULL ENABLE, "ACCTSESSI" VARCHAR2(64 BYTE) NOT NULL ENABLE, "ACCTUN" VARCHAR2(32 BYTE), "USERNAME" VARCHAR2(64 BYTE) NOT NULL ENABLE, "GROUPNAME" VARCHAR2(32 BYTE), "REALM" VARCHAR2(30 BYTE), "NASIPADDRESS" VARCHAR2(15 BYTE) NOT NULL ENABLE, "NASPORTID" VARCHAR2(15 BYTE),
ORA-39083: Object type TABLE:"PPB"."TRIP_ERR" failed to create with error:
ORA-00922: missing or invalid option
Failing sql is:
CREATE TABLE "PPB"."TRIP_ERR" ("ID" NUMBER(19,0) NOT NULL ENABLE, "VEHICLE_ID" NUMBER(10,0), "MESSAGE_TYPE" VARCHAR2(500 BYTE), "TRIPPOINT_START_TRIGGER" NUMBER(19,0), "TRIPPOINT_STOP_TRIGGER" NUMBER(19,0), "PACKET_ID" NUMBER(19,0), "INSERTTIMESTAMP" DATE NOT NULL ENABLE, "ANALYSYS_RESULT" VARCHAR2(500 BYTE), "ANALYSYS_T
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "PPUB"."TB_P"      64.19 GB 771212642 rows


How do I fix this error and be able to import the .dmp file …
0
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 am doing CSV upload on Asp.net and its taking more than 1 hour to upload, So I has set sessiontimeout as 2 hour, I have set time out in below places:
1) In web.config :<sessionState timeout="120"></sessionState>
2) In web.config: <httpRuntime maxRequestLength="1048576" executionTimeout="7200" />
3) In .aspx page :     <asp:ScriptManager ID="scrpt1" runat="server" AsyncPostBackTimeout="7200">
                            </asp:ScriptManager>
4) In IIS: Application Pool have sesstion timeout as 120min
5) In IIS: In Website Advance setting set session timeout as 120 min.

   Still my page get blank after 1 hour and If i try to refresh then it shows Session Timeout, However my CSV Upload continue to work on database and it completes all upload in database in 2hour but Page get expired. Please help asap.

   FYI I am using IIS 7 on Windows server 2008 machine.

Regards,
Sudhanshu
0
Microsoft provides a development SQL server that can run on your laptop or PC for developers to work against. I can put a Northwind database on such for example, and devise queries against it. I am looking for similar tools that I can do so for these database formats, if there is such an animal...

DB2
Oracle
SAP
AS400
0
Free Tool: Path Explorer
LVL 8
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have 2 tables
table 1

NAME        CHANGE_NAME
----------       -----------------
AVINASH    ABINASH
SAHU          SAHOO
ALIBHA       ALIVA
BAPUN       NULL
RAZA          RAJA
BAPU         BABU

table2

UPDATE_NAME(primary key)
----------------------
AVINASH
SAHU
ALIBHA
BAPUN
RAZA
BAPU

and i want to update the table2 and the o/p will be
table2

UPDATE_NAME
---------
ABINASH
SAHOO
ALIVA
BAPUN
RAJA
BABU
what will be the SQL query??

Thanks in Advance
0
Hi I have created .Net datatables and inserted around 10,000 rows into that, What I am looking is inserting that datatable values
in to Oracle Table directly. I am doing this to improve speed because If i insert data row by row from CSV to Oracle table then It takes 3hours to insert. Please help for correct solutions.
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
Hii expert...
I have 3 table having same no of column  and different data set including nulls .

I want to  copy all the 3 table data into a new table which also having same no of columns, please share some demo code .

Thanks in advance
0
I am able to find last refresh time from dba_mviews, but how or from where I will get the next refresh time info of each materialized views ?
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

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.