Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Trying to understand this quiz question on INSERT

S
shizuka last edited by

Why will there be 2 rows and not 1? The rollback is meant to take it back to the last COMMIT function which in this case is inputted after one INSERT
Reply Quote 0
1 Reply Last reply
J
jonetta last edited by
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COMMIT.html
Oracle Database issues an implicit COMMIT under the following circumstances:
- Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error
- After any data definition language (DDL) statement that completes without an error
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

A

RMAN - Fail To Restore Oracle DB with CDB and PDB
SQL, Database Testing • oracle • • authera

2

0
Votes

2
Posts

1
Views

B

The error message is a bit confusing. Normally this is solved by feeding a few extra archives to the recovery process and issue a cancel. Do this using sqlplus. Let it recover an archive and when it asks for the next archive issue 'cancel'. Most of the times the database will open.
S

Insert a large TEXT. Is INSERT the only way?
SQL, Database Testing • mysql blob • • shizuka

2

0
Votes

2
Posts

0
Views

M

I recommend avoiding putting more than 16MB (perhaps less) in a column in MySQL. There are many limitations in MySQL and possible in the companion tools you are using. For storing Text (as opposed to blobs) in a column, I would strongly consider compressing it. This is likely to shrink the data by 3x. Do the compression in the Client, not the Server. What is the Text for? How will be it be used eventually? An analogy: with images (which must be stored in BLOB/MEDIUMBLOB/LONGBLOB), it can be better to store them as files when the use is HTML's . This makes the fetching trivial. I once had the task of storing large data, even multi-GB movies. Obviously, I could not even use LONGBLOB (4GB limit). I chose to break the stream into 50KB (pick a number) chunks and store them in a BLOB in rows of a table. Together with it was a sequence number to allow reconstructing the result. This allowed me to "stream" things rather than "store (or fetch) it all at once". Hence I was not straining the limits of the client software (which was PHP and Apache). As for inserting blob (or compressed text), it may be optimal to convert it to hex, then use UNHEX("...") in the INSERT statement. This completely avoids any issue of escaping strings, etc. (But does not avoid SQL Injection problems.) It does, however, cut in half some of the limits you may encounter.
Specify INSERT columns with query
SQL, Database Testing • postgresql • • briley

2

0
Votes

2
Posts

0
Views

M

Altering a columns position is going to require a full table rewrite. My suggestion is not to alter column positions. However, there is an abundance of people doing this and there are numerous ways to do it. In my experience, one way is far better and easier, if you have the privileges, and most DBAs have those privileges. My suggestion is to use pg_dump. Dump with --column-inserts Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Remove from dump what you don't need. Modify table definition or ordering. In a transaction, Drop the old tables Execute the script creating the new tables. Commit
O

What is the best way to make a current contract selection and previous employee contract in the same row?
Software Programming • sql postgresql oracle orac languagele 11g • • obi

2

0
Votes

2
Posts

0
Views

S

Functions type windowingThere are windowing type functions to do these tasks: https://www.postgresql.org/docs/current/static/functions-window.html Function lag Gives you the top line and lead The bottom line. If you just want to show the last line (with the top one) you have to put a select within another to filter at the end. The example goes to understand:select * from ( select empleados.*, ciudades.ciudad, contratos.contract_id, contratos.start_date, contratos.end_date, lag(contratos.contract_id) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior, lag(contratos.start_date) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior_start_date, lag(contratos.end_date) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior_end_date from contratos inner join empleados on contratos.employee_id=empleados.employee_id left join ciudades on ciudades.city_id = empleados.city_id ) select_base_con_todo_junto where end_date is null; full versionI put a full version with the inserts and create table in: http://rextester.com/EPM24088
S

Help me understand sp_server_diagnostics
SQL, Database Testing • sql server sql server 2016 • • shizuka

2

0
Votes

2
Posts

0
Views

K

Ensure your servers follow VMware's Best Practices Guide for SQL Server. Specifically, ensure the VMs running SQL Server have 100% of their memory and CPU reserved in VMware. Ensure you're using the PVSCSI (para-virtualized SCSI) adapter for SQL Server RDMs or Virtual Disks. Try to use as many PVSCSI adapaters as you can since that will increase the number of disk queues available to SQL Server.
Getting catagory title total number of questions and answers order by number of questions
SQL, Database Testing • mysql 5.7 • • Demir

2

0
Votes

2
Posts

0
Views

Z

SELECT @no := @no + 1 AS no ,catagory ,number_of_questions ,number_of_answers FROM ( SELECT c.title AS catagory ,COUNT(DISTINCT q.id) AS number_of_questions ,COUNT(a.id) AS number_of_answers FROM material_univarcity_list_content AS c LEFT JOIN user_questions AS q ON q.note_id = c.id LEFT JOIN user_answers AS a ON a.question_id = q.id GROUP BY c.title ) AS t1 CROSS JOIN (SELECT @no := 0) AS t2 ORDER BY number_of_questions DESC; Since you use MySQL 5.7 which doesn't support window functions like ROW_NUMBER you need to use user variables to emulate it.
P

SQL Trigger Question
SQL, Database Testing • sql server trigger • • Pearlaqua

2

0
Votes

2
Posts

0
Views

A

The whole code of your trigger is weird. Why checking if SELECT COUNT(*) FROM INSERTED?, I mean, if the trigger is running is because you inserted some row on the table. Then, you are trying to assign posibly multiple values on a variable, and joining with a CTE when it really doesn't seem necessary. The code seems that it can be replaced with just: CREATE TRIGGER [dbo].[SENDTXT_MSG] ON [dbo].[db_log] after INSERT AS BEGIN INSERT INTO test(test) SELECT object_name FROM INSERTED WHERE msg_type = 'error'; END
T

MySQL Insert with Select
SQL, Database Testing • mysql • • Trenton

2

0
Votes

2
Posts

0
Views

J

Please try this: INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref) SELECT a.class_ref, b.teachers_ref, c.section_ref FROM (select class.id as class_ref from class where class.name = 'pe') as a, (select teachers.id as teachers_ref from teachers where teachers.last_name = 'lenevo') as b, (select section.id as section_ref from section where section.name = 'grade 4') as c; I assume it will be quiet slow, but should to the trick.
J

How do you use FUNCTION for the cycle?
Software Programming • sql oracle functions plsql • • juvenalb

2

0
Votes

2
Posts

0
Views

R

The definition of functions in CTE is not available for static requests in PL/SQL context.For the designated OBD facilities, the function should be defined either on the specification of the package or as a SQL function.It is also possible to fulfil the same request as dynamic SQL. For anonymous blocks, it's even preferable because they themselves are dynamic requests. For example:declare type nt is table of number; nums nt; begin execute immediate q'[ with function f(x in number) return number as begin return x*2; end; select f (rownum) r from xmlTable ('1 to 3')]' bulk collect into nums; for i in 1..nums.count loop dbms_output.put_line (nums(i)); end loop; end; / 2 4 6
J

Error Apos run procedure: no exibi output
Software Programming • oracle pl sql • • jonetta

2

0
Votes

2
Posts

0
Views

D

Sql DeveloperMenu Exibir Saída DBMS Botão **+** Escolher conexão In this window will display the outputs.
S

How to insert values into a junction table?
SQL, Database Testing • foreign key primary key relational theory • • shizuka

2

0
Votes

2
Posts

0
Views

F

If I guessed correct you are using PHP PDO, so what your are looking for is this function. http://us3.php.net/manual/en/pdo.lastinsertid.php. It will return the id off the last insert tuple into the database which you can save in a variable to be used in the insert for the junction table. $stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)"); $stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']); $stmt->execute(); $book_id = $stmt->insert_id; $stmt->close(); $stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)"); $stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']); $stmt->execute(); $cover_id = $stmt->insert_id; $stmt->close(); $stmt = $sqli->prepare("INSERT INTO book_main_cover VALUES(?,?)"); $stmt->bind_param($book_id, $cover_id); $stmt->execute(); $stmt->close();
S

How to find out the segment behind a corrupted block when the database cannot be opened?
SQL, Database Testing • oracle orac languagele 10g • • Saumya

2

0
Votes

2
Posts

0
Views

Y

Well, I guess the database is expendable since there is no proper backup. So just drop and recreate the database. OK, seriously there are not many options left. Before you do anything shutdown the database and copy all files which belong to the database somewhere else! Because everything you do can make it worse. Once you did that you have 2 options: Open an Oracle Service Request. With their help try to restore the block with lots of tricks and magic. Call the Oracle Consulting Team, they have unpublished/internal tools which might be helpful. This database is not in archivelog mode so there is no option to restore the block from a backup. If you still have an rman backup of datafile 1 you might be able to restore this file and then open the database with _allow_resetlogs_corruption (Don't use this option on your own!). Because you cannot fully recover the block, you have to tell Oracle that you don't care that the datafile do not have the same SCN. See details here: https://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive-log-required-for-recovery-is-missing/ If this is a block which contains standard objects you might be able to extract the block from a healthy database and inject it on this block. Good luck!
K

Conditionally OR vs CASE vs IF
SQL, Database Testing • sql server oracle • • Kadyn

2

0
Votes

2
Posts

0
Views

In sql-server: One option is dynamic sql, another is option (recompile). using option (recompile): select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile); dynamic sql example: declare @sql nvarchar(max); if nullif(@MyParam,0) is not null begin; set @sql = 'select * from MyTable where MyColumn=@MyParam;' exec sp_exeuctesql @sql, '@MyParam int', @MyParam; end; else begin; set @sql = 'select * from MyTable;' exec sp_exeuctesql @sql; end; Reference: Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White Dynamic Search Conditions - Erland Sommarskog Catch-all queries - Gail Shaw An Updated "Kitchen Sink" Example - Aaron Bertand
J

Simplification sql of Oracle request
Software Programming • sql oracle • • Jolied

2

0
Votes

2
Posts

0
Views

B

Something like that:select * from (select zd.*, row_number() over (partition by org_id, vbtyp order by psting_date desc) rn from z_delivery zd) where rn = 1 and batch like '%' || :pipeYear || '-' || :pipeNumber and ORG_ID = :shopID and VBTYP = 'T' And the pole index PSTNG_DATE It'll be very nice if not yet.
Z

How can I fix this kind of error when testing?
Software Programming • java oracle while ec languagelipse • • zymir

2

0
Votes

2
Posts

0
Views

S

According to the https://docs.oracle.com/javadb/10.6.2.1/ref/rrefjdbc1020262.html keywords are sensitive to the upper and lowercase. And as you return a single result according to id, you should use execute() only.Note: see comments to understand step by step.read(int id): // obtener conexión a base de datos oracle String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "root"; String password = "root"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); // preparar CallableStatement String pro_reg = "{ call pro_reg(?, ?, ?, ?) }"; CallableStatement cs = conn.prepareCall(pro_reg); // definir parametro de entrada cs.setInt(4,id); // Registrar parametros de salir cs.registerOutParameter(1, java.sql.Types.INTEGER); cs.registerOutParameter(2, java.sql.Types.VARCHAR); cs.registerOutParameter(3, java.sql.Types.INTEGER); // ejecutar procedimiento cs.execute(); Registro f = new Registro(); //clase debería estar capitalizada //obtienes parametros y los seteas a tu registro f.setId(cs.getInt("cod_bus")); f.setPlacas(cs.getString("placa_bus")); f.setViajes(cs.getInt("count(*)")); return f;

Trying to understand this quiz question on INSERT

Suggested Topics

RMAN - Fail To Restore Oracle DB with CDB and PDB SQL, Database Testing • oracle • • authera

Insert a large TEXT. Is INSERT the only way? SQL, Database Testing • mysql blob • • shizuka

Specify INSERT columns with query SQL, Database Testing • postgresql • • briley

What is the best way to make a current contract selection and previous employee contract in the same row? Software Programming • sql postgresql oracle orac languagele 11g • • obi

Help me understand sp_server_diagnostics SQL, Database Testing • sql server sql server 2016 • • shizuka

Getting catagory title total number of questions and answers order by number of questions SQL, Database Testing • mysql 5.7 • • Demir

SQL Trigger Question SQL, Database Testing • sql server trigger • • Pearlaqua

MySQL Insert with Select SQL, Database Testing • mysql • • Trenton

How do you use FUNCTION for the cycle? Software Programming • sql oracle functions plsql • • juvenalb

Error Apos run procedure: no exibi output Software Programming • oracle pl sql • • jonetta

How to insert values into a junction table? SQL, Database Testing • foreign key primary key relational theory • • shizuka

How to find out the segment behind a corrupted block when the database cannot be opened? SQL, Database Testing • oracle orac languagele 10g • • Saumya

Conditionally OR vs CASE vs IF SQL, Database Testing • sql server oracle • • Kadyn

Simplification sql of Oracle request Software Programming • sql oracle • • Jolied

How can I fix this kind of error when testing? Software Programming • java oracle while ec languagelipse • • zymir

RMAN - Fail To Restore Oracle DB with CDB and PDB
SQL, Database Testing • oracle • • authera

Insert a large TEXT. Is INSERT the only way?
SQL, Database Testing • mysql blob • • shizuka

Specify INSERT columns with query
SQL, Database Testing • postgresql • • briley

What is the best way to make a current contract selection and previous employee contract in the same row?
Software Programming • sql postgresql oracle orac languagele 11g • • obi

Help me understand sp_server_diagnostics
SQL, Database Testing • sql server sql server 2016 • • shizuka

Getting catagory title total number of questions and answers order by number of questions
SQL, Database Testing • mysql 5.7 • • Demir

SQL Trigger Question
SQL, Database Testing • sql server trigger • • Pearlaqua

MySQL Insert with Select
SQL, Database Testing • mysql • • Trenton

How do you use FUNCTION for the cycle?
Software Programming • sql oracle functions plsql • • juvenalb

Error Apos run procedure: no exibi output
Software Programming • oracle pl sql • • jonetta

How to insert values into a junction table?
SQL, Database Testing • foreign key primary key relational theory • • shizuka

How to find out the segment behind a corrupted block when the database cannot be opened?
SQL, Database Testing • oracle orac languagele 10g • • Saumya

Conditionally OR vs CASE vs IF
SQL, Database Testing • sql server oracle • • Kadyn

Simplification sql of Oracle request
Software Programming • sql oracle • • Jolied

How can I fix this kind of error when testing?
Software Programming • java oracle while ec languagelipse • • zymir