The below table and functions are examples, but issue is like this.
i have one issue while forming below sql , Application will form this query at run time wih required inputs it is failing because the application will not support the clob datatype.
Table : T_E (similar to EMP table structure)
This table has data similar to emp but bulk data around 10k records.
Query formed
select empno,ename, get_employees(deptno) from t_e;
This sql query this is failing when function return varchar2 string more than 4000 size.Because in sql query size should not exceed 4000 for varchar2 data type , function return size can be upto 20000
Function which CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE) RETURN varchar2 IS l_text varchar2(32767) := NULL; BEGIN FOR cur_rec IN (SELECT ename FROM t_e WHERE deptno = p_deptno) LOOP l_text := l_text || ',' || cur_rec.ename; END LOOP; RETURN LTRIM(l_text, ','); END;
I can try this with clob but application doesn't support that..so i tried with using clob in above function , after that while returning to application i want to conver the clob to char and return the entire string
select empno,ename, to_char(substring(get_employees(deptno),1,4000)) -- first 4000 characters , to_char(substring(get_employees(deptno),4000,8000)) -- next 4000 characters from t_e;
But this is failing how to return the entire string in above sql
Need a trigger in view with select statement that means
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE; CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1 INSTEAD OF DELETE ON TEST_VIEW DECLARE BEGIN Dbms_Output.Put_Line('STATEMENT TRIGGER.'); END;
i wanted to use select statement instead of delete.How can i get that
Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return: ------------------------------ create function abc return xyz%rowtype is rec xyz%rowtype; begin select * into rec from xyz where col1 = n; return rec; end; -------------------------------- How could I use this in a select clause, as there is a multi column return by the function ?
I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in
[URL]
This has a query which will run in SQLPLUS but fails with PRO*C
:~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX
I am using embedded sql in a Fortran program on IBM AIX. Whenever I execute a SQL statement other than connect I get a coredump. So I wrote a small test program, but same thing happens. This is the test program -
PROGRAM TEST EXEC BEGIN DECLARE SECTION CHARACTER*20 UDPWD EXEC END DECLARE SECTION [code]....
And then when I run test, I see the 'Connected' print & then coredump. Same thing happens for EXEC SQL Prepare statement. Am I not linking some library ?
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query: select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL: create or replace view my_view as select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id pt.party_id as id_klienta_mdm, pt.master_reference_no as id_klienta_ref_mdm,
I am having a issue, we are trying to restore our db from control files using Symantec Backup exec, but job fails giving me error go to table spaces select all table spaces and unchecked roll forward.
i am unable to find way to uncheck the table spaces. Here is the complete description.
Cause
The system catalog tablespace "syscatspace" contains all of the system catalog information for the tables. Syscatspace needs to be rolled forward first, and then the other tablespaces must be rolled forward.
Solution
Workaround:
When the restore operation is run from a tablespace level backup, perform the rollforward for the syscatspace tablespace first and then for other tablespaces.
Execute the rollforward operation as a DBA operation. The sequence of the recovery process should be as follows:
1. Select all tablespaces, uncheck the rollforward option and run the restore job. 2. Run the rollforward operation as a DBA initiated operation:
rollforward syscatspace to point in time / end of logs. e.g. db2 => rollforward database UEXIT to end of logs and complete Tablespace (SYSCATSPACE) online
3. Perform rollforward of all other tablespaces.
e.g. db2 => rollforward database UEXIT to end of logs and complete tablespace (SYSTOO LSPACE,USERSPACE1) online
Refer to IBM DB2 documentation for more information on DBA-initiated operations.
display or get values stored in a long column.i tried the below code, but myvar is shown as null. am i missing something here or is there a better apporoach than this ?. There are actually 16 rows returned for this query but with empty values for 'high_value' column which is critical for me. How can i do this long to varchar convertion ?
SET SERVEROUTPUT ON DECLARE my_var long; BEGIN for x in ( SELECT high_value FROM all_tab_partitions WHERE table_name = 'SALES_DISCOUNT' AND table_owner = 'CED12' ORDER BY partition_position DESC ) [code]....
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score John ==== 200* Zohaib ==== 299 Ali ==== 0* John ==== 200 Maria ==== 150* Ali ==== 0 Maria ==== absent John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score Zohaib ==== 299 John ==== 200* Maria ==== 150* Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
I have a varchar field in a table. When I store the following string, some undefined character is storing.
String to be stored: Test String String actually stored: Test ¿ string
Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.
I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS
I see you can build an address object consisting of street name and number, city and so on, and use this in CREATE TABLE, so all tables use the same fields for addresses. And moreover you can add check functions to prevent street numbers to be negative for instance in the constructor. You can even use inheritance. I like that.
However, all teaching on this subject I found on the Internet starts with structs (points consisting of x and y, persons consisting of name and address, etc.). Then they go to inheritance and show how to build an employee who is just a person plus an employee number. They never start with simple types.
What I would like to do would be rather to start with basic types such as number or varchar2 and inherit from them. An EAN is a VARCHAR2 holding only digits where the last digit is a check sum that must be matched. An item number may be a VARCHAR2 matching 'NNN-AAAA'. A simple string may be a VARCHAR2 where no functional characters such as carriage return, etc. occur.
I would then write my create table statement thus:
CREATE TABLE item (itemno T_ITEMNO, ean T_EAN, itemname T_SIMPLESTRING); and inserts like INSERT INTO item values('123--BCD', '1234567890abc', 'toy') would fail with exceptions like 'item numbers must be formatted NNN-AAAA' or '1234567890abc is not a valid EAN'.
However this seems not possible:
create or replace type T_EAN under varchar2 ( constructor function T_EAN(p_string in varchar2) return self as result );
fails with "PLS-00580: supertype must be an object type".
Rather than creating completely new types, I would start with extending existing types, but this seems not possible. Is that really impossible or am I using the wrong approach?
How to get max and min value from a varchar2 datatype column?
CREATE TABLE TEST ( WEIGHT VARCHAR2(20) ); INSERT INTO TEST VALUES('100'); INSERT INTO TEST VALUES('120'); INSERT INTO TEST VALUES('113'); INSERT INTO TEST VALUES('145'); INSERT INTO TEST VALUES('204'); INSERT INTO TEST VALUES('130');
I've to find the max and min weight from this data.
select to_clob(1) from dual union select wm_concat(sysdate) from dual;
wm_concat returns a clob. To make both queries in the union have the same type columns i convert the column in the first query to a clob but Oracle gives me an [1]: ORA-00932: inconsistent datatypes: expected - got CLOB error even though both are returning a clob value.
Each of the queries work individually and both return a clob value.
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
