Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Search for value in field across database

User_7WIPYSep 15 2021 — edited Sep 15 2021

Version 12c: simple really … how do I search for a value across all tables within a database?
example:
database name: Dallas
Multiple tables
searching for value : 956377
Search maybe for text or numeric value.
value may appear in multiple tables and multiple fields.

Comments

Christian.Shay -Oracle

I'm having trouble reproducing the autocommit issue. Did you see the words "Commit complete" in the results window after you executed the SQL?
Maybe you can help me reproduce this. Could you please provide me with some SQL that reproduces on your machine including any CREATE statements needed?
As for the connections open issue, we can't reproduce that either. Could you please execute:
 select count(*) from v$session where username = 'yourusername';
And then let me know how many VS Code tabs associated with our extensipon are open on your machine and how many connection nodes are in Oracle Explorer at the time you run that.

MagnusMG

I will try to collect this information.

MagnusMG

I have not had problems with failing autocommit again, so we can close this, at least for now.
VSCode still leaves a lot of connections open. When I executed your statement I got a count of 8 connections, even though I only had one tab open, and one database connected in the Oracle Explorer pane. (This is a bit annoying sometimes, but not a big problem.)
Thank you.

Thanks! We are considering a connection sharing feature for a future release for those customers with a limited number of connections available to them. In the meantime, if you run into issues with too many connections despite tabs being closed, you should try to right click on the connection in database explorer and select "Disconnect".
If that does not solve the problem, you could also try to close and reopen VS code itself. Let me know if any of that helps.

MagnusMG

The problem reappeared today. The message in VSC when I issue a commit is 'Commit complete.'
I have 5 connections open according to V$SESSION, 2 tabs open in VSC and 1 database connection open in VSC Oracle Explorer. (So, 2 connections that seems unnecessary.)
It is not easy to reproduce, and there are many SQL statements behind this. In this particular case I first create a table, fill it with data, and then add more data using three MERGE statements. When I checked the table in SQL Developer, the merge statements had had not effect. After I issued a commit, I could see all the changes in SQL Developer.
I tried a simple repeat of this structure using the same tab in VSC:
CREATE TABLE FOO (
a NUMBER NULL,
b VARCHAR2(50) NULL
);
Issuing this I got the response 'table created' and 'commit complete'. I checked in SQL Developer and could see thet table FOO there.
So I inserted som data:
INSERT INTO FOO VALUES (1, 'aaa');
Again, I got the message data inserted and commit complete. I could see the data in SQL Developer.
Then I tried a merge:
MERGE INTO FOO
USING (
SELECT 1 AS a, 'bbb' AS b FROM dual
) SRC ON (FOO.a=SRC.a)
WHEN MATCHED THEN UPDATE SET
FOO.b=SRC.b
;
I got the response '1 row merged', but no 'commit complete'. I could not see the change in SQL Developer.
I issued a commit in VSC, got 'commit complete', and could see the change in SQL Developer.

Is there a problem with MERGE statements?

Christian.Shay -Oracle

Yes, this is a bug. I will report it. Thanks for tracking this down! Please let me know if you come across anything else that causes autocomplete to fail.

Christian.Shay -Oracle

Hi, I forgot to address the connection count issue you raised:
If the two tabs you mention are files (sql scripts) then a count of 5 in V$SESSION is expected, because we use 2 connections per file (one for the script, and one to do autocomplete/intellisense), plus the one connection in Oracle Database explorer. Please let me know if the two tabs are sql script files.
If a tab is opened via an object in Oracle Explorer, eg, "Open Package Body", then we use two connections total, since we share a connection for all code objects to do autocomplete/intellisense. Eg, if you had 10 code objects open for the same connection in DB Explorer, we would still only use 2 connections total.
In the next release we hope to use connection sharing for files as well so you should see your connection count cut nearly in half.
Thanks,
Christian

MagnusMG

Hi,
Yes , they were SQL script files.
Glad I could help with tracking a bug down! I should also say that since I now know that the message 'commit complete' is crucial and not always there, it is quite easy to keep an eye out for it. I doubt that counts as a work-around, but still...
Thanks,
MagnusMG

We have reduced the connection usage in version 21.5 (available now) and are looking into reducing them further in future releases.

We have reduced connection usage even further in version 21.6. Please give it a try and let us know what you think.

1 - 10

Post Details

Added on Sep 15 2021
17 comments
4,309 views