Examine the following block of code:
Which two statements are correct about the code above? (Choose two.)
A.
The function goes through only the parse and executes phases.
B.
The function goes through the parse, bind, and execute phases.
C.
The function goes through the parse, bind, execute, and fetch phases.
D.
All the processing phases for the function are performed only at run time.
E.
Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time.
A looks like thue – this chunk of code is PARSED and EXECUTED (the result of this is “Function created”) and E of course
… so “A” and “E”
Answer should be A and E
A and E are correct
What about fetch? the attribute of implicit cursor are fetched to the %count? Are there any definition about the bind, parse, execute and fetch? any Oracle Document? thanks.
There’s no binding going on for this function so that already rules out D. As for fetching, there’s nothing in the code that shows it. So yeah, it’s A and E.
A E
A E
I executed the following:
CREATE OR REPLACE PROCEDURE test_ei
IS
BEGIN
EXECUTE IMMEDIATE ‘select job_id from assdfsddsfsefddrubbale’;
END;
the table ‘assdfsddsfsefddrubbale’ does not exists in my schema. It compiles with 0 errors and 0 warning so there is no parsing going on.
D and E are the answer.
you are noty doing the same thing…
EXECUTE IMMEDIATE ‘select job_id from assdfsddsfsefddrubbale’;
and
execute immediate ‘delete from ‘|| p_table_name || ‘where emp_no =’ ||p_empno;
are two different thing,
what you are doing is a simple execute immediate statement with no bind variables, where as the later says that you are using two variables from the procedure call…
La respuesta correcta es la A y la E, esto debido a lo siguientes:
Todas las sentencias SQL pasan por todas o alguna de las siguientes fases:
PARSE:todas las sentencias SQL deberán ser parseadas, el parseo de la sentencia checa la sintaxis de la sentencia y valida la sentencia, se asegura que todas las referencias a objetos son correctas y que los privilegios sobre esos objetos exista.
BIND: después de realizar el parseo, el servidor Oracle puede necesitar valores para cualquier variable bind, el proceso de obtención de estos valores se llama binding variables. Esta parte es omitida si la sentencia no contiene variables bind.
EXECUTE:en este punto el servidor Oracle tiene toda la información y recursos necesarios y entonces ejecuta la sentencia.
FETCH: en esta etapa de FETCH que es aplicable a consultas, las filas son seleccionadas y ordenadas si es que lo especifica la consulta.
Por otro lado:
* para sentencias SQL dinámicas todas las fases son realizadas en tiempo de ejecución.
* para sentencias SQL embebidas como SELECT, DML, MERGE, COMMIT, SAVEPOINT y ROLLBACK, las etapas de parseo y bind son hechas en tiempo de compilación.
DE CUERDO A LO ANTERIOR PODEMOS ANALIZAR EL EJEMPLO COMO SIGUE:
1 CREATE OR REPLACE FUNCTION del_rows —> entra etapa se parseo
2(p_table_name VARCHAR2, p_empno number)—> entra etapa de parseo
3 RETURN NUMBER IS —> entra etapa de parseo
4 BEGIN EXECUTE IMMEDIATE ‘DELETE FROM’||p_table_name||’WHERE empno=’|| p_empno;–> sentencia SQL dinámica por lo que todas las etapas son hechas en tiempo de ejecución.
5 RETURN SQL%ROWCOUNT;—> etapa de parseo
6 END;—> parseo
7 /–> ejecución
En este ejemplo no especificamos variables bind por lo que esta etapa es innecesaria.
The function is not executed. It is created only. So, answer Is D,E
You are wrong in answer and in fact that there were no execute.
Right answer is A and E
See the link:
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/sqlconce.htm#741
DDL Statement Processing:
The execution of DDL statements differs from the execution of DML statements and queries because the success of a DDL statement requires write access to the data dictionary. For these statements, the parse phase actually includes the parsing, data dictionary lookup, and execution.
Transaction management, session management, and system management SQL statements are processed using the parse and execute phases. To re-execute them, simply perform another execute.
D, E
D,E is answer
B E
B E
https://books.google.it/books?id=Cr6nAgAAQBAJ&pg=PA1043&lpg=PA1043&dq=ORACLE+PL/SQL+FUNCTION+PARSING&source=bl&ots=_apmQdj-LQ&sig=UJsV6UP8CLmGaEclBZ5vlUw7C1k&hl=it&sa=X&ei=w11kVbHKIse9swHVgYHICg&ved=0CGIQ6AEwCA#v=onepage&q=ORACLE%20PL%2FSQL%20FUNCTION%20PARSING&f=false
D,E is answer
A,B,C is not correct – function doesn’t go through the parse. Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time. So “E” excludes “A”.
Benefits of Stored Procedures and Functions:
Improved performance
– Avoid reparsing for multiple users by exploiting the shared SQL area
– Avoid PL/SQL parsing at run time by parsing at compile time
I think this question is very confusing.
The ‘function created’ in exhibit message says the DDL ‘create function’ code was executed. There is parse in ddl but not in execution of function.
As Uladzimir above said there is an execution in DDL.
Does ‘runtime’ mean this compile time or later execution?
I think someone who had 100% rate and remembers this question should answer to be sure.
Either way there is no fetch ( no select or explicit cursor) so its one of A,D,E
A and E are correct
A. DDL goes through parse and execute
B. function means function not what inside function so there is no bind
C. There is no bind
D. Benefits of Stored Procedures and Functions:
Improved performance
– Avoid reparsing for multiple users by exploiting the shared SQL area
– Avoid PL/SQL parsing at run time by parsing at compile time
E. EXECUTE IMMEDIATE executes at run time
a) error function is performed with plsql engine, nothing to do with sql statment running – parse, bind, execuet, fetch
b) same as a)
c) same as a)
sorry my last explaination is obsecure
(1) DDL parse includes parse, access data dictionary, and execure. Thus a) b) c) is incorrect since DDL(function here) has only parse phase
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/sqlconce.htm#741
DDL statement processing
(2) the execution of funtion body is occuring on runtime, not compiling time, thus the d) is correct
(3) e) dynamic sql has one parse phase. Notes pls that there is no bind, it’s literal concatenation, The variable binding is preferable over literal concatenation because the same sql statemenet and explaination plan would be put into the shared pool and no re-parsing would happen for the vairable binding like “SELECT * FROM my_table WHERE my_column=:my_column_val”
All SQL statements go through various stages:
Parse
Bind
Execute
Fetch
Some stages may not be relevant for all statements—for example, the fetch phase is applicable to queries.
Note: For embedded SQL statements (SELECT, DML, COMMIT and ROLLBACK), the parse and bind phases are done at compile time. For dynamic SQL statements, all phases are performed at run time.
———————–
Steps to Process SQL Statements
All SQL statements have to go through various stages. However, some stages may not be relevant for all statements. The following are the key stages:
Parse: Every SQL statement must be parsed. Parsing the statement includes checking the statement’s syntax and validating the statement, ensuring that all references to objects are correct and that the relevant privileges to those objects exist.
Bind: After parsing, the Oracle server may need values from or for any bind variable in the statement. The process of obtaining these values is called binding variables. This stage may be skipped if the statement does not contain bind variables.
Execute: At this point, the Oracle server has all necessary information and resources, and the statement is executed. For nonquery statements, this is the last phase.
Fetch: In the fetch stage, which is applicable to queries, the rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.
——————–
so i think the answers are
D) because the function just include Dynamic SQL so all process performed at run time.
E) EXECUTE IMMEDIATE means Dynamic SQL so it parsed at run time
Nice respond in return of this issue with solid arguments and telling all regarding that.|
It’s really a nice and helpful piece of information. I am satisfied that you just shared this useful information with us. Please keep us informed like this. Thank you for sharing.|
This is the best search system in the world
http://google.com
Very smooth write-up. I surely like this site . Thanks!
http://www.zeroplusfinance.com/how-to-properly-collect-gold-coins-to-achieve-the-maximum-profits/
Awesomely done article. I surely like this site . Stick with it!
http://wwwcenturionwealth.com/
Excellent article piece. I definitely love this site . Continue the good work!
http://www.investopedia.com/advisor-network/advisors/61757/wendy-ann-payne-csa-cep-/