@53:12 Useful Shortcuts 2 ctrl+shift+' hotkey for toggling between UPPER, LOWER, and INITCAP on highlighted text in worksheet ctrl + / hotkey for commenting in or out your line or highlighted lines of code in worksheet alt + pagedown and alt+pageup hotkeys for moving between worksheet and results grid (very helpful for when copying and pasting results while running queries)
@48:40 Useful Shortcuts 1. Hotkey for SQL History is F8 or View>SQL History From here you can move previous queries into your worksheet or remove them. 2. ctrl+space hotkey to fill in queries as you type them for table or column names. 3. ctrl+g hotkey to jump to a specific line in your worksheet 4. shift+f4 on object name in worksheet to give a floating window with even more information than the DESCRIBE command. Using this hotkey on a stored procedure or function will give you its code in a new tab. 5. shift+f5 hotkey for moving to the next tab to your left. shift+alt+f5 hotkey for moving to the next tab to your right 6. ctrl+tab+tab (hold ctrl and press tab twice while continuously holding ctrl) will take you to your last tab. If you continue to hold ctrl after you have pressed tab twice you will get a list of all of your tabs so you can toggle through them with tab.
@16:54 Using the Linegutter You can see your line and column at the bottom right of your SQL Developer window. Right click in your worksheet to the left of the dotted lines to turn on line gutter (toggle line numbers) Instead of trying to highlight a query with your mouse on the query text, start your click and drag at the line number in which your query begins and drag to highlight. If you go to Tools
@55:52 Using HTML If you concatenate html tags into your select list so that they surround an expression, the output will actually provide an html output. Alternatively, you can just use the opening tags and leave out the closing tags in your concatenation and you will get the same result. This includes img tags with a src. An example: SELECT ''||table_name name, FROM cat; An interesting use case is that you can place html code into the 'Display Null Value as' bar located at Tools>Preferences then click Database and then click Advanced. This can allow you to display an html image for example when a NULL is present in your data.
@1:02:51 Displaying Gauges Here is a hard-coded example (you can also do this dynamically since there is string involved): SELECT column_name1, 'SQL:GAUGE:::::', column_name2; In the above example, the min is the minimum value you want your gauge (a bar graph) to display and max is the maximum value. Low and High Threshold control where the bar graph will be colored to represent ranges of values in the low and high range and, if you leave an interval between the low and high threshold, there will be a third area colored in between the low and high threshold. values_the_gauge_needs is the expression you will use to feed the gauge values to display. For example: SELECT last_name, 'SQL:GAUGE:1:25000:2500:15000:' || salary, column_name2;
@19:05 Formatting Code ctrl+f7 hotkey to auto format all of your code in your worksheet (or highlight specific query(-ies) first before using the hotkey to only format a select amount of code) You may not like the autoformat above so you can change the output of ctrl+F7 by doing the following: Tools>Preference: Click Database (or possibly Code Editor and, if so, then click 'Format' and then 'Advanced Format') Then click SQL Formatter and then Oracle Formatter.
@29:32 Exporting Data Simply highlighting the data and then using ctrl+c and pasting somewhere with ctrl+v does not give you the column header. Instead, if you use ctrl+shift+c, you will also copy the column headers. You can also right click on any selection of data and then click 'Export" and this will bring up the export wizard. You can then export as your data as insert, html, or pdf. HTML and INSERT are two examples that can be particularly useful in some use cases. Also, you can insert a /* csv */, /* html */, /* insert */ and other comments immediately before your column list in the SELECT list and then run a script in order to output in the format of your comment's keyword. You can use any keyword from the export wizard except pdf and excel. If you use a spool command followed by a file path immediately before a SELECT statement as per the paragraph immediately above this one, you can export this as a file on a machine. Finally, you can export a table by right-clicking it in the connections window and clicking export.
@8:53 Creating database connections Connection color important to consider 1521 is default port of the Oracle listener xe is default instance name of express edition test before save
@11:39 Disabling Unneeded Features Unneeded features affect application performance. Furthermore, it can affect security. Tools>Features Starting with Database, consider disabling the following: Data Miner Oracle OLAP APEX listener Admin. Change Management Database Cart RESTful Services Admin. Spatial Unit Test XML Schema Within Database Migrations, consider disabling the following: Everything (if you don't work with migrations) Within Version Control, consider disabling the following: Everything (but Data Modeler depends on Subversion so may not be able to disable it) Apply Changes Tools menu will be shorter
@14:30 Executing Commands and Scripts ctrl+enter hotkey for 'run statement' A script is when you want to run two or more statements consecutively in one shot instead of individually. F5 hotkey for 'run script' You can reference an external script (one that is not present within your worksheet) by using: @[then path to the file present on your maching].sql
@42:00 Generating Relational Diagrams First of all, you can see the details of a table by finding it in the connections window and clicking on it. Alternatively, a hotkey for this is to hold down ctrl while clicking on the table's name within the worksheet window. Within this details window there are a number of tabs that are helpful. Clicking on the 'model' tab will provide a relational diagram of the given table and right clicking on one of the tables in this diagram will provide the option to 'show parent and child tables' which brings other tables that depend on that table into the diagram. Another way to make a relational diagram is to click ViewImport>Data Dictionary and then clicking the connection you want and the objects you want to include. Finally you can click File>Data Modeler>Print Diagram in order to create a file with your diagram for printing.
Hello, i'm using a MacOS but never manage to make Oracle SQL Dev. tool working on it... i always get an error message when trying to add a DB. Like this: "The Network Adapter could not establish the connection"
@24:15 Displaying NULLs in a special way Tools>Preferences Then click 'Database' and then 'Advanced' Choose a color from the drop down 'Display Null Using Background Color'
Hello Sir, I can't connect to the database like you showed. What is the password you entered? I entered the password of my oracle acc. Still unable to connect t9 database. Any other way to connect to local database?
I had sql developer tool working like a charm for years. Recently I got a new laptop and got sql developer installed and now it became too slow and it takes almost 30 minutes to run a simple select query. Do you see any configuration change could fix this issue?
Great lecture, thank you so much for your work! I have a question, when I use oracle sql at work, why tables showing filtered and not showing? How to make them appear?
Where can we get username and password.Do we have to put randomly any username and password or we have to create it from somewhere!Please sir help me out
how can i solve this error ? Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=GjNCEiZvSz67JdYsugt2/Q==)
Wonderful set of tips and tricks, but I have a question about SQL Editor Code Templates in Version 19.2.1.247 of SQL developer. Even though I tried to do a ssfw and parameterized it, it is not working the same way as you have shown us in this video with older version. I am unable to jump between parameters via Tab and I need to use my mouse to actually point over each parameter and manually fill it... Do you know what might have changed with the newer version of the SQL developer...? Thanks.
![[#2024MAMA] ROSÉ (로제), Bruno Mars - APT. | Mnet 241122 방송](http://i.ytimg.com/vi/dgGqD28J6aQ/mqdefault.jpg)
@53:12 Useful Shortcuts 2
ctrl+shift+' hotkey for toggling between UPPER, LOWER, and INITCAP on highlighted text in worksheet
ctrl + / hotkey for commenting in or out your line or highlighted lines of code in worksheet
alt + pagedown and alt+pageup hotkeys for moving between worksheet and results grid (very helpful for when copying and pasting results while running queries)
Hello,
I can not cennect to database. Where did you create username and passwrd? Thank you!
@48:40 Useful Shortcuts
1. Hotkey for SQL History is F8 or View>SQL History
From here you can move previous queries into your worksheet or remove them.
2. ctrl+space hotkey to fill in queries as you type them for table or column names.
3. ctrl+g hotkey to jump to a specific line in your worksheet
4. shift+f4 on object name in worksheet to give a floating window with even more information than the DESCRIBE command.
Using this hotkey on a stored procedure or function will give you its code in a new tab.
5. shift+f5 hotkey for moving to the next tab to your left.
shift+alt+f5 hotkey for moving to the next tab to your right
6. ctrl+tab+tab (hold ctrl and press tab twice while continuously holding ctrl) will take you to your last tab.
If you continue to hold ctrl after you have pressed tab twice you will get a list of all of your tabs so you can toggle through them with tab.
@16:54 Using the Linegutter
You can see your line and column at the bottom right of your SQL Developer window.
Right click in your worksheet to the left of the dotted lines to turn on line gutter (toggle line numbers)
Instead of trying to highlight a query with your mouse on the query text, start your click and drag at the line number in which your query begins and drag to highlight.
If you go to Tools
6 years after, your video is still very useful! Thank you for sharing!
@55:52 Using HTML
If you concatenate html tags into your select list so that they surround an expression, the output will actually provide an html output. Alternatively, you can just use the opening tags and leave out the closing tags in your concatenation and you will get the same result. This includes img tags with a src.
An example: SELECT ''||table_name name,
FROM cat;
An interesting use case is that you can place html code into the 'Display Null Value as' bar located at Tools>Preferences then click Database and then click Advanced. This can allow you to display an html image for example when a NULL is present in your data.
@46:30 Navigation Filtering and Zebra Pattern
Tools
@1:02:51 Displaying Gauges
Here is a hard-coded example (you can also do this dynamically since there is string involved):
SELECT column_name1, 'SQL:GAUGE:::::', column_name2;
In the above example, the min is the minimum value you want your gauge (a bar graph) to display and max is the maximum value. Low and High Threshold control where the bar graph will be colored to represent ranges of values in the low and high range and, if you leave an interval between the low and high threshold, there will be a third area colored in between the low and high threshold.
values_the_gauge_needs is the expression you will use to feed the gauge values to display. For example:
SELECT last_name, 'SQL:GAUGE:1:25000:2500:15000:' || salary, column_name2;
@19:05 Formatting Code
ctrl+f7 hotkey to auto format all of your code in your worksheet (or highlight specific query(-ies) first before using the hotkey to only format a select amount of code)
You may not like the autoformat above so you can change the output of ctrl+F7 by doing the following:
Tools>Preference:
Click Database (or possibly Code Editor and, if so, then click 'Format' and then 'Advanced Format')
Then click SQL Formatter and then Oracle Formatter.
@29:32 Exporting Data
Simply highlighting the data and then using ctrl+c and pasting somewhere with ctrl+v does not give you the column header.
Instead, if you use ctrl+shift+c, you will also copy the column headers.
You can also right click on any selection of data and then click 'Export" and this will bring up the export wizard.
You can then export as your data as insert, html, or pdf. HTML and INSERT are two examples that can be particularly useful in some use cases.
Also, you can insert a /* csv */, /* html */, /* insert */ and other comments immediately before your column list in the SELECT list and then run a script in order to output in the format of your comment's keyword. You can use any keyword from the export wizard except pdf and excel.
If you use a spool command followed by a file path immediately before a SELECT statement as per the paragraph immediately above this one, you can export this as a file on a machine.
Finally, you can export a table by right-clicking it in the connections window and clicking export.
This is the best Oracle SQL tutorial 👍
Why does everyone skip the part of creating the local database?
This one worked for me:
how to set up server: ruclips.net/video/GUpvXMHqe2U/видео.html
You tell
Excellent delivery of shortcuts
@8:53 Creating database connections
Connection color important to consider
1521 is default port of the Oracle listener
xe is default instance name of express edition
test before save
@11:39 Disabling Unneeded Features
Unneeded features affect application performance. Furthermore, it can affect security.
Tools>Features
Starting with Database, consider disabling the following:
Data Miner
Oracle OLAP
APEX listener Admin.
Change Management
Database Cart
RESTful Services Admin.
Spatial
Unit Test
XML Schema
Within Database Migrations, consider disabling the following:
Everything (if you don't work with migrations)
Within Version Control, consider disabling the following:
Everything (but Data Modeler depends on Subversion so may not be able to disable it)
Apply Changes
Tools menu will be shorter
The energy you have is contageous! I was excited when you were
excellent tutorial.. why so less view? highly recommend!!!! thank you sir. God Job!!!!!
@14:30 Executing Commands and Scripts
ctrl+enter hotkey for 'run statement'
A script is when you want to run two or more statements consecutively in one shot instead of individually.
F5 hotkey for 'run script'
You can reference an external script (one that is not present within your worksheet) by using:
@[then path to the file present on your maching].sql
@42:00 Generating Relational Diagrams
First of all, you can see the details of a table by finding it in the connections window and clicking on it.
Alternatively, a hotkey for this is to hold down ctrl while clicking on the table's name within the worksheet window.
Within this details window there are a number of tabs that are helpful.
Clicking on the 'model' tab will provide a relational diagram of the given table and right clicking on one of the tables in this diagram will provide the option to 'show parent and child tables' which brings other tables that depend on that table into the diagram.
Another way to make a relational diagram is to click ViewImport>Data Dictionary and then clicking the connection you want and the objects you want to include.
Finally you can click File>Data Modeler>Print Diagram in order to create a file with your diagram for printing.
Hello, i'm using a MacOS but never manage to make Oracle SQL Dev. tool working on it... i always get an error message when trying to add a DB. Like this: "The Network Adapter could not establish the connection"
@24:15 Displaying NULLs in a special way
Tools>Preferences
Then click 'Database' and then 'Advanced'
Choose a color from the drop down 'Display Null Using Background Color'
I have been using this video each time I wanna setup SQL since 2019 lmao
You can also use F9 to execute statements instead of ctr + enter
Sir, your presentation and selected topics for the video is really really great. Hats off. Thank you for the video.
I keep getting error. The network adapter could not establish the connection.
Hello Sir, I can't connect to the database like you showed. What is the password you entered? I entered the password of my oracle acc. Still unable to connect t9 database. Any other way to connect to local database?
Hi,
How did you find out the Username and Password to connect to local host?
AMAZING TUTORAL!!!
what an amazing course thanks man 👏
How do I connect to an existing connection? I am failing to do so.
I had sql developer tool working like a charm for years. Recently I got a new laptop and got sql developer installed and now it became too slow and it takes almost 30 minutes to run a simple select query. Do you see any configuration change could fix this issue?
Great, This is what i expected. Thanks 👌
I like this video, thanks a ton for taking effort. Is it possible for new video using toad for oracle?
Great video!
how to fix
ORA-01017 invalid username/password; logon denied problem
Thank you for all these tips! Awesome!
Great lecture, thank you so much for your work! I have a question, when I use oracle sql at work, why tables showing filtered and not showing? How to make them appear?
Are connections actually separate databases - i.e. if I make a table in one connection, it doesn't seem to show up in another connection?
Good explanation
Thank you so much, it was very useful.
Where can we get username and password.Do we have to put randomly any username and password or we have to create it from somewhere!Please sir help me out
I need that information too.
I m not sure but I think it's the password, you put when you download/install oracle *database*
@@toonkinga284 I have used it but it dosent work
Simply Awesome, these are really helpful, thank you :)
Hello, how can i just run a simple SQL Statement like " select getdate() "? The system show me a error "FROM is missing" . But Why
Well explained! Thank you!
Can anyone tell me which password should I use? I am beginner have no idea... I try but error came!
Great👍...thanks for sharing this information
Namaste
Oracle is offering free OCI database certifications till 28-Feb-2022
high recommend!!! Thank you!
Thanks , it's very useful
I couldnt able to use spool and /*CSV*/* from Sql developer.
Do i need to change any settings?
Awesome, amazing... Thank's a lot for share this!
Is a way to write a script for Data Pump Export in Oracle SQL developer?
hello, very good teacher....does exist an oracle sql course from same teacher?
why when i press CTRL + SPACE. II still reamis ? my query goes wrong because that.
regards,
Wow! 👏🏻👏🏻👏🏻 Great video sir.
how can i solve this error ? Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=GjNCEiZvSz67JdYsugt2/Q==)
Thank you very much
Thanks by the tips!
Very good explanation
Thank you. My respects to you Sir.
How can I increase the buttons and the code line???
Having problem with my databases using other databases fields
great video thanks
❤️❤️ Awesome explanation.
Thank You.
Wonderful set of tips and tricks, but I have a question about SQL Editor Code Templates in Version 19.2.1.247 of SQL developer. Even though I tried to do a ssfw and parameterized it, it is not working the same way as you have shown us in this video with older version. I am unable to jump between parameters via Tab and I need to use my mouse to actually point over each parameter and manually fill it... Do you know what might have changed with the newer version of the SQL developer...? Thanks.
Useful information, thanks.
Awesome mate. just awesome.
very useful info. Thanks!!!
What is the password how we can get the password of any connection no one is showing the full method to create a connection
hello my username and password work on the cli but not on sql developer plz answer ok
Excellent!
For creating "sys" connection what is the password
HI. CTRL+SPACE IS NOT WORKING WHEN WE ARE USING TEMPLATES.
This is a my SQL course or oracle SQL 19 c
Thank you
Is PL/SQL alternate to Oracle SQL Developer?
Real video begins @8:53
Thanks a ton
Thanks for the tips, its helps a lot!
I commented because I can't gave you 2 likes....lol
what is password for sys? our machine password?
cool. It is awesome.
非常好！
11:36
select /*html*/* from all_users; not working
This info was really helpful. Thanks!