No problem in. Connect and share knowledge within a single location that is structured and easy to search. The error messages generated when using this feature are more user friendly. Not the answer you're looking for? In our example, OPEN allocates EMPCURSOR and assigns the host variable SALARY to the WHERE clause, as follows: The FETCH statement returns a row from the active set, assigns column values in the select list to corresponding host variables in the INTO clause, and advances the cursor to the next row. I'll create one for next Sprint and follow up on this. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. now we need to create insert statement for the output and then insert that into respective tables so that we could insert that in different schema in other instance. For example, you know the following query returns two column values: However, if you let the user define the select list, you might not know how many column values the query will return. Every bind variable that corresponds to a placeholder for a subprogram parameter has the same parameter mode as that subprogram parameter and a data type that is compatible with that of the subprogram parameter. insert into t values ( 10 ); or forall i in 1 .. 10 insert into t values ( l_variable ); would not work because nothing in the insert is being bulk-bound. Here is the code you can use. When I execeuted Foo.this_thing.load_this(TO_DATE('20200629', 'YYYYMMDD'));, I got this in my error message: Error report - The syntax of the EXECUTE IMMEDIATE statement follows: In the following example, you use the host variable SQL-STMT to store SQL statements input by the user: Because EXECUTE IMMEDIATE parses the input SQL statement before every execution, Method 1 is best for statements that are executed only once. You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, MERGE, or single-row SELECT statement. Now the requirement is something like this Native dynamic SQL processes most dynamic SQL statements with the EXECUTE IMMEDIATE statement. It will reduce the size of the file. The main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute. "However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?". Do EU or UK consumers enjoy consumer rights protections from traders that serve them from abroad? Oracle In each example, the collection type is declared in a package specification, and the subprogram is declared in the package specification and defined in the package body. Eg: I am trying to do this for a table that has 5 columns in it. However, each method is most useful for handling a certain kind of SQL statement, as Appropriate Method to Use shows: Non-query with known number of input host variables. Also, if you have not specified MODE=ANSI, you need not re-prepare the SQL statement after a COMMIT or ROLLBACK (unless you log off and reconnect). You can also catch regular content via Connor's blog and Chris's blog. -- Check validity of column name that was given as input: -- Invoke raise_emp_salary from a dynamic PL/SQL block: -- Invoke raise_emp_salary from a dynamic SQL statement: service_type='Anything' AND date_created> DATE '2010-03-29', ORA-06512: at "SYS.GET_RECENT_RECORD", line 21. In the following example, the input SQL statement contains the place-holder n: With Method 2, you must know the datatypes of input host variables at precompile time. SQL injection maliciously exploits applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database to view or manipulate restricted data. Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. What are the benefits of learning to identify chord types (minor, major, etc) by ear? The SQL statement must not be a query (SELECT statement) and must not contain any place-holders for input host variables. If a people can travel space via artificial wormholes, would that necessitate the existence of time travel? This example uses an uninitialized variable to represent the reserved word NULL in the USING clause. Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database! which improves performance. This section introduces the four methods you can use to define dynamic SQL statements. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, You'll need dynamic SQL for that. In the last example, EMP-NUMBER was declared as type PIC S9(4) COMP. Dynamic query can be executed by two ways. For example, a simple program might prompt the user for an employee number, then update rows in the EMP and DEPT tables. In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL collection type nested table. dynamic SQL, but you can use them with dynamic SQL by specifying them What Method 1 does in one step, Method 2 does in two. Except for multi-row queries, the dynamic string can . The rc parameter is either a cursor variable (SYS_REFCURSOR) or the cursor number (INTEGER) of an open cursor. Are there anyways to create a dynamic insert statement in Oracle, or it's impossible? When this parameter is TRUE, the caller is treated as the client. For example, the following host strings qualify: With Method 1, the SQL statement is parsed every time it is executed (regardless of whether you have set HOLD_CURSOR=YES). Can we create two different filesystems on a single partition? One datetime format model is "text". This solved my problem! With Method 2, the SQL statement can contain place-holders for input host variables and indicator variables. They can be different; for example: The preceding EXECUTE IMMEDIATE statement runs this SQL statement: To associate the same bind variable with each occurrence of :x, you must repeat that bind variable; for example: If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is significant. However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. 1,abc,100 To learn more, see our tips on writing great answers. The text is copied into the conversion result. A datetime or numeric value that is concatenated into the text of a dynamic SQL statement must be converted to the VARCHAR2 data type. When you store the SQL statement in the string, omit the keywords EXEC SQL and the statement terminator. If the data type is a collection or record type, then it must be declared in a package specification. If the PL/SQL block contains no host variables, you can use Method 1 to EXECUTE the PL/SQL string in the usual way. When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows. If the dynamic SQL statement represents a SELECT statement that returns multiple rows, you can process it with native dynamic SQL as follows: Use an OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. We are still getting the actual data from our customer as we are doing the development. In this program, you insert rows into a table and select the inserted rows by using the cursor in the loop. You are creating a procedure where the compiler automatically converts parameters to bound variables. That is, Oracle does what the SQL statement requested, such as deleting rows from a table. If you do not know this information at compile time, you must use the DBMS_SQL package. I have used very limited data-types in the solution (number, date and varchar2 only). In the following example, PREPARE parses the query stored in the character string SELECT-STMT and gives it the name SQLSTMT: Commonly, the query WHERE clause is input from a terminal at run time or is generated by the application. Statement caching can be enabled in the precompiler applications, which will help in the performance improvement of all applications that rely on the dynamic SQL statements. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. In the USING clause of the OPEN FOR statement, specify a bind variable for each placeholder in the dynamic SQL statement. STATEMENT-NAME is an identifier used by the precompiler, not a host or program variable, and should not be declared in a COBOL statement. How can we optimize it. In the server, it means that cursors are ready to be used without the need to parse the statement again. In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL collection type associative array indexed by PLS_INTEGER. SQL data definition statements such as CREATE are executed once the PREPARE is completed. I've got this working ok. but I'd like to be able to return the id of the new record created so I can return it from my main function. Use dynamic query for this. Oracle - Insert into tables using dynamic queries, The philosopher who believes in Web Assembly, Improving the copy in the close modal and post notices - 2023 edition, New blog post from our CEO Prashanth: Community is the future of AI. That is, Method 2 encompasses Method 1, Method 3 encompasses Methods 1 and 2, and so on. But I did come across another project with the same problem as this one. The two procedures return results in the same order. The names of the place-holders need not match the names of the host variables. Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL. For example, in this dynamic SQL statement, the repetition of the name :x is insignificant: In the corresponding USING clause, you must supply four bind variables. Example 7-7 Uninitialized Variable Represents NULL in USING Clause. You did away with the temp table so it seemed simpler overall than your first example. Such statements can, and probably will, change from execution to execution. The RETURNING INTO clause specifies the variables in which to store the values returned by the statement to which the clause belongs. Its use is suggested when one or more of the following items is unknown at precompile time: Text of the SQL statement (commands, clauses, and so on), References to database objects such as columns, indexes, sequences, tables, usernames, and views. An associative array type used in this context must be indexed by PLS_INTEGER. With Methods 2 and 3, the number of place-holders for input host variables and the datatypes of the input host variables must be known at precompile time. The DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR variable (either strong or weak) to a SQL cursor number, which you can pass to DBMS_SQL subprograms. This example demonstrates the use of the stmt_cache option. Since you cannot FETCH from a PL/SQL block, use Method 2 instead. Select * from employee emp , department dept , salary sal Finding valid license for project utilizing AGPL 3.0 libraries. Dynamic Insert Statement - Oracle Forums SQL & PL/SQL Dynamic Insert Statement User_1M3BR May 19 2021 edited May 19 2021 Hi, There is a requirement to dynamically pick the filter condition from table and then insert the data in another table. seems that for an install script, it would be so much easier to. That way, you clear extraneous characters. This is mainly incase a tester re-runs a script without backing up their data. In fact, if the dynamic SQL statement is a query, you must use Method 3 or 4. It works well. LOAD_THIS:: v_sql set. You must put all host variables in the USING clause. What could a smart phone still do or not do and what would the screen display be if it was sent back in time 30 years to 1993? Note thatthe dynamic insert which is getting created does not take much time to execute. 00933. Hi All , I am seeking an advice .. we do have 2 database instance on oracle 19c now we would like to transfer /copy the specific data from a schema to another schema in another instance. Typically, the user retrieves unauthorized data by changing the WHERE clause of a SELECT statement or by inserting a UNION ALL clause. Following sample code can be used to generate insert statement. But it doesn't work, Then I got When the number of select-list items or place-holders for input host variables is unknown until run time, your program must use a descriptor. Repeated Placeholder Names in Dynamic SQL Statements. Last updated: May 04, 2021 - 9:54 am UTC, Maverick, April 08, 2008 - 10:33 am UTC, Maverick, April 08, 2008 - 1:43 pm UTC, A reader, April 09, 2008 - 1:41 am UTC, Maverick, April 09, 2008 - 7:54 am UTC, A reader, April 09, 2008 - 8:45 am UTC, Maverick, April 09, 2008 - 10:07 am UTC, A reader, July 04, 2011 - 6:26 am UTC, Zahirul Haque, June 07, 2012 - 9:33 pm UTC, Zahirul Haque, August 28, 2012 - 7:42 pm UTC, Thiruppathi, September 26, 2012 - 5:39 am UTC, DIPU V P, January 15, 2013 - 8:20 am UTC, Gireesh Puthumana, May 21, 2013 - 11:18 am UTC, Ravi B, May 22, 2013 - 11:25 pm UTC, Gireesh Puthumana, May 23, 2013 - 3:56 pm UTC, Gireesh Puthumana, May 24, 2013 - 10:04 am UTC, Ravi B, May 28, 2013 - 10:42 pm UTC, Gireesh Puthumana, June 05, 2013 - 2:40 pm UTC, A reader, August 21, 2015 - 12:29 pm UTC, poshan pandey, May 03, 2021 - 6:16 pm UTC. Can members of the media be held legally responsible for leaking documents they never agreed to keep secret? Otherwise, a malicious user who receives the error message "invalid password" but not "invalid user name" (or the reverse) can realize that he or she has guessed one of these correctly. It could also have been declared as type PIC X(4) or COMP-1, because Oracle supports all these datatype conversions to the NUMBER internal datatype. Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. This chapter shows you how to use dynamic SQL, an advanced programming technique that adds flexibility and functionality to your applications. sandeepgupta_18 Sep 29 2022 edited Sep 29 2022. Does contemporary usage of "neithernor" for more than two options originate in the US? This allows your program to accept and process queries. Parsing also involves checking database access rights, reserving needed resources, and finding the optimal access path. The SQL cursor attributes work the same way after native dynamic SQL INSERT, UPDATE, DELETE, MERGE, and single-row SELECT statements as they do for their static SQL counterparts. Thanks a lot for the two different solutions. Use the CLOSE statement to close the cursor variable. Query with known number of select-list items and input host variables. The following fragment of a program prompts the user for a search condition to be used in the WHERE clause of an UPDATE statement, then executes the statement using Method 1: This program uses dynamic SQL Method 1 to create a table, insert a row, commit the insert, then drop the table. Content Discovery initiative 4/13 update: Related questions using a Machine Oracle SQl Populating a cursor in a procedure from a sql statement saved in a table field, how to fetch cursor value into varchar2 in pl/sql, Generatting insert statement for given table/column value dynamically, Create Trigger with stored procedures by making dynamic in the trigger column. For example, if you execute the statements. For example, if the user is passing a department number for a DELETE statement, check the validity of this department number by selecting from the departments table. For example, the following host strings fall into this category: Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables. In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL collection type varray. Each unique placeholder name must have a corresponding bind variable in the USING clause. When the stmt_cache option is used to precompile this program, the performance increases compared to a normal precompilation. EXECUTE IMMEDIATE DBMS_SQL.EXECUTE (dynamic_sql_string)- It provides more functionality and control over EXECUTE IMMEDIATE, We can parse the incoming table name and column name. In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL (but not SQL) data type RECORD. ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'; ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"'; Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND, service_type='Anything' AND date_created>'' OR service_type='Merger'. After DBMS_SQL.RETURN_RESULT returns the result, only the recipient can access it. But for large data set , it is taking very long time. How to add double quotes around string and number pattern? set sqlformat insert select * from t1; The output can be spooled as well: set sqlformat insert spool C:\Users\balaz\Desktop\insert.sql select * from t1; spool off Run the above as a script (F5), and not a statement (Ctrl+Enter). As I'm already spooling to a log file and am only on Oracle 9i the script spools its generated sql statmenet to the unix box to an area which is accessible via a url. */. For information about using static SQL statements with PL/SQL, see PL/SQL Static SQL. Why does the second bowl of popcorn pop better in the microwave? Ensure that the converted values have the format of SQL datetime or numeric literals. Bind variables can be evaluated in any order. I am reviewing a very bad paper - do I have to be nice? However, the names of database objects such as tables and columns need not be specified until run time (they cannot duplicate the names of host variables). You don't need to use dynamic SQL within your package to do that. If the dynamic SQL statement includes placeholders for bind variables, each placeholder must have a corresponding bind variable in the appropriate clause of the EXECUTE IMMEDIATE statement, as follows: If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables (defines) in the INTO clause and in-bind variables in the USING clause. If you repeat a placeholder name, you need not repeat its corresponding bind variable. It then stores this information in the select descriptor. For details, see Oracle Dynamic SQL: Method 4. Dynamic query can be executed by two ways. looping the record one by one. Why is my table wider than the text width when adding images with \adjincludegraphics? Asking for help, clarification, or responding to other answers. The most effective way to make your PL/SQL code invulnerable to SQL injection attacks is to use bind variables. Instead, use C-style Comments (/* */). rev2023.4.17.43393. Dynamic SQL Statement is Not Anonymous Block or CALL Statement, Dynamic SQL Statement is Anonymous Block or CALL Statement. With Method 3, use the following syntax: To use output host tables with Method 3, use the following syntax: With Method 4, you must use the optional FOR clause to tell Oracle the size of your input or output host table. You must use the DBMS_SQL package to run a dynamic SQL statement if any of the following are true: You do not know the SELECT list until run time. If the dynamic SQL statement invokes a subprogram, ensure that: The subprogram is either created at schema level or declared and defined in a package specification. In most cases, the character string can contain dummy host variables. If the dynamic SQL statement represents a SELECT statement that returns multiple rows, you can process it with native dynamic SQL as follows: Use an OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. All references to that placeholder name correspond to one bind variable in the USING clause. PL/SQL can you INSERT INTO (SELECT GROUP BY)? *Cause: Then, I want to open the cursor and insert into a table which column's name come from the cursor. REGARDING TIMESTAMP ISSUE FOR DYNAMIC INSERT STATEMENTS Hi,I am new to oracle, i have used your create dynamic insert script for generating the insert script. or build the string 'select * from ' || table (being careful to avoid sql injection of course, but that is another discussion), problem comes when you fetch those values into variables. Can a rotating object accelerate by changing shape? Your concern to "safely select values" while laudable is unnecessary in this case. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide. Use the OPEN FOR, FETCH, and CLOSE statements. It designates a particular dynamic SQL statement. To process the dynamic SQL statement, your program must issue the DESCRIBE BIND VARIABLES command and declare another kind of SQLDA called a bind descriptor to hold descriptions of the place-holders for the input host variables. So, like a SQL statement, a PL/SQL block can be stored in a string host variable or literal. This example creates a procedure that is vulnerable to statement injection and then invokes that procedure with and without statement injection. This section gives only an overview. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it. Oracle does not recognize the null terminator as an end-of-string marker. There is a kind of dynamic SQL statement that your program cannot process using Method 3. The identifier SQLSTMT is not a host or program variable, but must be unique. With Methods 2, 3, and 4, you might need to use the statement. Similarly, if a user enters the name of a table to be deleted, check that this table exists by selecting from the static data dictionary view ALL_TABLES. For example, to use input host tables with dynamic SQL Method 2, use the syntax. In new applications, use the RETURNINGINTOclause. You do not know until run time what placeholders in a SELECT or DML statement must be bound. Hi, The term select-list item includes column names and expressions. Example 7-10 Repeated Placeholder Names in Dynamic PL/SQL Block. In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL (but not SQL) data type BOOLEAN. Query with unknown number of select-list items or input host variables. (Bind variables also improve performance. For example, using the DBMS_SQL.IS_OPEN function to see if a converted SQL cursor number is still open causes an error. The number of select-list items, the number of place-holders for input host variables, and the datatypes of the input host variables can be unknown until run time. Oracle Database PL/SQL Packages and Types Reference for more information about DBMS_SQL.RETURN_RESULT, Oracle Call Interface Programmer's Guide for information about C and .NET support for implicit query results, SQL*Plus User's Guide and Reference for information about SQL*Plus support for implicit query results, Oracle Database Migration Guide for information about migrating subprograms that use implicit query results, Example 7-11 DBMS_SQL.RETURN_RESULT Procedure. First, I create a curosr for select column's name which from a customed table. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. The dynamic SQL statement, which cannot be a query, is first prepared (named and parsed), then executed. OPEN also positions the cursor on the first row in the active set and zeroes the rows-processed count kept by the third element of SQLERRD in the SQLCA. Thanks for your help! If you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind variables depends on the kind of dynamic SQL statement. A generic bind SQLDA contains the following information about the input host variables in a SQL statement: Maximum number of place-holders that can be DESCRIBEd, Actual number of place-holders found by DESCRIBE, Addresses of buffers to store place-holder names, Sizes of buffers to store place-holder names, Addresses of buffers to store indicator-variable names, Sizes of buffers to store indicator-variable names, Current lengths of indicator-variable names. This is a first draft of the script. I started a new Sprint at work last week and don't have a story for this. where HOST-VARIABLE-LIST stands for the following syntax: EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable. Now suppose this query gives 20 rows Example 7-2 Dynamically Invoking Subprogram with BOOLEAN Formal Parameter. The variables can be either individual variables or collections. This method lets your program accept or build a dynamic SQL statement, then process it using the PREPARE and EXECUTE commands. After p returns a result to the anonymous block, only the anonymous block can access that result. No - the insert comment is a SQL Developer/SQLcl feature. LOBs are not supported in Oracle Method 4. If the number of columns in a query select list is known, but the number of place-holders for input host variables is unknown, you can use the Method 4 OPEN statement with the following Method 3 FETCH statement: Conversely, if the number of place-holders for input host variables is known, but the number of columns in the select list is unknown, you can use the following Method 3 OPEN statement with the Method 4 FETCH statement: Note that EXECUTE can be used for non-queries with Method 4. Content Discovery initiative 4/13 update: Related questions using a Machine Insert results of a stored procedure into a temporary table, Simple PL/SQL to check if table exists is not working, Nested tables: Insert values into specific columns of nested table, Oracle insert into using select to add first row and return columns without using pl/sql stored procedure, Oracle returning statement for an insert into operation with 'select from' source, How to intersect two lines that are not touching. In these situations, you must use native dynamic SQL instead of the DBMS_SQL package: The dynamic SQL statement retrieves rows into records. The cursor declaration is local to its precompilation unit. The conversion can be either implicit (when the value is an operand of the concatenation operator) or explicit (when the value is the argument of the TO_CHAR function). While you might not notice the added processing time, you might find the coding difficult unless you fully understand dynamic SQL concepts and methods. Dynamic queries with EXECUTE IMMEDIATE Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Using the EXECUTE IMMEDIATE Statement. For example, Oracle makes no distinction between the following two strings. If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices: Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause. I'm lazy so I started by reviewing your second example. No bind variable is the reserved word NULL. Instead, they are stored in character strings input to or built by the program at run time. Therefore, DBMS_SQL.RETURN_RESULT returns the query result to the subprogram client (the anonymous block that invokes p). When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using the "DBMS_SQL.TO_REFCURSOR Function" and "DBMS_SQL.TO_CURSOR_NUMBER Function". When the SQL statement EXECUTE is completed, input host variables in the USING clause replace corresponding place-holders in the prepared dynamic SQL statement. Scripting on this page enhances content navigation, but does not change the content in any way. The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The same binding technique fixes the vulnerable procedure shown in Example 7-17. The decision logic in Figure 9-1, will help you choose the correct method. LOAD_THIS:: this_date: 29-JUN-20 This function should be used only for small number of rows. Also it does not merge on the not-common-across-tables columns. Employee_name,dept_name,salary For example, you might use place-holder names to prompt the user for the values of input host variables. now we would like to transfer /copy the specific data from a schema to another schema in another instance. If you do not need dynamic SQL, use static SQL, which has these advantages: Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects. However, non-concurrent cursors can reuse SQLDAs. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. If the dynamic SQL statement is an anonymous PL/SQL block or a CALL statement, put all bind variables in the USING clause. Example 7-3 Dynamically Invoking Subprogram with RECORD Formal Parameter. You want a stored subprogram to return a query result implicitly (not through an OUT REF CURSOR parameter), which requires the DBMS_SQL.RETURN_RESULT procedure. Example 7-13 Switching from DBMS_SQL Package to Native Dynamic SQL. Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. The procedure in this example is invulnerable to SQL injection because it builds the dynamic SQL statement with bind variables (not by concatenation as in the vulnerable procedure in Example 7-16). 00000 - "SQL command not properly ended" You may find situations where you need to create insert statement dynamically. However, there are two differences in the way Pro*COBOL handles SQL and PL/SQL: All PL/SQL host variables should be treated in the same way as input host variables regardless of whether they are input or output host variables (or both).