|Oracle Unknown Command - CONSTRAINT|
Any empty lines will stop SQL*Plus from accepting
the inputs blocks and put it in buffer.
So, when you started your CONSTRAINT keyword after
an empty line, it treated it as a new command, and
thrown an error.
Try this, before you run all your DDLs.
set sqlblanklines on
You need to instruct the sql*plus to ignore empty
|ORA-14450 "attempt to access a transactional temporary table alreadyin use" with no autonomous transactions|
ORA-14450 means you have a blocking session on the
temp table. Find the blocking session and kill it
if need be.
SELECT * FROM v$lock
WHERE id1 = (SELECT object_id FROM all_objects
WHERE owner =<schema_name> AND object_name
=<glb temp table>)
SELECT * FROM v$session WHERE sid =<above query
ALTER SYSTEM KILL SESSION
|PL / SQL Trigger to format information with compilation errors|
You have to use old or new to refer the row value.
Not the table name.
create or replace trigger tgr_trigger_prueba
after insert on datos_prueba
for each row
if :new.numero <= 5 then
insert into store_datos_prueba
|How to change the background color of dropdown list|
You will need to use an ADF Skin (ex:
You will need to define several StyleClasses for
each of your cases and apply that style class when
|Why do C-style comments make insert statement run twice?|
/* This is a comment */
Just make sure you have a space after /* ,
So it is treated as a single/multi line comment.
And not mean to execute the last stored PL/SQL or
To put it in detail.
What ever SQL*Plus interprets after / is ignored
and it blindly pushes it's cached block into the
server. Except for /* followed by a new line or
SQL> SELECT * FROM DUAL;
|SQLException caught Invalid column index|
You are calling a stored procedure that does not
return any cursor, but your java code is assuming
that the procedure call is opening a cursor to
iterate through. Also note that DBMS_OUTPUT is
buffered on the server, and is not normally seen
through a JDBC call. Here is an example for what
you are trying to do
|Create an Apex form with multiple pages|
It's kind of a broad question - lots of possible
solutions. You will need to create several regions
on the page, so that you can easily show or hide
different sets of page items without submitting
If you simply want to keep the page tidy, you
could make use of the Hide and Show Region
template, or you could create a Region Display
To help the user experience flow a bi
|I have to access only -ve values of balance|
If you want to reference the balance you have to
embrace it again into a subquery
select sum(b.budget) as Budget,
sum(b.rev) as Revised,
sum(b.expe) as Expenditure,
(sum(b.budget) + sum(b.rev) -
sum(b.expe)) as balance,
b.accounts, b.abac_code, b.abac_name,
b.dac_code, b.dac_name, b.abacname
|SQL Server 2014 and DTS packages: The specified package could not be loaded from SQL server database|
Migrate your package to SSIS. Visit
|ORA-01830 Error for 11.2.0 client but not for 10.2 client|
According to the CREATE_JOB Procedure
documentation, the parameter start_date must be of
type TIMESTAMP WITH TIME ZONE.
In your PL/SQL code you are instead passing a
string thus relying on implicit conversion.
You could instead use an explicit conversion to
the TIMESTAMP WITH TIME ZONE data type such as:
TO_TIMESTAMP_TZ('15-NOV-08 12.00.00.000000000 AM
AMERICA/NEW_YORK', 'DD-MON-RR HH:MI:SS.FF
|Oracle case when exists using same variable as insert into|
The CASE and EXISTS cannot be used in the way you
You can do something like this.
SELECT COUNT(DECODE(value,'0',field)) as v1,
COUNT(DECODE(value,'1',field)) as v2,
COUNT(DECODE(value,'2',field)) as v3
|When sequence was updated in oracle|
The oracle sequence are NOORDER in default. please
refer the oracle documentation
. hence NOORDER behavior cannot be as expected.
You can check whether your oracle sequence is in
ORDER if not then it is some other problem.
|How to remove unnecessary characters from a SQL*Plus result?|
Easiest way -> Just concatenate your output (
gives you the benefit of easily formating your
dates and numbers on the fly)
||';'|| to_char( yourDateColumn1,
||';'|| trim( yourCharColumn3 ) --trim if
|What is the significance of connect role in oracle|
According to the Database Security Guide, the
CONNECT ROLE was changed in Oracle Database 10.2:
The CONNECT role was originally established a
special set of privileges.
These privileges were as follows:
CREATE DATABASE LINK
Beginning in Oracle Da
|missing right parenthesis insert statement ORA-00907|
INSERT INTO packageamounts
name, '', '', 1, 1,
NULL, 0, NULL, package_id, '', amount,
packageamounts.id IN (SELECT id
FROM (SELECT *
|Old and current location in single row|
Try this one you will get your desired result this
in case when you have one old flag per empid .
select empid,empname,location oldlocation
from empd where flag='old'
select empid,empname,location current_location
from empd where flag='current'
|ColdFusion Oracle Datasource Hangs After "Too Many" Rows|
Instead of using tag for limiting the number of
rows returned you can try limiting it from the
Try this code:-
SELECT * FROM (SELECT TRANSID, TRANSDATE
FROM TBL_TRANS ORDER BY TRANSID) TB_TRANSACTION
WHERE rownum <= 50
ORDER BY rownum;
Please let me know if this helps.
|How to call a Procedure which uses the same table in after trigger|
A normal row-level trigger cannot query the table
the trigger is defined on because that would raise
a mutating table exception. I'm assuming that's
why you have declared your trigger to use an
autonomous transaction (an autonomous transaction
for anything other than persistent logging is
almost certainly an error). If you do that,
however, your trigger cannot see the uncommitted
|Pool NeuraChlDS is Suspended, cannot allocate resources to applications in weblogic server 11g 10.3.6|
It means that you are out of physical connections.
Or your application is not releasing connections
that are not in use anymore, or it needs more
connections. In that case you can enlarge Maximum
capacity value in console.
"Services-> Data Sources -> your data source ->
Connection Pool -> Maximum Capacity"
|SQL query using Correlated joins|
considered two tables hist and fund. And required
query will be:
from fund f, hist h
where f.FUND_ID=h.FUND_ID and f.fund_id is not
and f.FUND_ID not in (select
nvl(REPLACEMENT_FUND_ID,'0') from fund)
and h.OLD_STATUS is null and h.NEW_STATUS='I';
|select statement oracle sql|
You could use a self join:
SELECT e.name AS name, m.name AS manager_name
FROM emp e
LEFT JOIN emp m ON e.mgr_id = m.emp_id
Note the usage of a left join so employees without
managers (supposedly the CEO) won't be excluded.
|oracle adf how to disable selected rows by click on bottun?|
I'm assuming you want to disable selection of rows
in a table. Your question is not very clear.
Create a bean and an attribute inside it which
will handle the table selection. This can have the
values "single", "none" etc. Read more about it
Drag and drop a button, double click it to create
the code in the backi
|pl/sql column name as variable using cursor|
First, the fact that you have a bunch of similarly
named columns and that you want to aggregate the
data in those columns strongly implies that your
data model is incorrect. If you had 30 rows of
data rather than 30 columns, your database would
be much more reasonably normalized and your
queries would be much easier.
Assuming that there is a valid reason that you
can't fix your data model, you r
|Join 3 table to find total cost|
select c.project_number, c.project_name,
sum(do.qty * do.Unit_price)
from XYZ_PROJECTS c
join XYZ_INVOICES i
on c.PROJECT_NUMBER = i.PROJECT_NUMBER
join XYZ_INVOICE_DETAILS do
on i.INVOICE_NUMBER = do.INVOICE_NUMBER
group by c.project_number, c.project_name
If you need to include zero cost projects who do
not have any invoices yet, use left outer join to
keep all rows from XYZ_PROJECTS
|Call a Package from Apex passing two values|
To receive 2 values in package you obviously need
procedure with 2 parameters. For example for your
create or replace PACKAGE "PKG_MAIL_SEND" as
-- let this procedure have 2 parameters:
PROCEDURE proc_mail_send(p_address in varchar2,
p_topic in varchar2);
After that click on Create page
|XML Publisher Oracle Apps|
if you are developing an .rtf template, it is as
easy as inserting an image in word document file.
if you have put your logo file (.png or .jpg) in
your server then follow the below steps:
Step 1: In your XML data generate the logo file
Step 2: In RTF template, create a form-field to
initialize a XSL variable “logo” using code as
Step 3: Insert a dummy image
|Storing table data as blob in column in different table(Oracle)|
I have impression you want to go from structured
content to non-structured.
I hope you know what you are trading off, but I do
not have that impression reading your question.
Going BLOB you lose relationship / constraints
It could be faster to read one block of data, but
when you need to write minor change, you may need
to write bigger "chunk" in case of big BLOBs.
|Oracle - Difference betwen Consistent Gets in Autotrace and Consistent Read in SQL TRACE|
The difference is that the tkprof output shows
your single SELECT statement, but the Statistics
section of autotrace output summarizes 2
statements, your SELECT plus 1 recursive calls, as
indicated by the first line of that section.
A few examples for recursive calls are: triggers
(INSERT, UPDATE, DELETE), dynamic sampling (in
case of missing table statistics) or internal
statements querying data
|Upgrade Oracle 10g to its higher version|
Patches are usually available on Oracle support
Currently available patches start from 10.2
More about versions, lifecycle support in
(Your 10.1 version has limited support)
If you plan to change your environment, you can
see that, in case of the problem, you would be on
|Can't access checkbox values from backing bean in ADF/JDeveloper|
See if these help in terms of combining checkboxes
and trees :
|Oracle SQL Developer: how do I make the Home and End keys behave?|
Set your shourcut behaviours in Tools ->
Preferences -> Shortcut Keys
|dependent Lov in oracle forms 10g|
For this, you simply create a RECORD GROUP with
the required columns
select order_no,order_name from
Now create LOV and assign this record group to it.
In your LOV properties you find Column Mapping
properties and map the columns accordingly.
And finally assign this LOV to your text item.
Then you are done.
|Confusion about user Authentication process in an APEX application|
Both of these functions are actually called. When
user clicks login button, APEX executes page
process with wwv_flow_custom_auth_std.login, and
this function calls your authentication function
later. To be sure, create a table for logging and
add in your function code that inserts a row in
logging table. Then try to login and check table.
|Oracle Query: Loop through columns dynamically to create rows|
This should give you an idea. I couldn't test it,
but fairly simple to understand.
create or replace package test_pkg AS
TYPE REP_CURS IS REF CURSOR;
TYPE output_REC IS RECORD(
TYPE output_TAB IS TABLE OF output_REC;
FUNCTION Get_Data RETURN output_TAB
CREATE OR REPLACE PACKAGE BODY tes
|ORA-00911: Invalid Character via ODBC, Adhoc SQL - within comments?|
If a comment includes the whole word end then the
Oracle driver (or server) appears to be
interpreting this badly.
If you remove the end word within the comment, the
statement executes correctly.
I think this is because it doesn't expect a
semi-colon after experiencing an 'end' directive
(even though it shouldn't be parsing a comment).
The following statement executes without issue and
|records less than average number of days between 2 columns|
SELECT t1.*,AVG(TO_DATE(end_date,'dd-mon-yy') -
TO_DATE(start_date,'dd-mon-yy')) OVER() total_avg
TO_DATE(start_date,'dd-mon-yy') diff FROM
university t1) q
WHERE diff< total_avg
|date conversion from DD/MM/YYYY HH:MM:SS to YYYYMM|
Format Code for Minutes is MI, not MM. MM is for
You are using 2-digit year. Better to use RR for
this. Even better use 4-digit year.
TO_DATE doesn't store fractional seconds. You need
to use TO_TIMESTAMP and use the FF as format code.
So, your query would be
09:00:50.000000000 AM','MM/DD/RR HH:MI:SS.FF9
|oracle epm1121 configure web server failed because of missing ohs_component|
Resolved this problem by finding out local host
name is not consist with its IP address. After
correcting the IP Address, the problem resolved.
Another point need focus is your user account
which used to install Hyperion better join the
root, or else may not satisfied some required
The other important need care is your
.bash_profile need adding below configuration.
|The different between TABLE_NAME vs "TABLE_NAME" in Oracle?|
When you specify the name of a table in Oracle
without double quotes then Oracle converts that
table name to uppercase. But if you specify the
name of the table within double-quotes then Oracle
will respect the lower-case letters that you may
So, as in your example the name of your table is
already all upper-case, then there is no
difference in specifying or not the double-quotes.