SQL & PL/SQL :: Check Datatype Of Result From Select Query?
Jul 7, 2011
I have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:
create or replace procedure myproc is
a varchar2(50); begin a := 'select * from mytable'; execute immediate a; end;
there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?
Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?
Something like this
cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked; row c%rowtype; begin open c;
[Code].....
Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?
SELECT SUM(salary) FROM employees WHERE department_id =60 O/P is : 28800But when i use the above query in anonymous block it gives me 684400 DECLARE v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME ------------------------------------------------ STATUS ------------------------------------ BUP OPEN
Another database has following characterset settings:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME STATUS ---------------- ------------------------------------ HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
I would need to convert the column datatype from BLOB to CLOB. currently in the table, the BLOB column has the data. the requirement is to convert this column from BLOB to CLOB datatype.
How to convert from BLOB datatype to CLOB datatype ?
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; -- drop table test1; CREATE TABLE TEST1(K1 NVARCHAR2(80)); INSERT INTO TEST1 VALUES ('gsdk');
When i change datatype in column to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT SQL_ID d3d64aupz4bb5, child number 2 select * from TEST1 where k1 like N'Ł%' Plan hash value: 4122059633 Id Operation Name Rows Bytes Cost (%CPU) Time 0 SELECT STATEMENT 2 (100) * 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; -- SELECT * FROM NLS_SESSION_PARAMETERS; -- drop table test1; CREATE TABLE TEST1(K1 NVARCHAR2(80));
[code]....
When i change datatype to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID d3d64aupz4bb5, child number 2 ------------------------------------- select * from TEST1 where k1 like N'Ł%'
[code]....
Note - dynamic sampling used for this statement (level=2)
Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.
