SQL & PL/SQL :: Obtain Result Set When Doing Select
May 13, 2010
when in sqlplus I execute:
select * from mytable;
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 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 ?
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.
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
I need to obtain the top 2 departments in terms of total salary.
I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.
DNo Fname Salary 2 Tom 10000 3 Mike 20000 2 Harry 30000
DeptNo DeptName 1 Administration 2 Special 3 Finance
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD( iQIFlag NUMBER(1), iIPFlag NUMBER(1), iRiskIndicator NUMBER(1), iDenominator NUMBER(8), iNumerator NUMBER(8) [code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE f2_data qi_test.typ_qi_data_tab; BEGIN EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;' USING OUT f2_data; [code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
I need to configure rman backup via oracle enterprise manager.Database is not in archive mode, rman shutdown the database and made a "startup mount" for backup.
I set up a backup on disk and the test is successful When I plan the backup job, database shutdown but the backup is not working and after database is not started.
here is the log of the backup job :
shutdown immediate; SP2-0640: Not connected -> ->Connected Database closed Database dismounted. ORACLE Instance shut down. startup mount
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.
And I need to get list of channels by contract which are an assigned to customers.
The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.
select o.channel_name from the_table_2 o minus select o.channel_name from the_table_1 a, the_table_2 o where a.contract_key=237092201 and a.offer_channel_key=o.OFFER_CHANNEL_KEY
And the select under (after minus) gives a list of non assigned channels by contract_key:
select o.channel_name from the_table_1 a, the_table_2 o where a.contract_key=237092201 and a.offer_channel_key=o.OFFER_CHANNEL_KEY
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
BANNER -------------------------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for 64-bit Windows: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production [code]....
Middle EastThe table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?
I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record ( name varchar2(20), sal number(6) [code].....
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 the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
