Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV. begin for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT') loop begin execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2 select a.* from DDSHIST.||r.table_name|| a INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION WHERE b.GOLDEN_COPY = 'N''; exception when others then null; end; end loop; end; I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2. But I can't get the script to run and I don't know where I am getting this wrong. Any help would be appreciated. Thank you.

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'; tab1(1).col2 := 1; tab2(1).col1 := 'str'; tab2(1).col2 := 0.5; tab2(1).col3 := 6; o_result := some_function(io_error, 2,'str', 5, to_date('01-JAN-23','DD-MON-YY'), to_date('31-DEC-23','DD-MON-YY'), 4, 'A', tab1, tab2, 'str', 'B', 'str'); end; how to get the explain plan for pl/sql function calls where its inputs are dates and custom types?

**This question concerns Oracle PL/SQL (Oracle version 12c/19c) and Oracle SQL Developer (version 20.4).** I have a procedure which calls a dynamically generated SELECT statement and save results into sys_refcursor. I would like to print content of the refcursor within the procedure and show it in ouput window of Oracle SQL Developer. So far, I was able to only return the refcursor from procedure through OUT parameter and bind variable, and then print it. My current code of the procedure looks like this:

create or replace procedure cursor_show (rc_out OUT sys_refcursor) is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin           
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 
    rc_out := v_rc; --return refcursor
end;

To print results, I need to call these statements:

var x refcursor;
execute cursor_show (:x);
print x;

I would like to encapsulate print into procedure cursor_show to get something like this:

create or replace procedure cursor_show is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin            
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 

    print v_rc; --print data
end;

After that, I would be able to call the procedure and print the refcursor content with one-row statement execute cursor_show;. However, once I tried to compile such procedure, I received this error message:

Error(51,11): PLS-00103: Encountered the symbol "V_RC" when 
expecting one of the following: := . ( @ % ; 
The symbol ":=" was substituted for "V_RC" to continue.

Could you please advise how to call print statement within body of the procedure?

I'd like to know if there is a way to get the DML (insert, delete) SQL of uncommitted statements in Oracle 19c in my own session? For example, if I have typed some statements and I haven't committed them and I've closed my worksheet, can I get them back? Another example of this. Say I've done some deletes using the SQL Developer GUI, selecting the rows I want to delete and then clicking the delete icon (without haven't committed nor made a rollback yet). Is there a way I can get this as SQL statements? I thought I could get them in the *Log* but no... Surely there has to be a way but I haven't found it. Can you help me out?

Here is my code: create table failed_login_attempts( login_id number(5) primary key, date_created timestamp default systimestamp, email varchar2(110), ip varchar2(24), mac varchar2(18), attempt number(1), isLocked number(1) ); I want to count attempt column and if 5 then isLocked become 1 from 0; How can I make query, view or procedure?

Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks! SELECT S.PRODUCT_ID, SUM(DISTINCT S.AWARD_AMT), SUM(DISTINCT S.EST_AMT), SUM(PMT.PMT_AMT), S.CREATE_DT, S.APPROVE_BY, S.EVALUATOR FROM SALES S INNER JOIN INVOICE INV ON S.PRODUCT_ID = INVS.PRODUCT_ID INNER JOIN PAYMENTS PMT ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID AND INV.SEQ_NO = PMT.SEQ_NO WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' AND INV.STATUS_CD = 'CREATE' GROUP BY S.PRODUCT_ID order by S.PRODUCT_ID This was my final query. It worked. I had to remove the dups in excel SELECT S.PRODUCT_ID, S.CREATE_DT, S.APPROVE_BY, S.EVALUATOR , SUM(DISTINCT S.AWARD_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS S.AWARD_AMT, SUM(DISTINCT S.EST_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS EST_COST_TOT, SUM(PMT.LINE_AM PMT.PMT_AMT T) OVER (PARTITION BY OM.O_OMO_NO) AS TOTAL_PMT FROM SALES S INNER JOIN INVOICE INV ON S.PRODUCT_ID = INVS.PRODUCT_ID INNER JOIN PAYMENTS PMT ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID AND INV.SEQ_NO = PMT.SEQ_NO WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' AND INV.STATUS_CD = 'CREATE' and SUBSTR(APPROVE_BY,1,3) IN ('PEP','SMI', 'MOP','3/4') GROUP BY S.PRODUCT_ID order by S.PRODUCT_ID ERROR msg:ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 1 Column: 91

Maybe that's a wrong form of answer... The problem is: I've got an Oracle database dump file (very old, created with imp utility) At last, I've imported it. The command was imp file=E:\Downloads\db_dispatcher\disp.dmp full=y The console shows me there was success. Now when I open my Oracle SQL developer and create any connection, I can't list my database (there must be at least one): in 'tables' section I only see this system stuff. I the DB name should be 'meltshop', so I tried to find it in my Windows Services list. But there's none. Oracle SQL developer 19, Windows 10. DB was created in 2009. I am a newbie with Oracle( The last year I worked with MySql database. Maybe here are any similarities? I also have php interface; the config shows me $DBNAME = "/orcl.meltshop"; $DBLOGIN = "ml2_rt"; Instance, version, etc.: select instance_name, host_name, version from v$instance; INSTANCE_NAME ---------------- HOST_NAME ---------------------------------------------------------------- VERSION ----------------- xe DESKTOP-T6U2873 11.2.0.2.0 The log of my last try: google disk

I'm using SQL Developer to migrate a database from MySQL 5.7 to Oracle 18c XE. The problem is that the tables are generated but empty, even though I'm specifying that the data should move online. I also looked at the scripts, and there's no data either. What I see in the log is DataMove.DISABLE_CONSTRAINTS_FAILED. How to fix this? These are the migration options:

**Summarize the problem** I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change pctfree, pctused, initrans and max trans and not the others (initial, next, pctincrease and maxextents) **Provide details and any research** I have done a lot of research, yet some of them do not work with sql developer at all. Whilst the others do work but as stated only for the 4 storage parameters. **When appropriate, describe what you’ve tried** alter table CUSTOMERS pctused 20 pctfree 80; This works perfectly for those two, but I am unable to add the others. From what I found online, this was in fact the only thing that worked for me. I appreciate all input!

I have a field called FREQUENCY and it has values like '10,0;30'. How do I write a regular expression to get the result like this: FREQUENCY 10 0 30

Oracle SQL Developer hangs post launch gets hanged, we are using Oracle SQL Developer 23.1.1 and we have JDK 11.0.17 installed.

I've been requested to make a dump of an Oracle 11g EE Database. However, i can't use the Oracle Data Pump utility, since I have no direct access to the host machine, nor I think the db user have the correct privileges. So I opted for using the Database export utility that SQL Developer provides. But I do have the following doubts: - Am I actually viewing the correct encoding through SQL Developer? I'm asking this because I can see there's a lot of question marks inside some tables. I'm wondering if the application that saved the data used the wrong encoding, or if SQL Developer is using the wrong encoding and i'm not viewing it correctly. - How do I export with the correct encoding? I'm aware that SQL Developer has an utility that would allow the user to export a script file with both DDL and DML instructions, but it asks me to specify the file encoding. I've make some tests myself, but since i do not trust the tools i've used completely (EG: WinMerge does not recognise the Windows equivalent of ISO 8859-15, as far as i know), i'm wondering if anyone has been in my shoes and tried better. The internal encoding of the DB should be WE8ISO8859P15, which I think it is the equivalent of ISO 8859-15. When I try to use the Export functionality of SQLDeveloper, it asks me in which encoding the file should use. I've used this utility twice: exporting both a ISO 8859-15 Windows equivalent (can't remember it right now, i'll update this section asap) and a UTF-8 encoding. Both WinMerge and Meld tells me that the only difference between the files are the encoding (and not the characters)

I am using SQL Developer, generally to assist in writing reports against various databases but there is one application that is run on an annual basis and at the end of that processing (about a 3 hour window) I use FME to further process the records that were added during that time frame. I am limited to view only permissions on the Oracle table(s) and that is all I really need to do my job. There is a CRM who insists that during that 3 hour window when this particular application is being used by customers outside the organization no one within the organization should access any other parts of the application or any associated database tables. Not for any technical reason, I understand, but because the CRM is afraid that somehow using the tables during that time frame (customers outside the organization are uploading data during that 3 hour window) will somehow potentially cause a problem for the customers. Recently, in order to get a general idea of the number of records I would later be processing, I ran a small SELECT query. Unfortunately, I inadvertently ran it at the tail-end of the 3 hour window, a little while before the black-out period was scheduled to end. Apparently, the CRM had advised the DBA to report if anyone internally accesses the database during the applications 3 hour window. The CRM ended up at my desk screaming that no one within the organization was to be using the database in any capacity during the applications 3 hour window. My questions are the following: Does running a SELECT query on a view only table interfere with any other processes that might be updating the tables in a database? Or is the CRM being overly cautious? The CRM is apparently lobbying for me to be written up for this transgression. So I am looking for some technical information to support my defense.

I have downloaded SQL developer on my Mac running OS Sonoma 14.1.2 but as soon as i launch it it quits unexpectedly. I have installed latest JDK 11. I don't seem to find the reason for its crash. I executed sqldeveloper.sh in terminal and i encountered following error line 16: [: missing `]'/Library/Java/JavaVirtualMachines/jdk-11.jdk/Contents/Home

I'm using Oracle SQL Developer Version 21.4.1.349 on Windows We have a large number of schemas. I only work with 3 of them. When I need to find something using the Find Database Object function, I only want to search those 3 schemas. I check just those boxes. Here's where it gets weird. Every time I quit and reopen SQL developer (which I occasionally need to do because it freezes randomly, or I need to restart my laptop), it still has 3 boxes checked... but they're the wrong ones. The checks shift upwards by 1 position. For example, if I had schemas named alpha beta gamma delta epsilon zeta eta theta and so on... and I cared about and checked gamma, epsilon, and eta. Upon a restart, the checked boxes will now be beta, delta, and zeta. It's not just a visual bug, searching will search the contents of the newly checked schemas instead of the ones I originally selected. Has anyone run into this? Is there a fix? It's really annoying. Not as annoying as the random freezing, but it seems there's no fix for that one. Thank you!

I like to see any sql statement in the Oracle Enterprise Manager (oem) Cloud Control 13c, e.g. in the active session history. A simple select is not shown in any view. After some research I found in the manual (e.g.: https://www.oracle.com/technetwork/database/manageability/owp-sql-monitoring-128746.pdf) , that **SQL monitoring is automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution**. So from my point of view I must be sure that an SQL statement runs at least 5 seconds to appear in the oem. Can I do this by an addition in the SQL statement (like an sleep), or can I change the default in the oem? Is there an best practice for this? Any help is appreciated Update: Thanks to Paul W : /*+ monitor */ did the trick. select /*+ monitor */ * from table; makes the query appear in the oem.

Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?

is it possible to use the select statement result as where clause statement/Condition in oracle SQL? Eg:

Condition_txt(FIELD) = 'AND 
b.mpal03d >= TO_DATE('01/10/2012','DD/MM/YYYY')' 
(Value of field)

I'm used to Ctrl-N to give me a new query window in SSMS. How do I do it in Oracle?

I am using a windows laptop and installed sql developer version 23.1.0. When I input the connection details for my database I get the following error: >a connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond My colleagues, using a macbook, are able to connect using the same creds. Anyone have any idea what is going on? I have searched the web but nothing is helping so far. Connection to the oracle database should be successful.