Oracle高级复制Step by Step

oracle中imp命令详解Oracle的导入实用程序(Importutility)允许从数据库提取数据，并且将数据写入操作系统文件。

imp使用的基本格式：imp [username[/password[@service]]]，以下例举imp常用用法。

1. 获取帮助imp help=y2. 导入一个完整数据库imp system/manager file=d:\daochu.dmp log=d:\daochu.log full=y ignore=y3. 导入一个或一组指定用户所属的全部表、索引和其他对象imp system/manager file=seapark log=seapark fromuser=seapark impsystem/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)4. 将一个用户所属的数据导入另一个用户imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1,amy1)5. 导入一个表imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)6. 从多个文件导入imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)log=paycheck, filesize=1G full=y7. 使用参数文件imp system/manager parfile=bible_tables.parbible_tables.par参数文件：#Import the sample tables used for the Oracle8i Database Administrator's Bible. fromuser=seapark touser=seapark_copy file=seapark log=seapark_import8. 增量导入imp system./manager inctype= RECTORE FULL=Y FILE=A Oracle imp/expC:Documentsand Settingsadministrator>exp help=yExport: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:04:43 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 通过输入 EXP命令和用户名/口令，您可以后接用户名/口令的命令:例程: EXPSCOTT/TIGER或者，您也可以通过输入跟有各种参数的 EXP命令来控制“导出”按照不同参数。

客户的数据库采用expdp命令导出的，这是Oracle10g以后采用的新的导出方式，比exp 执行效率上有所提高。

下面说明如何做导入导出：1、环境变量设置(在Sqlplus中执行)create or replace directory <dir_name> as ‘c:\tmpdir’;注：c:\tmpdir也可以是另外的随意目录，用单引号括起来2、导出ARADMIN库expdp <username>/<password>@<SID> directory=<dir_name> dumpfile=xxxxxx.datlogfile=xxxx.log schemas=ARADMIN;3、如果需要备份（通常不需要）CMDB WebService则导出AtriumAdmin库expdp <username>/<password>@<SID> directory=<dir_name> dumpfile=xxxxxx.datlogfile=xxxx.log schemas=ARADMIN;4、导出成功后，把dat和log文件都拷贝到目标数据库服务器上，记住两个文件的放置路径5、目标库导入前操作create or replace directory <dir_name_1> as ‘c:\tmpdir’;注：此处的c:\tmpdir替换成两个文件的存放路径Sqlplus登录目标库(一定要用管理员账号)；sqlplus <username>/<password>@<SID> as sysdba;核对连接的库名：show parameter name; 查看连接的是哪个SID;select username from dba_users order by created;查看所有表空间命令的最后两行应该有ARADMIN和ATRIUMADMIN表空间名；删除目标库里面原来的表空间（删之前停AR服务）：drop user ARADMIN cascade;未停服务时报错停服务后成功创建新的用户并赋权限：CREATE USER "ARADMIN" IDENTIFIED BY ARAdmin default tablespace ARSYSTEMtemporary tablespace TEMP quota unlimited on ARSYSTEM;Grant alter session,create cluster,create database link,create sequence,create session, create synonym,create table,create view,create procedure,create trigger,query rewrite to aradmin;6、导入库命令Impdp <username>/<password>@<SID> directory=<dir_name_1> dumpfile=xxxx.dat logfile=xxxx.log schemas=ARADMIN。

在Oracle 10g下，我们可以使用RMAN duplicate 命令创建一个具有不同DBID 的复制库。

到了Oracle 11gR2，RMAN 的duplicate 有2种方法实现：1. Active database duplication2. Backup-based duplicationActive database duplication 通过网络，直接copy target 库到auxiliary 库，然后创建复制库。

这种方法就不需要先用RMAN 备份数据库，然后将备份文件发送到auxiliary端。

这个功能的作用是非常大的。

尤其是对T级别的库。

因为对这样的库进行备份，然后将备份集发送到备库，在进行duplicate 的代价是非常大的。

一备份要占用时间，二要占用备份空间，三在网络传送的时候，还需要占用带宽和时间。

所以Active database duplicate 很好的解决了以上的问题。

它对大库的迁移非常有用。

如果是从RAC duplicate 到单实例，操作是一样的。

如果是从单实例duplicate 到RAC。

那么先duplicate 到单实例。

然后将单实例转换成RAC。

Oracle 10g 下Rman duplicate 的例子参考：RMAN 异机复制数据库下面我们看一下11gR2下，Active Database Duplicate的步骤如下：1. 创建Auxiliary 库的Initialization Parameter：如果使用spfile，那么在pfile文件里只需要设置一个DB_NAME参数，其他参数会在duplicate 命令中自己设置。

如果使用pfile，那么需要设置如下参数：DB_NAMECONTROL_FILESDB_BLOCK_SIZEDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTDB_RECOVERY_FILE_DEST2. 在Auxiliary库创建Password File 文件对于Backup-based duplication，Password File 不是必须的，但是对于Active Database Duplication，Password File是必须的。

/goto/oraclemidsizeSUMMER 2014RE-IMAGINING DATA MANAGEMENTComplete, integrated solutions for managing growing data volumes and keeping your business running smoothly as it grows.PAGE 2THE SUPPORTED CLOUDLow-risk cloud computing.PAGE 2THE ALWAYS ON BUSINESSStop growth slowing your business down.PAGE 3AUTOMATICALLY BETTER MARKETINGEnsure a better return with less effort.PAGE 4DATABASE AS A SERVICE IN ACTIONSmall business performance transformed.PAGE 5ORACLE MAGAZINE FOR MIDSIZE COMPANIESRe-imagining data managementHow much control do you have over the data you’re business is generating? How much time, money and resources are you using to manage it? Could you be extracting more value from it with less effort if you had the right tools?Having great answers to questions like these could make the difference between your business having a good year or a great one. It could also put you one step ahead of your competitors. The question is how you implement the solutions that put you in that enviable position.Putting your trust in the cloudManaging data and consuming applications as a service requires fewer resources and less investment than owning and managing your own servers within your own building. That’s why it’s so perfect for midsize businesses. And by choosing the rightprovider, you don’t have to worry about the availability or security issues that relying on unsupported open-source solutions can create. Oracle Database 12c was built to be the foundation of an efficient, effective and secure cloud computing environment for fast-growing businesses like yours. Y ou can choose to use it as a service via the Oracle Cloud, alongside powerful SaaS tools, or deploy it in a private cloud environment to retain more control over your most sensitive data.Either way, you get the scalable and high-performance and highly available infrastructure your business needs to continue its successful growth trajectory far into the future.This edition of Oracle ONE Magazine shows you how to use Oracle’s complete range of integrated hardware, software and solutions to manage your data optimally, and tap the power of cloud computing as your business grows. We hope you find it useful.Even clouds need supportFor all its benefits, it still pays to take a cautious approach when building a cloud-based database environment. So you need to ask the question: do you really want to risk business continuity by using a solution that involves reprogramming your apps, and which relies too heavily on unsupported open source technology?Oracle Database 12c , based on proven enterprise-class technology, plugs your business into the cloud with maximum support and minimum risk – all without making any changes to your existing applications.»Register for an Oracle Database 12c webcast now: /goto/technology/meaORACLE ONE MAGAZINE 2Black KeylineREADER01LASER%RELEASED3/242014Oracle and CetelemSpanish Financial Services firm Cetelem, a local subsidiary of the BNP Paribas personal finance division, needed to drive business agility and new revenue by reducing time to market for new products and services.The company used Oracle WebLogic Server, as part of anDealis Fund Operations GmbH is a German joint venture between Allianz Global Investors and DekaBank. Its 330 employees determine the daily price of administered funds based on the The company needed to improve back-office systemperformance to increase the reliability and efficiency of transaction management, while ensuring business continuity without increasing T4-1 and T4-4Oracle and 3 Step IT Group Based in Finland, 3 Step IT Group Oy provides global IT equipment leasing services as well as online asset management and equipment replacement services to around 3,000 customers in Scandinavia, the UK, and across Asia. It needed to guarantee high service availability for its rapidly growing customer base.3 Step IT Group implemented a complete Oracle solution featuring Oracle Database Appliance, Oracle Active Data Guard and Oracle Real Application Clusters. This enabled it to eliminate unplanned maintenance breaks and downtime, and replicate data live between the production site and the standby testing site.»Read the case study for more detail on the benefits now: /goto/technology/mea Single appliances, multiple strengthsThese days, you don’t needa raft of hardware to handle different data management and storage tasks.A single Oracle Database Appliance, combined with a single Oracle Sun ZS3 Storage Appliance can protect your data, enable fast and easy development and testing, rapidly expand storage capacity, tier your data storage intelligently, and lower storage costs.»Want to know if it’s right for you? Take our Oracle Database Appliance readiness test now: /goto/it-infrastructure/meaForget format, justmanage your dataWouldn’t it be nice if all your datawas in the same format – just thinkhow easy it would be to manage!Unfortunately, even Oracle can’tmake that dream a reality. However,with Oracle Database 12c, youcan manage and analyse all yourstructured and unstructured datamore easily.That’s important, because unstructureddata, such as that often producedvia social media and mobile devices,is increasing in volume within manyorganisations. Not only that, this kindof data can often provide some of thefreshest, most useful insights aboutwhat’s going on within a business or acustomer segment right now.Oracle Database 12c simplifies themanagement of unstructured data bysupporting specialized data types for manycommon data formats, making it as easyto interact with as standard relationaldata. And because it’s stored alongsideall your other data, you can use all thesame administrative, monitoring andmanagement features to handle it.»Our white paper reveals the value in moredetail. Read it now:/goto/technology/mea4JOIN US ON THE COUCH。

Library Management System
A library management system, also known as an automated library system is software that has been developed to handle basic housekeeping functions of a library.
Library automation standards
–library industry standards such as MARC and Z39.50
Cataloging module
Used for the creation, storage, retrieval and management of bibliographic records and/or indexes.
Manages library materials - circulation type, location and status; patron database - patron type, profiles, privileges; and other transactions such as computation and payment of overdue fines, lost books, etc.
Need for Library Automation
Provide new services not hitherto possible, Improve the management of their physical and financial
resources, Facilitate wider access to information for their clients,

oracle 多段with as用法-回复什么是Oracle多段WITH AS语句？Oracle多段WITH AS语句是一种被称为“公共表表达式（CTE）”的SQL 语法，它允许我们定义一个或多个临时命名结果集，以供后续查询使用。

这样，我们可以在一个SQL查询中多次引用这个结果集，从而简化复杂查询的编写过程，提高查询的可读性和可维护性。

为什么需要使用Oracle多段WITH AS语句？在Oracle数据库中，为了获取所需数据，我们常常需要进行多个嵌套的子查询。

这样的查询结构不仅使得SQL语句繁琐复杂，也降低了查询的效率。

使用Oracle多段WITH AS语句，我们可以将这些子查询转换为临时命名结果集，使得查询语句更加清晰简洁，同时也提高了查询的性能。

使用Oracle多段WITH AS语句的步骤是怎么样的？Step 1: 定义WITH AS子句及其结果集首先，在你的SQL查询语句之前，使用WITH AS子句来定义一个或多个临时命名结果集。

每个结果集由一个唯一的名称和一个SQL查询语句组成。

可以在结果集中使用列别名来为列命名。

例如，我们想要计算订单表中每个用户的总订单金额，可以使用以下WITH AS语句来定义结果集：WITH order_totals AS (SELECT customer_id, SUM(order_amount) AS total_amount FROM ordersGROUP BY customer_id)Step 2: 引用WITH AS结果集进行查询一旦我们定义了临时命名结果集，就可以在后续的查询中引用它们。

可以像引用表一样引用结果集，并在查询中使用它们来筛选、排序或汇总数据。

例如，我们想要获取订单总金额大于1000的所有用户的详细信息，可以使用以下查询语句：SELECT *FROM customersWHERE customer_id IN (SELECT customer_id FROM order_totals WHERE total_amount > 1000)Step 3: 完成查询，并使用WITH AS语句的结果集最后，在你的查询语句结束后，你可以继续使用WITH AS语句定义的结果集。

Setup of Advanced Pricing Qualifiers and Modifiers at _____________. Steps1.Login and select the Application Administrator Responsibility2.Create the value sets.3.Login and select the Pricing Manager Responsibility4.Setup the Pricing Attributes, and assign the value sets to the declared PricingAttributes5.Review the pl/sql program (mwac_qp_qualifications) to find the parameters topass, and what to receive.6.Setup the attributes and modifiers for the program call within Order Management7.Setup the Pricing Modifier’s qualifications as developed in Step 2 above.8.Change the responsibility to Order Management.9.Test the results using the Pricing Viewer.Login and select the Application Administrator ResponsibilityCreate the value sets using the already created value sets from the DTB team. For a naming convention the DTB team used ‘MFSG_BRAND_VS’ and create the advanced pricing (QP) value set named ‘MFSG_BRAND_QP_VS’.Below is the base value set: MFSG_BRAND_VSCreate the new Value Set as a type of ‘Table’ titled MFSG_BRAND_QP_VS. Once completed press the Edit Information button.Select the Pricing Manager Responsibility.Setup the Qualification Attributes by selecting the Context and Attributes menu option.Enter the name of the qualifier and pricing transacton entity. In the next block, enter the name of the field and attribute. As shown below, BRAND is assigned toQualifier_Attribute34 (you are allowed 31 to 100) for custom qualification attributes.Assign the value set previous done for QP to this attribute:Save your results. Also check to make sure you have the correct value set by pressing the ‘Value Set’ button.Next setup the Attributes call to the MWAC_QP_QUALIFICATIONS pl/sql program.Select the PTE entry (order fullfillment) and qualifier context then Order. Press the Contents button to verify you have the correct attribute:The screen below provides the validation of the BRAND qualifier. Press the value sets button to validate the correct Value Set was chosen. If not then update this screen. Once completed press the save icon and go back one screen.Now press the Link Attributes buttonSearch and select the Qualifier Attribute you are looking for. Select the level at which this qualifier is used for (sales order header, line level, or both) – see high lite below. Then press the Attribute Mapping button.Select the Request Type that is being updated – in this case select the 3rd row for Order Management.Validate you have the correct qualifier attributes, and press the Attribute Mapping button. The screen below should be displayed. Next press the Oracle Pricing line for Order Management Order, and update the calling segments (by line, header, or both depending upon where the call is made (SO line, SO header, or both). Update the User Source Type to PL/SQL API, and set the User Value String to call the custom program. In the example below only the sales order line is used to pass information. The value in the blue highlite is‘MWAC_QP_QUALIFICATIONS.GET_QP_BRAND(OE_ORDER_PUB.G_LINE.LIN E_ID)’ which means the pl/sql package mwac_qp_qualifications is being called to get the Brand Value get_qp_brand by using the line_id in the program headerow_order_pub.g_line.line_id.Save your changes and exit until you get to the Main Menu screen.Note: The passing value (ie layout) of the sales order line id can be found in Oracle’s Order Management User Guide.Select the Modifier setup on the menuQuery the modifer that will be using the qualifier already developed.Press the Qualification list at either the modifer’s header or line level (header is the top part of the form “List Qualifier” and line is the lower portion of the form “Line Qualifier”). In the example below the List Qualifier was pressed and the qualifier was entered as the second condition.Select the epsilon (…) and a pop up of your previously defined value set appears (in this case ‘MFSG_BRAND_QP_VS’. In this example MII was chosen as the BRAND.Update the Start date and press the OK button. This will save your results. From here exit all the way to the main menu, select the Order Management responsibility, and test your Price modifier.Select the Order Management responsibility and start testing the Pricing Modifier results.Press the Sales Orders menu optionEnter the sales order.Enter the sales line and press the save button (this should automatically price the sales line). If not press the Actions button and press the Price Line command (see next screen shot).Press ok to price the line.Validate the updates by viewing the price adjustments (press the Action and then View Adjustments command line).The modifiers from TEST REP were used to update the pricing in this order.Another way to view any adjustment is to use the pricing engine’s viewer screen. First turn on the profile option to show the debug on/off status. Turn this on.Next select the view on the Tools menu option.Now you can review the details of the pricing engine. This shows all the qualifiers and modifiers that were used to derive the final price on each line and order.Press which line was changed (in this case sale order line #2 was inserted).Press the Attributes button. Then the screen below shows with all the qualification attributes and their values.By pressing the Debug button you can see the actual Qualification Attributes and the resulting values.End of document.You are ready to setup and test your Advanced Pricing Qualifiers and Modifiers.PL/SQL Program ListingCREATE OR REPLACE PACKAGE MWAC_QP_QUALIFICATIONS AUTHID CURRENT_USERAS/*******************************************************************************************************-- **-- (c) Oracle India Private Limited, NAIO **-- **-- Package Name: MWAC_QP_QUALIFICATIONS Author: ArunMalhotra **-- (SPECIFICATION) **--**-- Creation Date: 24-MAR-2005 **-- Version Number: 1.0 Last Modified:24-MAR-2005 **-- **-- **-- Development and Maintenance History: **-- **-- Date Nodified By Current VersionDescription **-- ---------- ----------- ---------------- --------------- **-- 24/03/2005 Arun Malhotra 1.0 Initial VersionCoding **-- **--****************************************************************************************************/FUNCTION Get_QP_SALESREP (p_salesrep_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_ZIPCODE (p_ship_to_site_code_id IN NUMBER)RETURNVARCHAR2;FUNCTION Get_QP_STATE (p_ship_to_site_code_id IN NUMBER)RETURNVARCHAR2;FUNCTION Get_QP_BRAND (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_BRAND_ID (p_order_line_id IN NUMBER)RETURN NUMBER;FUNCTION Get_QP_PRODLINE (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_PRODTYPE (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_PRODLNGRP (p_order_line_id IN NUMBER)RETURN VARCHAR2;END MWAC_QP_QUALIFICATIONS;/PL/SQL Package BodyCREATE OR REPLACE PACKAGE BODY MWAC_QP_QUALIFICATIONSAS/*******************************************************************************************************-- **-- (c) Oracle India Private Limited, NAIO **-- **-- Package Name: MWAC_QP_QUALIFICATIONS Author: ArunMalhotra **-- (SPECIFICATION) **--**-- Creation Date: 24-MAR-2005 **-- Version Number: 1.0 Last Modified:24-MAR-2005 **-- **-- **-- Development and Maintenance History: **-- **-- Date Nodified By Current VersionDescription **-- ---------- ----------- ---------------- --------------- **-- 24/03/2005 Arun Malhotra 1.0 Initial VersionCoding **-- **--****************************************************************************************************/--FUNCTION Get_QP_SALESREP (p_salesrep_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_salesrepname (l_salesrep_id NUMBER)ISSELECT nameFROM jtf_rs_salesreps jrsWHERE jrs.salesrep_id = l_salesrep_id;lc_name jtf_rs_%TYPE;BEGINlc_name :='';OPEN get_salesrepname (p_salesrep_id);FETCH get_salesrepname INTO lc_name;IF(get_salesrepname%NOTFOUND)THENRETURN lc_name;END IF;CLOSE get_salesrepname;RETURN lc_name;END Get_QP_SALESREP;FUNCTION Get_QP_ZIPCODE (p_ship_to_site_code_id IN NUMBER) RETURN VARCHAR2ISCURSOR get_postalcode (l_ship_to_site_code_id NUMBER)IS SELECT loc.postal_codeFROM HZ_CUST_SITE_USES_ALL SITE,HZ_PARTY_SITES PARTY_SITE,HZ_LOCATIONS LOC,HZ_CUST_ACCT_SITES_ALL ACCT_SITEWHERE SITE.SITE_USE_CODE ='SHIP_TO'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_IDAND PARTY_SITE.LOCATION_ID = LOC.LOCATION_IDAND SITE.SITE_USE_ID = l_ship_to_site_code_id;lc_postalcode HZ_LOCATIONS.POSTAL_CODE%TYPE;BEGINlc_postalcode :='';OPEN get_postalcode(p_ship_to_site_code_id);FETCH get_postalcode INTO lc_postalcode;IF(get_postalcode%NOTFOUND)THENRETURN lc_postalcode;END IF;CLOSE get_postalcode;RETURN lc_postalcode;END Get_QP_ZIPCODE;FUNCTION Get_QP_STATE (p_ship_to_site_code_id IN NUMBER) RETURN VARCHAR2ISCURSOR get_state (l_ship_to_site_code_id NUMBER)IS SELECT loc.stateFROM HZ_CUST_SITE_USES_ALL SITE,HZ_PARTY_SITES PARTY_SITE,HZ_LOCATIONS LOC,HZ_CUST_ACCT_SITES_ALL ACCT_SITEWHERE SITE.SITE_USE_CODE ='SHIP_TO'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_IDAND PARTY_SITE.LOCATION_ID = LOC.LOCATION_IDAND SITE.SITE_USE_ID = l_ship_to_site_code_id;lc_state HZ_LOCATIONS.STATE%TYPE;BEGINlc_state :='';OPEN get_state(p_ship_to_site_code_id);FETCH get_state INTO lc_state;IF(get_state%NOTFOUND)THENRETURN lc_state;END IF;CLOSE get_state;RETURN lc_state;END Get_QP_STATE;FUNCTION Get_QP_BRAND (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_brand (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment3FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_brand MTL_CATEGORIES_B.SEGMENT3%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_brand :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_brand;END IF;CLOSE get_item_id;OPEN get_brand(ln_item_id,ln_ship_from_org_id);FETCH get_brand INTO lc_brand;IF(get_brand%NOTFOUND)THENRETURN lc_brand;END IF;CLOSE get_brand;RETURN lc_brand;END Get_QP_BRAND;FUNCTION Get_QP_BRAND_ID (p_order_line_id IN NUMBER)RETURN numberISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_brand_id (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT ffv.flex_value_idFROM fnd_flex_values ffv,fnd_flex_value_sets ffvsWHERE ffv.FLEX_VALUE_SET_ID = ffvs.flex_value_set_idAND ffvs.flex_value_set_name ='MFSG_BRAND_VS'AND ffv.flex_value =(SELECT mc.segment3FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID =mic.CATEGORY_SET_IDAND mic.inventory_item_id =l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID);lc_brand MTL_CATEGORIES_B.SEGMENT3%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type;ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;ln_brand_id number;BEGINln_brand_id :=0;OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN ln_brand_id;END IF;CLOSE get_item_id;OPEN get_brand_id(ln_item_id,ln_ship_from_org_id);FETCH get_brand_id INTO ln_brand_id;IF(get_brand_id%NOTFOUND)THENRETURN NULL;END IF;CLOSE get_brand_id;RETURN ln_brand_id;END Get_QP_BRAND_ID;FUNCTION Get_QP_PRODLINE (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodline (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment4FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodline MTL_CATEGORIES_B.SEGMENT4%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodline :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodline;END IF;CLOSE get_item_id;OPEN get_prodline(ln_item_id,ln_ship_from_org_id);FETCH get_prodline INTO lc_prodline;IF(get_prodline%NOTFOUND)THENRETURN lc_prodline;END IF;CLOSE get_prodline;RETURN lc_prodline;END Get_QP_PRODLINE;FUNCTION Get_QP_PRODTYPE (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodtype (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment5FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodtype MTL_CATEGORIES_B.SEGMENT5%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodtype :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodtype;END IF;CLOSE get_item_id;OPEN get_prodtype(ln_item_id,ln_ship_from_org_id);FETCH get_prodtype INTO lc_prodtype;IF(get_prodtype%NOTFOUND)THENRETURN lc_prodtype;END IF;CLOSE get_prodtype;RETURN lc_prodtype;END Get_QP_PRODTYPE;FUNCTION Get_QP_PRODLNGRP (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodlngrp (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment6FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodlngrp MTL_CATEGORIES_B.SEGMENT6%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodlngrp :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodlngrp;END IF;CLOSE get_item_id;OPEN get_prodlngrp(ln_item_id,ln_ship_from_org_id);FETCH get_prodlngrp INTO lc_prodlngrp;IF(get_prodlngrp%NOTFOUND)THENRETURN lc_prodlngrp;END IF;CLOSE get_prodlngrp;RETURN lc_prodlngrp;END Get_QP_PRODLNGRP;END MWAC_QP_QUALIFICATIONS;/。

Oracle的imp/exp组件是我们常用的工具，它的一个操作原则就是向下兼容。

下面是据此总结的几个使用规则和相关测试：规则1：低版本的exp/imp可以连接到高版本（或同版本）的数据库服务器，但高版本的exp/imp不能连接到低版本的数据库服务器--1.1 使用9i客户端通过imp连接到10g数据库C:\Documents and Settings\yuechaotian>exp userid=hdtest/test@s67tables=(ab01) rows=n file=d:\x.dmpExport: Release 9.2.0.1.0 - Production on 星期三 2月 20 10:09:55 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集注: 将不会导出表数据（行）即将导出指定的表通过常规路径 .... . 正在导出表 AB01在没有警告的情况下成功终止导出。

--1.2 使用10g客户端通过imp连接9i数据库：连接失败（而不是导出失败）C:\Documents and Settings\yuechaotian>exp userid=hbjb_kf_hd/test@s46 owner=hdtest file=d:\x.dmpExport: Release 10.2.0.1.0 - Production on 星期三 2月 20 09:57:22 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.EXP-00056: 遇到 ORACLE 错误 6550ORA-06550: 第 1 行, 第 41 列:PLS-00302: 必须说明 'SET_NO_OUTLINES' 组件ORA-06550: 第 1 行, 第 15 列:PL/SQL: Statement ignoredEXP-00000: 导出终止失败规则2：高版本exp出的dmp文件，低版本无法imp（无法识别dmp文件）--2.1 使用10g客户端exp出10g的数据C:\Documents and Settings\yuechaotian>exp userid=test/test@orclowner=test file=d:\10g.dmpExport: Release 10.2.0.1.0 - Production on 星期三 2月 20 11:16:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服务器使用 AL32UTF8 字符集 (可能的字符集转换)即将导出指定的用户...……导出成功终止, 但出现警告。

Oracle多⾏记录合并连接聚合字符串的⼏种⽅法什么是合并多⾏字符串（连接字符串）呢，例如：SQL> desc test;Name Type Nullable Default Comments------- ------------ -------- ------- --------COUNTRY VARCHAR2(20) YCITY VARCHAR2(20) YSQL> select * from test;COUNTRY CITY-------------------- --------------------中国台北中国⾹港中国上海⽇本东京⽇本⼤阪要求得到如下结果集：------- --------------------中国台北,⾹港,上海⽇本东京，⼤阪实际就是对字符实现⼀个聚合功能，我很奇怪为什么Oracle没有提供官⽅的聚合函数来实现它呢：）下⾯就对⼏种经常提及的解决⽅案进⾏分析（有⼀个评测标准最⾼★★★★★）：1.被集合字段范围⼩且固定型灵活性★性能★★★★难度★这种⽅法的原理在于你已经知道CITY字段的值有⼏种，且还不算太多，如果太多这个SQL就会相当的长。

看例⼦：SQL> select t.country,2 MAX(decode(t.city,'台北',t.city||',',NULL)) ||3 MAX(decode(t.city,'⾹港',t.city||',',NULL))||4 MAX(decode(t.city,'上海',t.city||',',NULL))||5 MAX(decode(t.city,'东京',t.city||',',NULL))||6 MAX(decode(t.city,'⼤阪',t.city||',',NULL))7 from test t GROUP BY t.country8 /COUNTRY MAX(DECODE(T.CITY,'台北',T.CIT-------------------- ------------------------------中国台北,⾹港,上海,⽇本东京,⼤阪,⼤家⼀看，估计就明⽩了（如果不明⽩，好好补习MAX DECODE和分组）。

Step 3. Is service handlers in blocked state Check if the handlers are in blocked state. Check the output of the 'l n c l e ice '. Examine the status information under the database service name. From the listener log you would know which database service was affected by the 12518 error, now with the output of the 'lsnrctl services' under that service name gather more information.
$ lncl a ----L i e n e P aa m ee F i l e/ oa 1 0 g / h o m e _ oa 1 0 g / n e ok / a d m i n / l i e n e. oa L i g / h o m e _ oa 1 0 g / n e ok / l o g / l i e n e. l o g -----
Troubleshooting Details
Section I: Steps to approach ORA-12518/TNS-12518 Error:
ORA-12518/TNS-12518 indicates a problem while listener hands off the client connection to the server process or dispatcher process.TNS-12518 is logged in the listener log. The client might receive ORA-12518 or some other disconnection errors like ORA-12537. Once TNS-12518 is noted in the listener log, follow the below steps to approach and resolve this error. Let us have a small discussion about how actually database connections are made: In Dedicated mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener spawns a dedicated server process and hands off the client connection to this dedicated server process. TNS-12518 indicates a problem while handing off the client connection to the server process. In Shared Server mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener hands off the client connection to one of the dispatcher configured for that service. TNS-12518 indicates a problem while handing off the client connection to the dispatcher server process. Though this error is logged in the listener log, the listener is just the messenger, ORA-12518/TNS-12518 is mostly related to RDBMS and OS resources.

Oracle PeopleSoft Strategic SourcingOracle’s PeopleSoft Strategic Sourcing, enables your organization to streamline its RFx processes, conduct real-time auctions, and strategically award contracts or purchase orders. The RFx process consists of formulating requirements, selecting and inviting bidders, receiving bids, scoring and analyzing the results, negotiating terms, and awarding the contract. Organizations experience unprecedented levels of profitability, control, and efficiency when buying goods and services.The Enterprise SolutionOrganizations recognize the importance of strategic procurement practices as a key component to improve profitability. Request for quote and request for proposal (RFx) activities focus on finding the best value and lowest total cost Supplier, rather than simply searching for the lowest price. Tools, such as online auctions are used to increase Supplier competition and benefits organizations across a growing number of industries. Spend analysis, such as monitoring contracts and optimizing Supplier performance are critical components when making sound sourcing decisions, and identifying opportunities for untapped value in the supply chain.Strategic sourcing refers to the process of determining the best Suppliers for goods or services, and the conditions by which you award them your business. The strategic element of this definition comes from the ability to apply different methodologies to create the most value in your supply chain. This value can come in the form of pricing, warranty terms, quality, delivery accuracy, or other factors. Strategic sourcing enables you to quantify and balance these requirements so organizations can achieve specific business objectives. A complete solution built on portal technology, reverse auction capabilities, and proposal analysis tools delivers significant value to organizations. Using PeopleSoft Strategic Sourcing, organizations can:Efficiently onboard Bidders with self-service registration.Reduce procurement costs using competitive bidding and reverse auctions. Align purchasing objectives and execution with overall business strategy.Automate and control the RFx and auction process, both inside and outside the enterprise. Key FeaturesComplete internet-based sourcing solutionAbility to invite bidders to participate without having tocreate them in the Suppliermaster fileSupport for multiple business processes such as auctions,private offers, basic request-for-quotes, and formal,sealed requests-for-proposalsProvide reverse auctioncapabilities and proposalanalysis toolsSourcing Event creation with round and version controlWeight assigned to BidFactors for responseoptimization and evaluationConfigurable Bidder search criteriaFilter criteria for EventWorkbenchSupplier registration to bid onlineBid forms available fordownload on the SupplierPortalBidder may respond online leveraging an easy-to-useactivity guideIntegrate with third-party Event Notification servicesSegregation of dutiesEvent collaboration securityPublic notification for Notice of Intent to Award and Notice of AwardProposal response analysis and awardLeverage existing supplier and customer relationships while reaching out to new trading partners.Effectively track Bidder participation and results.Retain knowledge and strategies in the application for future use andperformance analysis.Analyze awards to determine the optimal award.Plan sourcing activities using planning tools that help track progress.Estimate the total cost of transactions with Suppliers beyond the price.Track Supplier participation in the Sourcing Events you manage with the "My Sourcing Events" tile on the Procurement Operations Homepage.Figure 1. My Sourcing Events allows you to track the purpose of the sourcing events that you manage. In addition to supporting reverse auctions, PeopleSoft Strategic Sourcing auction supports selling events (forward auctions); Strategic Sourcing users can leverage this ability to auction off excess inventory or retired assets. A single product provides both buying and selling capabilities.The PeopleSoft Strategic Sourcing AdvantagePeopleSoft Strategic Sourcing sets the standard for online auctions and RFx activities. From rapid integrations to global market reach, our solutions enable the performance-driven enterprise. Immediate benefits include:Rapid decision-making and results.Automated and paperless transactions.Reduced purchase cycles and shortened negotiating times, enabling you to source a larger percentage of your spending.Fair-market pricing.Increased competition among Suppliers.Enhanced collaboration with key stakeholders in the line of business.Ability to implement selectively, based on strategic objectives. Key BenefitsReduce procurement costs through competitive biddingand reverse auctionsAlignment of purchasing objectives and execution ofbusiness strategyLong-term strategic Supplier relationships based on bestvalue and lowest cost ofSupplier performanceBuilt-in sell-side capability to increase revenue, decrease inventory/retired assets, and open new sales and liquidation channels.Product OverviewPeopleSoft Strategic Sourcing offers cost-saving features that streamline business processes and harness the potential of the internet. Strategic Sourcing enables organizations to:Develop RFx strategies and documents including weighted, user-defined, non-price sourcing factors such as warranty, lead-time, defect rate, andmore.Author a contract document at the time of RFx creation so Bidders have full visibility to terms.Invite named Suppliers to participate, or open a purchasing event to the general public.Hold a sealed event in which the buyer cannot review responses until the submission period has closed.Leverage knowledge from internal experts through online collaboration, while ensuring not all collaborators have access to sensitive data.Perform a "what if" analysis by adjusting factors, weighting, and scores.Award events into a purchasing application as purchase orders or contracts. Save strategies and results as templates, capturing results for future use.Facilitate collaboration between Buyers and Suppliers through discussion forums.Supplier ResponsePeopleSoft Strategic Sourcing provides a collaborative environment for Suppliers to access and respond to sourcing events in real-time, improving collaboration, participation, and employee productivity. If a Supplier cannot respond online, a PDF document of the event can be downloaded, printed, or faxed. The buying organization can also enter responses on behalf of Suppliers if necessary.When Suppliers log onto the system, they can access events to which they were specifically invited and decide how to respond. They can also search public events for other opportunities.Strategic Sourcing provides Suppliers with email notifications when they are invited to participate in an event. They can select a link in the email to access their Supplier login. If the Supplier has not previously participated in an event, they are asked to register as a Bidder.The module also enables organizations to notify Suppliers when changes are made to an event that is in progress, such as when amendments are issued with new requirements or an update is made to the terms and conditions This enables Suppliers to always be aware of the latest event activity. In turn, Supplier can quickly react and adjust responses accordingly.Bid responses are streamlined and intuitive, even for a newly on-boarded Bidders, walking them through a step-by-step activity guide to provide the required answers to key questions about the event.Figure 2. Bidder Response is the first step of the activity guide.Response Analysis and AwardWith PeopleSoft Strategic Sourcing, organizations can analyze Supplier’s bid responses based on several criteria, such as lowest price, best score, and lowest total costs. All three methods can be used simultaneously to give you the most information to make the best award decision.Determining the best way to fulfill sourcing needs usually involves looking at more than just price. The objective is to find the best overall value. Total Cost modeling provides a systematic way to compare bid factors in real dollar terms. Organizations can define cost-contributing bid factors such as switching the cost to a new Supplier, or the cost of longer lead times. Buyers can translate a Supplier’s bid response into a contribution to the total cost, and evaluate the bids based on the overall cost.Supplier responses can also be scored based on weights assigned to bid factors, and the corresponding answers given by participating Suppliers. Organizations can also enter scores for other “hidden” criteria not requiring a specific Supplier response, such as how they performed during a product demonstration, or what Buyers think of their customer service. PeopleSoft Strategic Sourcing enables “what-if” analysis so that bid reviewers can change the weight of the bid factors and analyze the impact of response scores. This enables you to fine-tune the requirements and evaluations based on specific circumstances. Organizations often have complex sourcing events that make the decision making process more difficult than laying bids side-by-side when determining which supplier is best, and how a contract should be allocated and awarded. To address these sourcing complexities, organizations can use sourcing optimization to determine the optimal award strategy that accounts for all the criteria specified in the bidding event. An organization’s defined policies and constraints is applied during optimization. Bid reviewers can also perform “what-if” analysis, such as adding and removing constraints, to see the impact on the optimal award recommendation. After bids have been tabulated and analyzed, the event can be awarded to one or more Suppliers. Related ProductsThe following PeopleSoft products integrate with Oracle PeopleSoft Strategic Sourcing:eProcurementPurchasingeSupplier ConnectionSupplier ContractManagementRelated ServicesThe following services support Oracle PeopleSoft Strategic Sourcing:Update Subscription ServicesProduct Support ServicesProfessional ServicesThe process of awarding an event can include automatically creating a purchaseorder or contract using PeopleSoft Purchasing.When a Sourcing Event is completed and the Awardees are determined,organizations can publish the successful bids with a “Notice of Intent to Award”capability on the Public Portal.Figure 3. Example of the Supplier Portal Public page with the Intent to Award tile that displays thenumber of notices.Connect with usCall +1.800.ORACLE1 or visit . Outside North America, find your local office at: /contact. /oracle /oracleCopyright © 2023, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.This device has not been authorized as required by the rules of the Federal Communications Commission. This device is not, and may not be, offered for sale or lease, or sold or leased, until authorization is obtained. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. 0120Disclaimer: If you are unsure whether your data sheet needs a disclaimer, read the revenue recognition policy. If you have further questions about your content and the disclaimer requirements, e-mail ********************.。

oracle同时多行转多列函数Oracle中没有直接提供将多行转换为多列的函数。

但是可以使用一些技巧和功能来实现这样的转换。

下面将介绍一种常见的实现方法。

一、场景描述假设有一个表格名为"example_table"，具有以下结构：```ID | Name | Value---+-------+-------1 | John | 101 | John | 201 | John | 302 | Alice | 152 | Alice | 25```我们需要将上述表格中的数据按照ID和Name两个列进行分组，并将对应的Value值转换为多列。

如下所示：```ID | Name | Value_1 | Value_2 | Value_3---+-------+---------+---------+--------1 | John | 10 | 20 | 302 | Alice | 15 | 25 |```二、解决方案我们可以使用Oracle中的PIVOT功能以及ROW_NUMBER()函数来实现上述转换。

具体步骤如下：Step 1：使用ROW_NUMBER()函数为每个ID和Name对应的Value值分配序号。

```SELECT ID, Name, Value, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY Value) AS rnFROM example_table;```Step 2：使用PIVOT函数将序号为1、2、3的Value值分别转换为Value_1、Value_2、Value_3列。

```SELECT ID, Name, Value_1, Value_2, Value_3FROM (SELECT ID, Name, Value, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY Value) AS rnFROM example_table)PIVOT (MAX(Value)FOR rn IN (1 as Value_1, 2 as Value_2, 3 as Value_3));```三、实际示例为了更加直观地展示上述解决方案的实际效果，我们可以将其应用到一个真实的示例中。

Appreciate need for library automation Understand basic features and functions of Library
Management System Define the role of standards in library automation
and resource sharing Making a plan for library automation Evaluate an integrated library management system Prepare and implementation strategy
Scope
User interface
–graphical user interface (GUI) is the norm for current systems because users find it easier to work with and it allows a wide range of tasks to be accomplished with a click of a mouse
Library automation standards
–library industry standards such as MARC and Z39.50
Cataloging module
Used for the creation, storage, retrieval and management of bibliographic records and/or indexes.
Library Management System
A library management system, also known as an automated library system is software that has been developed to handle basic housekeeping functions of a library.

Oracle存储过程语法Oracle存储过程基本语法存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END;⾏1: CREATE OR REPLACE PROCEDURE 是⼀个SQL语句通知Oracle数据库去创建⼀个叫做skeleton存储过程, 如果存在就覆盖它; ⾏2: IS关键词表明后⾯将跟随⼀个PL/SQL体。

⾏3: BEGIN关键词表明PL/SQL体的开始。

⾏4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中⾄少需要有⼀句; ⾏5: END关键词表明PL/SQL体的结束存储过程创建语法： create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; --vs_msg VARCHAR2(4000); 变量2 类型（值范围）;复制代码代码如下:Begin Select count(*) into 变量1 from 表A where列名=param1；If (判断条件) then Select 列名 into 变量2 from 表A where列名=param1； Dbms_output。

Put_line(‘打印信息'); Elsif (判断条件) then Dbms_output。

Put_line(‘打印信息'); Else Raise 异常名（NO_DATA_FOUND）; End if; Exception When others then Rollback; End; 注意事项：1，存储过程参数不带取值范围，in表⽰传⼊，out表⽰输出类型可以使⽤任意Oracle中的合法类型。

2，变量带取值范围，后⾯接分号3，在判断语句前最好先⽤count（*）函数判断是否存在该条操作记录4，⽤select 。

OracleEBS个性化⼗⽉总结⼆<个性化总结> Author: jun.zhan Creation Date: April 22, 2013 Last Updated: April 22, 2013 Document Ref: MD120 Version: 1.0安装⽂档⽂档控制更改记录审阅分发记录⽬录⽂档控制................................................................................................................................................... i i 个性化调⽤请求,报表,存储过程 (1)利⽤FORM个性化调⽤请求 (1)利⽤FORM个性化跳转块 (4)利⽤FORM个性化增加web link (7)Form个性化修改标准Form的LOV (8)利⽤FORM个性化插⼊或更新数据库 (11)解决和未解决的问题 (13)已解决的问题 (13)未解决的问题 (13)个性化调⽤请求,报表,存储过程本⽂将详细介绍个性化的应⽤仅作参考利⽤FORM个性化调⽤请求主要内容1)利⽤全局变量直接调⽤请求2)利⽤全局变量调⽤存储过程在调⽤请求Setp by Step 截图利⽤全局变量直接调⽤请求:利⽤全局变量调⽤存储过程在调⽤请求:安装⽂档利⽤FORM个性化跳转块主要内容1) 利⽤内置直接调⽤块(不含From操作)2) 利⽤内置传参数给from,在from中做逻辑处理后跳转Setp by Step 截图利⽤内置直接调⽤块(不含From操作):利⽤内置传参数给from,在from中做逻辑处理后跳转:eg:在销售订单的订单头模块的活动中启⽤功能'销售回款计划'，只有当订单为登记状态时，才允许进⼊。

1、先在when-new-form-instance触发器中添加菜单项<例⼦添加的是special41>；如果只需要在某个特定的块启⽤某功能的话，在表单个性化的活动中的‘已在块中启⽤’选择启⽤的块;2、在菜单项special41触发器中校验逻辑是否成⽴3、在菜单项special41中启⽤功能。

Oracle 11gr2 软件安装和数据库创建步骤详解本文是一篇step-by-step 文档，演示了如何安装oracle 数据库软件以及使用DBCA创建数据库。

同时，我们对每一步的功能，注意事项和容易犯的错误都进行了描述。

希望对大家了解数据库安装过程有所帮助。

安装数据库软件Start The Installer请参考各平台对应的安装手册，在满足安装前提条件下运行如下命令来启动数据库安装：$ /media/database/runInstallerConfigure Security Updatess本步骤可以选择提供您的My Oracle Support email来接收Oracle安全通知，安装产品和初始化Oracle配置管理器。

●EMAIL:填写您的My Oracle Support email或者用户名●I wish to receive security update via My Oracle Support. 该项选中则表明您期望通过My Oracle Support来接收安全更新。

推荐选项：如果您有My Oracle Support账户，建议填写您的EMAIL和密码来接收Oracle安全更新；否则您可以跳过该步骤。

如果您跳过该步骤，会弹出一个警告：点击Yes来确认您的选择：Download Software Updates从Oracle11.2.0.2开始，您可以使用软件更新选项来动态下载和应用最新的软件更新，该步骤可以为本次的安装选择软件更新，比如推荐的更新，PSU或者其他推荐的补丁:该步骤有3个选项：●Use My Oracle Support credentials for download: 该选项指的是在线下载更新，需要您的主机能连上My Oracle Support网站，并且需要提供您的My Oracle Support账户和密码●Use pre-download software update: 该选项适用于您已经将所需的软件更新下载到本地，您可以直接指定本地的已经下载了软件更新的路径。

TEXTURA:Customer Introduction to My Oracle Support (MOS) Training for Textura PQM CustomersAgendaChanges for Textura CustomersNew Terminology, Product Mapping, Data Migrations Getting started with My Oracle Support Learn how to Create a Service RequestTransition Resources and Additional Learning Options1 2 34 5Changes for Textura PQM Support UsersTelephone Numbers:All telephone numbers you use today to obtain assistance will remain the same and will not change Support Email Addresses:–Customer specific email addresses will remain available–Generic Support email addresses will remain availableWhat’s Not Changing for CustomersWhat’s Changing for CustomersNovember 13, 2018: PQM customers will have access to the My Oracle Support (MOS) portal for creating and tracking Service Requests (SR) The Support Identifier (SI) was sent to customer contacts via email the week of November 5, 2018Email subject: “Welcome to My Oracle Support: Access for Textura PQM Customers”A Support Identifier (SI) may be required to register on My Oracle Support and to create and track SRs on My Oracle SupportYour SI can also be used when working with Oracle Textura Support by phone beginning November 13, 2018Support Identifiers (SI)Your Support Identifier (SI) is the key to Oracle support:Tells Oracle who you areIdentifies products you haveVerifies support entitlement because it is tied to your cloudsubscriptionAccess to your SI and your associated privileges in My OracleSupport are managed by the customer, through a CustomerUser Administrator (CUA)NOTE: Customers may authorize their systems integrator, managed services provider, or other service providers to access My Oracle Support on their behalf. All access to My Oracle Support is governed by the Terms of Use.Product DocumentationWhere can I find the Textura product documentation? Oracle Textura Product Documentationhttps:///en/industries/construction-engineering/New Terminology, Product Mapping, and Data MigrationTerminologyOracle Terminology Textura PQM Terminology DescriptionMy Oracle Support (MOS) None The customer accessible web support portal, My OracleSupport. will be available for all Textura productsSupport Identifier (SI) None A unique identifier used to access Support, that correlatesto the cloud subscription and supported productsService Request (SR) Case or Ticket A specific customer reported request for assistance Knowledge Document None Helpful information about common issues, frequently askedquestions and product informationBug Defect Possible defect in the existing code or functionality notperforming as documentedEnhancement Request (ER) None An enhancement is a request to add functionality or tochange the behavior of existing functionality. Enhancementsare delivered via scheduled releases.Textura Product Mapping•The SR Create process will display a list of product names related to SIs in your My Oracle Support account•SI relates to a specific cloud subscription and the covered products•Select the appropriate product name based on the issue you are reporting•A product name cross reference list mapping Textura product names to the product names displayed in MyOracle Support is available in the Textura Welcome CenterTextura Product Name Oracle Textura Product NamePre-Qualification Management Oracle Textura Pre-Qualification Management Cloud Service Early Payment Program Oracle Textura Payment Management Cloud Service GradeBeam Invitation to Bid Oracle GradeBeam Invitation to Bid Cloud ServiceLatista Field Management Oracle Latista Field Management Cloud Service Construction Payment Management Oracle Textura Payment Management Cloud Service Submittal Exchange Primavera Submittal Exchange Cloud ServiceData Migration Migration to My Oracle Support•New Textura SRs formerly known as tickets or cases can be created in My Oracle Support Portal as of November 13, 2018 for PQM customers •Open SRs were migrated to My Oracle Support on April 30, 2018 •Some closed PQM SRs were migrated to My Oracle Support in June 2018•Note: SRs are assigned a unique SR tracking number in My Oracle Support for use in all interactions with Oracle Textura Support•Textura Support Center Documentation and Knowledge Base •Knowledge articles are available in My Oracle Support •Product documentation is available on Oracle Construction and Engineering DocumentationRegistering for My Oracle SupportRegistering for My Oracle Support Have you registered?https://Registering for My Oracle Support -Oracle Account CreationRegistering for My Oracle Support -Confirmation of Account CreationRegistering for My Oracle Support -Email ConfirmationRegistering for My Oracle Support -Support Identifier RegistrationNavigate to: to get started.Register your Support IdentifierRegister your Support IdentifierWhat is a CUA?Customer User Administrators (CUAs) areresponsible for:•Approving or denying requests for access to SIs•Assigning, removing, changing User privileges•Maintaining users by Support identifierOracle Support Training•Oracle Support How-To Training: Cloud Support Portal –How to use the CUAFunctionalityRegister your Support IdentifierLearn more by reviewing KnowledgeDocument ID 1278196.1My Account: Manage your Account (Cloud UI)Customer User Administrator (CUA)Customer User Administrator Granting User Access**********************************************Organization NameRequester 55555555Requester 55555555Service Request Flow and Best PracticesTextura Support Assistance Matrix Request Type ContactQuestions regarding use of or access to My Oracle Support (MOS) Call Oracle Textura Support via the Oracle Textura Support hotlinesTechnical product issues Call Oracle Textura Support or create an SR via MyOracle SupportProduct defects Call Oracle Textura Support or create an SR via MyOracle SupportQuestions regarding the use of the product Call Oracle Textura Support or create an SR via My Oracle SupportProduct enhancement requests Call Oracle Textura Support or create an SR via My Oracle SupportCreating a Cloud Service RequestClick on Cloud SupportNavigate to: SR Creation -Cloud UI DashboardSRs loggedby youMy AccountSettings andLogoutSRs loggedby otherusersCreatenewServiceRequestCreate SR using icon pre-populates Service Type/Name fieldsCreate Service Request buttonEasy 3-step process Proceed to next stepSelect Problem Type CarefullyAnswer all mandatory and optional questionsVerify contact informationSet the SeverityResolutionPartnershipCreating a Cloud Service Request – Severity Levels • Set the severity level appropriately to represent the urgency of your issue • Severity 3 is the defaultSeverityLevel Business Impact Technical Impact 1Mission Critical Business Impact 2Serious Business Impact 3Minor Business Impact 4 No Business ImpactService Request Flow: Severity / Contact Severity Level details and more are described in the Hosting and Delivery Policy for your productsSeverity Level 1 Service RequestsIf you do not have the resourcesto work 24x7,select ‘No’Provide additional detailsUpload screenshots, log files, etc. Proceed to next stepSupport Service LevelsOracle Service Levels•Oracle will continue to fulfill the terms of current Textura agreements until expired or terminated •Prior to expiration of the Textura contract and once Support is moved to My Oracle Support, Oracle will deliver the higher level of service whether that is the current Textura terms or Oracle’s standardcloud support terms•For additional detail, review the following support policies:Oracle Cloud Services -Hosting and Delivery PoliciesTransition Resources and Additional Learning OptionsTransition ResourcesTextura Support site•Support Process Guide for Oracle Textura Customers•Support Assistance Matrix•Support Phone Numbers and Email AddressesTextura Welcome Center on My Oracle Support•Customer Training slides and Link to Training recording•Customer FAQ•Product Cross Reference•Customer communicationsTextura Training Resources pageOracle Textura Product DocumentationReference modules for all functionality covered in this presentation•How to use My Oracle Support•Cloud Support Portal –How to use the CUA FunctionalityCopyright © 2018, Oracle and/or its affiliates. All rights reserved. |41New slide replacingthe Q&A slide whichdoes not apply hereTHANK YOU for watching thisTextura PQM Customer Transition toMy Oracle Support TrainingCopyright © 2018, Oracle and/or its affiliates. All rights reserved. | 4243。