create or replace p_aggop(dno number, maxi number,mini number,avgi number,tot number,cnt number) is cursor c1 is select * from emp1 WHERE DEPTNO=dno GROUP BY DEPTNO; v_emp1 c1%rowtype; begin open c1; loop fetch c1 into v_emp1; select min(v_emp1.sal),max(v_emp1.sal),avg(v_emp1.sal),sum(v_emp1.sal),count(*) into mini,maxi,avgi,tot,cnt from v_emp1; exit when c1%notfound; Dbms_output.put_line('EXECUTED '); end loop; dbms_output.put_line('The minimum salaray in dept is :'||v_emp1.mini); dbms_output.put_line('The maximum salaray in dept is :'||v_emp1.maxi); dbms_output.put_line('The average salaray in dept is :'||v_emp1.avgi); dbms_output.put_line('The total salaray in dept is :'||v_emp1.tot); dbms_output.put_line('The total members in dept is :'||v_emp1.cnt); CLOSE C1; end;
I am creating a stored procedure to create a table during run time using 3 passed in parameters to build the table name. Oracle is giving me the ORA-00922 missing or invalid operation error message on the EXECUTE IMMEDIATE statement of my stored procedure. I am new to writing stored procedures in Oracle. I am pasting a copy of the stored procedures. I have also changed the names of the field names to be generic so I can post the code in this forum.
CREATE OR REPLACE PROCEDURE createTable(PARAMETER1 in string, PARAMETER2 in string, PARAMETER3 in string) IS
TABLE_NAME NVARCHAR2(50); QUERY_STRING LONG; BEGIN
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone ( emplid VARCHAR2(10), seqno NUMBER, action VARCHAR2(3), office NUMBER, mobile NUMBER );
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999) / INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888) /
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE ---------- ---------- --- ---------- ---------- 100 1 OLD 1111 9999 100 1 NEW 2222 9999 100 2 OLD 2222 9999 100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2 authid current_user is l_search_condition user_constraints.search_condition%type; begin select search_condition into l_search_condition from user_constraints where constraint_name = p_cons_name; return l_search_condition; end;
and ran it...then when I write the below command:
alter table employee drop constraint select constraint_name from user_constraints where table_name = 'EMPLOYEE' and get_search_condition(constraint_name) like '%(( ROLL_NUMBER > 0 ))%' ;
The system throws:
Error starting at line 1 in command: alter table FL_ITEMSITE drop constraint select constraint_name from user_constraints where table_name = 'FL_ITEMSITE' and get_search_condition(constraint_name) like '%(( SS_QTY > 0 ))%' Error report: SQL Error: ORA-02250: missing or invalid constraint name 02250. 00000 - "missing or invalid constraint name" *Cause: The constraint name is missing or invalid. *Action: Specify a valid identifier name for the constraint name.
i want to drop constraint without specifying its name. i want the system to fetch it dynamically.
I am getting an ORA-00902: invalid datatype error when I am trying call the below function from a select statement. Here I am trying to get a table out from a function.
create or replace package pkg10 as type tabletype1 is table of table1%rowtype index by binary_integer; function func1 return tabletype1; end pkg10;create or replace package body pkg10 as
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
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
If I remove this line then the SQL runs without problems. I think it's the first part FAF_CALC_CCONTRIB (FA_ASSESSMENT.IDENTIFIER,'C'thats causing the problems because I am using near identical code in another script which works perfectly.
I'm working on my database homework, and I came across an error that I cannot seem to fix. I'm getting a missing right parenthesis error on line 7, but I seem to have all my parenthesis, endless I'm overlooking one.
I ran into a problem recently which has to do with the following query:
SELECT '' || ROUND(GEOGR_LAENGE,5) || ',' || ROUND(GEOGR_BREITE,5) || '," [BUE] ' || BAUFORM_GRUPPE || '"', KM AS TEXT FROM MYTABLEA mmtable WHERE (mmtable.GEOGR_LAENGE IS NOT NULL AND mmtable.GEOGR_BREITE IS NOT NULL AND mmtable.STRECKE_NR IN (
[code].....
The problem is, that Oracle alerts a missing expression at line 4, and highlights the "IS NOT NULL". Personally I don't see that anything is wrong with this line. I think the problems source is somewhere else but I cannot find it.
I'll give you a little bit of background to the script:
What I am trying to achieve is to request the values in the first line with the following conditions:
- mmtable.GEOGR_LAENGE, GEOGR_BREITE must not be empty - rbtable.STRECKE_NR for the requested line (which is similar to mmtable.STRECKE_NR) must have "MITTE" as a value for NL_NAME - mmtable.KM => rbtable.VON_KM - mmtable.KM <= rbtable.BIS_KM
I'm working on Oracle SQL Developer: ODMiner. I keep getting a syntax error with the following code,
CREATE TABLE FINAL_WEBLOG AS select SESSION_DT, C_IP, CS_USER_AGENT, LISTAGG(WEB_LINK, ' ') WITHIN GROUP (ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS", FROM WEBLOG_VIEWS GROUP BY C_IP, CS_USER_AGENT, SESSION_DT ORDER BY SESSION_DT
I understand if it's the semicolon that is missing after "FROM WEBLOG_VIEWS", but I'm not sure on how I can rephrase it.
I am getting the ORA-00936: missing expression error while running the below stated query. Problem is that it is running and fetching the data successfully for the first 5 runs. But from the 6th run it is not fetching the data and throwing the ORA-00936: missing expression error. consider I am passing parameters to the query and for the first 5 values i pass its runnign fine. when i run the query for the 6th time or more with different parameter, its throwing the error. it does not seems to be the data issue because when i pass the 6th parameter for the first run on the next day, it is fetching data.
(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I, ABC.EST_LIQN_D, '' AS EVNT_TYPE_C, ABC.CORR_STAT_C FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I and ED.ENTR_DOC_I = '123'AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I AND ED.ENTR_TYPE_C != 11 AND POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS' [code]....
SELECT LOCALTIMESTAMP,SYSTIMESTAMP,EXTRACT(hour FROM LOCALTIMESTAMP) +2, CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP) +2 Between 9 and 17 OR (EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 = '5' AND EXTRACT(MINUTE FROM LOCALTIMESTAMP)+2 > '60') THEN TO_CHAR(FROM_TZ(LOCALTIMESTAMP,'GMT') AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS') CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 < '9' THEN TO_CHAR(FROM_TZ(SYSTIMESTAMP,'GMT')+2 AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS') END FROM dual;
What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?
l am loading data i.e text file of huge size into oracle 9i db using sqlldr through command prompt, but my table contains already some data so while loading it shows error as
SQL*Loader-601: For INSERT option, table must be empty.
i cant truncate my table since present data is important.