Knowledge

Saturday, July 29, 2017

OCA Cretificate Practice

Oracle Exam

Q: 1)
Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will
display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate
the DEPT table
Answer:A,D,F

Q:2)
QUESTION NO: 2
You need to design a student registration database that contains several tables storing academic
information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID.
The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table
that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the
foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY
students(student_id));
B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES
students(student_id));

Answer: D

Q:3)
Here is the structure and data of the CUST_TRANS table:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno > '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Answer: A,C,D
Q:4)
See the Exhibit and examine the structure and data in the INVOICE table:

Which two SQL statements would executes successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
C. SELECT (AVG(inv_date) FROM invoice;
D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;

Answer: A,D

Q:5)
QUESTION NO: 5
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Main query and sub query can get data from different tables
C. Sub queries can contain GROUP BY and ORDER BY clauses
D. Main query and sub query must get data from the same tables
E. Sub queries can contain ORDER BY but not the GROUP BY clause
F. Only one column or expression can be compared between the main query and subqeury
Answer: A,B,C
Q:6)See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit
for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
A.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;
B.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D.
SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');
Answer: C

Q:7)
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by
default.
D. The two statements can be made to produce identical results by adding a column alias for the
salary column in the second SQL statement.

Answer: A
Q:8)
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A,B,C,F
Q:9)
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A.
SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
B.
SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C.
SELECT TO_CHAR(1890.55,'$0G000D00')

FROM DUAL;
D.
SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
E.
SELECT TO_CHAR(1890.55,'$9,999D99')
FROM DUAL;

Answer: A,C,D
Q:10)
Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
A. It produces an error because the ORDER BY clause should appear only at the end of a
compound query-that is, with the last SELECT statement
B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end
of the compound statement
D. It produces an error because positional notation cannot be used in the ORDER BY clause with
SET operators
Answer: A
Q: 11)
Which statement correctly describes SQL and /SQL*Plus?
A. Both SQL and /SQL*plus allow manipulation of values in the database.
B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle
proprietary interface for executing SQL statements.
C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL
recognizes SQL statements and sends them to the server.
D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow
manipulation of values in the database.

Answer: A
Q: 12)
Which four are types of functions available in SQL? (Choose 4)
A. string
B. character
C. integer
D. calendar
E. numeric
F. translation
G. date
H. conversion

Answer: B,E,G,H
Q:13)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

A.
MERGE INTO new_employees c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT
value
S(e.employee_id, e.first_name ||',
'||e.last_name);
B.
MERGE new_employees c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN INSERT
valueS(e.employee_id, e.first_name ||',
'||e.last_name);
Oracle 1z0-051 Exam
"Pass Any Exam. Any Time." - www.actualtests.com 11
C.
MERGE INTO new_employees cUSING employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT
value
S(e.employee_id, e.first_name ||',
'||e.last_name);

D.MERGE new_employees c
FROM employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE
MERGE INTO new_employees cUSING employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT
value
S(e.employee_id, e.first_name ||',
'||e.last_name);
E.MERGE new_employees c
FROM employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT INTO
new_employees valueS(e.employee_id, e.first_name ||',
'||e.last_name);
Answer: A
Q: 14)
Which view should a user query to display the columns associated with the constraints on a table
owned by the user?
A. USER_CONSTRAINTS
B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS
E. USER_COLUMNS

Answer: D

Q: 15)
The COMMISSION column shows the monthly commission earned by the employee.

Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step?
(Choose two.)
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average
commission of the company
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for department 20 in
descending order of the employee ID
F. listing the employees whose annual commission is more than 6000

Answer: A,C

Q:16)
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT
SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99
AND course_id = 'INT_SQL'
ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID "Rank"
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks;
C. SELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-
99'
AND course_id = 'INT_SQL'
ORDER BY marks DESC);
D. SELECT student_id, marks, ROWNUM "Rank”
FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’
AND course_id = ‘INT_SQL’
ORDER BY marks DESC)
WHERE ROWNUM <= 10 ;
E. SELECTstudent id, marks, ROWNUM “Rank”
FROM(SELECT student_id, marks
FROM students
ORDER BY marks)
WHEREROWNUM <= 10
ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’
ANDcourse_id = ‘INT_SQL’;
Answer: D

QUESTION NO: 17
Evaluate the following SQL statements:
Exhibit:

The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type
Answer: B

Q: 18)
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
A. It removes all the rows as well as the structure of the table
B. It removes all the rows in the table and deleted rows cannot be rolled back
C. It removes all the rows in the table and deleted rows can be rolled back
D. It would not remove the rows if the table has a primary key
Answer: C
Q: 19)

Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run,
the INSERT statement should insert a row with the specified values into the EMPLOYEES table.
The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?
A. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
B. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. INSERT INTO (SELECT *
FROM employees
WHERE (department_id = 20 AND
department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
Answer: D

Q: 20)
Which two statements are true regarding constraints? (Choose two.)
A. A constraint can be disabled even if the constraint column contains data
B. A constraint is enforced only for the INSERT operation on a table
C. A foreign key cannot contain NULL values
D. All constraints can be defined at the column level as well as the table level
E. A columns with the UNIQUE constraint can contain NULL values
Answer: A,E
Q:21)
Which two statements are true about sequences created in a single instance database? (Choose
two.)
A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit
by using the ALTER SEQUENCE statement
E. When a database instance shuts down abnormally, the sequence numbers that have been
cached but not used would be available once again when the database instance is restarted

Answer: A,D
Q: 22)
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the
ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query
successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database
administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database
administrator.
Answer: D
Q: 23)
Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?
A. remove the ORDER BY clause
B. remove the table alias prefix from the WHERE clause
C. remove the table alias from the SELECT clause
D. prefix the column in the USING clause with the table alias
E. prefix the column in the ORDER BY clause with the table alias
F. replace the condition
”d.department_id NOT IN (10,40)”
in the WHERE clause with
”d.department_id <> 10 AND d.department_id <> 40”
Answer: C,E
Explanation:
Prefix the column in the ORDER BY Clause would cause the statement to succeed, assuming that the statement failed because the dept_name existed in employee & department tables.
Not C: Removing the alias from the columns in the SELECT clause would cause the Statement to fail if the columns existing in both tables.
Q: 24)
Examine the statement:
Create synonym emp for hr.employees;
What happens when you issue the statement?
A. An error is generated.
B. You will have two identical tables in the HR schema with different names.
C. You create a table called employees in the HR schema based on you EMP table.
D. You create an alternative name for the employees table in the HR schema in your own schema.
Answer: D
Q:25)
select trunc( round(156.00,-1),-1) from dual
What would be the outcome?
A. 200
B. 16
C. 160
D. 150

E. 100

Answer:C

Q: 26)
Which two statements are true regarding single row functions? (Choose two.)
A. They can be nested only to two levels
B. They always return a single result row for every row of a queried table
C. Arguments can only be column values or constant
D. They can return a data type value different from the one that is referenced
E. They accept only a single argument

Answer: B,D

Q: 27)
Which statement is true regarding the UNION operator?
A. The number of columns selected in all SELECT statements need to be the same
B. Names of all columns must be identical across all SELECT statements
C. By default, the output is not sorted
D. NULL values are not ignored during duplicate checking

Answer: A
Q: 28)
Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format
B. The RR date format automatically calculates the century from the SYSDATE function but allows
the user to enter the century if required
C. The default internal storage of dates is in the character format
D. The RR date format automatically calculates the century from the SYSDATE function and does
not allow the user to enter the century
Answer: A,B
Q:29)
View the Exhibit and examine the structure of the CUSTOMERS table.

INSERT INTO NEW_CUSTOMER(cust_id,cust_name,cust_city)
values(select cust_id,cust_first_name||','||cust_last_name
from customers
where cust_id >23004)

The INSERT statement fails when executed. What could be the reason?
A. The VALUES clause cannot be used in an INSERT with a subquery
B. The total number of columns in the NEW_CUSTOMERS table does not match the total number
of columns in the CUSTOMERS table
C. The WHERE clause cannot be used in a sub query embedded in an INSERT statement
D. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match

Answer: A

Q:30)
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will
accomplish the task?
A.
UPDATE customers
SET cust_credit_limit = NULL
WHERE CUST_INCOME_LEVEL = NULL;
B.
UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;
C.
UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);
D.

UPDATE customers
SET cust_credit_limit = TO_NUMBER(' ',9999)
WHERE cust_income_level IS NULL;
Answer: B

Q: 31)
Which two statements about sub queries are true? (Choose two.)
A. A sub query should retrieve only one row.
B. A sub query can retrieve zero or more rows.
C. A sub query can be used only in SQL query statements.
D. Sub queries CANNOT be nested by more than two levels.
E. A sub query CANNOT be used in an SQL query statement that uses group functions.
F. When a sub query is used with an inequality comparison operator in the outer SQL statement,
the column list in the SELECT clause of the sub query should contain only one column.

Answer: B,F
Q:32)
View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that
started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
A.
SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND
promo_category = 'INTERNET';
B.
SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category='INTERNET');
C.
SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET');
D.
SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET');

Answer: C

Q:33)
SQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation:
Describe is a valid iSQL*Plus/ SQL*Plus command.
INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL
Statement not an iSQL*Plus Statement.RENAME is a DDL Statement.
Q: 34)
Which two statements are true regarding the COUNT function? (Choose two.)
A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL
value in any of the columns
B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and
NULL value in the CUST_ID column
C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates
and NULL values in the INV_AMT column
D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE
clause
E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Answer: A,C

Q: 35)
Examine the description of the EMP_DETAILS table given below:

Exhibit:

Name NULL DATATYPE
------------------------------------------------------------
EMP_ID NOTNULL NUMBER
EMP_NAME NOTNULL VARCHAR2(40)
EMP_IMAGE LONG

Which two statements are true regarding SQL statements that can be executed on the
EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause
B. You cannot add a new column to the table with LONG as the data type
C. An EMP_IMAGE column cannot be included in the ORDER BY clause
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: B,C

Q:36)
Which CREATE TABLE statement is valid?
A.
CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B.
CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C.
CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D.
CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Answer: D

Q:37)
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

CUSTOMER
CUSTNO NOT NULL NUMBER(2)
CUSTNAME VARCHAR2(20)
CUSTADDRESS VARCHAR2(20)
CUST_CREDIT_LIMIT NUMBER(5)

GRADE
GRADEID NOT NULL VARCHAR2(1)
STARTVAL NUMBER(5)
ENDVAL NUMBER(5)

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C.
SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D.
SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: B,C

Q:38)See the Exhibit and Examine the structure of the CUSTOMERS table

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit
limit by 15% for all customers. Customers whose credit limit has not been entered should have the
message "Not Available" displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM
customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM
customers;

Answer: D

Q: 39)
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

Answer: A,C
Q:40)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the select clause and in the WHERE
clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT
statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameter to an
aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single
group.
F. You cannot group the rows of a table by more than one column while using aggregate
functions.

Answer: A,D

Q:41)
See the structure of the PROGRAMS table:
NAME NULL TYPE
-------------------------------------------------
PROG_ID NOTNULL NUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END-DATE DATE

Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)
FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')
FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM
programs;
Answer: A,D

Q:42)
You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data in the table are deleted but the table structure will remain
B. All data along with the table structure is deleted
C. All views and synonyms will remain but they are invalidated
D. The pending transaction in the session is committed
E. All indexes on the table will remain but they are invalidated
Answer: B,C,D
Q:43)

Table PRODUCTS
NAME NULL TYPE
--------------------------------------------------------
PROD_ID NOTNULL NUMBER(6)
PROD_NAME NOTNULL varchar2(50)
PROD_DESC NOTNULL VARCHAR2(50)
PROD_CATEGORY NOTNULL VARCHAR2(4000)
PROD_CATEGORY_ID NOTNULL NUMBER
PROD_UNIT_OF_MEASURE VARCHAR2(50)
SUPPLIER_ID NOTNULL NUMBER(6)
PROD_STATUS NOTNULL VARCHAR2(20)
PROD_LIST_PRICE NOTNULL NUMBER(8,2)

PROD_MIN_PRICE NOTNULL NUMBER(8,2)

SELECT PROD_NAME

FROM PRODUCTS

WHERE PROD_ID IN(SELECT PROD_ID FROM

PRODUCTS

WHERE PROD_LIST_PRICE =(SELECT MAX(PRODUCT_LIST_PRICE)

FROM PRODUCTS

WHERE PRODUCT_LIST_PRICE<

(SELECT MAX(PRODUCT_LIST_PRICE)

FROM PRODUCTS)))

What would be the outcome of executing the above SQL statement?

A. It produces an error

B. It shows the names of products whose list price is the second highest in the table.

C. It shown the names of all products whose list price is less than the maximum list price

D. It shows the names of all products in the table

Answer: B

Explanation:

Q:44)

select p.product_id,prod_name,prod_list_price,
quantity_sold,cust_last_name
from products p natural join sales s natural join customers c
where prod_id =148

Which statement is true regarding the outcome of this query?
A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier
D. It executes successfully
Answer: B
Q:45)
create view sales_product
select p.product_id,cust_id,sum(quantity_sol)"quantity",sum(prod_list_price)
from products p sales s
where p.prod_id =s.prod_id
Which statement is true regarding the execution of the above statement?
A. The view will be created and you can perform DLM operations on the view
B. The view will not be created because the join statements are not allowed for creating a view
C. The view will not be created because the GROUP BY clause is not allowed for creating a view
D. The view will be created but no DML operations will be allowed on the view
Answer: D

Q: 46)
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose
three.)
A. The BLOB data type column is used to store binary data in an operating system file
B. The minimum column width that can be specified for a VARCHAR2 data type column is one
C. A TIMESTAMP data type column stores only time values with fractional seconds
D. The value for a CHAR data type column is blank-padded to the maximum defined column width
E. Only One LONG column can be used per table
Answer: B,D,E
Q: 47)
Which three are true? (Choose three.)
A. A MERGE statement is used to merge the data of one table with data from another.
Oracle 1z0-051 Exam
"Pass Any Exam. Any Time." - www.actualtests.com 39
B. A MERGE statement replaces the data of one table with that of another.
C. A MERGE statement can be used to insert new rows into a table.
D. A MERGE statement can be used to update existing rows in a table.
Answer: A,C,D
Q: 48)
Which two statements are true regarding views? (Choose two.)
A. A sub query that defines a view cannot include the GROUP BY clause
B. A view is created with the sub query having the DISTINCT keyword can be updated
C. A Data Manipulation Language (DML) operation can be performed on a view that is created
with the sub query having all the NOT NULL columns of a table
D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot
be updated
Answer: C,D
Q:49)
Which DELETE statement is valid?
A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM
new_employees);
C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM
new_employees WHERE name = ‘Carrey’);
D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM
new_employees WHERE name = ‘Carrey’);
Answer: C
Q:50)
SQL)select p.prom_id,p.promo_name,s.prod_id
from sales s right outer join promotions p
on(s.promo_id=p.promo_id);
Which statement is true regarding the output of the above query?
A. It gives details of product IDs that have been sold irrespective of whether they had a promo or
not
B. It gives the details of promos for which there have been no sales
C. It gives the details of promos for which there have been sales
D. It gives details of all promos irrespective of whether they have resulted in a sale or not
Answer: D
Q:51)
Employees

Employee_id number primary key
first_name varchar2(25)
last_name varchar2(25)
hire_date date

Which UPDATE statement is valid?
A.
UPDATE employees
SET first_name = ‘John’
SET last_name = ‘Smith’
WHERE employee_id = 180;
B.
UPDATE employees
SET first_name = ‘John’,
SET last_name = ‘Smoth’
WHERE employee_id = 180;
C.
UPDATE employee
SET first_name = ‘John’
AND last_name = ‘Smith’
WHERE employee_id = 180;
D.
UPDATE employee
SET first_name = ‘John’, last_name = ‘Smith’
WHERE employee_id = 180;

Answer: D
Q:52)
select prod_id from products
intersect
selcet prod_id from sales
minus
select prod_id from costs;

Which statement is true regarding the above compound query?
A. It shows products that have a cost recorded irrespective of sales
B. It shows products that were sold and have a cost recorded
C. It shows products that were sold but have no cost recorded
D. It reduces an error
Answer: C

Q:53)
The products table has following structure:

Name NULL DATATYPE
------------------------------------
prod_id Notnull number(4)
prod_name varchar2(25)
prod_expiry_date date

SQL>SELECT PROD_ID,NVL2(PROD_EXPIRY_DATE,PROD_EXPIRY_DATE + 15,'') FROM PRODUCTS;
SQL>SELECT PROD_ID,NVL(PROD_EXPIRY_DATE,PROD_EXPIRY_DATE_15) FROM PRODUCTS;

Which statement is true regarding the outcome?
A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully
Answer: B

Q: 54)
Which statements are correct regarding indexes? (Choose all that apply.)
A. For each data manipulation language (DML) operation performed, the corresponding indexes
are automatically updated.
B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a
unique index.
C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
D. When a table is dropped, the corresponding indexes are automatically dropped
Answer: A,B,D
Q:55)
Which two SQL statements would execute successfully? (Choose two.)
A.
UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
B.
SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
C.
UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
D.
SELECT TO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));

Answer: A,B

Tuesday, June 30, 2015

oracle

what is oracle?
Oracle says it is the world's leading supplier of software for information management but it is best known for its sophisticated relational database products
A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970

Generic function and procedures

Purpose: This function will return the date from the input - no. of business days and from date

Modifications: 11/4/2003 xxxxxxxxxxxxxxxxxxxxxx
--------------------------------------------------------------------------------------------------------*/
FUNCTION calc_date_add_business_days(from_date date, no_of_business_days number) RETURN DATE IS

out_date date;
weekend_day varchar2(20);
temp_date date;
i number:= 0;

BEGIN
out_date := from_date;

loop exit when (i = no_of_business_days);
out_date := out_date +1;
weekend_day := to_char(out_date, 'D');
if (weekend_day not in (1,7)) then
i := i+1;
end if;

end loop;

return (out_date);
end;
--first parameter is input and second parameter number of days after
select calc_date_add_business_days(trunc(TO_DATE('27-JUN-2015')),1) from dual

2.) get age range

FUNCTION GET_AGE_RANGE(date_in IN date, /* e.g. birthday */
as_of_date_in IN date default sysdate, /* e.g. as of sysdate */
default_if_null IN varchar2 default null) RETURN VARCHAR2 IS
temp number;
ret_val varchar2(50) := null;
BEGIN
temp := floor((as_of_date_in - date_in)/365.25);
if temp between 0 and 18 then
ret_val := 'JRA';
elsif temp between 18 and 29 then
ret_val := '18-29';
elsif temp between 30 and 49 then
ret_val := '30-49';
elsif temp between 50 and 64 then
ret_val := '50-64';
elsif temp > 64 then
ret_val := '65+';
else
ret_val := default_if_null;
end if;
RETURN ret_val;
END get_age_range;
---------------------
select GET_AGE_RANGE(to_date('20-JUN-2015')) from dual
3.)
/* this function is needed for delimited files*/
function c_get(p_text varchar2,p_occur number, p_delimiter varchar2) return varchar2 is
v_out varchar2(32000) :=null;
begin
v_out:=substr(p_text,instr(p_text,p_delimiter,1,p_occur)+1,instr(p_text,p_delimiter,1,p_occur+1)-(instr(p_text,p_delimiter,1,p_occur)+1));
return (v_out);
end;

select dtc_sql_util.c_get('|subbu|srikanth|viswa|',1,'|') from dual
4.)
/**Returns only numbers from the given string**/
function return_numbers(p_text varchar2) return varchar2 is
v_length number:=length(p_text);
v_text varchar2(30000):=null;
v_char varchar2(10):=null;
begin
if (p_text is not null) then
for i in 1..v_length loop
v_char:=substr(p_text,i,1);
if (ascii(v_char) between 48 and 57) then
v_text:=v_text||v_char;
end if;
end loop;
end if;
return trim(v_text);
end;
5.)
/**Returns only alpha bytes(abcds) from the given string**/
function return_abcds(p_text varchar2) return varchar2 is
v_length number:=length(p_text);
v_text varchar2(30000):=null;
v_char varchar2(10):=null;
begin
if (p_text is not null) then
for i in 1..v_length loop
v_char:=substr(p_text,i,1);
if (ascii(v_char) between 65 and 90) or (ascii(v_char) between 97 and 122) then
v_text:=v_text||v_char;
end if;
end loop;
end if;
return trim(v_text);
end;
6)Routine to get next sequence no from the sequence
/*************************************************************
*
* Routine to return the next sequence value from any sequence
* that is passed to the routine.
*
*************************************************************/
FUNCTION get_nextval (in_sequence IN VARCHAR2)
RETURN NUMBER
IS
str VARCHAR2(100) := 'SELECT ' || in_sequence || '.' ||
'NEXTVAL next_seq FROM dual where rownum = 1' ;
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
upd_rows INTEGER;
return_value INTEGER;
BEGIN
DBMS_SQL.PARSE(cur, str, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (cur, 1, return_value);
upd_rows := DBMS_SQL.EXECUTE_AND_FETCH(cur);
IF upd_rows = 1 THEN
DBMS_SQL.COLUMN_VALUE (cur, 1, return_value);
END IF;
DBMS_SQL.CLOSE_CURSOR (cur);
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur);
RAISE_APPLICATION_ERROR(-20100,'DTC_UTIL.GET_NEXTVAL: Unable to get nextval for ' || in_sequence);
END get_nextval;
PROCEDURE ddl (ddl_string IN VARCHAR2, out_err_msg OUT VARCHAR2)
IS
cur INTEGER;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, ddl_string, DBMS_SQL.V7);
DBMS_SQL.CLOSE_CURSOR (cur);
EXCEPTION
WHEN OTHERS THEN
out_err_msg := 'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm;
--RAISE_APPLICATION_ERROR(-20101,'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm);
END ddl;
/******************************************************************************
Returns the value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_column_value ( column_name IN VARCHAR2
, table_name IN VARCHAR2
, pk_column IN VARCHAR2
, pk_value IN VARCHAR2
, err_text OUT VARCHAR2)
RETURN VARCHAR2 IS
sql_stmt VARCHAR2(2000);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
col_value VARCHAR2(2000);

BEGIN
sql_stmt := 'SELECT '||column_name||' '||
'FROM '||table_name||' '||
'WHERE '||pk_column||' = '''||pk_value||'''';
DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN col_value;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := 'Too Many Rows Selected';
RETURN 'TOO_MANY_ROWS';
WHEN NO_DATA_FOUND THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
RETURN NULL;
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := SQLERRM;
RETURN 'ERROR';
END get_column_value;

/******************************************************************************
Returns the date value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_date_column_value ( column_name IN VARCHAR2
, table_name IN VARCHAR2
, pk_column IN VARCHAR2
, pk_value IN VARCHAR2
, err_text OUT VARCHAR2)
RETURN VARCHAR2 IS
sql_stmt VARCHAR2(2000);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
col_value VARCHAR2(2000);

BEGIN
sql_stmt := 'SELECT to_char('||column_name||',''MM/DD/YYYY'') '||
'FROM '||table_name||' '||
'WHERE '||pk_column||' = '''||pk_value||'''';
DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN col_value;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := 'Too Many Rows Selected';
RETURN 'TOO_MANY_ROWS';
WHEN NO_DATA_FOUND THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
RETURN NULL;
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := SQLERRM;
RETURN 'ERROR';
END get_date_column_value;

/************************************************************************
*
* Return the roles for the user/program that are currently active.
*
************************************************************************/
/***** Commented out for DTC - Needs to be modified to support new user/role structure *****
FUNCTION get_user_roles(in_user_id IN cmn_user.user_id%TYPE DEFAULT 1,
in_program_id IN cmn_program.program_id%TYPE DEFAULT 1)
RETURN VARCHAR2
IS
CURSOR get_program_role IS
SELECT role
FROM cmn_user_prog_role
WHERE
program_id = in_program_id
AND user_id = in_user_id
AND sysdate between start_date and nvl(end_date,sysdate);
role_str VARCHAR2(1000);
ret_val VARCHAr2(1000);
BEGIN
FOR i IN get_program_role LOOP
role_str := ',' || i.role || role_str;
END LOOP;
-- strip off the leading comma from the role streing, if it is NULL then
-- return 'DTC_USER'.
ret_val := nvl(substr(role_str,2,1000),'DTC_USER');
RETURN ret_val;
END get_user_roles;
/******************************************************************************
This procedure sets the seq value to the value passed
******************************************************************************/
PROCEDURE set_seq_value( in_seq_name IN VARCHAR2
, in_value IN INTEGER) IS
sql_drop VARCHAR2(500);
sql_create VARCHAR2(500);
sql_stmt VARCHAR2(500) := 'SELECT '||in_seq_name||'.NEXTVAL FROM DUAL';
cur_id INTEGER;
exe_err INTEGER;
curr_val INTEGER;
BEGIN
/* First Drop The Sequence */
sql_drop := 'DROP SEQUENCE '||in_seq_name;
cur_id := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE(cur_id,sql_drop,DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20169,'Could Not Drop Sequence '||
in_seq_name||', it may be an invalid sequence!');
END;
DBMS_SQL.CLOSE_CURSOR(cur_id);
sql_create := 'CREATE SEQUENCE '||in_seq_name||CHR(10)||
'INCREMENT BY 1'||CHR(10)||
'START WITH '||in_value|| CHR(10)||
'MINVALUE '||in_value ||CHR(10)||
'MAXVALUE 9999999999'||CHR(10)||
'NOCYCLE'||CHR(10)||
'CACHE 20'||CHR(10)||
'NOORDER';
/* Then re-create the sequence */
cur_id := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE(cur_id,sql_create,DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20169,'Could Not Re-Create Sequence '||
in_seq_name||', it has been dropped so you must recreate it so it will work, Sorry!'||
'--'||SQLERRM);
END;
DBMS_SQL.CLOSE_CURSOR(cur_id);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cur_id) THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
END IF;
RAISE_APPLICATION_ERROR(-20569,'DTC_UTIL.SET_SEQ_VALUE Error--'||SQLERRM);
END set_seq_value;
/******************************************************************************
This function returns a format mask (99,999,999.99) based on the length and
decimal places passed
******************************************************************************/
FUNCTION gimme_a_mask( in_length IN INTEGER
, in_dec_places IN INTEGER
, do_commas IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2 IS
curr_length INTEGER := in_length;
curr_decimal INTEGER := NVL(in_dec_places,0);
mask VARCHAR2(50);
num_of_commas INTEGER;
BEGIN
/* mask := RPAD('9',curr_length,'9'); */
mask := RPAD('9',10,'9');
IF do_commas THEN
mask := REPLACE(TO_CHAR(TO_NUMBER(mask),'9,999,999,999'),' ',NULL);
END IF;
IF curr_decimal > 0 THEN
mask := mask||'.'||RPAD('9',curr_decimal,'9');
END IF;
RETURN mask;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20187,'DTC_UTIL.GIMME_A_MASK Error--'||SQLERRM);
END gimme_a_mask;
/******************************************************************************
This function returns 'Y' if the value passed is a number or 'N' if it is not
******************************************************************************/
FUNCTION is_this_a_number( in_string IN VARCHAR2)
RETURN VARCHAR2 IS
test_number NUMBER;
BEGIN
test_number := TO_NUMBER(in_string);
RETURN 'Y';
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN 'N';
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20152,
'DTC_UTIL.IS_THIS_A_NUMBER Error--Can Not Display SQLERRM, Sorry!');
END is_this_a_number;
/******************************************************************************
This function returns the given string to the DD-MON-YY format
******************************************************************************/
FUNCTION valid_date (in_date IN VARCHAR2,
in_format IN VARCHAR2 DEFAULT 'DD-MON-YY'
)
RETURN VARCHAR2
IS
ret_val VARCHAR2(50);
BEGIN
ret_val := TO_CHAR(TO_DATE(in_date, in_format), in_format);
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END valid_date;
/******************************************************************************
This function returns the datatype of the table.column
******************************************************************************/
FUNCTION get_datatype(in_table IN VARCHAR2,
in_column IN VARCHAR2,
in_global IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR get_column_datatype IS
SELECT data_type
FROM user_tab_columns
WHERE table_name = in_table
AND column_name = in_column;
CURSOR get_global_datatype IS
SELECT datatype
FROM dtc_global_variable
WHERE variable_name = in_global;
ret_val VARCHAR2(30);
BEGIN
IF in_column IS NOT NULL THEN
OPEN get_column_datatype;
FETCH get_column_datatype INTO ret_val;
CLOSE get_column_datatype;
ELSE
OPEN get_global_datatype;
FETCH get_global_datatype INTO ret_val;
CLOSE get_global_datatype;
END IF;
RETURN ret_val;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20519,'dtc_util.get_datatype: Unable to find datatype.');
END get_datatype;
/******************************************************************************
This function returns the position of the column in an index
******************************************************************************/
FUNCTION get_ind_col_position(in_owner IN VARCHAR2,
in_table IN VARCHAR2,
in_column IN VARCHAR2,
in_dblink IN VARCHAR2 default null
)
RETURN INTEGER
IS
CURSOR get_ind_col IS
SELECT
column_position
FROM
sys.all_ind_columns
WHERE
TABLE_OWNER = in_owner
AND TABLE_NAME = in_table
AND COLUMN_NAME = in_column;

v_select_statement varchar2(1000);
CURSOR_HANDLE INTEGER;
EXECUTE_RESULTS INTEGER;

ret_val INTEGER;

BEGIN

if in_dblink is null then
OPEN get_ind_col;
FETCH get_ind_col INTO ret_val;
IF get_ind_col%NOTFOUND THEN
ret_val := 0;
END IF;
CLOSE get_ind_col;

else
v_select_statement := 'select column_position from sys.all_ind_columns@'||in_dblink||
' where table_owner = '''||in_owner||''''||
' and table_name = '''||in_table||''''||
' and column_name = '''||in_column||'''';

CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_HANDLE,v_select_statement,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1,ret_val);
EXECUTE_RESULTS := DBMS_SQL.EXECUTE(CURSOR_HANDLE);
IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE) > 0 THEN
DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1,ret_val);
END IF;
DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);

end if;
RETURN ret_val;

EXCEPTION

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20783,'dtc_util.get_ind_col_position ' || sqlerrm);

END get_ind_col_position;

/******************************************************************************
This function returns the number of pipe sessions running
******************************************************************************/
--/************************* Commented out for DTC ******************************
FUNCTION get_pipe_count
RETURN INTEGER
IS
CURSOR get_count IS
SELECT count(*)
FROM sys.dba_jobs_running
WHERE job < 0; -- all PAP j0obs run with job < 0
ret_val INTEGER;
BEGIN
OPEN get_count;
FETCH get_count INTO ret_val;
CLOSE get_count;
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END get_pipe_count;
--******************************************************************************/
PROCEDURE delimited_to_table ( in_string IN VARCHAR2
,in_delimiter IN VARCHAR2 DEFAULT '|'
,out_rec_count OUT INTEGER
,out_tab OUT dtc_global.vc_2000
)
IS
i integer;
p integer;
np integer;
BEGIN
if in_string is null then out_rec_count := 0; return; end if;
i := 0;
p := 0;
np := 0;
LOOP
np := INSTR(in_string,in_delimiter,p+1);
IF np = 0 THEN
-- IF i <> 0 THEN
i := i + 1;
out_tab(i) := substr(in_string,p+1);
-- END IF;
EXIT;
ELSE
i := i + 1;
out_tab(i) := substr(in_string,p+1,np-(p+1));
p := np;
END IF;
END LOOP;
out_rec_count := i;
END;
PROCEDURE table_to_delimited( in_tab IN dtc_global.vc_2000
, in_delimiter IN VARCHAR2 DEFAULT '|'
, out_rec_count OUT INTEGER
, out_string OUT VARCHAR2
)
IS
i integer;
j integer;
t_out_string varchar2(2000);
BEGIN
t_out_string := '';
i := in_tab.COUNT;
FOR J in 1..i
LOOP
IF j <> i THEN
t_out_string := t_out_string || in_tab(j) || in_delimiter;
ELSE
t_out_string := t_out_string || in_tab(j) ;
END IF;
END LOOP;
out_rec_count := i;
out_string := t_out_string;
END;

/******************************************************************************
This procedure writes a message to a pipe named 'HOST_' || user
The messages can be retrieved real-time by executing host_r on the host
host_r is below in the comments
******************************************************************************/
procedure host( cmd in varchar2 ) is
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_' || UPPER(user) );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end host;

/******************************************************************************
This section uses set_c_string and set_c_delimiter to declare a string and its
delimiter, then get_val (given an number) will return that argument in the
string
******************************************************************************/
FUNCTION get_val (arg NUMBER,
in_string VARCHAR2, -- DEFAULT c_string,
in_delimiter VARCHAR2 -- DEFAULT c_delimiter
) RETURN VARCHAR2 IS
val VARCHAR2(256) := SUBSTR(in_string,
ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1),
ifelse(INSTR(in_string,in_delimiter,1,arg) = 0,1000000,INSTR(in_string,in_delimiter,1,arg))-ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1));
BEGIN
IF INSTR(in_string,in_delimiter,1,arg-1) = 0 THEN
val := NULL;
END IF;
RETURN val;
END get_val;
PROCEDURE set_c_string (str VARCHAR2) IS
BEGIN
c_string := str;
END set_c_string;
PROCEDURE set_c_delimiter (del VARCHAR2) IS
BEGIN
c_delimiter := del;
END set_c_delimiter;

/******************************************************************************
This section creates a DECODE for pl/sql. Overloaded, evaluates boolean expression
and returns tval_in if true else fval_in
Also includes ex_dyn, a generic execute for dbms_sql
******************************************************************************/
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN BOOLEAN,
FVAL_IN IN BOOLEAN)
RETURN BOOLEAN IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN DATE,
FVAL_IN IN DATE)
RETURN DATE IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN VARCHAR2,
FVAL_IN IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN NUMBER,
FVAL_IN IN NUMBER)
RETURN NUMBER IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ex_dyn(sql_statement varchar2) RETURN VARCHAR2 IS
cursor_handle INTEGER;
curs_result INTEGER;
ret_column VARCHAR2(2000);
rows_fetched INTEGER;
BEGIN
cursor_handle := dbms_sql.open_cursor;
dbms_sql.parse(cursor_handle, sql_statement, dbms_sql.native);
dbms_sql.define_column(cursor_handle,1,ret_column,2000);
curs_result := dbms_sql.execute(cursor_handle);
rows_fetched := dbms_sql.fetch_rows(cursor_handle);
dbms_sql.column_value(cursor_handle,1,ret_column);
dbms_sql.close_cursor(cursor_handle);

RETURN ret_column;
EXCEPTION
WHEN others THEN
dbms_sql.close_cursor(cursor_handle);
RETURN null;
END ex_dyn;

/******************************************************************************
This section evaluates any range. yes_end_null and no_end_null determine
if the second value of the range can be null or not.
******************************************************************************/
PROCEDURE yes_end_null IS
BEGIN
end_null_ok := TRUE;
END yes_end_null ;
PROCEDURE no_end_null IS
BEGIN
end_null_ok := FALSE;
END no_end_null ;
FUNCTION C (begin_val DATE, end_val DATE) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF TRUNC(begin_val) > TRUNC(end_val) THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR TRUNC(begin_val) > TRUNC(end_val) THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;
FUNCTION C (begin_val NUMBER, end_val NUMBER) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF begin_val > end_val THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR begin_val > end_val THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;
FUNCTION C (begin_val VARCHAR2, end_val VARCHAR2) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF begin_val > end_val THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR begin_val > end_val THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;

/******************************************************************************
This function returns the number of delimiters passed until it finds the value
passed in the string passed if the value.
******************************************************************************/
FUNCTION get_occurance ( in_string IN VARCHAR2
, in_value IN VARCHAR2
, in_delimiter IN VARCHAR2)
RETURN NUMBER IS

new_string VARCHAR2(2000);
value_pos NUMBER;
counter NUMBER;

BEGIN

value_pos := INSTR(in_string,in_value);

IF value_pos = 0 THEN
RETURN NULL;
END IF;

new_string := SUBSTR(in_string,1,value_pos - 1);

counter := 0;

LOOP
counter := counter + 1;

EXIT WHEN new_string IS NULL;

value_pos := INSTR(new_string,in_delimiter);
IF value_pos = 0 THEN
new_string := NULL;
ELSE
new_string := SUBSTR(new_string,value_pos + 1);
END IF;

END LOOP;

RETURN counter;

END get_occurance;

FUNCTION over_punch (opunch varchar2)
return varchar2
IS
/***************************************************************
* HDS Information Systems
****************************************************************
*
* Name: OVER_PUNCH
*
* Author: Dave Robinson
*
* Purpose: Converts numbers that contain over punch
* characters into real numbers. Returns
* either the converted number or null.
*
* Mod History:
* Mapped from PAP into DTC by Wayne Xu on 2/10/1999
*
****************************************************************
***************************************************************/
cnvt_result varchar2(15);
begin
select decode(substr(opunch,1,1),'{','+','}','-',
decode(substr(opunch,-1,1),'{','+','}','-',
decode(sign(trunc(ascii(substr(upper(opunch),1,1))/74)),1,'-',
decode(sign(trunc(ascii(substr(upper(opunch),-1,1))/74)),1,'-','+'
))))||translate(upper(opunch),
'{}ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0012345678912345678900000000')
into cnvt_result
from dual;
if to_number(cnvt_result)>=-10
and to_number(cnvt_result) <=-1 then
cnvt_result:=null;
end if;
return cnvt_result;
exception
when others then
cnvt_result:=null;
return cnvt_result;
end over_punch;

/**************** Moved from dtc_global **************************/

/******************************************************************************
This procedure assigns the value passed to the global package variable
passed.
******************************************************************************/
PROCEDURE assign_global_value_char( in_var_name IN VARCHAR2
, in_value IN VARCHAR2)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||':char_value;';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.NATIVE);
IF in_value IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(assign_cur_id,'char_value',in_value);
END IF;

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_char;

PROCEDURE assign_global_value( in_var_name IN VARCHAR2
, in_value IN NUMBER)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||TO_CHAR(in_value)||';';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value;

PROCEDURE assign_global_value_date( in_var_name IN VARCHAR2
, in_value IN DATE)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||'TO_DATE('''||TO_CHAR(in_value,'MM/DD/YYYY')||''',''MM/DD/YYYY'');';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_date;

/******************************************************************************
This function returns the title of the current application title.
******************************************************************************/

/*********************** This is not used by DTC ******************************
FUNCTION get_application RETURN VARCHAR2 IS

app_record dtc_cur.app_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.app_cur(dtc_global.application_id);
FETCH dtc_cur.app_cur INTO app_record;
IF dtc_cur.app_cur%NOTFOUND THEN
CLOSE dtc_cur.app_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.app_cur;

RETURN app_record.application_code;

END get_application;
******************************************************************************/

/******************************************************************************
This function returns the current organization name. (previously get_customer_name)
******************************************************************************/
FUNCTION get_organization_name RETURN VARCHAR2 IS

org_record dtc_cur.org_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.org_cur(dtc_global.organization_id);
FETCH dtc_cur.org_cur INTO org_record;
IF dtc_cur.org_cur%NOTFOUND THEN
CLOSE dtc_cur.org_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.org_cur;

RETURN org_record.org_name;

END get_organization_name;

/******************************************************************************
This function returns the program name of the id passed
******************************************************************************/
FUNCTION get_program_name (in_org_prog_id IN dtc_org_prog.org_prog_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

prog_record dtc_cur.prog_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.prog_cur(NVL(in_org_prog_id,dtc_global.org_prog_id));
FETCH dtc_cur.prog_cur INTO prog_record;
IF dtc_cur.prog_cur%NOTFOUND THEN
CLOSE dtc_cur.prog_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.prog_cur;

RETURN prog_record.program_desc;

END get_program_name;

/******************************************************************************
This function returns the campaign name of the id passed
******************************************************************************/
FUNCTION get_campaign_name (in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

cam_record dtc_cur.cam_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.cam_cur(NVL(in_org_prog_cam_id,dtc_global.org_prog_cam_id));
FETCH dtc_cur.cam_cur INTO cam_record;
IF dtc_cur.cam_cur%NOTFOUND THEN
CLOSE dtc_cur.cam_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.cam_cur;

RETURN cam_record.campaign_desc;

END get_campaign_name;

/******************************************************************************
This function sets the DTC_GLOBAL.USER_ID and returns the username
******************************************************************************/

FUNCTION assign_user_id RETURN VARCHAR2 IS

return_username VARCHAR2(30);

CURSOR get_user IS
SELECT
APP_USER_ID
, USERNAME
FROM
DTC_APP_USER
WHERE
USERNAME = NVL(dtc_global.current_user,USER);

BEGIN
OPEN get_user;
FETCH get_user INTO
dtc_global.user_id
, return_username;
IF get_user%NOTFOUND THEN
dtc_global.user_id := 0;
return_username := 'UNKNOWN';
END IF;
CLOSE get_user;

RETURN return_username;

END assign_user_id;

/******************************************************************************
This function returns the form title of the module name passed.
******************************************************************************/

FUNCTION get_module_title( in_mod_name VARCHAR2) RETURN VARCHAR2 IS

mod_title VARCHAR2(50);

CURSOR get_title (in_name IN VARCHAR2) IS
SELECT MODULE_TITLE
FROM DTC_MODULE
WHERE
MODULE_NAME = in_name;

BEGIN

OPEN get_title(in_mod_name);
FETCH get_title INTO mod_title;
CLOSE get_title;

IF mod_title IS NULL THEN
OPEN get_title('UNKNOWN');
FETCH get_title INTO mod_title;
IF get_title%NOTFOUND THEN
mod_title := 'Module Title Unknown';
END IF;
CLOSE get_title;
END IF;

RETURN mod_title;

END get_module_title;

/******************************************************************************
This function returns the current date in character format.
******************************************************************************/
FUNCTION get_char_date RETURN VARCHAR2 IS

BEGIN
RETURN TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI');
END get_char_date;

/******************************************************************************
This procedure gathers the entered/last updated infomation for the
table and rowid passed.
******************************************************************************/
PROCEDURE get_entry_info( in_table IN VARCHAR2
, in_rowid IN VARCHAR2
, out_created OUT NUMBER
, out_create_dt OUT DATE
, out_lst_up_by OUT NUMBER
, out_lst_up_dt OUT DATE) IS

cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
sql_stmt VARCHAR2(1000);
exe_err INTEGER;

created_user NUMBER;
create_dt DATE;
last_upt_by NUMBER;
last_upt_dt DATE;

BEGIN

sql_stmt := 'SELECT '||
' CREATED_BY'||
', CREATE_DATE'||
', LAST_UPDATED_BY'||
', LAST_UPDATE_DATE '||
'FROM '||in_table||' '||
'WHERE ROWID = '''||in_rowid||'''';

DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(cur_id,1,created_user);
DBMS_SQL.DEFINE_COLUMN(cur_id,2,create_dt);
DBMS_SQL.DEFINE_COLUMN(cur_id,3,last_upt_by);
DBMS_SQL.DEFINE_COLUMN(cur_id,4,last_upt_dt);

exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,FALSE);

DBMS_SQL.COLUMN_VALUE(cur_id,1,created_user);
DBMS_SQL.COLUMN_VALUE(cur_id,2,create_dt);
DBMS_SQL.COLUMN_VALUE(cur_id,3,last_upt_by);
DBMS_SQL.COLUMN_VALUE(cur_id,4,last_upt_dt);

DBMS_SQL.CLOSE_CURSOR(cur_id);

out_created := created_user;
out_create_dt := create_dt;
out_lst_up_by := last_upt_by;
out_lst_up_dt := last_upt_dt;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END get_entry_info;

/******************************************************************************
This function returns the value of the Global package variable passed.
******************************************************************************/
FUNCTION get_global_value(in_variable IN VARCHAR2)
RETURN VARCHAR2 IS
plsql_text VARCHAR2(500);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
BEGIN
plsql_text := 'DECLARE m_char VARCHAR2(255); m_date DATE;' ||
'BEGIN m_char := TO_CHAR('|| in_variable || '); ' ||
'BEGIN m_date := TO_DATE('||in_variable||');' ||
'DTC_GLOBAL.GLOBAL_VAR_VALUE := TO_CHAR('||in_variable||',''MM/DD/YYYY''); ' ||
'EXCEPTION WHEN OTHERS THEN DTC_GLOBAL.GLOBAL_VAR_VALUE := '||in_variable||'; END; ';

plsql_text := plsql_text ||' END;';
DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
exe_err := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);

return DTC_GLOBAL.GLOBAL_VAR_VALUE;
EXCEPTION
WHEN OTHERS THEN -- Was a Character Global
BEGIN
plsql_text := ' BEGIN DTC_GLOBAL.GLOBAL_VAR_VALUE := ' || in_variable || ';' ||
' END;';
DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
exe_err := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN DTC_GLOBAL.GLOBAL_VAR_VALUE;

EXCEPTION
WHEN OTHERS THEN
RETURN '**ERROR**' || SQLERRM;
END;
END get_global_value;

/******************************************************************************
This function returns the table and column name of the table_def_col_id passed
(used in order by in forms)
******************************************************************************/
FUNCTION get_table_column_name
(in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_table_column_name VARCHAR2(61);

CURSOR table_column_cur IS
SELECT table_name||'.'||column_name
FROM dtc_table_def,dtc_table_def_col
WHERE dtc_table_def.table_def_id = dtc_table_def_col.table_def_id
AND dtc_table_def_col.table_def_col_id = in_table_def_col_id;

BEGIN
OPEN table_column_cur;
FETCH table_column_cur INTO return_table_column_name;
IF table_column_cur%NOTFOUND THEN
return_table_column_name := NULL;
END IF;
CLOSE table_column_cur;

RETURN return_table_column_name;

END get_table_column_name;

/******************************************************************************
This function returns the organization*program*campaign name of the ids passed
(Used in order_by in forms)
******************************************************************************/
FUNCTION get_org_prog_cam_name
(in_organization_id IN dtc_organization.organization_id%TYPE DEFAULT NULL,
in_org_prog_id IN dtc_org_prog.org_prog_id%TYPE DEFAULT NULL,
in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_org_prog_cam_name VARCHAR2(555);
v_org_name dtc_organization.org_name%type;
v_program_desc dtc_org_prog.program_desc%type;
v_campaign_desc dtc_org_prog_cam.campaign_desc%type;

CURSOR organization_cur IS
select org_name
from dtc_organization
where organization_id = in_organization_id;

CURSOR org_prog_cur IS
select program_desc
from dtc_org_prog
where org_prog_id = in_org_prog_id;

CURSOR org_prog_cam_cur IS
select campaign_desc
from dtc_org_prog_cam
where org_prog_cam_id = in_org_prog_cam_id;

BEGIN

if in_organization_id is not null then
open organization_cur;
fetch organization_cur into v_org_name;
if organization_cur%NOTFOUND then
v_org_name := NULL;
end if;
return_org_prog_cam_name := v_org_name;
close organization_cur;

if in_org_prog_id is not null then
open org_prog_cur;
fetch org_prog_cur into v_program_desc;
if org_prog_cur%NOTFOUND then
v_program_desc := NULL;
else
return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_program_desc;
end if;
close org_prog_cur;

if in_org_prog_cam_id is not null then
open org_prog_cam_cur;
fetch org_prog_cam_cur into v_campaign_desc;
if org_prog_cam_cur%NOTFOUND then
v_campaign_desc := NULL;
else
return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_campaign_desc;
end if;
close org_prog_cam_cur;
end if;
end if;
else
return_org_prog_cam_name := NULL;
end if;

return return_org_prog_cam_name;

END get_org_prog_cam_name;

/******************************************************************************
This function returns the table title of the table_def_id passed
(used in order by in forms)
******************************************************************************/
FUNCTION get_table_title
(in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_table_title dtc_table_def.title%type;

CURSOR table_cur IS
SELECT t.title
FROM dtc_table_def t, dtc_table_def_col c
WHERE c.table_def_col_id = in_table_def_col_id
and t.table_def_id = c.table_def_id;

BEGIN
OPEN table_cur;
FETCH table_cur INTO return_table_title;
IF table_cur%NOTFOUND THEN
return_table_title := NULL;
END IF;
CLOSE table_cur;

RETURN return_table_title;

END get_table_title;

/******************************************************************************
This function calculates the business (working) days between the two days
It doesn't take into account the Holidays.
******************************************************************************/
FUNCTION calc_business_days(P_start_date date,
P_end_date date)
RETURN NUMBER IS

begin_date date;
thru_date date;
weekend number;
days number;
maxweekend number;
weekday number;

BEGIN
IF P_start_date is null or P_end_date is null THEN
return(null);
ELSIF TRUNC(P_start_date) > TRUNC(P_end_date) THEN
return( -1 );
ELSE
begin_date := P_start_date;
thru_date := P_end_date;
END IF;

days := TRUNC(thru_date) - TRUNC(begin_date);
IF days = 0 THEN
return(0);
ELSE
weekday := to_char(begin_date,'D');
days := days + 0;
IF mod(days,7) = 0 THEN
weekend := TRUNC(days / 7) * 2;
ELSE
MAXWEEKEND := (TRUNC(days / 7) + 1) * 2;
IF weekday = 1 THEN
weekend := maxweekend - 1;
ELSE
IF (weekday + days) = (TRUNC(days / 7) + 1) * 7 + 1 THEN
weekend := maxweekend - 1;
ELSE
IF (weekday + days) < (trunc(days / 7) + 1) * 7 + 1 THEN
weekend := maxweekend - 2;
ELSE
weekend := maxweekend;
END IF;
END IF;
END IF;
END IF;
RETURN ((days - weekend) * SIGN(TRUNC(P_end_date) - TRUNC(P_start_date)));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN (0);
END; -- calc_business_days

/***************************************************************************
This function is used by the Job Queue screen DTCFM560 for getting the description
of the opc_item.
******************************************************************************/

FUNCTION dtc_opc_item_desc_sort(i_opc_item_id IN NUMBER) RETURN VARCHAR2
IS
opc_item_desc VARCHAR2(255);
BEGIN
DECLARE
item_desc dtc_opc_item.opc_item_desc%TYPE;
BEGIN
SELECT opc_item_desc
INTO item_desc
FROM dtc_opc_item
WHERE opc_item_id = i_opc_item_id
AND sysdate between start_date and nvl(end_date,sysdate);

RETURN item_desc;
EXCEPTION
WHEN OTHERS THEN NULL;
RETURN ('OPC ITEM DESCRIPTION NOT FOUND');
END;
END dtc_opc_item_desc_sort;
7)Conversion Cvs to xml code
SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
v_rec_count NUMBER := 0;

--File Variables
v_file_out UTL_FILE.FILE_TYPE;
v_file_in UTL_FILE.FILE_TYPE;
l_in_path varchar2(100) := '/u02/apps/dtc/incoming_data/lc_5050/';
l_out_path varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';
v_input_buffer varchar2(4000);
v_issuedate varchar2(20);
v_char clob;
v_firstag varchar2(30):='<Document_Revision_Request>';
v_lasttag varchar2(30):='</Document_Revision_Request>';

BEGIN
dbms_output.put_line('First Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dtc_global.org_prog_id := 5050;

v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_PHS_20150917.csv','R');
UTL_FILE.get_LINE(v_file_in,v_input_buffer);
IF utl_file.is_open(v_file_in) then
LOOP
begin

UTL_FILE.get_LINE(v_file_in,v_input_buffer);
v_rec_count := v_rec_count + 1;

v_issuedate :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);

select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
XMLElement("Document_Revision_ID",v_rec_count),
XMLElement("Payment_Code",'CHK'),
XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
XMLElement("Revised_Document_Status",'V'),
XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
XMLElement("Document_Status_Revision_Description")
))) into v_char from dual;

IF v_char is not null then
IF v_rec_count=1 then
v_char:=v_firstag||v_char;
END IF;
END IF;

IF NOT utl_file.is_open(v_file_out) then
v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_PHS_20150917.xml', 'W');
END IF;
UTL_FILE.PUT_LINE(v_file_out,v_char);
EXCEPTION

WHEN NO_DATA_FOUND THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
v_char:=null;
v_char:=v_char||v_lasttag;
UTL_FILE.PUT_LINE(v_file_out,v_char);
UTL_FILE.fclose(v_file_in);
END IF;
IF ( UTL_FILE.is_open(v_file_out) ) THEN
UTL_FILE.fclose(v_file_out);
END IF;
EXIT;

end;
END LOOP;
END IF;
dbms_output.put_line('No of records converted:' ||v_rec_count);

UTL_FILE.FCLOSE(v_file_in);
UTL_FILE.FCLOSE(v_file_out);

dbms_output.put_line('First Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dbms_output.put_line('Second Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
v_rec_count:=0;

v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_FRS_20150917.csv','R');
UTL_FILE.get_LINE(v_file_in,v_input_buffer);
IF utl_file.is_open(v_file_in) then
LOOP
begin

UTL_FILE.get_LINE(v_file_in,v_input_buffer);
v_rec_count := v_rec_count + 1;

v_issuedate :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);

select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
XMLElement("Document_Revision_ID",v_rec_count),
XMLElement("Payment_Code",'CHK'),
XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
XMLElement("Revised_Document_Status",'V'),
XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
XMLElement("Document_Status_Revision_Description")
))) into v_char from dual;

IF v_char is not null then
IF v_rec_count=1 then
v_char:=v_firstag||v_char;
END IF;
END IF;

IF NOT utl_file.is_open(v_file_out) then
v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_FRS_20150917.xml', 'W');
END IF;
UTL_FILE.PUT_LINE(v_file_out,v_char);
EXCEPTION

WHEN NO_DATA_FOUND THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
v_char:=null;
v_char:=v_char||v_lasttag;
UTL_FILE.PUT_LINE(v_file_out,v_char);
UTL_FILE.fclose(v_file_in);
END IF;
IF ( UTL_FILE.is_open(v_file_out) ) THEN
UTL_FILE.fclose(v_file_out);
END IF;
EXIT;

end;
END LOOP;
END IF;
dbms_output.put_line('No of records converted:' ||v_rec_count);

UTL_FILE.FCLOSE(v_file_in);
UTL_FILE.FCLOSE(v_file_out);

dbms_output.put_line('Second Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
UTL_FILE.fclose(v_file_in);
dbms_output.put_line( 'file_in'||SQLERRM);
END IF;
RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
dbms_output.put_line(SQLERRM);
END;

/

7)Write to txt file

SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
v_rec_count NUMBER := 0;
v_prog_recp_cnt NUMBER:=0;
v_terminate_cnt NUMBER:=0;
v_call_cnt NUMBER:=0;
v_class_cnt NUMBER:=0;
v_out_msg varchar2(4000);
v_term_evnt_exp EXCEPTION;

--File Variables
v_file UTL_FILE.FILE_TYPE;
l_dir_path varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';

cursor cur_pat is
select opcr.org_prog_cam_recipient_id opcr_id,opcr.recipient_pk_id,
dtc_report_util.get_attr_usage_val(8466,opcr.org_prog_cam_recipient_id)coachid ,
email.comm_value email,
opcr.create_date create_date
from dtc_org_prog_cam_recipient opcr, dtc_recipient_comm_value email
where opcr.recipient_pk_id=email.recipient_pk_id
and opcr.org_prog_cam_id=7103
and opcr.end_date is null
and email.comm_type_id=76
and (email.comm_value like '%VOXIVA%' or email.comm_value like '%TEST%')
and email.end_date is null
;
CURSOR get_event_chk(in_opcr_id number)
IS
SELECT opcr_event_id
FROM dtc_opcr_event
WHERE org_prog_cam_recipient_id = in_opcr_id
AND end_date IS NULL
AND current_status <> 'CLOSED TERMINATED';
v_opcr_event NUMBER(10);
BEGIN
dbms_output.put_line('DML Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dtc_global.org_prog_id := 5050;
FOR rec in cur_pat
LOOP
IF NOT utl_file.is_open(v_file) then
v_file := utl_file.fopen(l_dir_path,'dtc_pmo_5400_out_file.csv', 'W');
END IF;
v_rec_count := v_rec_count + 1;
--(1) PART Please create an output file with the following columns so we can send to the
IF v_rec_count=1 then
utl_file.put_line(v_file,'COACH ID'||','||'EMAIL'||','||'MEMBER CREATE DATE');
END IF;
utl_file.put_line(v_file,rec.coachid||','||rec.email||','||TO_CHAR(rec.create_date,'MM/DD/YYYY'));
--(2)PART For these recipients we need do the following:
--dbms_output.put_line(rec.opcr_id);
Update dtc_org_prog_cam_recipient
set end_date=trunc(sysdate)
where org_prog_cam_recipient_id=rec.opcr_id
and org_prog_cam_id=7103;
v_prog_recp_cnt:=v_prog_recp_cnt+sql%rowcount;
v_opcr_event:=null;
OPEN get_event_chk(rec.opcr_id);
LOOP
FETCH get_event_chk into v_opcr_event;
exit when get_event_chk%notfound;
dtc_dupe_process_pkg.terminate_event(v_opcr_event, v_out_msg, 'TERMINATED AS PER PMO-5400');
v_terminate_cnt:=v_terminate_cnt+1;
END LOOP;
CLOSE get_event_chk;
IF v_out_msg<>'0' THEN
RAISE v_term_evnt_exp;
END IF;
UPDATE dtc_opcret_call_attempt
SET end_date=sysdate
WHERE org_prog_cam_recipient_id=rec.opcr_id;

v_call_cnt:=v_call_cnt+sql%rowcount;

--Class Term and new class creation:
dtc_lc_5077_util_pkg.end_dt_cur_cls_and_ins_class(8283,rec.opcr_id);

v_class_cnt:=v_class_cnt+sql%rowcount;

END LOOP;
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('No of records Updated Prog_cam_recipient:'||v_prog_recp_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_event:'||v_terminate_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcret_call_attempt:'||v_call_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_class:'||v_class_cnt );
dbms_output.put_line('DML Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
commit;
EXCEPTION
WHEN v_term_evnt_exp THEN
RAISE_APPLICATION_ERROR(-20012,SQLERRM);
WHEN OTHERS THEN
IF ( UTL_FILE.is_open(v_file) ) THEN
UTL_FILE.fclose(v_file);
END IF;
RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
END;
/

Generating txt for to .csv files

declare
Procedure upd_card_type ( p_file_name IN VARCHAR2, p_card_type IN varchar2 ) IS
f1 UTL_FILE.FILE_TYPE;
f2 UTL_FILE.FILE_TYPE;
f3 UTL_FILE.FILE_TYPE;
v_file_name1 varchar2(100) ;
v_file_name2 varchar2(100) ;
v_file_name3 varchar2(100) ;
v_file_dir varchar2(300) := '/u02/apps/dtc/incoming_data/lc_5050/';
v_record varchar2(500);
v_rownum number := 0 ;
v_cnt number;
v_random_no dtc_opc_random_no.RANDOM_NO%type ;
begin
v_cnt:=0;
DBMS_OUTPUT.put_line ('DML Script Start Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
v_file_name1 := p_file_name ;
v_file_name2 := replace(v_file_name1, '.txt', '_reject.csv');
v_file_name3 := replace(v_file_name1, '.txt', '_log.csv');
f1 := UTL_FILE.FOPEN(v_file_dir, v_file_name1 , 'r', 4000);
f2 := UTL_FILE.FOPEN(v_file_dir, v_file_name2 , 'w', 4000);
f3 := UTL_FILE.FOPEN(v_file_dir, v_file_name3 , 'w', 4000);
dtc_global.org_prog_id := 5050 ;
LOOP
BEGIN
v_record := null ;
utl_file.get_line(f1,v_record);

v_random_no := null ;
if substr(v_record, 1, 4) = 'TRLR' then
EXIT ;
END IF;
v_random_no := substr(v_record, 1, 9);

for rec IN(select count(1) cnt from dtc_opc_random_no
WHERE random_no =v_random_no
AND attribute01='IN OFFICE'
AND org_prog_cam_id=6967)
LOOP
v_cnt:=v_cnt+rec.cnt;
END LOOP;

if SQL%ROWCOUNT = 1 then
v_rownum := v_rownum + 1 ;
else
utl_file.put_line(f2, v_random_no || ','||p_card_type ||', Rows updated = '||SQL%ROWCOUNT);
end if;

if mod(v_rownum, 5000) = 0 then
commit;
utl_file.put_line(f3, 'Record processed = '||v_rownum );
end if;
EXCEPTION
WHEN no_data_found THEN
EXIT ;
END ;
END LOOP;
DBMS_OUTPUT.put_line('No Of records CNT:'|| v_cnt);
DBMS_OUTPUT.put_line('No Of Update records:'||v_rownum);
DBMS_OUTPUT.put_line('DML Script End Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
utl_file.fclose(f1);
utl_file.fclose(f2);
utl_file.fclose(f3);
commit;
end;
begin
upd_card_type('IDs_sq9416_RESPIMAT6967LS_grp6967_cnt117335_typePATIENTSTARTERKIT_10152014.txt', 'PATIENT STARTER KIT' );
end ;
/

Thursday, May 3, 2012

Select operators or set operators

Also called a set operators

Union:
This is combines the results of two queries and returns the set of distinct rows returned by either query.
Union All:
This is combines the results of two queries and returns all rows returned by either query,including duplicates.
Intersect:

This combines the results of two queries and returns the set of distinct rows returned by both queries

Minus:

This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second

Other select operators

(+)Denotes that the preceding column is an outer join

* wildcard operator.Equals all columns in a select statement

Prior Denotes a parent-child relation ship operator in prior

Thursday, April 26, 2012

Sample Emp Tables Backup

CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL primary key,
                    DNAME   CHAR(14),
                    LOC     CHAR(13));

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE EMP (
EMPNO               NUMBER(4) NOT NULL,
ENAME               CHAR(10),
JOB                 CHAR(9),
MGR                 NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE            DATE,
SAL                 NUMBER(7,2),
COMM                NUMBER(7,2),
DEPTNO              NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE BONUS (
ENAME               CHAR(10),
JOB                 CHAR(9),
SAL                 NUMBER,
COMM                NUMBER);

CREATE TABLE SALGRADE
( GRADE               NUMBER,
   LOSAL               NUMBER,
   HISAL               NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

Thursday, January 5, 2012

Q: How to enter a single quotation mark in Oracle?

Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.

SELECT 'test single quote''' from dual;

The output of the above statement would be:
test single quote'

Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.

This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.

Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.

The same example inside PL/SQL I will use like following:

DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;

The output above is same as the Method 1.

Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:

SELECT 'test single quote'||CHR(39) FROM dual;

The output is same in all the cases.