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
ADVERTISEMENT
Jun 30, 2011
correct the code where i made mistake.
while select RA_CUSTOMERS orderby customer_id
innerJoin AR_PAYMENT_SCHEDULES_ALL where RA_CUSTOMERS.customer_id = AR_PAYMENT_SCHEDULES_ALL.customer_id
AND AR_PAYMENT_SCHEDULES_ALL.DUE_DATE > 90
(select CUSTOMER_ID FROM AR_CASH_RECEIPTS_ALL where AR_CASH_RECEIPTS_ALL.customer_id = RA_CUSTOMERS.customer_id);
View 3 Replies
View Related
Jul 30, 2012
I'm teaching myself to write stored procedures, working in TOAD 10.5 with Oracle 10g. I keep getting error ORA-00900: invalid SQL Statement. Here's the code, cut down to bare minimum sample size. I can't see where I'm doing anything wrong.
If I cut out the cursor (taking it down to just "Begin" and "End"), it does run, but I can't see anything wrong with the cursor.
CREATE OR REPLACE PROCEDURE IN_PROCESS_CASES_BOS
IS
V_HELLO VARCHAR2(10);
CURSOR C_MAIN IS
SELECT 'HELLO' FROM DUAL;
[code]....
View 6 Replies
View Related
Jan 3, 2011
CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)
View 3 Replies
View Related
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
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
Feb 4, 2012
Here is the code I am using:
CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
[code]....
The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.
Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.
View 3 Replies
View Related
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
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
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
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
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
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
Aug 20, 2008
We are getting an error in our web application that is using Oracle.DataAccess.dll v2.111.6.20. When a couple users are using the site everything is fine, but when the load goes up we start getting the error ORA-24373: invalid length specified for statement. We are unable to duplicate this error in Visual Studio and don't know where to turn. We use stored procedure and the .dll to access the database for everything. Also, when this error occurs, it occurs indefenitely for all OracleCommand objects until the web server is rebooted. Also, when I attempt to remote debug with SQL Developer, the process doesn't even make it to the database!
View 3 Replies
View Related
Apr 15, 2009
I am having trouble getting the following update statement to work.
update Team
set Names.Team_Name = "Not Yet Assigned"
WHERE
Team.ROE_PROCESS = 'G' AND
[code]...
The error message I am receiving is:
ORA-00904: "Department"."UI_WKLY_EARNS": invalid identifier
View 4 Replies
View Related
Mar 18, 2011
Where I run this update query, I get the error:
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
UPDATE XXX_CURR_EOM SET ID =
(select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM
where
XXX_CURR_EOM.ID = CAPITALPLAN_id)
don't know what it means.
View 10 Replies
View Related
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
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
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
May 11, 2010
I am trying to import a schema backup using sqldeveloper and getting this syntax error:
ORA-00900: invalid SQL statement
Here is the import statement:
impdp sys/*****@ccpsp schemas=ccpsp_staging directory=dmpdir DUMPFILE=CCPSP_USERCCPSP_STAGINGSCHEMABACKUP_07MAY2010.dmp LOGFILE=IMPDP_CCPSPStagingSchemaImport.log;
View 3 Replies
View Related
Apr 4, 2013
I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
View 4 Replies
View Related
Nov 8, 2011
We use this software Toad for Oracle here and I have just started learning PL SQL.I am coming across a few errors in Toad for Oracle
First of all,
When I execute snippet (Ctrl + Enter), I get this ORA-00900 error and the message that I need to upgrade my version of knowledge xpert. I did that to 10.1. I then restarted my computer and now it is still showing me that error ORA-00900 but without any message. I do not get output, even for simple programs but when I try to execute snippet, it asks for my 'Edit SQL' screen and after which it gives me a few outputs.
View 1 Replies
View Related
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
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
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
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
Apr 17, 2013
I am trying to use execute immediate in a trigger. See the below example
create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;
create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......
insert into test_tbl values(3,'test',sysdate);
I got the Error : -1008->ORA-01008: not all variables bound
In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.
View 2 Replies
View Related
Aug 18, 2010
Need to execute @/sql file after checking an output of below sql.
SELECT COUNT(*) FROM all_directories WHERE DIRECTORY_NAME='RMANDUMP';
If directory exist then oonly we require to run sql file.
If there a way we can execute sql files depending on sql output.
View 4 Replies
View Related
Feb 23, 2011
I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.
execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....
What's the correct way to format this kind of dynamic query?
View 4 Replies
View Related
