spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » ORACLE » Page 1
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 lines

Categories : Oracle

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 result> ALTER SYSTEM KILL SESSION '<sid>,<serial#'; Credit

Categories : Oracle

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 declare cambio_numero number; begin if :new.numero <= 5 then insert into store_datos_prueba (nombre,numero,mensaje,fecha) values(:new.nombre,666,:new.mensaje,:new.fecha); el

Categories : Oracle

How to change the background color of dropdown list
You will need to use an ADF Skin (ex: https://blogs.oracle.com/jdevotnharvest/entry/how_to_learn_adf_skinning). You will need to define several StyleClasses for each of your cases and apply that style class when needed.

Categories : Oracle

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 SQL 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 space. SQL> SELECT * FROM DUAL; D - X SQL> /*t*/

Categories : Oracle

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 http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/

Categories : Oracle

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 the page. 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 Selector region. To help the user experience flow a bi

Categories : Oracle

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 * from ( 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 from ( select

Categories : Oracle

SQL Server 2014 and DTS packages: The specified package could not be loaded from SQL server database
Migrate your package to SSIS. Visit http://sqlmag.com/sql-server/coordinating-dts-ssis-migration Or http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169050

Categories : Oracle

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

Categories : Oracle

Oracle case when exists using same variable as insert into
The CASE and EXISTS cannot be used in the way you expect. You can do something like this. DECLARE localvariable1 NUMBER; localvariable2 NUMBER; localvariable3 NUMBER; localvariable NUMBER; BEGIN SELECT COUNT(DECODE(value,'0',field)) as v1, COUNT(DECODE(value,'1',field)) as v2, COUNT(DECODE(value,'2',field)) as v3 INTO localvariable1,

Categories : Oracle

When sequence was updated in oracle
The oracle sequence are NOORDER in default. please refer the oracle documentation https://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6014.htm . 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.

Categories : Oracle

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) select yourCharColumn1 ||';'|| yourCharColumn2 ||';'|| to_char( yourDateColumn1, 'YYYY-MM-DD HH24:MI:SS') ||';'|| trim( yourCharColumn3 ) --trim if necessary from yourTable

Categories : Oracle

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: ALTER SESSION CREATE SESSION CREATE CLUSTER CREATE SYNONYM CREATE DATABASE LINK CREATE TABLE CREATE SEQUENCE CREATE VIEW Beginning in Oracle Da

Categories : Oracle

missing right parenthesis insert statement ORA-00907
Try this: INSERT INTO packageamounts SELECT '6abc148c-20b7-cd34-c433-5472b1b9627d', name, '', '', 1, 1, NULL, 0, NULL, package_id, '', amount, taxtype_id, unit_value, 'd754c9c9-a0cb-ad43-b237-546c7f6eb903' FROM packageamounts WHERE packageamounts.id IN (SELECT id FROM (SELECT * FR

Categories : Oracle

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 curr.empid,curr.empname, nvl(old.oldlocation,'-') oldlocation,curr.current_location from ( select empid,empname,location oldlocation from empd where flag='old' ) old, ( select empid,empname,location current_location from empd where flag='current' ) curr where old.empid(+)=c

Categories : Oracle

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 database side. Try this code:- <cfquery name="qry_Test" datasource="dsn_orcl"> SELECT * FROM (SELECT TRANSID, TRANSDATE FROM TBL_TRANS ORDER BY TRANSID) TB_TRANSACTION WHERE rownum <= 50 ORDER BY rownum; </cfquery> Please let me know if this helps.

Categories : Oracle

How to call a Procedure which uses the same table in after trigger
You can't. 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 cha

Categories : Oracle

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"

Categories : Oracle

SQL query using Correlated joins
considered two tables hist and fund. And required query will be: select * from fund f, hist h where f.FUND_ID=h.FUND_ID and f.fund_id is not null 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';

Categories : Oracle

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.

Categories : Oracle

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 here: https://docs.oracle.com/cd/E28280_01/apirefs.1111/e12419/tagdoc/af_table.html Drag and drop a button, double click it to create the code in the backi

Categories : Oracle

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

Categories : Oracle

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

Categories : Oracle

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 package: 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); PROCEDURE proc_job_mail_send; PROCEDURE proc_kill_job_mail; end PKG__MAIL_SEND; First way After that click on Create page

Categories : Oracle

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 path. e.g server_path/logo.jpg Step 2: In RTF template, create a form-field to initialize a XSL variable “logo” using code as Step 3: Insert a dummy image

Categories : Oracle

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 between values. 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. To insert

Categories : Oracle

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

Categories : Oracle

Upgrade Oracle 10g to its higher version
Patches are usually available on Oracle support site http://support.oracle.com. Currently available patches start from 10.2 version. More about versions, lifecycle support in http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf (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 saf

Categories : Oracle

Can't access checkbox values from backing bean in ADF/JDeveloper
See if these help in terms of combining checkboxes and trees : https://pinboard.in/search/u:OracleADF?query=af%3Atree+af%3AselectBooleanCheckbox

Categories : Oracle

Oracle SQL Developer: how do I make the Home and End keys behave?
Set your shourcut behaviours in Tools -> Preferences -> Shortcut Keys source : http://www.thatjeffsmith.com/archive/2012/11/keyboard-shortcuts-in-oracle-sql-developer/

Categories : Oracle

dependent Lov in oracle forms 10g
For this, you simply create a RECORD GROUP with the required columns select order_no,order_name from xx_customer_details; 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.

Categories : Oracle

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.

Categories : Oracle

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( id_ number, name_ varchar2(50), field_ varchar2(50)); TYPE output_TAB IS TABLE OF output_REC; FUNCTION Get_Data RETURN output_TAB PIPELINED; END test_pkg; CREATE OR REPLACE PACKAGE BODY tes

Categories : Oracle

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 returns 7

Categories : Oracle

records less than average number of days between 2 columns
Try this: SELECT dep_id,class_id,start_date,end_date,comments FROM ( SELECT t1.*,AVG(TO_DATE(end_date,'dd-mon-yy') - TO_DATE(start_date,'dd-mon-yy')) OVER() total_avg ,TO_DATE(end_date,'dd-mon-yy') - TO_DATE(start_date,'dd-mon-yy') diff FROM university t1) q WHERE diff< total_avg

Categories : Oracle

date conversion from DD/MM/YYYY HH:MM:SS to YYYYMM
Format Code for Minutes is MI, not MM. MM is for months. 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 select to_char(to_timestamp('04/03/10 09:00:50.000000000 AM','MM/DD/RR HH:MI:SS.FF9 AM'),'YYYYMM') from table;

Categories : Oracle

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 right. The other important need care is your .bash_profile need adding below configuration.

Categories : Oracle

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 have. 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. But for

Categories : Oracle


Recently Add
Oracle Apex Data loader - 4 unique columns to check
Oracle Unknown Command - CONSTRAINT
ORA-14450 "attempt to access a transactional temporary table alreadyin use" with no autonomous transactions
PL / SQL Trigger to format information with compilation errors
How to change the background color of dropdown list
Why do C-style comments make insert statement run twice?
SQLException caught Invalid column index
Create an Apex form with multiple pages
I have to access only -ve values of balance
SQL Server 2014 and DTS packages: The specified package could not be loaded from SQL server database
ORA-01830 Error for 11.2.0 client but not for 10.2 client
Oracle case when exists using same variable as insert into
When sequence was updated in oracle
How to remove unnecessary characters from a SQL*Plus result?
What is the significance of connect role in oracle
missing right parenthesis insert statement ORA-00907
Old and current location in single row
ColdFusion Oracle Datasource Hangs After "Too Many" Rows
How to call a Procedure which uses the same table in after trigger
Pool NeuraChlDS is Suspended, cannot allocate resources to applications in weblogic server 11g 10.3.6
SQL query using Correlated joins
select statement oracle sql
oracle adf how to disable selected rows by click on bottun?
pl/sql column name as variable using cursor
Join 3 table to find total cost
Call a Package from Apex passing two values
XML Publisher Oracle Apps
Storing table data as blob in column in different table(Oracle)
Oracle - Difference betwen Consistent Gets in Autotrace and Consistent Read in SQL TRACE
Upgrade Oracle 10g to its higher version
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.