Set - 7

Question 1 :

How is a connection established by ODBC ?

Answer :

ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.

Question 2 :

How can variables be passed to a SQL routine?

Answer :

Level: Low
Expected answer: By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

Question 3 :

You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?

Answer :

Level: Intermediate to high
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

Question 4 :

How can you call a PL/SQL procedure from SQL?

Answer :

Level: Intermediate
Expected answer: By use of the EXECUTE (short form EXEC) command.

Question 5 :

How do you execute a host operating system command from within SQL?

Answer :

Level: Low
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.