SQL & PL/SQL :: Block To Select 2 Rows From Table And Execute Them

Aug 3, 2010

I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC

The contents of rows in Table above are:

select * from emp;
select * from dept;

Can I write a PLSQL block that can select these 2 rows from the table and execute them?

View 9 Replies


ADVERTISEMENT

How To Select 10 Rows From Mission Table

Sep 2, 2008

i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work

select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;

this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records

View 10 Replies View Related

SQL & PL/SQL :: Select Updated Rows From One Table

Jul 13, 2013

There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.

TABLE A
COL_A1COL_A2

AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN

TABLE B
COL_B1COL_B2

AAAMOBILE
BBBUNKNOWN
CCCDESKTOP

The select statement should return following rows from TABLE A

COL_A1COL_A2
BBBTABLET
DDDUNKNOWN

View 2 Replies View Related

SQL & PL/SQL :: Select Rows From Table With Same Particular Field?

Aug 20, 2012

select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.

View 7 Replies View Related

Select Query With Four Table Its Generating Around 650 Rows?

Feb 20, 2013

i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.

for example

update ps_po_lining b
set y.recv_req = 'N'
where recv_req in

[Code]....

this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.

View 5 Replies View Related

Table Has No Rows - Select Taking Time

Aug 23, 2010

We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.

The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select

select count(*)
from table

It's taking approx 2 mins to come out with the result.

We checked and found that there are no locks on the table currently.

what do we need to do to get the performance back

View 5 Replies View Related

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

May 28, 2012

I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),

[code]...

But the case is I need to display the value in table format Horizontally as below

EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING

[code]...

View 14 Replies View Related

Block Of Procedure Taking Time To Execute

Feb 9, 2011

I have connected to the database from two sessions

Box 1 : i have executed a procedure.it took few seconds to get executed

Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.

why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.

View 2 Replies View Related

Execute Immediate Command For Multiple Rows?

Mar 25, 2007

I have to build a select query but its where conditions will be retrieved from a table. I was told that the execute immediate command can handle it.

lets say i have this:

string_var:= 'select field1, field2, field3
from mytable
where' ' || i.condition_selection || ';'

If the above select resuls in a single row, i could do this:

EXECUTE IMMEDIATE string_var INTO var_field1, var_field2, var_field3;

In my case the select will return multiple rows. How do I proceed ?

View 4 Replies View Related

PL/SQL :: Execute Immediate On SELECT Statement

May 3, 2013

Execute Immediate on SELECT Statement

declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;

it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.

View 14 Replies View Related

SQL & PL/SQL :: Execute The Expression In Select Statement

Feb 12, 2013

CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;

CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;

I want to be execute the expression should run in select statement how to do? and tried as like below,it's not working.

select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;

View 15 Replies View Related

Execute AUDIT SELECT ON EMPLOYES From Procedure?

Dec 16, 2010

how can i execute "AUDIT SELECT ON EMPLOYES" from a procedure?

Ex:

create or replace procedure myfunc()
begin
AUDIT SELECT ON EMPLOYES;
end;

The above procedure will cause an error because AUDIT is no a DML statement.But... I still want to do that bebause i wanna call that function from PHP Application.With PHP, i can't execute AUDIT SELECT ON EMPLOYES.

View 3 Replies View Related

Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 2 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

TimesTen In-Memory :: Execute Statement SELECT CURRENT_DATE FROM DUAL On 11.2.2?

Sep 24, 2013

We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2  . It throws error unknown referenced column error. Command> select current_date from dual; 2211:

Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support. 

TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function

Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.

View 4 Replies View Related

SQL & PL/SQL :: Filtering Rows Out Of Block

Aug 11, 2010

I need to filter rows out of a plsql block but its not working correctly. the logic is to filter on 2 conditions.

DEPT_ID BALANCES
------------------------------
10 1000
15 2000
30 4000
50 3000
10 2000
30 600

I do not want to show deptid 10 and balance of 1000 and do not want to show dept 30 and balance of 4000

AND (
dept_id <> 10 AND balances <> 1000
OR
dept_id <> 30 AND balances <> 4000
)

but i am still getting the rows..

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Block Vs SQL SELECT?

Oct 19, 2011

I have a question regarding the optimal way to code a dynamic SELECT INTO statement. Below are the 2 posiibilities I know of:

1. Dynamically executing the SELECT statement and making use of the INTO clause of the EXECUTE IMMEDIATE
statement
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);

[code]...

2. Encapsulating the SELECT INTO statement in a block and dynamically exectuting the block

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);

[code]...

which way would be preferred? I know the second method uses a bind variable for the INTO clause, but does the first one also use bind varialbes (no semi-colon)? Any differences in terms of efficiency or speed?

View 4 Replies View Related

Block Can Contain Rows / Data From Multiple Tables?

Jul 1, 2013

A block shouldn't have rows from multiple tables... Is that true? I read in one of the OTN thread (i don't exactly remember the thread name) that a block can have data from multiple tables. If it doesn't have, what's the table directory in block signifies?

View 9 Replies View Related

SQL & PL/SQL :: Select On Dba_pending_transactions Inside Block?

May 14, 2011

While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.

But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.

SQL> declare
2 v_count number(2);
3 begin
4 execute immediate 'select count(*) from dba_ending_transactions' into v_count;
5 dbms_output.put_line(v_count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4

Same error I get when i use it inside a procedure.

View 2 Replies View Related

Forms :: Handle Huge Number Of Rows In Data Block

Jun 14, 2011

I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.

View 4 Replies View Related

SQL & PL/SQL :: Tracking Exception In One Single Block For Three Different Select Statement?

Mar 8, 2010

how can i track the exception for three select statement in one pl-sql block. here is synario.......

begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;

i want to track exception any one for ex no_data_found for all these three different statement.

I know if i put this three statement in three different pl-sql sublock then i can trap it....

how can i trap it in one single block?

View 4 Replies View Related

PL/SQL :: Unexpected Result With Select Max (column) In Anonymous Block

Nov 27, 2012

The following query gives me 28,800 as sum(sal)

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

View 6 Replies View Related

Forms :: (Select All) Function In Data Block With All Checkbox Item Types?

Aug 19, 2010

I need to implement a "Select All" function in a Data Block with All the Check boxes. It's a Database data block with 10 records to be displayed at a time. Check Box Items are in default layout. The requirement is when user checks/un-checks that select all check box which is placed on the left hand side of the other check boxes, it should select/deselect all the check box database items in that particular record.

View 7 Replies View Related

PL/SQL :: Select 4 Rows And Then Next 4 Rows?

May 27, 2013

in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?

I have fifty rows inside the table

select * from student where rownum between 0 and 4 order by stuid ->it work

select * from student where rownum between 5 and 9 order by stuid -> did not work

what is the correct way to do it ?

View 10 Replies View Related

SQL & PL/SQL :: Rows Not In Inner Select?

Apr 11, 2013

I am trying to find records in table A that don't have any corresponding records in table B

select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)

Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.

View 2 Replies View Related

How To Select Rows From Two Tables

Aug 30, 2007

Consider the following tables

MANAGERID
101
102
103
104

MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27

write sql query to get the following output without using minus,union,intersect.

MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27

View 3 Replies View Related

SQL & PL/SQL :: How To Select Last 10 Days Rows

Aug 2, 2012

My Table structure is

SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM

Question

How to select last 10 days rows only (from sysdate)

Error

1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)

2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected

[Code]...

View 5 Replies View Related

SQL & PL/SQL :: Select Rows With Max Condition

Aug 13, 2010

I have been trying to find out how to write the following query:

Suppose the following table:

ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2

I would like to retrieve the rows for every different ID with its max value. For one ID that would be:

select * from TABLE where seq in(
select max(seq) from TABLE where id=24)

How can i do this for all the rows??

View 2 Replies View Related

Select All Rows And Order By Timestamp

Dec 27, 2006

I have 3 tables that I want to select all rows from and then order by the timestamp of when the row was inserted.

View 7 Replies View Related

50000 Rows Only From Select - How To Increase

Jun 1, 2012

I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?

SQLSELECT MIN(major_zipcode)
FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved