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

I want an oracle pl/sql scripts/procedure/package anything compatible to oracle 12c, which can help me to send the result of a query over my email.

For example: Select * from employee;

1. If this query returns any rows, the count will be emailed to a@b.com
2. The result of this query will stored in excel and the excel file will mailed to a@b.com
0
Hi Expert,

I have a package which was perform sometimes great/poor, attached file is the package .

Sorry for attachment of large set of code.

If possible please guide me with quick bad written line!

Thanks,
Mihir
pkg_to_tune.sql
0
I have a PLSQL procedure that embed with HTML code. How can I use this procedure to print in JSP? Can provide sample code?
0
Using xmlspy, I created a sample xml document from a valid schema definition to use as test data for an Oracle database (11.2.0.4). However, the resulting document is substantially large and I am running into a "string literal too large" error when trying to load it. I have searched and searched for a possible solution and none of them seem to work. Has anyone at EE been able to load a large (>32 kb) text file into an xmltype column? If so, how?
0
How can I selectively import a package from a export dump(schema_dump.dmp) file using DBMS_DATAPUMP.
for example I want to import a package called sttm_customer_pkg from a dump called schema_dump.dmp. please help me on this.
0
I wanted to use a CASE statement to return a multi-row message based on a condition.
My understanding is that CASE cannot return multi rows... but IF THEN can.... maybe it can.

IF CON_CODE like 'HCTRU%' then 
  (select mes_line_text from m_mes_d where mes_code = 'TEST' and comp_code = '00')
ELSE IF CON_CODE like 'HCAMZ%' then 
  (select mes_line_text from m_mes_d where mes_code = 'CADB' and comp_code = '00')
from E_ORD_H where comp_code = 'W1' and ord_num = 923185

Open in new window


For each M_MES_D entry, the message may be stored on 1 to 12 rows
How can this be done?
0
Hello,

I'm wondering if someone has some experience with sending SOAP APIs from Oracle. What I need is
1. Take data from oracle table
2. compose an xml message using selected data
3. post this message to a host
4. get a response back

I've been reading about oracle's UTL_HTTP . Is this something I can use to achieve what I want ?
0
Hi Expert,

I have following stored procedure using merge SQL.
I am trying to find a way to log which record have been inserted or updated by the merge SQL.
May I know is there a way to achieve this?

CREATE OR REPLACE PROCEDURE PRC_001_CD IS
BEGIN

  MERGE INTO tmp_cd_01 D USING (select * from tmp_cd_02) S
  ON (D.i_id = S.i_id AND D.e_id = S.c_no)
   WHEN MATCHED THEN
      UPDATE SET
      D.c_date = D.c_date
      WHERE S.d_ind = 1
      DELETE
      WHERE S.d_ind = 1
    WHEN NOT MATCHED THEN
      INSERT (i_id, e_id, e_date, l_code, c_date)
      VALUES
      (
      S.i_id,
      S.c_no,
      S.e_date,
      S.l_code,
      S.c_date
      )
      where S.d_ind = 0;
    COMMIT;

END;
0
Dear Experts, I'm testing Oracle 11g on Centos 7 64b.

I installed Oracle but could not connect to the globalDB by sqlplus, it keeps showing error: ORA-12514: TNS:listener does not currently know of service requested in connect even I start the Oracle Database

ora1.PNG
ora2.PNG
I also attached the response file from Installation process. These are the details of listener and tnsname files:

ora3.PNG
lsnrctl status:

ora4.PNG
Can you please help? Many thanks!
db.rsp
0
Hi Experts
As I am currently extract data in excel file via MSSQL from local computer in which there is linked server with Oracle Database, I got errors message for excel -2147417484 (80010108).
But I am successfully extract data if small dataset. What should I do? Transfer to sever side instead of local computer? Please kindly advise

Many thanks
1
HI,

I am comparing a data warehouse solution for MySQL/MariaDB, we are looking at :

1) Oracle Database 12c Data Warehouse
2) IBM BigInsights
3) Microsoft Azure
4) Pentaho Kettle / Other OSS

any idea?
0
i want to change password_lifspan_days for all user to 180
0
HI There! I have two schemas (A & B) having multiple tables(Huge no. of records), and have to load data into schema (B) tables by capturing table names from all_tables & all_tab_columns of (A).  The table names are same in source and the target schemas but in target there're some tables has extra columns with null constraint. Need Help to INSERT the data automatically.

Oracle 11gR2 Release 4.
Source Schema Names: A
 Target Schema Names: B
Tables: Same in both schemas and in some tables more NULL columns datatypes are Number, Varchar2, Char, Date & Timestamp.

Need Help.
0
HI,
I can access the oracle tables and stored procedures using C#, but i am getting error when trying to use select oracle VIEW.
Below code i am using. I have access to that view also.
I don't even understand what mistake i did, please do the needful, very urgent.
If you have any piece of code to connect oracle view, please post it.

Error:Operation is not valid due to the current state of the object."

OracleConnection con = null;
OracleCommand cmd = new OracleCommand();
OracleDataReader dr = null;

try
{
    con = new OracleConnection("COnstring");
    con.Open();
    string strQuery = string.Empty;

    strQuery = "select col1,col2 from VW_test_view where col1 ='" + COL1 + "'";
    cmd.CommandText = strQuery;
    cmd.CommandType = CommandType.Text;
    dr =cmd.ExecuteReader(); 

    if (dr.HasRows)
    {
        while (dr.Read())
        {
            statusCode = dr["col2"].ToString();
        }
    } 
}

Open in new window

0
I'm having a performance issue with an Oracle SQL query. When I add a subselect, the query takes 5 min, 40 sec.

SELECT DISTINCT
    prokeyi,
    probezc,
    provf2c,
    artbezc,
    artkeyi,
    artanrc,
    katbezc,
    etybezc,
    stabezc,
    vgbbezc,
    abpbezc,
    kvtbezc,
    kvtinac, 
    rafbezc,
    rfavf1c,
    rfavf2c,
    rfavf3c,
    rfavf4c,
    rfavf5c,
    depbezc,
    MIN((select distinct pflfldc from pfl,pty where pflptykeyi = 17 and pflobjkeyi = prokeyi and prokavkeyi = pflvarkeyi))
FROM
    pro,
    art,
    aez,
    kat,
    ety,
    dok,
    wob,
    abp,
    vgb,
    sta,
    kvt,
    kav,
    dep,
    rfa,
    raf
WHERE
    prokeyi = aezprokeyi (+)
    AND   aezkavkeyi (+) = prokavkeyi
    AND   artkeyi (+) = aezartkeyi
    AND   artkavkeyi (+) = aezkavkeyi
    AND   prokatkeyi = katkeyi
    AND   etykeyi (+) = proetykeyi
    AND   dokkatkeyi = katkeyi (+)
    AND   doksnei = prosnei (+)
    AND   wobabpkeyi = abpkeyi
    AND   wobobjkeyi (+) = dokkeyi
    AND   wobvarkeyi (+) = dokkavkeyi
    AND   wobstakeys = stakeys
    AND   dokvgbkeyi = vgbkeyi (+)
    AND   staabpkeyi = abpkeyi
    AND   abptyps = 150
    AND   wobtyps = 150
    AND   kavkatkeyi = katkeyi
    AND   kavkvtkeyi = kvtkeyi
    AND   kavkeyi (+) = prokavkeyi
    AND   rfakavkeyi (+) = dokkavkeyi
    AND   rfadokkeyi (+) = dokkeyi
    AND   rfadepkeyi = depkeyi
    AND   prorafkeyi = rafkeyi (+)
    AND   doksnei = prosnei (+)
    AND   probezc <> '---Page element---'
  

Open in new window

0
I want search At character press in forms 6i
0
I have code within a view something like:

select
case
when substr(Col1,1,2)='12' and substr(Col1,3,2)='34' then
        'Abc'
when substr(Col1,1,2)='49' or substr(Col1,3,2)='55' then
       'Def'
as Val1,
Val2,
...
ValN
from Tbl1

These conditions will change always. For example, in the next version of the code, the logic above could become:

select
case
when substr(Col1,1,2)='56' or substr(Col1,3,2)='47' then
       'Abc'
when substr(Col1,1,2)='44' and substr(Col1,3,2)='77' then
       'Def'
as Val1,
Val2,
...
ValN
from Tbl1

I am trying to make this table-driven (let's call it Tbl2) so that I can build and execute the CASE statement on the fly based on values in Tbl2. So Tbl2
will have values such as:

Col1          Final Value                                      Logic                                      
12345           Abc                    substr(Col1,1,2)='12' and substr(Col1,3,2)='34'      
49557           Def                       substr(Col1,1,2)='49' or substr(Col1,3,2)='55'  

When the logic changes, the values will be:

Col1          Final Value                                      Logic                                      
56478             Abc                   substr(Col1,1,2)='56' or substr(Col1,3,2)='47'      
44779             Def                       substr(Col1,1,2)='44' and substr(Col1,3,2)='77'        
 
etc.

As you can see, Col1 has the condition characters (like '56') and additional characters after them as well. I am looking for some ideas as to how, and if, it can…
0
I want to select a row,when we select a row colour of row will change what is the code
0
Hi Expert,
 Could anyone please share the E-R for this. (Looking for E-R model without numeric ids)?

Problem Statement
-----------------------------
A company record information on its feet of vehicle and the employee who are permitted to drive them. The database is used by three groups of people, the department managers, the finance division and the service department.

For the department managers, each vehicle has a unique registration number and each driver has a unique employee number. Drivers may be authorized to drive a number of vehicles and any vehicle may be used by a number of drivers. Vehicles are allocated to departments within the company, although they may  be used by drivers in other departments.

Some classed of vehicle require specialist driver-qualifications. There are occasional accidents which may lead to the vehicle being written off and/or the driver being disqualified from driving  some or all classes of vehicle.

For the finance division each vehicle, identified again by registration number, has a current and a replacement value, must be taxed on a certain date and was bought on a certain date. For accounting purpose the allocation of vehicle to departments is also required. Finally details are recorded of any insurance claims associated with accidents or repair costs if no insurance claims arose.

The service department is responsible for giving regular services to each vehicle. There are different types of service corresponding to …
0
Hi Team,

I have a written a procedure which is execute in package . But the procedure is not working .
Could you please below procedure and help me. And the data is not loaded into the Iaml_Custphon_cdds_stg.
procedure P_custphon_cdds
AS
  /* Truncate Staging Tables*/

Begin
  --Reload Iaml_Custphon_cdds_stg
   EXECUTE IMMEDIATE 'TRUNCATE TABLE Iaml_Custphon_cdds_stg';

Insert into Iaml_Custphon_cdds_stg
  (cust_id,
   phone_rec_number,
   phone_purpose,
   phone_number,
   phone_ext,
   source_system,
   country_of_phone_number)

   select distinct
   ltrim(rtrim(p.customer_identifier)) as cust_id,
    ROW_NUMBER () OVER (PARTITION BY p.customer_identifier,p.address_purpose ORDER BY p.customer_identifier ASC) as phone_rec_number,
    decode (ltrim(rtrim(p.address_purpose)),'Party Registered Address','B','Corresponding Address','H' )as phone_purpose,
  ltrim(rtrim(p.phone_number)),
  ltrim(rtrim(p.phone_extension)) as phone_ext,
   'CDS',
   get_country_cde(p.country_of_phone_number)
 from CDDS_CUST_ACCT_TEMP p
 where p.customer_identifier is not null
-- and p.account_type1!='FCLTATN'
  group by     p.customer_identifier,
    p.address_purpose,
p.phone_number,
  p.phone_extension ,
 (decode( ltrim(rtrim(p.source_system)) ,'ICON_PARTY','CDS')),   p.country_of_phone_number  ;


EXCEPTION WHEN OTHERS
THEN dbms_output.put_line(SQLERRM);


commit ;

Open in new window

0
FISCAL WEEK CALCULATION IN SQL MONTH STARTS FROM JUN AND THE WEEK SHOULD START FROM SUNDAY
0
Hi, I need help in coding sales ration in sql, plsql. I have a file attached with details
C--Tanuja-Lake_IL-BRDs-sales-ratio-.docx
0
I want to create a excel through forms using forms 6i to migrate into forms 12c
0
I am working on forms 6i i have two table one is database block and one is control block i want a approval form in which three radio buttons are created 1 approval 2 back 3 cancel and a table in which check the hierarchy for approval
0
Hello,
Iam running the below query on oraccle:

select distinct m.sc_msds_id as sc_msds_src,m.msds_num as msds_num_src
from (
select distinct sc_msds_id,msds_num,
 TO_CHAR(REVISION_DATE, 'MM/DD/YYYY')  as revision_date_lookup,created,status
from sc_msds 
)m
where 
(revision_date_lookup = ('04/19/2018 00:00:00') or revision_date_lookup is null )  
AND status='Rejected' and TRUNC(created) = '05-OCT-2018';

Open in new window

The query is not returning value as  revisiondatelookup value on the db is 04/19/2008. How do I update the query so it can return the data row matching the revision date
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.