BlackFriday 2024! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions
Mail Us support@pass4success.com
Location US
Mob_nav_barsMENU

SAP Exam C_HANADEV_18 Topic 2 Question 19 Discussion

Actual exam question for SAP's C_HANADEV_18 exam
Question #: 19
Topic #: 2
[All C_HANADEV_18 Questions]

Which of the following are characteristics of database procedures?

Show Suggested Answer Hide Answer
Suggested Answer: A, B

According to the SAP HANA SQLScript Reference1, database procedures are subroutines that can be called from SQL statements or other database procedures. Database procedures can have the following characteristics:

Database procedures can have both input and output parameters; however, a parameter CANNOT be both input and output. Input parameters are used to pass values to the procedure, while output parameters are used to return values from the procedure. A parameter can be either scalar or table type, depending on the data type and cardinality. A parameter cannot be both input and output, because this would create ambiguity and inconsistency in the parameter passing mechanism. For example, you cannot declare a parameter as IN OUT, or assign a value to an input parameter, or read a value from an output parameter.For more information on database procedure parameters, see [Parameters of Database Procedures]2.

Database procedures can have several output parameters, and a mix of both scalar and table types is possible. Output parameters are used to return values from the procedure to the caller. A procedure can have zero or more output parameters, depending on the purpose and logic of the procedure. Output parameters can be either scalar or table type, depending on the data type and cardinality. A procedure can have a mix of both scalar and table output parameters, as long as they are compatible with the caller's expectations and syntax. For example, you can use a SELECT statement with INTO clause to assign values to scalar output parameters, or use a SELECT statement with RESULT clause to return a table output parameter.For more information on database procedure output parameters, see [Output Parameters of Database Procedures]3.

If, in the database procedure header, you use the READS SQL DATA option, then INSERT statements are prohibited; however, dynamic SQL is allowed. The READS SQL DATA option is used to indicate that the procedure only reads data from the database, and does not modify or write any data. This option is useful for performance optimization and security enforcement, as it allows the database to apply certain optimizations and checks to the procedure. However, this option also imposes some restrictions on the procedure, such as prohibiting any INSERT, UPDATE, DELETE, or MERGE statements, or any other statements that modify the database state. Dynamic SQL, which is SQL code that is constructed and executed at run time, is still allowed, as long as it does not violate the READS SQL DATA option.For more information on the READS SQL DATA option, see [Procedure Header Options]4.

If, in the database procedure header, you use the SQL SECURITY INVOKER option, then only the owner of the procedure can start it. The SQL SECURITY INVOKER option is used to indicate that the procedure is executed with the privileges of the user who invokes the procedure, rather than the privileges of the user who created the procedure. This option is useful for security enforcement and access control, as it allows the database to apply the appropriate authorization checks and restrictions to the procedure. However, this option does not affect who can start the procedure, as this is determined by the GRANT EXECUTE statement, which grants the execute privilege on the procedure to a specific user or role. The SQL SECURITY INVOKER option only affects how the procedure is executed, not who can execute it.For more information on the SQL SECURITY INVOKER option, see [Procedure Header Options]4.


by Candida at Jan 25, 2024, 03:53 PM

Limited Time Offer

25%

Off

Get Premium C_HANADEV_18 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel
Martin
5 months ago
Ooh, this is a good one. Gotta love those database procedure nuances. Let's see, I'd go with B and C.
upvoted 0 times
...
Skye
5 months ago
Ha! Sounds like a tricky question. I bet the exam writers had fun coming up with these answer choices.
upvoted 0 times
Lucille
4 months ago
C) If, in the database procedure header, you use the READS SQL DATA option, then INSERT statements are prohibited; however, dynamic SQL is allowed.
upvoted 0 times
...
Maia
5 months ago
B) Database procedures can have several output parameters, and a mix of both scalar and table types is possible.
upvoted 0 times
...
Patria
5 months ago
A) Database procedures can have both input and output parameters; however, a parameter CANNOT be both input and output.
upvoted 0 times
...
...
Arlean
5 months ago
I'm not sure about D. Isn't the SQL SECURITY INVOKER option about who can run the procedure, not just the owner?
upvoted 0 times
Mary
5 months ago
C) If, in the database procedure header, you use the READS SQL DATA option, then INSERT statements are prohibited; however, dynamic SQL is allowed.
upvoted 0 times
...
Shonda
5 months ago
C) If, in the database procedure header, you use the READS SQL DATA option, then INSERT statements are prohibited; however, dynamic SQL is allowed.
upvoted 0 times
...
Tyra
5 months ago
B) Database procedures can have several output parameters, and a mix of both scalar and table types is possible.
upvoted 0 times
...
Mary
5 months ago
D) If, in the database procedure header, you use the SQL SECURITY INVOKER option, then only the owner of the procedure can start it.
upvoted 0 times
...
Vivienne
5 months ago
A) Database procedures can have both input and output parameters; however, a parameter CANNOT be both input and output.
upvoted 0 times
...
...
Rosalind
6 months ago
C looks good to me. If you use the READS SQL DATA option, you can't do INSERTs, but dynamic SQL is allowed.
upvoted 0 times
Maynard
6 months ago
That's correct. READS SQL DATA option in the database procedure header restricts INSERT statements but allows dynamic SQL.
upvoted 0 times
...
Maynard
6 months ago
C
upvoted 0 times
...
...
Dean
6 months ago
I think option B is the correct answer. Database procedures can definitely have multiple output parameters of different types.
upvoted 0 times
...

Save Cancel