Oracle SQL Developer: Product Demonstration

[@markparee99]

Actual demo starts at 17:10

[@Ambu_Singh]

My man was using zoom in 2017, truly ahead of his time.

[@JeffSmiththat]

Something....like that :)

[@rogerh2390]

THANK GOODNESS!! Someone who speaks english!!!

[@JeffSmiththat]

Thanks, I think?

[@cakimmell]

Any overviews of Oracle are mostly folks from India and are hard to understand. I have been searching for this for months.

[@RockstahRolln]

hahaha!! True!

[@rickcaldwell7425]

As a beginner, I want to say thanks for sharing. Info is greatly appreciated.

[@JeffSmiththat]

awesome, thanks Rick! I appreciate you took the time to watch this and leave me a nice comment :)

[@mardiseptianto7039]

that pink floyd one is the greatest part of the video :)

[@PlutoisaPlanet]

Just saw your blog post that SQL Dev is going to move to VS Code, fantastic! In the meantime I may just use sqlcl exclusively. I am running into a few bugs but they should be addressable.

[@JeffSmiththat]

SQL Developer 23.1 is going to come out soon with about 30-40 bug fixes in it. Stay tuned.

[@RC-nn1ld]

I’m coming from MS SQL Server world and trying out what it is like on the other side, and liking what I see!

[@JeffSmiththat]

you should find a lot of things in common, both being a rdbms. If you get stuck, please feel free to drop me a line anytime.

[@pavanmadamset]

Sir, Thank You Very Much For Making This Useful Video

[@JeffSmiththat]

Thanks very much for the note, I appreciate it.

[@volodymyrpatriiuk2291]

Definitely very useful lesson!

[@JeffSmiththat]

Glad to hear that!

[@DushmanthaRanasinghe]

Tablespace information and compression information can not be view in Table window in SQLdeveloper this is very useful for DBAs, when can we see it with the product ?

[@JeffSmiththat]

That information can be seen in Details page for your table if you have it open.

[@ps-ri2qk]

Great video Jeff, I have a question - using insight in the worksheet - selecting multiple columns to add to a statement - Using insight I can select multiple columns no problem but when I try add them it only pastes the last column I selected rather than 3 selected columns for example - tried enter, tab, spacebar etc. I can't find the correct key(s) to enter them all at once into the query. I couldn't see how you did it during your video, looked at KB shortcuts in settings, nothing about it online that I can locate in the forum, YT, etc.
Any assistance would be appreciated, Thanks! On Win11, Oracle 21c express, SQLDdev 23.1

[@JeffSmiththat]

Yeah, it's a bug. It's been logged already, sorry about that inconvenience. Possible workarounds, if you have table/view avail in the tree, you can drag and drop multiple columns. Or you could use SELECT *, then use the * expansion trick to get the actual column names, and remove the ones you don't want.

[@LennieLenford]

This video is over five years old but is still linked to from SQL Developer, is it still relevant? (Just upgraded from version 4...)

[@JeffSmiththat]

Yes, I think so at least. Most people don't get beyond running 'select * from employees' and exporting query results to Excel.
I plan on updating this video once our Extension for VS Code has been released.

[@George19_]

Thanks for this video Jeff, is very useful. One question, is there something that I can do to make Oracle SQL Developer run smoother on Windows? I'm not sure if it's because of Java, but the GUI is slow to load, the application itself hangs (freezes) while loading or performing certain tasks... it's no unusable but the experience is not good (It feels slow) compared to similar tools developed for Windows.

[@JeffSmiththat]

See my advice here, but also try running with Java 11 and with more memory for JVM www.thatjeffsmith.com/archive/2012/02/sql-developer-tastes-great-less-filling/

[@kalyangonela]

Hi Jeff,
How to get the view for Dataguard status under DBA in the latest version.

[@xucaen]

Hi, at 26:42, you say when you expand the connection you get a worksheet. That does not happen for me. I have installed Oracle database Express with SQL Developer version 17.3.1.279 build 279.0537. I'm not sure what I did to break the worksheet and I'm also not sure how to fix it.

[@JeffSmiththat]

Preferences > Database > Worksheet > Open a Worksheet on connect. Make sure that's enabled. If that doesn't work, try Window > Reset Windows to Factory Settings.

[@BrentDominic]

Hello! Do I need to install oracle database in order for me to create a connection? I am really confused on how to use it and how to create a database in SQL developer. I am completely new to this and hope you can help. Thank you!

[@JeffSmiththat]

You either have a database and a set of credentials you can use already..or you do not. I wrote up a more detailed answer with suggestions here
+ www.thatjeffsmith.com/archive/2012/12/i-have-oracle-sql-developer-installed-now-what/
+ www.thatjeffsmith.com/archive/2021/02/im-a-student-studying-oracle-and-i-need-a-database-help/

[@looploop6612]

Thanks
Is it worth taking oracle certification for a beginner?

[@sandeepkumar-h8b1w]

Hello Jeff im kanna here, where can i found this folder first and im usng oracle sql 11g am not getting a page like what to do and where i can find this all help me )-:🤕

[@JeffSmiththat]

Which folder are you looking for?

[@weiruquan1353]

Can I open all related properties of an object like toad, such as dependencies, usage, scripts, etc.

[@JeffSmiththat]

Yes, of course

[@KaterynaSTAR]

Could you say please, how to change size of tabs and additional windows? Thx

[@JeffSmiththat]

the doc tab size is determined by the length of the name of the file/object open for that tab...most if not all of the windows/panels can be re-sized with your mouse. Can you share an example of something you're struggling with? Happy to help ([e-mail])

[@sureshbe405]

Hi Jeff,
I just started to learn Oracle, I have downloaded Oracle SQL Developer Version 19.2.1.247 but I am unable to start working with it as I am unable to create connection at all. I checked many things on internet(youtube/webpages) but it did not help. Please guide how can i create connection and start working.
Please excuse me if i my request looks very silly, if so am sorry for that.
Thank You

[@JeffSmiththat]

One thing to remember up front - the learning curve for getting started with databases can be quite high. The biggest hurdle up front will be actually creating or getting access to a database. It's a task reserved for experienced professionals - so as a 'n00b', it can be extremely frustrating.
I wrote this article to help folks figure out the easiest way to 'bootstap' themselves medium.com/oracledevs/i-installed-oracle-sql-developer-now-what-71230b9c9303
I think the easiest method discussed in that article is our VirtualBox appliance. You simply download it, import it into VirtualBox (also free), hit 'play' - then you have a database you can immediately connect to from your sqldev on your machine, using any account with all passwords set to 'oracle'
Give it a look-see and feel free to come back or my blog (www.thatjeffsmith.com) with any questions.
And finally, congrats on starting your Oracle journey - good luck, and have fun!

[@sureshbe405]

Thank You Jeff.
will go through the link shared and let you know my if any hurdles.👍🙂

[@ahmedhamdyabdelnabi228]

thanks .. I have benefited from the video

[@JeffSmiththat]

Thanks!

[@baseerx]

when i create new connection using same username and password it copies existing connection data into new one connection..is it possible to create new connection with same username and password with no existing table data??

[@JeffSmiththat]

Baseer Khan if you have the same username then you're in the same schema... Create a new user and connect there for no tables/fresh setup

[@josephamiegbe4552]

thanks Jeff. I'd like to understand using the set server output on function. i tried an example i saw online but i get a pl/sql error message each time. thanks again

[@JeffSmiththat]

Joseph Amiegbe what is your error? Set server output on is a client command... you'd run that in sqlplus or sqldev...don't put in your function plsql code

[@josephamiegbe4552]

set serveroutput on
create or replace procedure coleman_employees
is
lv_name varchar2(20);
lv_position varchar2(20);
lv_yr_empyt number(4,0);
cursor coleman_employees_cursor
is
select name, position, yr_empyt
from coleman_employees;
begin
open coleman_employees_cursor;
loop
fetch coleman_employees_cursor into lv_name, lv_position, lv_yr_empyt;
exit when coleman_employees%NOTFOUND;
dbms_out.put_line ('employee' || lv_name || 'works as a' || lv_position || 'and was hired in' || lv_yr_empyt);
end loop;
end coleman_employees;
commit;

[@josephamiegbe4552]

Error starting at line : 3 in command -
create or replace procedure coleman_employees
is
lv_name varchar2(20);
lv_position varchar2(20);
lv_yr_empyt number(4,0);
cursor coleman_employees_cursor
is
select name, position, yr_empyt
from coleman_employees;
begin
open coleman_employees_cursor;
loop
fetch coleman_employees_cursor into lv_name, lv_position, lv_yr_empyt;
exit when coleman_employees%NOTFOUND;
dbms_out.put_line ('employee' || lv_name || 'works as a' || lv_position || 'and was hired in' || lv_yr_empyt);
end loop;
end coleman_employees;
commit;
Error report -
ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:

[@JeffSmiththat]

not sure why you're getting the 955, but I found other errors. Here is an equivalent to what you're looking for.
CREATE OR REPLACE PROCEDURE coleman_employees IS
lv_job_id employees.job_id%TYPE;
lv_name employees.first_name%TYPE;
lv_hire_date employees.hire_date%TYPE;
CURSOR coleman_employees_cursor IS
SELECT
first_name,
job_id,
hire_date
FROM
employees;
BEGIN
OPEN coleman_employees_cursor;
LOOP
FETCH coleman_employees_cursor INTO lv_name,lv_job_id,lv_hire_date;
EXIT WHEN coleman_employees_cursor%notfound;
dbms_output.put_line('Employee '
|| lv_name
|| ' works as a '
|| lv_job_id
|| ' and was hired in '
|| to_char(lv_hire_date, 'YYYY')
|| '.');
END LOOP;
END coleman_employees;
/
Use %type to bind your variables to the table.column definitions - if someone ever changes your table definition, your PL/SQL won't break.
Always use dates to store dates. Tranform the date to the format you want using the TO_CHAR function.
It's DBMS_OUTPUT not DBMS_OUT.
Execute this in SQLDev worksheet with
set serveroutput on
set sqlformat ansiconsole
clear screen
begin
coleman_employees();
end;
/
and you get back
Employee Steven works as a AD_PRES and was hired in 1987.
Employee Neena works as a AD_VP and was hired in 1989.
Employee Lex works as a AD_VP and was hired in 1993.
Employee Alexander works as a IT_PROG and was hired in 1990.
Employee Bruce works as a IT_PROG and was hired in 1991.
Employee David works as a IT_PROG and was hired in 1997.
Employee Valli works as a IT_PROG and was hired in 1998.
Employee Diana works as a IT_PROG and was hired in 1999.
Employee Nancy works as a FI_MGR and was hired in 1994.
Employee Daniel works as a FI_ACCOUNT and was hired in 1994.
Employee John works as a FI_ACCOUNT and was hired in 1997.
Employee Ismael works as a FI_ACCOUNT and was hired in 1997.
Employee Jose Manuel works as a FI_ACCOUNT and was hired in 1998.
Employee Luis works as a FI_ACCOUNT and was hired in 1999.
Employee Den works as a PU_MAN and was hired in 1994.
Employee Alexander works as a PU_CLERK and was hired in 1995.
Employee Shelli works as a PU_CLERK and was hired in 1997.
Employee Sigal works as a PU_CLERK and was hired in 1997.
Employee Guy works as a PU_CLERK and was hired in 1998.
Employee Karen works as a PU_CLERK and was hired in 1999.
Employee Matthew works as a ST_MAN and was hired in 1996.
Employee Adam works as a ST_MAN and was hired in 1997.
Employee Payam works as a ST_MAN and was hired in 1995.
Employee Shanta works as a ST_MAN and was hired in 1997.
Employee Kevin works as a ST_MAN and was hired in 1999.
Employee Julia works as a ST_CLERK and was hired in 1997.
Employee Irene works as a ST_CLERK and was hired in 1998.
Employee James works as a ST_CLERK and was hired in 1999.
Employee Steven works as a ST_CLERK and was hired in 2000.
Employee Laura works as a ST_CLERK and was hired in 1997.
Employee Mozhe works as a ST_CLERK and was hired in 1997.
Employee James works as a ST_CLERK and was hired in 1997.
Employee TJ works as a ST_CLERK and was hired in 1999.
Employee Jason works as a ST_CLERK and was hired in 1996.
Employee Michael works as a ST_CLERK and was hired in 1998.
Employee Ki works as a ST_CLERK and was hired in 1999.
Employee Hazel works as a ST_CLERK and was hired in 2000.
Employee Renske works as a ST_CLERK and was hired in 1995.
Employee Stephen works as a ST_CLERK and was hired in 1997.
Employee John works as a ST_CLERK and was hired in 1998.
Employee Joshua works as a ST_CLERK and was hired in 1998.
Employee Trenna works as a ST_CLERK and was hired in 1995.
Employee Curtis works as a ST_CLERK and was hired in 1997.
Employee Randall works as a ST_CLERK and was hired in 1998.
Employee Peter works as a ST_CLERK and was hired in 1998.
Employee John works as a SA_MAN and was hired in 1996.
Employee Karen works as a SA_MAN and was hired in 1997.
Employee Alberto works as a SA_MAN and was hired in 1997.
Employee Gerald works as a SA_MAN and was hired in 1999.
Employee Eleni works as a SA_MAN and was hired in 2000.
Employee Peter works as a SA_REP and was hired in 1997.
Employee David works as a SA_REP and was hired in 1997.
Employee Peter works as a SA_REP and was hired in 1997.
Employee Christopher works as a SA_REP and was hired in 1998.
Employee Nanette works as a SA_REP and was hired in 1998.
Employee Oliver works as a SA_REP and was hired in 1999.
Employee Janette works as a SA_REP and was hired in 1996.
Employee Patrick works as a SA_REP and was hired in 1996.
Employee Allan works as a SA_REP and was hired in 1996.
Employee Lindsey works as a SA_REP and was hired in 1997.
Employee Louise works as a SA_REP and was hired in 1997.
Employee Sarath works as a SA_REP and was hired in 1998.
Employee Clara works as a SA_REP and was hired in 1997.
Employee Danielle works as a SA_REP and was hired in 1999.
Employee Mattea works as a SA_REP and was hired in 2000.
Employee David works as a SA_REP and was hired in 2000.
Employee Sundar works as a SA_REP and was hired in 2000.
Employee Amit works as a SA_REP and was hired in 2000.
Employee Lisa works as a SA_REP and was hired in 1997.
Employee Harrison works as a SA_REP and was hired in 1998.
Employee Tayler works as a SA_REP and was hired in 1998.
Employee William works as a SA_REP and was hired in 1999.
Employee Elizabeth works as a SA_REP and was hired in 1999.
Employee Sundita works as a SA_REP and was hired in 2000.
Employee Ellen works as a SA_REP and was hired in 1996.
Employee Alyssa works as a SA_REP and was hired in 1997.
Employee Jonathon works as a SA_REP and was hired in 1998.
Employee Jack works as a SA_REP and was hired in 1998.
Employee Kimberely works as a SA_REP and was hired in 1999.
Employee Charles works as a SA_REP and was hired in 2000.
Employee Winston works as a SH_CLERK and was hired in 1998.
Employee Jean works as a SH_CLERK and was hired in 1998.
Employee Martha works as a SH_CLERK and was hired in 1999.
Employee Girard works as a SH_CLERK and was hired in 2000.
Employee Nandita works as a SH_CLERK and was hired in 1996.
Employee Alexis works as a SH_CLERK and was hired in 1997.
Employee Julia works as a SH_CLERK and was hired in 1998.
Employee Anthony works as a SH_CLERK and was hired in 1999.
Employee Kelly works as a SH_CLERK and was hired in 1997.
Employee Jennifer works as a SH_CLERK and was hired in 1997.
Employee Timothy works as a SH_CLERK and was hired in 1998.
Employee Randall works as a SH_CLERK and was hired in 1999.
Employee Sarah works as a SH_CLERK and was hired in 1996.
Employee Britney works as a SH_CLERK and was hired in 1997.
Employee Samuel works as a SH_CLERK and was hired in 1998.
Employee Vance works as a SH_CLERK and was hired in 1999.
Employee Alana works as a SH_CLERK and was hired in 1998.
Employee Kevin works as a SH_CLERK and was hired in 1998.
Employee Donald works as a SH_CLERK and was hired in 1999.
Employee Douglas works as a SH_CLERK and was hired in 2000.
Employee Jennifer works as a AD_ASST and was hired in 1987.
Employee Michael works as a MK_MAN and was hired in 1996.
Employee Pat works as a MK_REP and was hired in 1997.
Employee Susan works as a HR_REP and was hired in 1994.
Employee Hermann works as a PR_REP and was hired in 1994.
Employee Shelley works as a AC_MGR and was hired in 1994.
Employee William works as a AC_ACCOUNT and was hired in 1994.
PL/SQL procedure successfully completed.

[@jmalsalih1651]

Thanks Jeff.nice videos.

[@JeffSmiththat]

jmal salih You're very welcome!

[@MeliTX]

Help please! How do I change the script output view to a grid I can ctrl+c ?

[@JeffSmiththat]

two thoughts...
1 - don't use the script output if you want a grid, run the statement with Ctrl+Enter or
2 - use SET SQLFORMAT CSV, then when you copy the output, you can prob paste it into Excel and get the cell separation you're looking for

[@juliafagas1652]

Hi Jeff, when I try to create a connection I get the following error message
IO Error: The Network Adapter could not establish the connection
Can you please tell me what to do?

[@JeffSmiththat]

Julia Fagas tell me what you're doing: where is your database?

[@juliafagas1652]

Jeff Smith sorry I don't understand what do you mean. I filled in the fields for connection like in the video but I can't create a connection for some reasons

[@JeffSmiththat]

You need a database. SQL Developer is just a software application for working WITH Oracle Database - it doesn't include a database. You have a few options, all free, for getting there though. I talk about it here medium.com/oracledevs/i-installed-oracle-sql-developer-now-what-71230b9c9303

[@juliafagas1652]

I use JDK

[@aconejos]

Hello Jeff. You Talked about migrations white pappers for 3rd Party RDBMS. can you share that please?

[@JeffSmiththat]

Agustin Conejos sure thing, as soon as I get back to my pc

[@JeffSmiththat]

Start with this one www.oracle.com/us/products/database/migrating-to-oracle-database-wp-3610526.pdf

[@aconejos]

Genius :D

[@milkiasmarkos3294]

Thanks!

[@JeffSmiththat]

You're welcome!

[@suhasbyrapuneni4384]

SIR,THE PL/SQL PROGRAM I HAVE EXECUTED IS EXECUTING SUCCESSFULLY BUT OUTPUT WAS NOT DISPLAYING CAN U TELL ME REASON? PLZZ

[@JeffSmiththat]

What kind of output were you expecting? Or in other words, what is your program doing, and what did you tell it to with the results?

[@suhasbyrapuneni4384]

sir, the statments that i have typed in dbms_output.put_line command are not dispalying in out put....but it is just displaying that "your procedure is executed succesfully"...why sir?

[@suhasbyrapuneni4384]

i am getting "pl/sql procedure completed" in script output....but i am not getting output in dbms out even though i added my connection to dbms output.....

[@JeffSmiththat]

@@suhasbyrapuneni4384 did you
Set serveroutput on
?

[@suhasbyrapuneni4384]

yes sir....even though ntg in coming in dbms output....but its showing that pl/sql procdure is completed successfully....

[@DimarikCanada]

Hello, good video, do you have some videos for beginners ?

[@JeffSmiththat]

Dmitri Atanasov sure, see on my channel the videos on the query builder, desktop tour, and you're doing it wrong.

[@DimarikCanada]

Thank you Jeff, the second problem is to instal SQL Developer, it show me error with adaptor((

[@JeffSmiththat]

Dmitri Atanasov that sounds like a connection problem, not an installation issue. Can you read this and tell me which scenario you're in?
www.thatjeffsmith.com/archive/2012/12/i-have-oracle-sql-developer-installed-now-what/

[@DimarikCanada]

My error is 17002 network adapter

[@JeffSmiththat]

Dmitri Atanasov did you read my article, do you have a database?

[@chaitueditography5422]

how to solve network adapter error in oracle sql developer?

[@JeffSmiththat]

The client (SQLDev) needs a network path to where the Database is. So...can you ping that server from your machine? Do you have the right connection details?

[@chaitueditography5422]

@@JeffSmiththat yes

[@JeffSmiththat]

@@chaitueditography5422 then you simply can't access the network location specified

[@chaitueditography5422]

@@JeffSmiththat okay i will try

[@lindequan]

Why the SELECT always have under red mark?

[@JeffSmiththat]

It's either b/c the parser has found a syntax error, and it's seeing the issue at the SELECT (not likely) OR you're probably seeing the SQL Text Expansion feature. See this www.thatjeffsmith.com/archive/2019/06/gif-sql-text-and-expansion/

[@tedthebed7877]

Great

[@ibrahimmuhammad9509]

sIR,
I want to learn SQl DEVELOPER WITH YOU,

[@JeffSmiththat]

I have many tutorials for you here www.thatjeffsmith.com/sql-developer/

[@alfredsfutterkiste7534]

@@JeffSmiththat damn happy I expanded your response so I found this :)

[@birajadhikari2237]

I have only email on Oracle my Username is "biraj ad" there. However, it didn't work and gave me IO error. I am on linux.

[@JeffSmiththat]

Biraj Adhikari sorry, that's not enough information to help you. Where is your database, who maintains it?

[@birajadhikari2237]

It is my laptop so it is me. However, I don't remember setting any database password.

[@JeffSmiththat]

Biraj Adhikari did you create a database? Did you install the server software? SQL Developer isn't a database, just a client.

[@birajadhikari2237]

I am guessing no. Can you please tell me how do i do that?

[@JeffSmiththat]

Biraj Adhikari this should help medium.com/oracledevs/i-installed-oracle-sql-developer-now-what-71230b9c9303

[@sandeepkumar-h8b1w]

is this video for freshers or for working professionals >>>>>>>?????

[@JeffSmiththat]

Yes!

[@phanmemthanhphong]

Thank you so much !!!

[@JeffSmiththat]

you're very welcome!

[@myatsabai]

Is Data Miner also available in PL/SQL?

[@JeffSmiththat]

data miner has an intereface in Oracle SQL Developer, Data Miner also has at least 3 pl/sql packages for working with it, look at DBMS_DATA_MINING

[@myatsabai]

@@JeffSmiththat Thank you. Since I already have PL/SQL Developer installed and using, I just want to avoid installing SQL Developer again. It looks like SQL Developer is needed to utilize ODM.

[@JeffSmiththat]

@@myatsabai i'm not aware that Allround Automation's PL/SQL Developer product has an interface for Data Miner. Additionally, the Data Miner interface in SQL Developer is HUGE. It's not something you simply build...
Also, you just download, unzip, and run SQL Developer - about as easy as it gets.

[@myatsabai]

@@JeffSmiththat Thank you. I'm excited to do Analytic work within Oracle.

[@asiaapplications6446]

how to get model in the tab?

[@JeffSmiththat]

If you're on a newer version of SQL Developer and you're connected to an Oracle Database, when you open a table, you should just see the Model tab there, automatically.

[@igorigor2094]

how do you get a horse instead of null

[@JeffSmiththat]

a horse?

[@haedrichowen]

@@JeffSmiththat horse is at 17:31 in your query result

[@JeffSmiththat]

@@haedrichowen Ah, funny. I totally don't remember that. How to do that here - twitter.com/thatjeffsmith/status/1539628019639144453 .. just make sure you are using a Display Font that supports the Unicode characters.

[@haedrichowen]

@@JeffSmiththat Cool. And thanks for the great video! Still relevant today.

[@xamrinsupport3657]

Good, if they have also translation in Urdu

[@jeremyflowers8908]

Not fast paced enough. You take way to long to get to the details. Would be good if you'd bookmarked parts, so you could cut past the pre-amble. @17:41 to get past all the sales history waffle.

[@JeffSmiththat]

Jeremy Flowers I prefer the 10 minute takes for sure. But folks insisted on the big, everything take. I like your bookmark idea and will take a look at it today.

[@JeffSmiththat]

Done! Thanks for the input. This was a recording of a webcast I did, so it's a bit differently paced than what I would have done on my own, but I hope this helps.

[@roeundaroofficial]

Hellow teacher

[@JeffSmiththat]

Hello, let me know if you have any questions!

[@muhammadhafizbinmohdnasaru6689]

bababooey

[@Zifoxpiohmte]

pls coment some translate

[@JeffSmiththat]

what do you need?

[@sunandamishra592]

42:38

[@vaibhavpingalkar987]

Looks like Jeff was not prepared for the recording, not so informative one...waste of time

[@JeffSmiththat]

What were you looking for?

[@florisdevries4729]

Boring!

[@JeffSmiththat]

ruclips.net/video/YbBiXPVKuTA/видео.html
It's a development and query tool, not a video game.