I have a problem with the code below. I would like to define a variable (exrate1) which I would like to use in another script. Also, the variable should be different for every month(that's why timestamp is between some period).
The problem is that I receive some errors which I don't know haw to fix
Oracle and I am receiving ora-00905 missing keyword error.what could possibly be wrong with this SQL statement?
Select
T1.*,
T2.*
From
Table1 T1 INNER JOIN
Table2 T2 On
[code]....
I am trying to select the maximum dates from 2 different tables and I am using Oracle 10.
Check out following query
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;
ORA-00905: missing keyword
i need to get values from lookup table dynamically,i am getting the missing keyword error.
create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
AS
vsql varchar2(2000);
CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
BEGIN
[code]....
I trying to Assign XML content to the clob variable inside the pl/sql block, But i am getting the Below Error:
declare
t clob;
begin
t := 'xml content exceeds 32000 characters'
update test
clob_cloumn = t;
where id =2;
exception
when others then
null;
End;
ORA-06550: line 5, column 4:
PLS-00172: string literal too long
I need to handle this exception, i know it length exceeds 32000 characters, but even though i need to handle the exception and to perform other operation after handling the exception.
I am trying to resolve by using a cursor C2 inside the already existing cursor C1, i will use a inner loop to get the select statement value to cursor c2 then i will assign the vlaues the variables, then once i read all the values of cursor c2 i will exit inner loop and go to outer loop to read the next value of c1 and then again go to cursor c2 and inner loop.
I have pasted the code below.
Declare
temp1 source.source%type;
temp2 QUOTE_LETTERS_MASTER%rowtype;
Cursor c1 is
Select * from Source;
[code]...
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin
dbms_output.put_line(a ||'->'||b);
end;
Anyonymous Block:
begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...
I am trying to accept three numbers and one string as input, and then display the values on the screen. I don't understand why I am getting these errors.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 id_number NUMBER := &id_number_input;
3 trans_num NUMBER := &trans_num_input;
4 remar VARCHAR2(75) := &remark_input;
5 receipt NUMBER := &receipt_input;
[Code] .....
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00201: identifier 'HELLO' must be declared
ORA-06550: line 4, column 8:
PL/SQL: Item ignored
ORA-06550: line 10, column 61:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
I am facing the below mentioned error with .net framwork and oracle.
PL/SQL: ORA-02185: a token other than WORK follows COMMIT
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like
Chng
OReilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character is changing as the names are changing as Chng OReilly. We are loading the data from the text file into our tables and the names are loading as
Chng OReilly and i tried to replace the special character with while loading but it is loading as .If I try to change the character with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
Version Oracle 9i
I have a view in schemaA called viewA for example. The view is quite complex, with multiple joins.I've granted ALL to schemaB to the view, and to all the referenced tables within the view.However, when I run execute the view from schemaB, I get a ORA-00942 table or view does not exist.
SQL> select * from schemaA.viewA;
select * from schemaA.viewA
*
ERROR at line 1: ORA-00942: table or view does not exist
I suspect that Oracle is creating to temporary tables/views internally (or using something that is already there), and that I need to grant access to these temporary objects.
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like '
Ch'ng
O'Reilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character ' is changing as ¿the names are changing as Chang Reilly.
We are loading the data from the text file into our tables and the names are loading as Chang O¿Reilly and i tried to replace the special character ¿ with ' while loading but it is loading as ¿.
If I try to change the character ¿ with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
I created the following procedure in my local database.When I executed the procedure SEND_MAIL, recipient is not receiving mail.Do I need any setup in my database?
here is my procedure.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle11.2';
mailhost VARCHAR2(30) := '10.137.133.18'; -- local database host
[code]....
Receiving ORA-29913 and ORA-30653 errors and query does not complete. The queries that receive the error are shown below <> represents substitute your filename; There are actually three different queries that must run to produce an expected output. Those queries run in the order which they appear below:
Query1
select distinct HLQ "highlevel", RESOURCE "RES" from <filename1>
Query2
select distinct ENT.RESOURCE "RES", ENT.ID "PLEX"'
case
when ENT.ACTION is null then nextkey'
else ent.action
end "ACT",
case
when ENT.ACTION is null then ENT.CONDITION_NEXTKEY
end "NRULE",
'TESTSET' "ENTTYPE", replace(My.Owner, '"',' ') "Owner"
from <filename1> ENT, <filename2> ORS
where ENT.f1 = ORS.f2
and ENT.APPLICATION = '<entertypehere>'
Query3
select ENT.ID "AID", ENT.RESOURCE "RES", ENT.ID "PLEX", replace(My.Owner, '"',' ') "Owner", TESTSET' "ENTTYPE" from <filename1> ENT, <filename2> ORS
where APPLICATION = '<entertype>'
and ENT.<f1_column_name> = ORS.<f2_column_name>
Error returned is: "Ora-29913: error in executing ODICEXTTABLEFETCH callout; Ora-30653: reject limit reached"
In the log file of the job that runs these queries, I can also see the following
NULL if (CAS9 = ~) error processing column C5 in row 1 for datafile /home/oracle/DataDir/<filename>_metadata.data
I have checked and it looks like the routine creates ODICEXTTABLEFETCH as it needs it and then throws it away leaving me with Ora-12899 Value too large for column C5 (actual: 13, maximum: 10)
sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..
View 6 Replies View Related
