I dont know where was the mistake, getting column ambiguously defined error
CREATE OR REPLACE PROCEDURE FLXUSER.testgg
AS
BEGIN
/*----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------*/
BEGIN
[code]....
I am getting ORA-00918: column ambiguously defined error when running the below SQL query. I am gettig this error at the last line of code where v1.objectid=v2.parent_object_id
I have not written this query so i find difficult to debug it. Also i am not that well verse with SQL
select distinct v1.UNIQUE_DOC_NAME,v1.UNIQUE_DOC_NAME_1,v1.vendor_object_name,v1.display_name,v1.document_description,
[Code].....
I have written this code below in Disco Admin 10g but get Ora-00918 column ambiguously defined error even though I have used alias in the table name.
Select
porh.segment1
,Porh.creation_date
,Porh.last_update_date
,porh.closed_code
,porh.authorization_status
,porh.description
,porh.note_to_authorizer
,porh.cancel_flag
,porh.enabled_flag
,porl.line_num
[code]....
I am getting an above errors while starting the emctl start dbconsole..
View 9 Replies View Relatedi want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)
for example
create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');
then i got my function
create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop
[code]....
now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;
when i try t compile i got:
Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope
I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.
Question 1:In case of dropping and recreating the index, should the following steps be done:
- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index
Question 2:Is there anything else that should be done when the index is dropped and re-created?
Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?
Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
I am using trim function in my select query. But still I am getting white space in my output. because of this, I am getting the error "value too large for column... " when I load the data into a table through sqlloader.
define APPName="&1"
set heading off;
set verify off;
set newpage 0
set feedback off;
set rtrimspool on;
set termout off;
set pagesize 40000;
[code].....
I have a scenario where I need to update field in report_input table.It has bind variables in it which will be prompted while running the query.I see that QUERY_VALUE field that need to be updated has length of VARCHAR2(3000).
desc report_input
Name Null Type
----------------- -------- --------------
QUERY_NAME NOT NULL VARCHAR2(64)
QUERY_VALUE NOT NULL VARCHAR2(3000)
[code]....
I am trying to run the below query to extract the outline data from v$sql_plan(querying other_xml column), but i get the below error...this works perfectly fine on 10.2.0.4 + version...but on 10.2.0.2 and and 10.2.0.1 i get the below error... how to fix it ?
SQL> @sql_hint
Enter value for sql_id: d15cdr0zt3vtp
Enter value for child_no: 0
extractvalue(value(d), '/hint') as outline_hints
*
ERROR at line 2:
ORA-00904: "D": invalid identifier
the query is taken from below...[URL]....
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
[code]...
i am running 11.2.0.1 on HP-UX PA-RISC (64-bit). My Memory max target is defined as 34 GB and memory target is 32 GB on the database. The host has 256 GB physical memory. i see memory utlization is almost 90% used when seen from Grid Control on the host and wondering, what's going on? when i see into more details, i see the following 3 HP Processes consuming around 3.6 GB memory and rest very small is consumed by Oracle processes. Who's using the rest of the memory?
View 3 Replies View RelatedIn SQL server the cursor is defined to retrieve row by row and process it. We can fetch records as FIRST, LAST, NEXT, PREVIOUS and SCROLL like that. Can we do the above in oracle. Else what is the use of cursor in Oracle. I know that it is a private area in which the records are processed.
View 6 Replies View RelatedI am trying to create a new data type but keeping getting a ORA-0902 invalid data type error. I am using the example show at:
Guess I cannot post a link until I have posted more than 5 messages so here is the link another way:
www(dot)psoug.org/reference/type.html
Specifically, all the steps work up to Create Table which fails with the error.
By work -- I mean the type gets created & the 3 select statements prior to Create Table confirm the existence of the ssn_t data type.
I've set up a query that creates 2 columns 'UVLCredit' and 'UVLDebit' and what I'm trying to do is subtract the 'UVLDebit' column from the 'UVLCredit' column and have the resulting value show up in a column called 'UVLTotal.
I'm multiplying 1 column times another to create a new column called UVLCredit, and then doing it again (with a different column) to create a 2nd new column called UVLDebit...the last thing I'm trying to do is to take the results of the 1st new column and subtract the results of the 2nd newly created column to create a 3rd new column called UVLTotal. The error states that the 'UVLDebit' column is an invalid identifier.
See code below....
CODESELECT
T.PO_RELEASE_NBR, T.PO_LINE_STATUS, T.FACILITY,
TI.STATUS_DATE, TI.QTY_ORDERED_UP,
TI.PO_UNIT_PRICE, TI.QTY_REC_TOTAL_UP, TI.QTY_INVOICED_UP,
[b]sum(TI.PO_UNIT_PRICE) * (TI.QTY_REC_TOTAL_UP) as "UVLCredit",
sum(TI.PO_UNIT_PRICE) * (TI.QTY_INVOICED_UP) as "UVLDebit",
[code]........
I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));
SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';
CREATE TABLE Z (A CHAR(4), B SSN_T);
recently i saw SYS_CONTEXT function in one package.
query, which can display the All Namespaces defined in database.
Can we override the pre defined functions in oracle. I am able to do this in my schema. But if DB server contains more than one schema do I need to write the same procedure in all schemes.
we can call the same procedure with schema name from different schemes also . But is there any way to over ride the actual function provided by oracle (just like over riding the equals method of object class in java).
create or replace function to_number(var varchar2) return number
is
no number := var;
begin
return 99999;
end;
SQL> select to_number(' 234 ') oracle_function , dbo.to_number(234) user_function from dual;
ORACLE_FUNCTION USER_FUNCTION
--------------- -------------
234 99999
i have flash item on canvas weh nthe nw form insatance i have that erros frm 41344 ole object not defined
View 5 Replies View Relatedi have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')
records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)
now when i try to use to_date function i get the error that
" ORA-01858 a non-numeric character was found where a numeric was expected "
in sql i have added a where clause to only pick rows with flag, but even then it gives the error.
using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.
I am trying to load the data from .csv file into the table using SQL Loader.
The table has the following schema:
src_id : number,
dest_id : number,
range: intsys.interval_typ --- > a type containing (lower,upper)
payload : varchar2(100)
The loader.ctl file is :
load data
infile *
append
into table sb_packet
fields terminated by "," optionally enclosed by ' " '
(src_id,dest_id,range,payload)
BEGINDATA
3,32,intsys.interval_typ(10043,142703),"misc data"
When I use the following this ctl file to transfer the data, i get the following error:
SQL*Loader-418: Bad datafile datatype for column RANGE
I'm getting an error as follows .
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name
SQL> create table t51(t5 NUMBER(25,8));
Table created.
SQL> insert into t51 values (100000000000000000.00000);
insert into t51 values (100000000000000000.00000)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.
Is it possible to easily set up a single User Defined Metric, to monitor all the users quotas? I know I can do it, if I make individual metrics for each user. But was hoping there was a simple way of combining them all into a single metric.
I am using Oracle 10G2 Standard Edition on a windows 2003 Server.
I'm calculating a Z score based on some simple numerical data thus:
create table t (id number, val number);
insert into t values(1, 1795);
insert into t values(2, 1753);
insert into t values(3, 1743);
insert into t values(4, 1876);
insert into t values(5, 1848);
[Code] .....
the logic is quite simple - calculate a moving average over the previous 12 rows, and a stdev over the same window. Then subtract the prior row's moving average from the current value, and divide by the prior row's stdev.
The issue is I want to expose this logic in a BI tool (OBI EE v10g), meaning I can't use the nested analytic functions. How to achieve this logic in a single analytic pass? The sql above took about 2 minutes to write this morning, then I've spent all day looking at user-defined aggregate functions, but haven't even been able to get the first step, the moving average, working. I can understand that I can probably create an udaf to replicate the avg(val) over (order by id ROWS BETWEEN 11 PRECEDING AND 0 FOLLOWING) functionality, but I can't see how to bundle the logic for the other three steps in the calculation into this.
From what I've read, the ODCIAggregateMerge should allow me to combine different threads that can return the different values I need for the current row calculation. Is this correct?
The only example udafs I can find are either not relevant (STRAGG) or very simple (ie don't appear to invoke multiple passes). I've also had a look at the COLLECT function, but again I can't see a way to use this.
I migrate schema with data from oracle 10gR2(Windows) to oracle RAC 11gR2 (Linux).My JOBS are created but they are not running on defined schedule. but they run manually successful.
----script of my job are as below --------
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'JHON.TAX_IT);
END;
/
[code]...
I have designed a Login form which takes username, password and connect string is hardcoded.I have compiled it, made an fmx file. created a shortcut to desktop. given the BIN path in shortcut.
when i want to lauch it, oracle's default login popup is shown.It can be by passed while defining username/password@string in shortcut properties but it reveals the password. every one can access it. I want to launch my own form without logging in to oracle. how can i avoid default login popup?
Can we able to declare a user define exception name as per defined exception name?Ex: we have predefined exception called :"NO_DATA_FOUND "
Now in my PL/SQL block can i able to declare "NO_DATA_FOUND" like
Begin
no_data_found exception;
//statements
raise no_data_found;
end
I have made a function to add two number create or replace function add_num(a in number, b in number) return number
as
begin
return a+b;
end;
/
i run it via
select add_num(2,5) from dual;
my question is how to call add_num in procedure.