The dbms_assert package is used in databases that don’t employ bind variables to help prevent SQL injection attacks, by “sanitizing” the SQL.

it has several procedures inside ex :

• simple_sql_name: Validates the syntax of the SQL to ensure that the SQL statement only contains valid characters and proper quotes around internal values.
• schema_name: Allows you to validate a schema name against the dictionary

and this a simple example of using (dbms_assert.simple_sql_name)

[code]CREATE OR REPLACE PROCEDURE oraask_test (tbl_name VARCHAR2, col_name VARCHAR2)
IS
qry VARCHAR2 (500);
BEGIN
qry := ‘ALTER TABLE ‘ || dbms_assert.simple_sql_name ( :tbl_name) || ‘ ADD ‘ || :col_name || char (1);

EXECUTE IMMEDIATE qry USING col_name;
END oraask_test;[/code]

Hello Beter,

Views in Oracle may be updateable under specific conditions. It can be tricky, and usually is not advisable.

From the Oracle 10g SQL Reference:

Notes on Updatable Views

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

• Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.
• The view must not contain any of the following constructs:

1. A set operator
2. a DISTINCT operator
3. An aggregate or analytic function
4. A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
5. A collection expression in a SELECT list
6. A subquery in a SELECT list
7. A subquery designated WITH READ ONLY
8. Joins, with some exceptions, as documented in Oracle Database Administrator’s Guide

In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.

If you want a join view to be updatable, then all of the following conditions must be true:

• The DML statement must affect only one table underlying the join.
• For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
• For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
• For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.

Hi Waqas,

it’s applicable by using Standard API

[code]fnd_webfile.get_url
(file_type => fnd_webfile.request_out,
— for output file. Use request_log to view log file
ID => l_request_id,
gwyuid => l_gwyuid,
two_task => l_two_task,
expire_time => 500 — minutes, security!.
);[/code]

note : there are two profile options this API must take as following

l_gwyuid : Gateway User ID

[code]oadbtransactionimpl.getAppsContext().getEnvStore().getEnv(“GWYUID”)[/code]

l_two_task: Two Task(TWO_TASK)

[code]oadbtransactionimpl.getAppsContext().getEnvStore().getEnv(“TWO_TASK”)[/code]

you can register out parameter of calling this API to String variable then use

[code]pageContext.sendRedirect[/code]

Hope this helpful 🙂

Hi Albert,

You can use DBMS_LOB.substr like this for example:

DBMS_LOB.substr(column, 3000)

but don’t forget that substring of a CLOB column has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size.

For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000

and you can refer to Oracle Substr Function Article for more information about the substr function