Morning

☕

Morning

Morning

Oracle Database: Working with External Tables (youtube.com/watch?v=7MG4-UcCZY4)

unfortunately this seems to be referring to >11g

still struggling to find a simple explanation how I can read a csv into a temp table to use in a join when connecting to the PROD database

a csv stored locally (on windoze desktop), not uploaded to any server

on client, not on server

if I go through the "oracle sql dev" import wizard (e.g. docs.oracle.com/database/121/ADMQS/…), would this be "imported" only in memory / on the client / my local machine,

or

would it be imported-and-uploaded to the server/database I'm connected to (which I wouldn't want - and hope I do not have the permission to do so, either)

?

How can I check if the "oracle sql loader" utility is installed?

Is it part of "oracle sql developer" ?

5 different ways to load flat file into Oracle table (sqlandplsql.com/2017/02/16/…)

of the 5 ways the 4th ("import wizard" of "oracle sql dev.") seems most appealing intuitively

but before I give it a shot I'm still looking for some confirmation that the "import" is local-only (and won't mess with the server / the "real" database)

Importing and Exporting using the Oracle SQL Developer 3.0 (oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/…)

can't find a defintion of what is meant by "import"

could anyway help me get unstuck here?

please

dba-oracle.com/t_import_csv_file_into_oracle_table.htm

:(

Loading data from a CSV file into Oracle DB using Oracle SQL Developer (youtube.com/watch?v=XUotHrK41Iw)

3:15 / 5:23

"Destination Connection: Academic Oracle Service"

in the import wizard

that's what throws me off:

I don't want to import to my connection / to the database I'm connected to

I just want to use the contents of the csv to run a join-query vs a table from the connected database

stackoverflow.com/a/49077724/2153622 ("Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as persistent object in the data dictionary.")

that might be it? but it seems to be available only in newer versions (>18c; not in 11g :(

still stuck

@nuttyaboutnatty I think usually it means importing data into a database. If you are importing something into Oracle, then you are importing it into your Oracle database (the one that's handled by the Oracle RDBMS).

What do you mean by importing data locally only?

Where would it be stored? What software would be handling it as a low-level layer that provides you with the access to that data as a flat dataset (presumably)?

When speaking about using data handled by different platforms (e.g. Oracle and Access), I think there's a different term for that.

I'm unfamiliar with many concepts here

so thanks for bearing with me

by "locally" I mean just reading the contents of a csv stored on the local computer desktop

read into "memory" (runtime?)

I think, in PostgreSQL it's handled using the concept called Foreign Data Wrapper. In SQL Server you would use Linked Servers (although there are ways to provide raw connection attributes without persisting a connection as an object).

and not stored anyware on the "connected" database

Not sure what it's called in Oracle (don't remember of the top of my head) and I've certainly never heard about how it's handled in Access

no need for Access

I'm trying to migrate a cumbersome multi-stage sql-query sequence from Access to Oracle SQL Dev

in Access, everything is local

(in my set-up)

there is no "live" connection to any "real" database,

instead, the data is exported from the "real" database and imported to the local Access database

now I'd like to simplify this by avoiding Access altogether

(why, after all, use Access at all if the real data is elsewhere)

in the SQL in the Access database,

What's the real database where the data is exported from?

the first query is a LEFT JOIN from the "big table" (exported from the "real" database) and a flat-file

@AndriyM an oracle 11g database (?) to which I'm connected (with credentials, etc) via "oracle sql developer"

sorry if I'm struggling with the correct terminology/concepts

Oh, I thought the Oracle 11g database was the one where you wanted to use the exported data.

steep learning curve!

@AndriyM not really

I don't want to manipulate / touch any tables on the "real" database,

I just want to run queries on it,

in order to create reports

so, in other words, "READ-ONLY"

I don't want my csv file to be written to any server / database

I'm trying to use SQL to extract the info I need from the connected 11g database

but I don't want to add any info to the database

so I do not want to "import" (in the sense of WRITE to the database)

still unclear what I'm trying to do?

I'd open a proper question, but I fear my question is still not phrased "correctly"

So there may be some kind of initial query that extracts the subset of data that you are interested in, and then you want to be able to run other queries on that subset without "disturbing" (certainly without writing to) the source database?

kind of

I get the contents of the csv through other channels

I just want to use the csv in a LEFT JOIN when "talking to" the real database

in order to "filter" it the way I need to

think Venn diagram (towardsdatascience.com/…)

((in Access (everything stored locally, no connection to any other database hosted on servers), I'm running the LEFT JOIN on two imported local tables))

Okay, another attempt. You have a database, you get a CSV extract from it (doesn't matter how), you want to use the RDBMS that handles that database as an engine (to run SQL queries on the obtained CSV) without writing the CSV contents to that database first. And the reason you are thinking about that RDBMS is because you don't have any other, except Access, which apparently doesn't count

Close?

@AndriyM I run a select * from [the database/tables from the "real" database I'm connected to via "oracle sql dev"]

and get "real" / "live" data

then I have a separate, static, flat-file csv stored locally

As a file, OK

I wanna LEFT JOIN query these two tables (or query results) to give me a new table ( = query result), which I can use for a report

a report which I'll dump into a *** excel file to make certain people happy

I do not want to create any new tables on the server

I just wanna run sql queries on "live" data, combined with local-static data, to give me query results (= tables)

seems so trivial

I don't want to "import" (as in "upload") anything

The read-only requirement concerns not just the data itself (you don't want to upload the CSV into the database), but presumably also any meta-data (you don't want to create any objects in the database that would serve to just temporarily facilitate querying of the CSV), correct?

correct

I hope I only have READ-ONLY permissions with my RDBMS connection

the read-part works well

I can run several queries on the "live" data

I just wan to use a flat-file (csv) for some of these queries

I could, theoretically, copy-paste them into the queries I'm running,

but it would results in a super-long query (as in many thousands of rows)

SELECT ... FROM [live-RDBMS-database] LEFT JOIN [/tmp/local-csv-file.csv] ON .. = .. WHERE .. IS NULL;

not possible?

I mean, this should be solvable theoretically. An RDBMS should in theory be able to create an on-the-fly connection to a system ODBC driver capable of representing a CSV to the RDBMS in a standardised form. I think SQL Server uses the OPENROWSET function for that. No idea what, if any, counterpart to that Oracle has.

To which the answer appears to be creation of a Database Link, which probably fails the read-only requirement

see above? (chat.stackexchange.com/transcript/message/51378998#51378998)

That looks much closer as an equivalent

my "local" IT expert is still on vacation for ~ a week; if I come up with a solution to this I'll post a Q&A for dummies (like myself)

But it's 18c

exactly

@nuttyaboutnatty I haven't read all through this chat but if you want read only just use a dblink connecting with a user that only has read privileges

hmm

Morning!

morning

Morning

Can't remember an Ashes series that started this late in the summer.

@JackDouglas As an attempt at a quick recap, nutty has a CSV file and wants to use it as a table in a database while having read-only privileges in that database. Importing the data or creating any metadata objects to facilitate the connection is out of the question. Is that possible in Oracle 11g?

(sorry)

@AndriyM docs.oracle.com/cd/E11882_01/server.112/e22490/…

"The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database"

> External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.

That seems to defy the read-only requirement right there

they are read-only

oracle-base.com/articles/9i/external-tables-9i

"No DML can be performed on external tables but they can be used for query, join and sort operations."

I mean, would you be permitted to run that CREATE TABLE command without any issues?

As a read-only user

I guess the problem quickly turns into a fully blown question for the main

If it hasn't already

normally the external table is created by the DBA

Yeah, that might be the clincher. But I guess all that could be used as part of the question to help explain the problem and request for an alternative

@nuttyaboutnatty see above if it helps in any way ^^^

seems to me like a dblink to the live database and no csv intermediate might be worth considering

My impression is that the intermediary can't be helped

But let @nuttyaboutnatty speak for themselves

a readable standby might be another possibility

like you say, only @nuttyaboutnatty can know for sure!

Morning

Morning

@JackDouglas thank you guys! I guess I'll put it on a back-burner for a week or so until my local IT guy comes back; either way, will report back