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

how to use merge statement in cursor .please provide some examples for this
0
I want to add a search to my classic report so I added a textbox and a button on my page, above the report and I added a process to the page, executing PL/SQL code:
    DECLARE
        v_sql varchar2(4000);
    BEGIN
	IF :P3_SEARCH IS NOT NULL THEN
  		v_sql := q'[select field1, field2, field3
			   FROM Table1
			   WHERE category_id = ]' ||TO_NUMBER(:P3_CATEGORY)||
			   q'[ AND (function(id) LIKE '%]'||:P3_SEARCH||| 
    			   q'[%' OR field2 LIKE '%]' ||:P3_SEARCH|| 
			   q'[%' OR field3 LIKE '%]' ||:P3_SEARCH|| q'[%')]';
  	ELSE
  		v_sql := q'[select field1, field2, field3
			   FROM Table1
			   WHERE category_id = ]' ||TO_NUMBER(:P3_CATEGORY)|| q'[]';
        END IF;
  
      apex_debug.MESSAGE('my query is: ' || v_sql);      

        --   RETURN v_sql;
    END;

Open in new window


I have two issues - my resulting query does not come out as expected - `%` signs disappear and instead of
    select field1, field2, field3
	FROM Table1
	WHERE category_id = 4
	AND (function(id) like '%test%' OR field2 like '%test%' OR field3 like '%test%')

Open in new window

I get
    select field1, field2, field3
	FROM Table1
	WHERE category_id = 4
	AND (function(id) like 'test' OR field2 like 'test' OR field3 like 'test')

Open in new window

And I also cannot figure out how do I switch the source sql for my report region when the search button is clicked. Can anyone help?
0
Oracle PL SQL Stored Procedure version 11

How do I create an Oracle Stored Procedure Code for below insert statement?

My Insert Statement Code:

INSERT INTO TABLE1
(NAME, NAME_ID, Q004)

--Fields inserting to --

SELECT X.NAME, X.NAME_ID,
CASE WHEN X.Q004 IS NOT NULL THEN 1 ELSE 0 END Q004
FROM TABLE2 X;

commit;

Thank you!
0
I am trying to select records from last week only
How can I properly subtract 1 from the current ISO week number

This is my current attempt which does not work

and TRUNC(O.ORD_CONF_DATE,'IW') = TRUNC(SYSDATE,'IW')-1

Open in new window

0
Related to converting Oracle pl/SQL, would like to understand the libraries and functions that needs to be used?

In oracle there are so many advanced functions, in case of converting to python scripting accessing HIVE how to convert those functions in Python scripting? Appreciate your input.
0
hi,

right now want to check the Oracle 10g and 12c RAC configuration, how can I check it?
0
Would like to understand whether pandas(python) can be used to convert from oracle pl/sql to python(accessing hive) and later ported into spark(Big data) environment or development.

Is to be done using Pyspark for executing in spark(Big data) environment?
0
How would I write an insert statement to only insert a record in a department table only if a specific order number doesen't exisit?
For example, I have department 12345 and it has order numbers 20, 30, 40, 70, and 90.
I need to insert a record for order number 80 with the necessary values?
0
Hello,
I want to make with an existing database and tables an ER-Diagram like mySQL Workbench.
The tables (over 200) are displayed overlapped in mySQLWorkbench. So, I am looking for an alternative.
I am using MariaDB/HeidiSQL.
Which software is free and has these functions?

* Import ERD as png and pdf?
*  Reverse Engineering (make from an existing database a ERD)
* Triggers should also be displayed in the ERD
0
Can I do something like this, without using case statement, and be able to change these values to
those listed in the replace columns, like whenever 1,2,7,8 appears, then SE, if NE,SE,NW,SW appears then All and so on and so forth else col1 end;


SELECT COL1 from dual
Order by decode(1,2,7,8,3,4,5,6,9,10,15,16,11,12,13,14, 'ALL') ,
decode(1,2,7,8, "SE"),
decode(3,4,5,6, "SW" ,
decode(9,10,15,16, "NE") ,
decode(11,12,13,14, "NW") ,
decode(NE,NW,SW,SE, "ALL")

FROM DUAL;

Replace(location, "1,2,7,8", "SE")
Replace(location, "3,4,5,6", "SW")
Replace(location, "(9,10,15,16", "SE")
Replace(location, "11,12,13,14", "SE")
Replace(location, "NE,NW,SW,SE", "ALL")



col1
-----
NE,NW,SE,SW
SE,NE,NW,SW
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
NE,NW,SE,SW
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
10,11,12,13,14,15,16,9,SE,SW
10,11,4,7,15,16,9,SE,SW
1,6,7,8,9
10,11,12,13,14,15,16,9,SE,SW
1,10,11,12,13
10,11,12,13,14,SE
1,2,3,4,5,6,7,8,9
NE,NW,SE,SW
SE,SW
NW
SW,NW
0
In Oracle fusion, using HCM is it possible to mass assign a role/roles to existing user accounts? I know you can update elements in the Person Record, but was looking at the roles. which is previous versions of oracle might be referred to as 'responsibilities'.

I can do this manually, locate user record and then add the role, but it is time consuming to recall each user and assign the role manually. Would like to find a way I can do this en mass for non auto provisioned roles.

I've scoured google for examples/literature but there is not much there for a novice like me. Any information be gratefully received.
0
hi,

As I am testing Oracle to MariaDB converison and when I migrate to MariaDB some table may fail to create when there are foreign key prevent me from creating tables .

from your point of view, how can I check quickly which MySQL constraint I have to remove before dropping that table and recreate again .

it is a very time consuming task if I have to check tables by tables .. ( 3xxx tables!)

or I should look at Oracle constraint instead to give hints  ?
0
Hello Everyone

We recently upgraded our Oracle database from 11g to 12.2.

Actually we are using Oracle developer Forms 6i and after the upgrade we are not able to issue any rollback from the form screen to the database and we receive below error:

FRM-40655 SQL Error Forced Rollback.

Please advise.

Thank you.
0
Hi everyone -

We recently upgraded db from 11g to 12.2.
We have a dilemma with one form that was created with Forms 6i. it is a standalone Utility Form created around 10 yrs ago
We had no problems working with it on 11g but now (12.2) when we exit the Form we get - FRM-40655 SQL Error Forced Rollback Clear Form and Re-Enter Transaction-
Does anyone have any idea why this is happening on 12.2. and how to solve?

Thank you.
0
how can I do a self join on the query below, I ha attached the result set for both queries buy I want it to be one.   please expert advise?        

 select
            t1.unit_code,
            t1.dls_range,
            t1.dls_section,
            t1.lowdesc_case as lowdesc 
        from
            ( 
                select
                    unit_code,
                    dls_range,
                    dls_section,
                    case
                        lowdesc 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 22, 23, 24, 25, 28, 29, 30, 32, 33, 34, 36, NE, NW, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, NW, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 15' 
                        then
                            'SW,SE,9.10,11,12,13,15' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW' 
                 

Open in new window

0
Hi all,

I have created the following function and it does not retrieve the snapids from  the remote db using the dblink -

Type, Objects and function created -

create or replace TYPE Obj_snapids AS OBJECT
(   snap_id NUMBER,
    end_interval_time date,    
    instance_number NUMBER
);
/
create or replace TYPE tab_snap_ids AS TABLE OF Obj_snapids;
/
create or replace FUNCTION rr_snapids(p_link_name IN VARCHAR2)
    RETURN tab_snap_ids
IS
    c_sql_template   CONSTANT VARCHAR2(1000) := '
          SELECT Obj_snapids(
                         snap_id,
                         end_interval_time,
                         instance_number
                 ) obj
            FROM dba_hist_snapshot@~insert_db_link_here~';
    v_results                 tab_snap_ids;
    v_dblink                  VARCHAR2(2000);
BEGIN
   apex_debug.enter(
        'rr_snapids'
        'p_link_name',
         p_link_name);
     v_dblink := p_link_name; --- added replace function to the hostname
    if is_valid_db_link(v_dblink)  
    THEN
        EXECUTE IMMEDIATE REPLACE(c_sql_template, '~insert_db_link_here~', p_link_name)
            BULK COLLECT INTO v_results;
    ELSE
        DBMS_OUTPUT.put_line('Invalid Link Name used: ' || p_link_name);
        v_results := tab_snap_ids();  
    END IF;
    apex_debug.info('rr_snapids exiting with ' || to_char(v_results.count,'fm99999') || ' rows returned');
    RETURN v_results;
END;
/

Please let me know what I …
0
Dear Oracle Gurus,

I am trying to write a oracle query to display all the parent and child tables in a schema based on Primary key , foreign key constraints.
In some cases a particular parent table (which has multiple child tables) will be a child table to another table itself. My query output should look like:

Parent      Child      Level
            
T1                T2      
T1                T3      
T1                T4      
T1                T5      
T6                T7      
T6                T8      
T10                T9      
T10                T11      
T12                T1      
T12                T10      
T12           T13      

I am trying to solve a situation where for me to delete data in Table 12, I need to first delete T9 , T11 and then T10. So looking for additional columns in the query result that will tell me the order in which data needs to be deleted.


Regards
Lavanya
0
I am trying to check if a certain I am trying to check  on a windows server if a certain file exists in oracle on a unix server that I have rights too. I have done this and works

SET SERVEROUTPUT ON SIZE 1000000;
set echo off
set head off
set feedback off
set verify off
set pagesize 0
set pause off
set termout off
set term off
set timing off
#SET LINESIZE 1200
SET TRIMSPOOL ON
spool D:\Admin\Data\chk.txt
DECLARE 
 PROCEDURE checkFileExistOrNot(v_dir IN VARCHAR2, v_file in VARCHAR2) IS
  lb_file_exist boolean;
  ln_size number;
  ln_block_size number;
  BEGIN
    sys.utl_file.fgetattr(v_dir,v_file,lb_file_exist,ln_size,ln_block_size);
    if lb_file_exist then
        dbms_output.put_line('true');
        else
        dbms_output.put_line('false');
      end if;
  END;

 BEGIN
   checkFileExistOrNot('HRUTL','accuhrsBW.csv');
 END;
/
spool off;
EXIT;

Open in new window



Now I trying the same thing but only know part of the filename, I’m trying to pass it in as a variable – this does not work

SET SERVEROUTPUT ON SIZE 1000000;
set echo off
set head off
set feedback off
set verify off
set pagesize 0
set pause off
set termout off
set term off
set timing off
SET LINESIZE 1200
SET TRIMSPOOL ON
--spool D:\Admin\BSwift\BSwift_Export\Data\chk.txt
declare  PROCEDURE checkFileExistOrNot(v_dir IN VARCHAR2, v_file in VARCHAR2) IS
  lb_file_exist boolean;
  ln_size number;
  ln_block_size number;
  BEGIN
   sys.utl_file.fgetattr(v_dir,v_file,lb_file_exist,ln_size,ln_block_size);
    if lb_file_exist then
        dbms_output.put_line('true');
        else
        dbms_output.put_line('false');
      end if;
  END;
  
 declare vfile varchar2(20);
 BEGIN
  vfile:='*ADP*'; 
   checkFileExistOrNot('HRUTL',vfile);
 END;
/

Open in new window

0
I have Oracle 11g, trying to find a way to check the connection to DBSEC when running a script, tried to locate a way to do this.  Any help or guidance would be helpful.
0
Hi APEX Masters,

I have a dashboard with about 50 complicated queries displayed in charts and interactive reports (APEX 18) , getting data from about 3-4 tables and one of the table has about a million records. All the tables have indexes.
It is taking about 65sec to display the page / load, is there any option to increase the performance  of the page , can anyone give a fast solution asap.

Appreciate for your time.

Thanks,
R
0
hi,

May I know how can I list down which Oracle table has user defined type ?

and how can I find out how many Oracle object defined in Java/contain Java?
0
Hello,

Have one Oracle Instance 8i, the client is taking backup through export utility as scheduled task to create DMP files, DMP files size suddenly changed from 20 GB to 1.6 GB , the export is done without issues or interruptions, checked historical big tables and data sounds intact ? what do you think ?

can I compare between 2 files ?

Thanks
0
i am preparing an disaster recovery plan for an oracle database that i inherited management of when we took of maintaining the web application it is used for.

The production database is over 10 years old running 10.2.0 on a windows 2003 server and i started running a backup using RMAN every night as there no backups were being done prior to inheriting the database.

I have created a 2008 server virtual machine running 11.2.0 as i thought such a big jump for 10.2.0 to 18c would be to problematic.

So far i have restored the control file, restored the data files from backupset and been able to mount the database.

What i'm having an issue with is i have to use start upgrade as the database won't start without the upgrade option.

I have tried running the upgrade scripts and have tried using the database upgrade assistant but i get the following error.

ora-04023 Object SYS.Standard could not be validated or authorized.

I tried a suggested fix "alter system flush shared_pool;" but to no avail it didn't fix the problem.

The previous manager didn't really know what they were doing and cancelled the oracle support contract 9 years ago.

The manager is reluctant to pay the thousands of dollars to reinstate a support contract at this point as they were lucky enough to get by with no support or backups for almost a decade so i can't oracle support at this point and seeing as i'm just preparing a disaster recovery plan i doubt this would be covered by the …
0
hi,

anyone know how to use Oracle SQL developer to show a report and export as an excel for number of record information of all table belongs to a schema ?


I want to compare the same tables in MariaDB once I migrate from Oracle.
0
Need oracle pl sql query to do the following

A given 'EmpID' has many records with a 'Status', So based on the individual 'Status' field i need to figure out the 'FINAL STATUS'

If      'All the Statuses' =      'Complete', then 'Complete'                                    
else if      'Some of the Statuses'      =      'In Progress', then 'In Progress'                              
else      'Not Started'                                          

How do i do this in PLSQL?  This is very urgent and help will be appreciated.
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.