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
Technology Partners: We Want Your Opinion!
Technology Partners: 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!

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
Informatica Client Installation issue on windows --- >

Registry Key : HKEY_LOCAL_MACHINE\SOFTWARE\Informatica 9.0.1\Client
Value Name : PC_CLIENT_HOME
Data : C:\Informatica_Client\clients\PowerCenterClient\client
Configuring files
=================
C:\Informatica_Client\clients\PowerCenterClient\client\custom-configurator\customwizard.bat
C:\Informatica_Client\clients\PowerCenterClient\client\custom-configurator\customwizard.properties
C:\Informatica_Client\clients\PowerCenterClient\CommandLineUtilities\MM\mmcmd\mmcmd.bat
C:\Informatica_Client\clients\PowerCenterClient\CommandLineUtilities\MM\mmBackupUtil\backupCmdLine.bat
Installing o2003pia.msi
=================================

Command Executed
----------------
"C:\Windows\SysWOW64\msiexec.exe" /i "C:\OBIEE_Software\Informatica_client\V26112-01_1of2\901HF2_Client_Installer_win32-x86_DOCS_KEY\901HF2\source\clients\PowerCenterClient\visio\Office2003PIA\o2003pia.msi" /qn

Output of execution
-------------------
Output :
C:\Users\Administrator\AppData\Local\Temp\17\I1498234176\Windows>"C:\Windows\SysWOW64\msiexec.exe" /i "C:\OBIEE_Software\Informatica_client\V26112-01_1of2\901HF2_Client_Installer_win32-x86_DOCS_KEY\901HF2\source\clients\PowerCenterClient\visio\Office2003PIA\o2003pia.msi" /qn


The system cannot be open the specified device or file specified


Create Shortcut:          C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Informatica 9.0.1\Client\PowerCenter Client\PowerCenter Mapping…
0
Hello Experts

This file chk_fs.log came to weigh almost 1GB, I see that only saves this information:

PL / SQL procedure successfully completed.


PL / SQL procedure successfully completed.

The problem is that I do not have disk space, what can I do?
chk_fs.log
0
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
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
Salesforce Made Easy to Use
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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
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
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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!

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

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.