modify records without rowid

[Daniele]

Hi,
it is possible to change the value of records without entering rownum in the select statement every time?

Select name, rownum
  from table

And when I export the result of a select, yis possible to do not include the rownum?

 

[mike]

Yes. For things like this, you can try it and see. I'll share a couple of tips that might help. If you already know, then just skip them.

PL/SQL Developer has a cool thing under the "Help/Oracle HTML Manuals" menu. If you set this up and search "update", it gives an example.

SELECT * FROM employees;

INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);

UPDATE employees SET salary = 9100 WHERE employee_id=1234;

Here is one other thing I'll share. It's useful for test databases if you just need a quick change. In the SQL window, if you select "for update", you can click the lock icon above the grid, make your changes press the lock again. I do a commit; after I make the changes. Like this:

-- Run this first and make the change
SELECT * FROM employees WHERE employee_id=1234 FOR UPDATE;

-- Run this after the changes. I'm not sure this is necessary, but I do it.
COMMIT;

Hope that helps!
Mike

 

[mike]

About, "And when I export the result of a select, yis possible to do not include the rownum?"

As far as I know, You can either highlight every column except the row number before exporting, or delete it in the worksheet after the export. Either way is kind of a bother for me, but that's how it is.

 

[Daniele]

I thank you for showing me some methods to insert records with sql language, I use these commands when I work with the sql plus program.
I was talking about PL/SQL Developer.

You can see the image below (you need Google Drive):
Image on Google Drive

It is normal to enter explicitly the rownum in the select statement also when I press the lock button to change the record?

For instance with the toad when I want to change the contents of a table I do not have to enter the rownum.

thanks.

 

[IvanZ]

Use "select ... for update" statement. Works in PLD in SQL window just fine. Keep in mind that it locks the table until you commit.

 

[T-Gergely]

While I prefer select... for update, it's rowid, not rownum that's used for row identification.

 

[mike]

Hi Daniele, The second code snippet was for the SQL Window in PL/SQL Developer like the picture you showed. IvanZ and T-Gergely did a better job getting to the point. I'll remember to be more succinct next time.

 

[Daniele]

Am I the only one thinking that the "lock" button should be able to edit the data without asking to enter the rowid or to write "select for update" (which locks all the recod selected and all my colleagues will be happy for this) or to write "UPDATE employees SET salary"....?

Is it the best user friendly solution?

 

[IvanZ]

That will require PLD to fetch rowid with every select implicitly which may not be the good idea.

You can use: Right click on table name -> Edit data. It will "do the rowid" for you

 

[Daniele]

And if PLD fetch rowid only when I press the "lock" button?
What is the problem if you fetch rowid implicitly?

 

[T-Gergely]

Most people don't us an RDBMS as a spreadsheet, so we use joins, views, functions, xquery, etc all the time. Implicit fetching of rowid is an OCI feature for the FOR UPDATE clause. It's not a good idea to reinvent the whole SQL parser in client applications.

 

[Daniele]

I did not mean to use an RDBMS as a spreadsheet.
I wanted to understand why it is required rowid when I push the "lock" button in the results of a simple query in the "SQL Window".
To solve my request is necessary to reinvent the whole SQL parser in client applications?

 

[IvanZ]

I wanted to understand why it is required rowid when I push the "lock" button in the results of a simple query in the "SQL Window"

PLD had to have some unique identifier of the row - to understand what you had changed in your result set.
Not every table in the world has primary key.

And when you have to modify 10 rows out of 100 billions - getting rowid is the only way to make it fast.
Not every table in the world has indexes.

Think about it carefully and you'll understand "why".

 

[T-Gergely]

I'm afraid this forum is not the place to get deep understanding of Oracle concepts. Fortunately, the RDBMS comes with quite good and thorough documentation.

 

[Hilarion]

The reason for this discussion is probably that it can be done. It works in Toad for example.
As far as I know, Toad allows it for simple select statements (where "from" clause contains only one table) on table that has primary key defined (or maybe even any unique constraint - not sure). What it does "under the hood" is constructing "update" statements with "where" clause based on values from all columns that are part of primary key. The case of "insert" statements is even easier.

I'm fine with PL/SQL Developer limitation in this matter, but can understand others wanting it - to make their life easier - as apparently it can be done and in fact it can be done quite easily.

 

[Daniele]

Thanks for your response, Hilarion.
I was afraid that we would talk about the importance of RDBMS documentation or about the 100 billions of rows that you can modify for the next hundred posts.

If my request is easily achievable (like is for the toad), as I thought, can you insert it in the upcoming releases?

Thank you very much.

 

[T-Gergely]

It works in Toad for example.

Would you explain how you can make it work in Toad? I just installed Toad for Oracle Trial Version and I can't make it work.
Even its online help states

Understand Editable Resultsets
A data grid is fully editable providing that the query itself returns a resultset that can be updated. Query statements must return the ROWID to be editable. For example:

Not Editable
select * from employee

Editable
select employee.*, rowid from employee

Notes:

You can substitute EDIT for SELECT * FROM. Toad translates it into the editable version of the statement. For example, edit employee returns the same result as select employee.*, rowid from employee.

 

[Daniele]

In the sql window of Toad:
- Write the name of the table, press f4 (describe the table)
- In the new window go to the data tab
- Here you can view all records and modify them, without rownum (also in the export data)

In PL/SQL developer:
- You can't modify records with describe, choice Edit Table (another window)
- Now choice Edit (another window)
- Finally you have a select written in the new window with the rownum in the select statement and rownum in the result set, now you can modify the record.

The steps, time and windows for to do the same operation are not the same.
In the results you don't have the rownum (with Toad).

 

[IvanZ]

Toad fetches rowid and doesnt show it to you.

PLD fethes rowid (when asked to) and shows it to you.
(Right click on the table name -> Edit data).

Some ppl like it TOADs way. Some ppl like PLDs approach.
Some stuff PLD does better, some does TOAD. I personally use both.

 

[T-Gergely]

In the sql window of Toad:
- Write the name of the table, press f4 (describe the table)
- In the new window go to the data tab
- Here you can view all records and modify them, without rownum (also in the export data)

Thanks, but this is nothing like what Hilarion mentioned.
It seems you still don't know the difference between rowid and rownum. They are completely different. Rowid is a pointer to the physical data, while rownum is just like a line number.
As IvanZ pointed out, Toad actually fetches rowid. And it dispalys it, too, as long as the checkbox Options/Data Grids/Data/Show ROWID in editable grids is checked.

What you really want has nothing to do with fetching rownum or rowid. What you want is some kind of a shortcut to edit table data. I don't know the later PSD versions, but I'd put it in the local menu that's shown when you click over a table name. Even Toad's virtual EDIT SQL statement could be added.

You may want an option to hide rowids. As PSD puts rowid in the last column (unlike Toad that either hides it or shows it in the first column) this option may not be so important for most people.

Note that Oracle describe in both Oracle Call Interface and SQL*Plus are read-only operations (as the word suggests), and I don't find it logical to mix that functionality with table data editing. While I personally don't need it, and don't find it logical to mix structure with content, a lazy-loaded Data tab could be added to View/Edit table.

Talking about shortcuts, you should be able to switch view table (or any other object) to editing easily. I don't find an option for this in PSD7.

 

[Daniele]

In this post we were talking about the possibility of including implicitly the rowid when you want to change the results of a select statement.
Even though you have said that this thing is impossible, I have shown you that this is possible in the toad (by using the describe).

Thanks, but this is nothing like what Hilarion mentioned

Even Hilarion was trying to tell you that this thing is possible, but it is probably completely different.

What you really want has nothing to do with fetching rownum or rowid. What you want is some kind of a shortcut to edit table data

I did not ask to include this functionality in a shortcut or "descibe" functionality.
It was just an example to make you understand that this thing is possible (in Toad).
I wanted to add this feature in the "lock" button because it is assumed that when the users presses this button they want to edit the record.

this option may not be so important for most people

This option can be useful for many people, certainly none of those known to you.

It seems you still don't know the difference between rowid and rownum

I apologize if sometimes confuse the word "rowid" with "rownum", I know the difference.

 

[mike]

You aren't changing the results of a select statement in TOAD, if you describe the table and go to the data tab. A select statement can return just one row, but the data tab in TOAD will return all data.

PL/SQL Developer does the same thing by right clicking the table name in a select statement and selecting "Edit data" just as IvanZ said. It's even simpler to do. It just shows rowid instead of hiding it. I don't see any advantage to hiding it.

 

[mike]

I can see that it might be nicer to not see the rowid since nobody will edit it anyway. Maybe, an option to hide the rowid could be added to the enhancement list?

 

[Daniele]

A select statement can return just one row, but the data tab in TOAD will return all data.

No, it isn't true, if you know and use the toad surely you know that you can filter the results of the data displayed in the data tab, as in any select statement.
In the filter option of the current table you can insert all where clause you know (a = b, like, in (), not in, , >=, decode, ..........)

How do they can show the contents of a table and let me filter it like:

Select s.ute, s.rowid
  from serviz s
 where s.name like ('%son%')

???????
It is magic!
You can not do this in Oralce, have you read the RDBMS documentation like T-Gergely?

I don't see any advantage to hiding it

I have already talked about the benefits in previous posts... time of execution, number of windows opened, export of results......

 

[mike]

So, what I hear you say is, with TOAD, you can:
1. Write a table name in an editor.
2. Press F4 to describe the table.
3. Select the data tab.
4. Click the Edit query button to edit the SQL how you like.
5. This is like magic and saves lots of time!

With PL/SQL Developer, either you:
1. Write a table name in an editor.
2. CTL-Click the table name.
3. Click the query button at the bottom.
4. Edit the SQL how you like.
5. This is horrible, because it opened a new editor, shows the rowid and wastes time.

or

1. Write a table name in an editor.
2. Right Click and select Edit data.
3. Edit the SQL to how you like.
4. This also is horrible, because it opened a new editor, shows the rowid and wastes time.

Is that correct?

 

[Daniele]

In this post we were talking about the possibility of including implicitly the rowid when you want to change the results of a select statement

Did you hear this?

A select statement can return just one row, but the data tab in TOAD will return all data.

Is this correct?

We want to do a study on the performance of the toad vs pl/sql developer???

In this post I want to talk about the possibility of inserting this improvement (no rowid required) in the "lock" button.

If you want you can open a post on the best performance of PL/SQL DEVELOPER vs TOAD and there I can tell you my opinion.

I get the impression that since you have found out that you can edit a record in a table without insert and view the rowid, you want to move the conversation to another topic.

I have the impression that the main purpose is disallow this thing and not to include this feature in pl/sql developer.

 

[Daniele]

So, what I hear you say is, with TOAD, you can:
1. Write a table name in an editor.
2. Press F4 to describe the table.
3. Select the data tab.
4. Click the Edit query button to edit the SQL how you like.
5. This is like magic and saves lots of time!

With PL/SQL Developer, either you:
1. Write a table name in an editor.
2. CTL-Click the table name.
3. Click the query button at the bottom.
4. Edit the SQL how you like.
5. This is horrible, because it opened a new editor, shows the rowid and wastes time.

The fact that you took the same time writing all these points does not mean that it takes the same time to execute.

Try doing what you said with both programs and you'll see that the execution time is not the same.

But I repeat, this is not the topic of the post.

 

[mike]

My personal purpose of replying are to:
1. Help understand how to use PL/SQL Developer.
2. Help understand what you are saying. It's been kind of a round-about understanding for me.

I think that we are saying the same things by now. You prefer to not have the rowid show up. It's true that I don't care if rowid shows up or not, but I did suggest that the feature be added to the enhancement list. I'm not trying to disallow anything. It's not my product.

As far as comparisons, I personally prefer PL/SQL Developer. I'm not going to trash talk TOAD though. I think they do a nice job. If you like TOAD, that's perfectly fine.

Anyway, I wish you well.

Mike

 

[IvanZ]

So you basically want an option to show/hide rowid column from Data grid (this can be done instantly).

That's OK.

 

[T-Gergely]

So, what I hear you say is, with TOAD, you can:
1. Write a table name in an editor.
2. Press F4 to describe the table.
3. Select the data tab.
4. Click the Edit query button to edit the SQL how you like.
5. This is like magic and saves lots of time!

With PL/SQL Developer, either you:
1. Write a table name in an editor.
2. CTL-Click the table name.
3. Click the query button at the bottom.
4. Edit the SQL how you like.
5. This is horrible, because it opened a new editor, shows the rowid and wastes time.

or

1. Write a table name in an editor.
2. Right Click and select Edit data.
3. Edit the SQL to how you like.
4. This also is horrible, because it opened a new editor, shows the rowid and wastes time.

Is that correct?

Thank you for this tutorial. I don't know these possibilities well, because I don't edit databases directly.
This thread seems a bit trollish, so I'm outta here. I'll probably start a new one about whether PSD/Toad can export a data grid to xls without rownum (or rowid?) in the first colum.

 

[mike]

T-Gergely - A coworker showed me just yesterday. In the SQL Window preferences, there is an option "Include row number with export and copy". Uncheck that and the row number is not in the worksheet. I never noticed that before!

 

[Daniele]

I think T-Gergely has read too many RDBMS manuals.
He knows that you can not update records without the rowid.
He will explain to you the difference between rowid and rownum...

 

[IvanZ]

T-Gergely - A coworker showed me just yesterday. In the SQL Window preferences, there is an option "Include row number with export and copy". Uncheck that and the row number is not in the worksheet. I never noticed that before!

This was introduced in PLD ver 10.0.4.
Release notes are also usefull to read )

 

[Daniele]

Thank you IvanZ but I'm not interested in the rownum.

we were talking about the possibility of including implicitly the rowid when you want to change the results of a select statement.

Do you have any suggestions for me about it?

 

[IvanZ]

Ask Marko for an option to hide rowid from the export results and copy.