Very Common Questions on Oracle SQL


Q- Can two different users own tables with the same name?

A- Yes, tables are unique by username.tablename.

NOTE: Problems will occur if synonyms are used to make table names the same name; private synonyms override public synonyms.

 Q- Is it possible to update another table with 2 concatenated columns?
A- Yes, the following update statement will work:
UPDATE tablename SET colname = columnX || columnY
WHERE colname = ¤t_value_for_this_column ;
NOTE: The result is truncated to 255 characters.

Q- How can a new view be created from two old views that have common field names?
A- Use aliases in the fields. For example:
CREATE VIEW x AS
SELECT tab1.fld1 afld1, tab2.fld1 bfld1
FROM tab1,tab2
WHERE tab1.fld1=tab2.fld1;

Q- Can columns be called TO and FROM?
A- These are reserved words and cannot be used to name database objects.

Q- Is it allowed to have % in the field name?
A- Allowed, Yes! Recommended, No! It can be confused with the wildcard symbol ‘%’ used in the LIKE clause.

 

Q- Can you index columns defined in views?

A- No, view columns themselves cannot be indexed.

 

Q- Is it possible to prompt a user for data when running a SQL script?

A- Yes, prefix the column name with & or &&.

The following method can be used :

SELECT fld1,fld2

FROM tab1,tab2

WHERE key1=’&key1′;

Prompts for Enter value from key1: when data is entered this produces an old/new information listing which may be turned off with

SET VERIFY OFF

If && is used, the value is prompted for once and then used automatically if that value is used again during that SQL*Plus session.

 Q- How do you select the first 10 rows of a table?
A- Use system variable rownum in where clause. For example:
SELECT *
FROM table1
WHERE ROWNUM < 11;

Q- How do I return the first 10 values that occur most frequently?
A- CREATE VIEW v1 as:
SELECT name, count(*) num
FROM table
GROUP BY name;

sELECT name,num
FROM v1 a
WHERE 10 > (SELECT COUNT(*)
FROM v1
WHERE a.num < num)
ORDER BY num;

Removing ”^M” from SQL*Loader datafile


You notice that you have extra ‘^M’ characters at the end of your records in your SQL*Loader datafile. You need to remove them so your load will work successfully.

————————–

You can remove the ^M by doing the following:

 

Bring the datafile into the Unix editor vi. In command mode you would type in:

 

:1,$s/^V^M//

 

Make sure you are entering ‘control V’ and ‘control M’. The ‘control V’ says to accept the next character exactly as I type it. Adding the two slashes at the end of the command says to subsitute the ^M with null.

 

The extra ^M characters are most likely due to moving the file from one platform to another. The carriage returns or end-of-line commands are sometimes interpreted differently.

 

You can see this if you move a file from a PC to a UNIX system or if you copy a file from one system through a gateway to a different system.

Variable Input file handling in Oracle SQL*Loader


SQL*Loader cannot handle variable input file names. This can be needed for periodic loads to Oracle database with file names changing each time. This article shows four workarounds to use variable input file names with SQL*Loader.

This article is helpful for SQL*Loader users need to load from input files with variable names. It covers four detailed workaround examples.

How to Pass the Input File with Variable Names to SQL*Loader Dynamically

SQL*Loader cannot handle variable input file names. The parameter INFILE specified in the control file doesn’t allow variable names – if the name of the data file is changed, then the control file must be edited and the name of the inputfile must also be changed.

This article describes 4 workarounds, how to pass the input file to SQL*Loader dynamically, without changing the control file every time the input file name is changed.

For a better understanding we will consider the following test case:

— The table to be loaded

CREATE TABLE LDR_TEST

(

ID NUMBER,

TEXT VARCHAR2(10)

);

— Input data records (file LDR.DAT)

1;Text 1

2;Text 2

3;Text 3

 

1. The simplest method to do this is to rename the input file (accordingly to the control file parameter INFILE). If the control file (LDR1.CTL) was defined as:

LOAD DATA

INFILE ‘LDR.DAT’

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

then every new input file must be renamed to LDR.DAT before starting the load process.

 

******************

* SAMPLE 1 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) renames the input file name ($1)

# to LDR.DAT that will be later used by SQL*Loader.

# Note: The shell must have at least 774 permissions to run. (chmod 774)

# This works only for UNIX platforms

# Rename (or copy) the file

mv $1 LDR.DAT

# cp $1 LDR.DAT

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR1.CTL

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

2. Pass the data file to SQL*Loader with the command line parameter DATA. In this case the control file (LDR2.CTL) doesn’t contain the parameter INFILE.

LOAD DATA

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

The data file is specified in the command line (parameter DATA).

******************

* SAMPLE 2 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) passes the inputfile ($1) to SQL*Loader through

# the parameter DATA in command line

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR2.CTL data=$1

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

3. Use a pipe to transfer the data from data file to SQL*Loader. First, the records from the data file will be sent in a pipe, and then SQL*Loader will receive the records from the pipe and insert them in the database. The command lines for different operating systems are the following:

VMS (7.2+) pipe type ldr.dat | sqlldr scott/tiger control=ldr3.ctl

UNIX cat LDR.DAT | sqlldr scott/tiger control=LDR3.CTL

NT type ldr.dat | sqlldr scott/tiger control=ldr3.ctl

 

The appropriate control files (LDR3.CTL) are these:

UNIX and NT

———–

LOAD DATA

INFILE “-”

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

VMS

LOAD DATA

INFILE SYS$INPUT

TRUNCATE

INTO TABLE TEST_LDR

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

******************

* SAMPLE 3 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) uses a pipe to pass input file ($1) to SQL*Loader.

# Start SQL*Loader with the new control file

cat $1 | sqlldr scott/tiger control=LDR3.CTL

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

4. Use a place holder in control file and automatically change it before starting the load process. In this example the control file (LDR_TEMPLATE.CTL) looks like this:

LOAD DATA

INFILE $INPUT

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

The idea is to programatically change the name of the place holder $INPUT before starting SQL*Loader. This will be done with the following UNIX shell:

******************

* SAMPLE 4 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) passes the input file name ($1) to the SQL*Loader

# control file – it searches the place holder $INPUT in the control file

# LDR_TEMPLATE.CTL, changes it to the new name ($1) and save it in the new

# control file named LDR4.CTL. This file will be later used by SQL*Loader.

# Change the place holder

cat LDR_TEMPLATE.CTL | sed -e “s%\$INPUT%’$1’%” > LDR4.CTL

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR4.CTL

—————————— Shell end ——————————

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new input file.

Oracle iExpense- What are the tables involved in the credit card process?


Tables are used in Credit Card process in Oracle iExpense are as follows

ap_credit_card_trxns_all
ap_card_programs_all
ap_cards_all

In 11i.OIE.H and higher Mini-packs, the following additional tables are introduced:

ap_card_details
ap_card_emp_candidates
ap_card_parameters
ap_map_codes
ap_map_types_b
ap_map_types_tl
ap_no_match_trxns

 

What concurrent programs and forms does credit cards use?

Credit Card uses these concurrent programs:
1. (Card Name) Transaction Loader
2. Credit Card Transaction Validation Report
3. Create Credit Card Invoice Interface
4. Credit Card Outstanding Charges Report

Credit Card uses these windows:
1. Credit Card Programs window
2. Credit Cards window.

Oracle iExpense – How To Enter Internet Expense Reports For Other Persons


This feature is called “Authorized Delegate”. To have the ability to enter expense reports, withdraw expense reports, etc. for other individuals, do the following:

 Authorization Preferences (Self-Service Delegate Assignment)
Users can now maintain their own list of authorized delegates in the Expenses Preferences page. An authorized delegate is someone who has the authority to enter expense reports on another user’s behalf. This feature existed in previous versions of Internet Expenses as a system administrator task only. The ability for system administrators to define authorized delegates still exists.

 Choose Responsibility:
Internet Expenses
Navigate to the Expenses Preferences
Choose the Authorization Preferences Link

Expenses Entry Delegations:
The people in this list can enter expense reports for you. They can also view all of your previously submitted expense reports. Expenses Entry Delegations can be granted by you to another person

Expenses Entry Permissions

You can enter expense reports for the people in this list. You can also view all of their previously submitted expense. Expenses Entry Permissions must be granted to you by the other person (You cannot grant your own Expense Entry Permissions).

 OR

Prior to this functionality, Authorized delegate permissions were granted as follows. This functionality still exists.

Choose Responsibilty: System Administrator

Navigate: Security – User – Define
Query up the user who is going to be be an authorized delegate.

Go to the Securing Attribute region

Add a row

Securing Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Choose the employee name from the list of values for the employee for which this user needs to be able to enter expense reports form. Bounce Apache Server and Clear Server Cache if Authorized Delegates are updated using this method.

Oracle AP – Payables Payment Selection Criteria


The following is a checklist and description of conditions that prevent an invoice from being selected for payment. You can use this bulletin to investigate why your invoice may not be getting paid.

 

CHECKLIST OF CONDITIONS:

o Is the invoice approved?

o Is there a hold on the invoice?

o Is the invoice due?

o Is the invoice currently selected in another payment batch?

o Does the payment method (i.e., check) match the payment method in the invoice?

o Does payment currency you requested match the invoice currency?

o Has the invoice already been paid?

o Is the invoice for #0? (not selected by Autoselect and Quickcheck)

o Does the invoice match criteria specified in the payment batch? (Paygroup, Minimum or Maximum amounts, Payment Priority …)

 

DESCRIPTION OF CONDITIONS:

a. Invoice has not been approved.

o An invoice MUST be approved in order to be paid.

o Remember that an invoice must be re-approved if it has been modified. (This is important to remember if you rely on a daily autoapproval batch)

 

b. A hold is preventing payment of the invoice.

o There are the 3 types of holds that can prevent payment and where to check if there is a hold on your invoice.

o Invoice Hold

o Payment Schedule Hold

o Vendor Hold

 

c. Invoice has already been selected for payment in a payment batch.

o Payables prevents you from paying an invoice which has already been selected for payment.

o Query all payment batches that have a status of In Process.

 

d. Payment method for payment request does not match payment method for invoice.

o The value in the Payment Method field in the Invoice must match the Payment Method for the payment request.

o Check the Payment Method field for Autoselect Payments.

o Check the Payment Method for the chosen Payment Document for Manual or QuickCheck payments.

o Example: Payment Method for the invoice is EFT, but you are doing a payment request for Check

 

e. Payment request currency does not match the invoice currency.

o The Invoice currency and Payment Request currency must be the same (release 10.7 and 11).

 

f. Invoice has already been paid.

o You cannot fully pay an invoice twice.

 

g. Invoice is for $0 (AutoSelect and QuickCheck payment methods only)

o AutoSelect and QuickCheck will not select $0 invoices.

o You can record a $0 invoice as paid with a payment batch or manual payment

 

h. Invoice does not match payment criteria (AutoSelect only)

o The invoice must match the criteria you select when you run AutoSelect. If used, the following criteria may prevent your invoice from being selected.

o Minimum and Maximum payment amounts.

o Is your invoice above the minimum and below the maximum amounts?

o PayGroup.

o Is your invoice in the PayGroup selected?

o Pay Through Date.

o Is the date used greater than or equal to the Due Date for the invoice? (Check the Invoice Payment Schedule)

o Priority Range.

o Did you specify a range other than the default 1 – 99? If so, is your invoice’s payment priority in the range?