Edit D:\app\Administrator\product\11.2.0\dbhome_1\apex\images\doc\concept_bind_var.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>About Bind Variable Syntax</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1" /> <meta name="date" content="2009-04-14T16:11:25Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="About Bind Variable Syntax" /> <meta name="relnum" content="Release 3.2" /> <meta name="partnum" content="E11947-01" /> <link rel="copyright" href="dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="up" href="preface.htm" title="Home" type="text/html" /> <link rel="up" href="concept.htm" title="Application Builder Concepts" type="text/html" /> <link rel="up" href="concept_ses_val.htm" title="Managing Session State Values" type="text/html" /> <link rel="up" href="concept_bind_var.htm" title="About Bind Variable Syntax" type="text/html" /> <link rel="prev" href="concept_ses_clear.htm" title="Previous" type="text/html" /> <link rel="next" href="concept_sub.htm" title="Next" type="text/html" /> </head> <body> <p id="BREADCRUMBING"><a href="preface.htm" title="Home">Home</a> > <a href="concept.htm" title="Application Builder Concepts">Application Builder Concepts</a> > <a href="concept_ses_val.htm" title="Managing Session State Values">Managing Session State Values</a> > About Bind Variable Syntax</p> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="concept_ses_clear.htm"><img src="dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="concept_sub.htm"><img src="dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="BEIEGCCC" name="BEIEGCCC"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1><a name="HTMDB03015|About Bind Variable Syntax"></a>About Bind <a id="sthref196" name="sthref196"></a>Variable Syntax</h1> <p>You can use bind variables syntax anywhere in Oracle Application Express where you are using SQL or PL/SQL to reference session state of a specified item. For example:</p> <pre xml:space="preserve" class="oac_no_warn"> SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%' </pre> <p>In this example, the search string is a page item. If the region type is defined as SQL Query, you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.</p> <p>When using bind variable syntax, remember the following rules:</p> <ul> <li> <p>Bind variable names must correspond to an item name.</p> </li> <li> <p>Bind variable names are not case-sensitive.</p> </li> <li> <p>Bind variable names cannot be longer than 30 characters (that is, they must be a valid Oracle identifier).</p> <p>Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variable syntax, the item name must be 30 characters or less.</p> </li> </ul> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref197" name="sthref197"></a> <h2><a name="HTMDB25030" id="HTMDB25030"></a>Using Bind Variables in Regions Based on a SQL Query or <a id="sthref198" name="sthref198"></a>LOV</h2> <p>If your region type is defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV), you can reference session state using the following syntax:</p> <pre xml:space="preserve" class="oac_no_warn"> :MY_ITEM </pre> <p>One common way to do this is to incorporate a session state variable in a <code>WHERE</code> clause. The following example shows how to bind the value of the item <code>THE_DEPTNO</code> into a region defined from a SQL Query.</p> <pre xml:space="preserve" class="oac_no_warn"> SELECT last_name, job_id, salary FROM employees WHERE department_id = :THE_DEPTNO </pre> <div align="center"> <div class="inftblnotealso"><br /> <table class="NoteAlso oac_no_warn" summary="" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">See Also:</p> <a href="ui_region.htm#CHDCIJJE">"Understanding Regions"</a> for information about creating regions</td> </tr> </tbody> </table> <br /></div> <!-- class="inftblnotealso" --></div> </div> <!-- class="sect3" --> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref199" name="sthref199"></a> <h2><a name="HTMDB25031" id="HTMDB25031"></a>Using Bind Variables in PL/SQL <a id="sthref200" name="sthref200"></a>Procedures</h2> <p>For region types defined as a PL/SQL Procedure, regions are constructed using PL/SQL anonymous block syntax. In other words, the beginning and ending keywords are used to enclose the PL/SQL block. For example:</p> <pre xml:space="preserve" class="oac_no_warn"> <code>IF</code> :P1_JOB IS NOT NULL THEN INSERT INTO employees (employee_id, first_name, job_id) VALUES (:<code>P1_EMP_ID</code>, :P1_NAME, :P1_JOB) end if; </pre> <p>In this example, the values of the <code>employee_id</code>, <code>first_name</code>, and <code>job_id</code> are populated by the values of <code>P1_EMP_ID</code>, <code>P1_NAME</code>, and <code>P1_JOB</code>.</p> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <!-- class="sect1" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2003, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="concept_ses_clear.htm"><img src="dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="concept_sub.htm"><img src="dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de