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

Hi

We have an apex application that was upgrade from R4.2 to R5.1 during oracle database upgrade from 12.1 to 12.2.

The application allows a user to update an organization or contact information and then send an email if anything changed to the admin email address showing the changes.

We found that the hidden item that should be set to TRUE when updates takes place does not SET so no email is triggered.
Was there any behavior code changes in these releases or do you see any issues in the code? The code in the after submission process is shown below.

DECLARE
l_body clob;
l_subject varchar2(300) ;

BEGIN


   IF :P430_ADR_CHANGED = 'TRUE' THEN
     l_subject :=  :P430_LIBCD || ' Address Change';
     l_body := l_body || 'ADDRESS CHANGE ' || utl_tcp.crlf || utl_tcp.crlf; 
     l_body := l_body || :P430_ADR_OLD_DATA ;
     l_body := l_body || utl_tcp.crlf || utl_tcp.crlf;
     l_body := l_body || '<<New Data>>'||utl_tcp.crlf ;
     l_body := l_body || 'Orgtype:' || :P430_ORGTYPE ||utl_tcp.crlf ;
     l_body := l_body || 'Libcd:'|| :P430_LIBCD || utl_tcp.crlf ;
    
   END IF;


   IF :P430_CONTACT_CHANGED = 'TRUE' THEN
     l_subject :=  :P430_LIBCD || ' Contact Info Change';

      l_body := l_body || 'CONTACT INFORMATION CHANGE ' || utl_tcp.crlf || utl_tcp.crlf; 
     l_body := l_body || :P430_CONTACT_OLD_DATA ;
     l_body := l_body || utl_tcp.crlf || utl_tcp.crlf;
     l_body := l_body || '<<New Data>>'||utl_tcp.crlf ;

   END IF;


  

    IF 

Open in new window

0
Hi,

I want to use an array to store a list of records  as a collection. Then I want to check if a value from the collection has a child value in the actual table ( a child also exists in the collection) and if so I want to remove that row from the collection.

SET SERVEROUTPUT ON
DECLARE
CURSOR FirstListSearch
IS
SELECT Parent_List,product_group,consignment,final_loc_id,priority,work_zone FROM (...)


Type First_List_Rt 
IS RECORD
(Parent_List task.list_id%TYPE,
Product_Group product.product_group%TYPE,
Consignment task.consignment%TYPE,
Final_Loc task.final_loc%TYPE,
Priority task.priority%TYPE,
Zone task.zone%TYPE
);

TYPE First_List_Aat
IS TABLE OF First_List_Rt INDEX BY BINARY_INTEGER;
First_List First_List_Aat;
l_start  NUMBER;
L_count integer := 0;
l_child_list dcsdba.move_task.list_id%TYPE;
BEGIN
                       
   -- Time bulk population.  
  l_start := DBMS_UTILITY.get_time;
  
        Open FirstListSearch ;
        FETCH FirstListSearch BULK COLLECT INTO First_List;
        CLOSE FirstListSearch;

  DBMS_OUTPUT.put_line('Bulk    (' || First_List.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
                       
    FOR indx IN First_List.FIRST .. First_List.LAST
    LOOP l_child_list := func.getchildlist(First_List(indx).Parent_List,First_List(indx).final_loc_id) ;
      DBMS_OUTPUT.put_line('l_child_list is   (' || l_child_list ||')');
      IF l_child_list is not null
      then 

Open in new window

0
hi...i'm testing Automatic Receipts Processing

i load csv file to apply payments, but it is food with amounts exactlys not with partial imports applieds to invoices

some one could help me?

thanks a lot
0
From C++ how can I capture and iterate through the output results of an Oracle PL/SQL stored procedure that returns data as "TABLE of NUMBER(7)", or "TABLE of VARCHAR2(100)" etc?  I haven't found a clear example or documentation that details the accessing data returned as tables.
   I'm using the MS VC9++ (VS2008 SP1) with the Oracle 11.2 32-bit Instant Client. At this point I need to stay at these versions to not risk breaking the compilation of a much larger project with a lot of legacy hardware and drivers.  I've tried many variations that don't compile, or give runtime errors. Running OTT has prove problematic as the TYPE definitions only exist when the procedure is run.
   I'm currently using the OCCI interface but am open to using C++ .net if it will coexist with the current C++ code.

PL/SQL package definition

create or replace PACKAGE          "CARRIERPKG" AS
    TYPE tcarrier_site     is TABLE of NUMBER(7)      INDEX BY BINARY_INTEGER;
    TYPE tpart_serial      is TABLE of VARCHAR2(100)  INDEX BY BINARY_INTEGER;
    TYPE tpart_rtn_msg     is TABLE of VARCHAR(200)   INDEX BY BINARY_INTEGER;
   
 
      PROCEDURE get_parts_v12
      (p_carrier_serial	  IN  phase2.carrier_site.carrier_serial_num%TYPE,
       p_part_num         IN  phase2.part_info.PART_NUM%TYPE,
       p_carrier_type     IN  phase2.carrier_type.id%TYPE,
       carrier_site       OUT NOCOPY tcarrier_site,
       part_serial        OUT NOCOPY tpart_serial,
       rtn_msg            OUT 

Open in new window

0
Hi All,

I am trying to add data in oracle table with the help of sql loader. i have a column in table as REPO_DATE
i am putting REPO_DATE in control fine as SYSDATE. Example below of control file

OPTIONS (DIRECT=FALSE, ROWS=10000)
LOAD DATA

APPEND INTO TABLE RJ_SONIC_STAGING
EVALUATE CHECK_CONSTRAINTS
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
  (
    ITEM,
    STATE_ID,
    REPO_DATE "SYSDATE"
  )


Is there any way, i can have a constant SYSDATE for this load.
i mean table column gets updated with SYSDATE but as the file is huge, the SECOND part keeps on changing.
Like for first 1000 records it will be 21-JUN-2019 05:05:11 PM
and for next records it will be 21-JUN-2019 05:05:12 PM

So is there any way i can have a SAME SYSDATE for all the records.
By putting DEFAULT value in table property for column also results in the same.
0
Hi All,

I am having below 3 tables

Table 1-
RJ_SONIC_STAGING

ITEM, SCAN_ID, STATE_ID, VENDOR
1234, 9876656, VA, 5643738
3453, 8374839, WA, 6382927
1283, 7352902, VA,
1234, 1722718, WA,

above is example of table records, For few records Vendor is NULL

Table 2-
CONFIG_TABLE

STATE_ID, VENDOR
VA, 432345
WA, 362819

Here i have some default value for STATE_ID

Table 3-
INCEPTION_TABLE

Table format is
ITEM, SCAN_ID, VENDOR

I want to insert into table 3, data from table 1.
But if VENDOR is NULL in table 1, it should take default value from TABLE 2 and put into TABLE 3.

How should i do it in oracle sql.
i dont want to hardcode the value, like
CASE if VA then 432345

cuz they can change Value in table 2 in future..
0
I have vbscribt and trying to make Oracle database connection there but it doesnt seems to work, Here is my script
Option Explicit

const strComputer = "."



Dim objWMIService, objItem, colItems
Dim strDriveType, strDiskSize, StrSqlBody,intFreeSpace 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk WHERE DriveType=3")
StrSqlBody = StrSqlBody + "<div style='font-family:Verdana;font-size:15px;margin-top:10px;margin-bottom:10px;font-weight:bold'>Low Disk Space</div>"
StrSqlBody = StrSqlBody + "<table style='font-family:Verdana;width:700px;font-size:12px;border-collapse:collapse' border='1' bordercolor='black' cellpadding='0' cellspacing='0'>  "
StrSqlBody = StrSqlBody + "<tr style='background-color:#336699;height:20px;text-align:center;color:white'> "
StrSqlBody = StrSqlBody + "<td><b>Drive</b></td> "
StrSqlBody = StrSqlBody + "<td><b>Actual Size</b></td> "
StrSqlBody = StrSqlBody + "<td><b>Used Size</b></td> "
StrSqlBody = StrSqlBody + "<td><b>Free Size</b></td> "
StrSqlBody = StrSqlBody + "<td><b>Machine Address</b></td> "
StrSqlBody = StrSqlBody + "</tr> "
For Each objItem in colItems
	
	DIM pctFreeSpace,strFreeSpace,strusedSpace
	If objItem.Name ="C:" Then
	pctFreeSpace = INT((objItem.FreeSpace / objItem.Size) * 1000)/10
	strDiskSize = Int(objItem.Size /1073741824) & "Gb"
	strFreeSpace = Int(objItem.FreeSpace /1073741824) & "Gb"
	intFreeSpace = Int(objItem.FreeSpace 

Open in new window

0
When an Oracle table is created that contains an XMLTYPE column, a dependent table named with this pattern: SYS9999999_S_PATH_TABLE gets created automatically, apparently to contain the "domain index" for the XML column of the parent table.  We created a custom table like this some years ago, and used it regularly for a while.  Apparently the parent table is no longer active in our system because this particular SYS%_S_PATH_TABLE has had no new records added for some years now.  Prior to July of 2015, it did get new records added regularly.  I would like to truncate or drop this SYS%_S_PATH_TABLE now to reclaim the space it is using (since it contains over 12 million records) but when I try to do that, I get this error:
ORA-30967: operation directly on the Path Table is disallowed.

When I query USER_OBJECTS for: SYS14714159_XXGEN_S_PATH_TABLE, this is listed as a "TABLE".  But when I query USER_XML_INDEXES, this row is not returned.  A different SYS%_S_PATH_TABLE is returned.
0
Hi Experts,

We are trying to install the oracle client in the server., But when its try to install the application its self closing.
No clue what is causing the installer to disappear.

Let me know If you have any question's.

Regards,
Kumar
0
Hi Experts,

I am trying to write the oracle sql to update a table column based on the spread sheet ..

We have the spread sheet like this values

BatchID     Queue     comment
1                KTM          Test1
2                KTM2         Test2
3                KTM3          Test3

we want to use the above spread sheet (That is the output of a SQL) --and update the table column
This is the one-time job in my current requirement., Please help me how to achieve?

Let me know if you have any questions...

Regards,
Kumar
0
I have an Oracle database (11gR2) that processes records on a weekly basis. One of the business requirements is that the records be maintained for the current processing year plus the previous six years. An additional requirement is that records prior to that period be deleted. This is all relatively easy to manage as the records are maintained in tables that are partitioned by processing year, so each new year the partition, for the processing year that is seven years prior to the current year, is dropped. Now a new requirement has been added and that is to determine the number of records that were in the dropped partition. Going forward this can be readily determined prior to the new year. Unfortunately, there is also a need to know the number of records dropped at the beginning of this year. Considering that we are currently in processing year 2019 and the previous six years were for 2013-2018, we dropped the partition for 2012 before getting a count of the records. Therefore, my questions are, "Is there any kind of log or audit file that may have captured this information?" or, if not, "Is there any other means of getting this count?"
0
i have a windows cmd script which has calls to other scripts
it runs tasks in consecutive order and is called from several config scripts
echo.starting upgrade on %oracle_sid% at %dt% >%mlog%

echo  hit a key to start 
pause

rem step 1
call:eecho "preparing source"
call %sdir%\prepare_source_12102.cmd 

rem step 2
call:eecho "creating standby 12102"
call %sdir%\create_standby.cmd

rem step 3
call:eecho "rollforward standby"
set activate=NO
call %sdir%\activate_standby.cmd

echo hit a key to ACTIVATE STANDBY 
pause 

rem step 4
call:eecho "activate standby"
set activate=YES
call %sdir%\activate_standby.cmd

echo hit a key to start upgrade to 12.1
pause 

rem step 5
call:eecho "upgrade to 12.1.0.2"
call %sdir%\upgrade_db_12102.cmd

rem step 6
call:eecho "post upgrade on 12.1.0.2"
call %sdir%\post_upgrade_db_12102.cmd
call:eecho "end of post upgrade on 12.1.0.2"

echo hit a key to upgrade to 12.2
pause

rem  step 7
call:eecho "preparing source for 12.2.0.1"
call %sdir%\prepare_source_12201.cmd 

rem step 8
call:eecho "upgrade database to 12.2.0.1"
call %sdir%\upgrade_db_12201.cmd 

rem step 9
call:eecho "post upgrade database to 12.2.0.1"
call %sdir%\post_upgrade_db_12201.cmd 
call:eecho "end of post upgrade database to 12.2.0.1"

echo hit a key to switch sids
pause

rem step 10
call:eecho "rename sids %oracle_sid% to %dest_sid%"
call %sdir%\switch_sid.cmd %dest_home_2% %oracle_sid% %dest_sid%
call:eecho "end of switch sids to "%dest_sid%"

goto exit

:eecho
title 

Open in new window

0
Hi All,

Really stuck in one place. I am new to plsql and shell scripting.

I have a shell script which is taking $1 parameter as JOB_ID while executing. means i am passing JOB_ID as $1 while running the script
like
sh test.ksh SW -- where SW is my JOB_ID

Now i need to create a new function inside an already existing package in oracle. This function should be called from script.
It should take $1 as input parameter and then i have a query which should go in function like-

select count(*) from job_tables where job_id=$1

Now the result can be either 0 or 1.

if result is 0 then i need to set a variable
JOB_NO in shell script as /home/users/abc

if result is 1 then i need to set
JOB_NO in shell script as /home/users/def

Please help me in solving this. How should i write and approach
0
Hi Team,

Need suggestion for one of my oracle problem.

I have 1 main table AVAILABLE_STG having columns IDENTIFY_1 and PROC_CD. This table DOES NOT have SKU column.

Other table REFERENCE_IDENTIFIER is having columns IDENTIFY_1 and SKU. It is 1-1 mapping. 1 IDENTIFY_1 will have 1 SKU only.

Now i need to check if value of IDENTIFY_1 from main table AVAILABLE_STG is present in REFERENCE_IDENTIFIER.
If it is present then i need to take some other columns from main table AVAILABLE_STG and insert into MAIN_SUCCSESS table along with SKU information against each IDENTIFY_1.
Once this is done i need to make PROC_CD as 'S' in main table AVAILABLE_STG

If SKU not found, then i need to update other table MAIN_FAILURE with some column from main table  AVAILABLE_STG  and also update the PROC_CD as 'N' AVAILABLE_STG .

How to achieve this. i am stuck at this from long time. Any pointers would be helpful.
0
How can I update the contents of one table with the values from another table.
I have a table called PS_PURCH_ITEM_ATTR with a field called STD_LEAD and I need to populate it with the correct values from a table called PS_BU_ITEMS_INV and a field called REPLENISH_LEAD.

I've tried using the following code, but it is not working:
UPDATE (SELECT name1.std_lead name1,
               name2.replenish_lead name2
        FROM   ps_purch_item_attr name1,
               ps_bu_items_inv name2
        WHERE  name1.inv_item_id = name2.inv_item_id)
  SET name1 = name2;
0
Hi everyone
I'm new to Delphi programming.
As far as I'm looking at the internet, I have created my own web service (simple calculator style).
I have created SOAP server aplication.
VLC forms an interface for users to use to create forms.
I connected the web service client with the WSDL importer.
I have created a simple web service that collects two numbers from the user in the web service and returns the result.
web service is running.
 I have a database .
What I want is that I want to search for people in this database.
I created a grid object in the user interface.
I put one edittext.
I want to search the database based on the value entered in Edittext.
For example, if I type 'SAMSUNG' in the edit box, I want to transfer samsung data from the database to the grid of the vlc form.veritabani_baglantisi_icin_webservic.png
0
hi; i i have develop a tool with use database metadata to define order of integration between the different tables and build and execute sql merge query dynamically.
I got an issue 0RA- 00001

MERGE INTO HARPAGON.PTSUIV Cible USING
( SELECT DISTINCT Tempo.CODPARAM,
Tempo.LIBPTSUIV,
Tempo.TYPPTSUIV,
Tempo.LIBIHM,
Tempo.MODIF_USER,
Tempo.MODIF_DATE,
Tempo.UNITE,
Tempo.ORDVISU,
Tempo.TYPCONV,
FromMap2.LIB_RES_ID_NEW PTSUIV_LIB_RES_ID,
FromMap1.LIB_RES_ID_NEW IHM_LIB_RES_ID
FROM UTIPORTABLE.PTSUIV Tempo  
Inner Join UTIPORTABLE.LIB_RES_MAP FromMap1 ON (Tempo.IHM_LIB_RES_ID = FromMap1.LIB_RES_ID_OLD)
Inner Join UTIPORTABLE.LIB_RES_MAP FromMap2 ON (Tempo.PTSUIV_LIB_RES_ID = FromMap2.LIB_RES_ID_OLD))
Source
ON (Source.CODPARAM = Cible.CODPARAM AND Source.LIBIHM = Cible.LIBIHM)
WHEN NOT MATCHED THEN
INSERT (CODPARAM,LIBPTSUIV,TYPPTSUIV,LIBIHM,MODIF_USER,MODIF_DATE,UNITE,ORDVISU,TYPCONV,PTSUIV_LIB_RES_ID,IHM_LIB_RES_ID)
VALUES (Source.CODPARAM, Source.LIBPTSUIV, Source.TYPPTSUIV, Source.LIBIHM, Source.MODIF_USER, Source.MODIF_DATE,
Source.UNITE, Source.ORDVISU, Source.TYPCONV, Source.PTSUIV_LIB_RES_ID, Source.IHM_LIB_RES_ID)
0
I'm running a legacy oracle 11gR1 environment that has clusterware, asm, and a database. I accidentally removed the clusterware binaries. I tried to remove all remnants of clusterware and just reinstall it. When I run  Oracle Universal Installer (runInstaller), to reinstall the clusterware, I cannot make changes because all the options are dithered out. See images. What can  I do to make changes?
Capture1.JPG
Capture2.JPG
0
We are migrating from a SQL 2008 DB to a Oracle DB.

I have some complex SQL queries that run perfectly in SQL 2008, 2012 etc., but throw an error in Oracle.

Is there a tool that can convert the TSQL language from SQL to Oracle?
0
I want to pass in a rownum parameter to a  procedure in oracle and also have a default value for the rownum ( say 25) such that

Select * from TABLET where Id = paramId and rownum < paramRowNum

How to declare and pass parameter to oracle procedure and also how to set a default value for the paraameter inside the procedure?
0
I am new to oracle. I have to update a procedure (see sample below) , which is within a package, say abc_pkg.

I want to write sql scripts to update existing procedure, below. also, if i want to add another procedure to the same package, how do i do that.

I want to write sql scripts , which i can execute to update the database.

Procedure get_info (
   id IN Number
)
IS
BEGIN
    select * from TABLE where id = id..
0
Hi!
I have a similar problem like https://www.experts-exchange.com/questions/26668199/Decimals-Lost-When-Selecting-Data-From-Database-Link.html.

On MS SQL Server I have a View with a type decimal(9,6). When I create a view on ORACLE to that View on MS SQL I get a type NUMBER(18,2) but the numbers are cutted.
0.980340 gets 0!

When I make a multiplication with 100000 i get 980340.
When I make to_char (var * 1000000,'999999') it say's 0!!

Can You help me?
Thanks Peter
0
Hi Oracle Masters,

I have function which retrieves values from multiple selects and one the sql's is not correct as it is as below -

I want to change the part where the select with "Max" values are returned (highlighted in Italic and bold) by replacing it with another function(or calling another function) by passing a SID as input parameter and returning AID without using the max function -

create or replace function func1 as
cursor c  is
select
col1,
col2,
col3,
col4,
col5,
col6
from
(
Select it_id ,
           it_date,
           max(cs17),
           max(p43),
           max(p44),
           max(p32)
from

max(
select csm12
from var, vmi, vmcf, vmit
where  <join cond1 >
   and   <join cond2 >
   and   <join cond3 >
   and  ci18 ='x'
   and rownum =1) as MaID,
max(
select .. from tablename3 ) as EcID,
max(
select .. from tablename4) as nsid ;
end function_name;
   
Can anyone let me know how to resolve this issue.

Thank you,
Rads
0
Hi,

I am facing this error on ADF, but I am not able to reproduce the scenario, but the users is getting this error several times during the day, any idea how I can find a solution?

[2019-04-30T09:38:51.255+03:00] [AdminServer] [WARNING] [ADF_FACES-00009] [oracle.adf.view.rich.component.fragment.UIXRegion] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: WDAHER] [ecid: c87619c7-def0-4891-ace9-679f979f364f-00000634,0] [APP: ProviderApp] [partition-name: DOMAIN] [tenant-name: GLOBAL] [DSID: 0000MdgmAT20ZrwN0EFg6F1SlrOI00000D] Error processing viewId: /provider/ProviderView URI: /WEB-INF/com/ba/network/fragments/ProviderView.jsff actual-URI: /WEB-INF/com/ba/network/fragments/ProviderView.jsff.[[
java.lang.NullPointerException
                at oracle.jbo.uicli.binding.JUCtrlHierBinding.bringNodeToRangeKeyPath(JUCtrlHierBinding.java:1513)
                at oracle.adfinternal.view.faces.model.binding.FacesCtrlHierBinding.bringNodeToRangeKeyPath(FacesCtrlHierBinding.java:123)
                at oracle.adfinternal.view.faces.model.binding.RowDataManager.setRowKey(RowDataManager.java:152)
                at oracle.adfinternal.view.faces.model.binding.FacesCtrlHierBinding$FacesModel.setRowKey(FacesCtrlHierBinding.java:932)
                at org.apache.myfaces.trinidad.component.UIXCollection.setRowKey(UIXCollection.java:563)
                at org.apache.myfaces.trinidad.component.UIXTable.setRowKey(UIXTable.java:794)
      

Open in new window

0
how to use with clause , merge statement and triggers inside the cursor .please explain with examples.
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.