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


ADVERTISEMENT

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

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

PL/SQL :: Missing Expression Error during EXECUTE IMMEDIATE?

Jun 6, 2012

CREATE TABLE  "T_LIDER"
   (     "LIDER" NUMBER(5,0),
     "TRR" NUMBER(8,0),
                "SUG_I"  NUMBER(2,0),
      "LIDER_TAY" NUMBER(2),
   
[code]...

i've tried to simplify the problem and i saw that the function returns this error what seems to be the problem ?

View 11 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Statement In Database Package - Missing Expression

Feb 29, 2012

I have the following statement dynamic sql statement in a database package.

begin
EXECUTE IMMEDIATE 'SELECT REPLACE(SUBSTR('||V_WHERE_CLAUSE||',1,3, ''AND'', NULL) INTO V_WHERE_FILTER FROM DUAL';
EXCEPTION
NULL;
end;

When executed it gives following error.

ORA-20000: ORA-20000: ORA-00936: missing expression
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 931
### END TRACE ###
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 935
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 164
### END TRACE ###
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 184
ORA-06512: at line 9

View 4 Replies View Related

PL/SQL :: Regular Expression Select

Jun 6, 2012

Sample Data:

SELECT 'A/BCD/CCE/DFFFF' test  from dual
Expected Output:

SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;  

View 6 Replies View Related

Reports & Discoverer :: ORDER BY Item Must Be Number Of A SELECT-list Expression

Sep 18, 2009

When i'm sorting on a calculated field i'm getting the following error:

ORDER BY item must be the number of a SELECT-list expression

View 3 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

Forms :: Windows Won't Allow To Execute SQL Statement

Oct 25, 2011

reconciliation logic using Oracle

Version

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Current written logic for reconciliation:

1. Load data from source_a in a staging table using date filter

2. Load data from a file (Source_B) in temp table

3. Algo for reconciliation:

fetch value from source_B and if an entry exists in source_a then match say 10 columns if they match update reconciliation_oke = TRUE

there is an and written for testing all the 10 columns There is report generated out this which shows non matched columns and the entries which are missing....Now the requirement is to modify the logic in a way which shows which all columns are mismatched on the report in case present.

Since there are around 10 thousand records which would be reconciled on a daily basis, performance also needs to be taken care of...I guess i would be required to use PL/SQl tables...

View 3 Replies View Related

PL/SQL :: ORA-00900 Invalid SQL Statement When Doing Execute Immediate Using

Jun 21, 2012

I'm writing simple code db is 9.2.0.8

create or replace procedure gather_part_stats(p_schema in varchar2, p_table in varchar2)
as
l_quarter varchar2(8) := to_char(sysdate, '"Y"YYYY"_Q"q') ;
l_sql varchar2(2000) := 'exec dbms_stats.gather_table_stats(:schema, :table, method_opt => ''for all columns size 1'' , cascade => true , estimate_percent => 1 , granularity => ''PARTITION'', partname=>:part);';
[code].......

I'm using using to avoid sql injection,

Error is:
SQL> exec gather_part_stats('TEST' , 'TEST');
l_quarter: Y2012_Q2
exec dbms_stats.gather_table_stats(:schema, :table, method_opt => 'for all columns size 1' , cascade => true , estimate_percent => 1 , granularity => 'PARTITION', partname=>:part);
BEGIN gather_part_stats('TEST' , 'TEST'); END;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "INSTALL.GATHER_PART_STATS", line 8
ORA-06512: at line 1

View 12 Replies View Related

PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?

Dec 14, 2012

Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.

DECLARE
TEMP   VARCHAR2(20);
BEGIN
TEMP     := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;

This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.

View 10 Replies View Related

SQL & PL/SQL :: How To Execute Sql Statement With Table_name As A Variable

Apr 20, 2012

I have statement like SELECT * FROM DIVISIONS;

i have to run this statement for different table, and I would like to pass the table name as variable

ex:
create or replace precedure dynamic_execute
v_tbl varchar2(30);

begin
---- assign table name here
v_tbl := DIVISIONS;

EXECUTE IMMEDIATE('SELECT * FROM '||v_tbl);
end;
/

When I executed the procedure I got error, how to pass the table name as variable and execute the statement successfully.

View 5 Replies View Related

SQL & PL/SQL :: How To Execute The Statement Exist In CLOB Column

Jun 30, 2010

I have oracle table with a column of datatype CLOB. In the clob column having the manipulation statements (update, insert) as records.

How to execute the statements exists in a clob column as records?

my table structure and one record is

CREATE TABLE ACTN_CFG_T
(
OBJ_ID NUMBER(32) NOT NULL,
ACTN_ID NUMBER(38) NOT NULL,
ACTN_SEQ NUMBER(2) NOT NULL,
RU_DEF CLOB NOT NULL

[code]....

I want to execute the update statement when i call the obj_id. I used the follwing procedure but error.

1 DECLARE
2 V_ABC CLOB;
3 BEGIN
4 SELECT RU_DEF INTO V_ABC FROM ACTN_CFG_T WHERE OBJ_ID = 1625;
5 EXECUTE IMMEDIATE V_ABC;
6 END;

error is...

ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored

View 10 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

SQL & PL/SQL :: Oracle Error For Invalid Identifier In Execute Immediate Statement

Apr 5, 2011

I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.

I call the procedure as

exec exec_imm( exec_imm_t(1,'asd','1/2-34'));

ERROR:
Error starting at line 9 in command:
exec exec_imm( exec_imm_t(1,'asd','1/2-34'))
Error report:
ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier
ORA-06512: at "PTK_ADM.EXEC_IMM", line 26
ORA-06512: at line 1
00904. 00000 - "%s: invalid identifier"

NOTE:
When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.

CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t)
AS
v_type_str CLOB := NULL;
v_type_data CLOB := NULL;
v_type_name VARCHAR2(25) := NULL;
BEGIN
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

Oct 25, 2010

I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.

UPDATE transaction SET loannum = lpad(loannum,12,'0')

View 15 Replies View Related

PL/SQL :: DML Query Execution - Length Exceeds 4000 Characters With Execute Immediate Statement

Jun 18, 2013

I want to execute a DML query with execute immediate statement. That DML query length exceeds 4000 characters. This query has Xquery related conditions, i can not split the query. when i tried execute it is giving "string literal too long".  I tried with DBMS_SQL.Parse() and DBMS_SQL.Execute also, but it is giving same error. I have to execute this DML query inside a Procedure. We are using oracle 10g version

View 13 Replies View Related

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 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

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 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

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Select Statement Errors

May 5, 2010

I have a question about select statements, as I am new to them and don't know how to work all the commands yet.

I'm making a select statement that is about half right... it is shown below:

select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);

I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.

View 2 Replies View Related

Format Of Select Statement

Jun 15, 2007

decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,

After executing this statement. I get negative values if cancel time is null

I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time

View 1 Replies View Related

How To Set Timing On For Every SELECT Statement

Aug 23, 2008

how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.

View 6 Replies View Related

SQL & PL/SQL :: Using Alias In Same SELECT Statement

Sep 1, 2011

I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).

Ex:

Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;

Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?

View 12 Replies View Related







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