Paradiseblog482

Last updated on NOVEMBER 17, 2020

• Bind Values In Peoplesoft
• Bind In Peoplesoft Employee
• Oracle Bind Variable Values View
• Bind In Peoplesoft System

Bind Values In Peoplesoft

Applies to:

Bind In Peoplesoft Employee

If the LDAP Authentication Signon PeopleCode is enabled, then system invokes LDAP authentication with the directory via the LDAPSEARCH and LDAPBIND Business Interlinks. Using these business interlinks the Signon PeopleCode will then validate the User ID & Password.

1. I have the below PeopleCode step in an Application Engine program that reads a CSV file using a File Layout and then inserts the data into a table, and I am just trying to get a better understanding of how the the line of code (&SQL1 = CreateSQL('%Insert(:1)');) in the below script gets generated. It looks like the CreateSQL is using a bind.
2. A new functionality in PeopleSoft is the ability to use Text Catalog instead of Message Catalog and display the text on the page. Some main advantages, as I’ve noticed, by using Text Catalog is the ability to use HTML and make text effective dated.

Symptoms

After applying PUM 18, The Student Advisor Assignment appears there area few bind records missing in the query options - SR_STAD_ADV and SSR_STAD_STU.
Checking the prompt table on the setup record - it isn't looking into the query for a bind record, but instead looking for a query name (not expected) SELECT QRYNAME , DESCR FROM PSQRYDEFN WHERE QRYNAME LIKE 'SSR_STAD_ADV%' .
The issue can be reproduced at will with the following steps:
1. Records and Enrollment > Student Background Information > Student Advisor Assignment
2. Try to run using PS Query Optons
3. Note the process completed but no records are retrieved and no errors in the log file

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables.Bind variables are «substituion» variables that are used in place of literals(such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit

Oracle Bind Variable Values View

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

Bind In Peoplesoft System

and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.