Client Tools :: Error / ORA-00900 / Invalid SQL Statement After Executing Procedure

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


ADVERTISEMENT

PL/SQL :: 00900 Invalid SQL Statement Error?

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

Client Tools :: TOAD Error ORA-00900 Procedural Software Installation?

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

SQL & PL/SQL :: ORA-00900 / Invalid SQL Statement

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

Client Tools :: Executing SQL Statement For UNIX Script

Jun 10, 2010

I am executing the SQL statement in a unix korn shell script. and the output of this SQL is stored in a file.

`sqlplus -S $OraUserID@$OraInstance/$OraUserPW <<endofsql >result
set heading off;
SELECT * from emp;
exit;
endofsql`
cat result

this code working fine and the output i see in file is as below.

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 9 23:09:33 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release <number> -
and Real Application Testing options

SQL> SQL>
30-APR-10
01-MAY-10
02-MAY-10
03-MAY-10
04-MAY-10
05-MAY-10
06-MAY-10
07-MAY-10

8 rows selected.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release and Real Application Testing options

How can to get only the SQL output in the file but not all the other oracle connecting messages.

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

Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error

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

Client Tools :: Toad Error / Can't Continue Running Procedure

Jun 4, 2012

I run procedure on toad 10.5, it gave me this error

Buffer overflow, limit of 20000 bytes

then it can not continue running the procedure.

View 8 Replies View Related

Client Tools :: Closed Connection Error After Procedure Debug In SQL Developer

Feb 25, 2011

Oracle Database: Oracle 11g Version: 11.1.0.6.0
Tool: Oracle SQL developer. Version : 1.1.3

Problem:
After completing debug of procedure from SQL Developer, whenever I try to compile that procedure or execute any query from the SQL worksheet, I get the closed connection error message. However I can debug that procedure for any number of times. Any other activity for that database connection gives closed connection error.

View 2 Replies View Related

Server Utilities :: Privileges Error - Executing INSERT Statement For Table

Jul 9, 2013

I am using OWB to load a table which write sql loader command. When running the load i am getting below error.

SQL*Loader-643: error executing INSERT statement for table "STG_EWORK"."STG_ISF_LUCC"

I am unable to guess which privileges is missing.

My control file as below

OPTIONS (SKIP=2,BINDSIZE=50000,ERRORS=0,ROWS=200,READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE '\devora003.dev.tfl.localPDWPDW_SourceISF_LUCC_Loadfile.csv'
CONCATENATE 1
INTO TABLE "STG_EWORK"."STG_ISF_LUCC"

[code]....

View 4 Replies View Related

SQL & PL/SQL :: ORA-06550 / Error Executing Stored Procedure

Nov 18, 2010

Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is

begin
dbms_output.put_line(a ||'->'||b);
end;

Anyonymous Block:

begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...

View 5 Replies View Related

Client Tools :: Substr Invalid Number Of Parameters

Dec 27, 2012

I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:

substr(PBBDT,length(PBBDT)-1)

Calling <substr> with <2> parameters, but <3> are expected.

View 2 Replies View Related

Client Tools :: Update Failed ORA-01722 - Invalid Number

May 18, 2011

Enviroment is Embarcadero RAD Studio XE. Work with DataSnap WebBroker Application. Its Server methods ancestor is TDSServerModule.

I've got SQLConnection (dbx), SQLDataSet, DataSetProvider on server side and SQLConnection, DSProviderConnection, ClientDataSet plus DataSource, DBGrid, DBNavigator on client side.
SQLConnection on server side uses Oracle driver - dbxora.dll. DB: Oracle 11g.
SQLConnection on client side uses Datasnap driver.
SQLDataSet has DbxCommandType set to Dbx.SQL, CommandText: "Select * from Table1".
All fields except indexed one have their pfInWhere set to false.
updateMode of DataSetProvaider is set to upWhereKeyOnly.

All is well up to point where ApplayUpdates is fired. It does nothing. HandleReconcileError shows ORA-01722: invalid number.

Oracle explains: ORA-01722:invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. But all updated fields are of character strings type. There is no need for conversion. I suppose the automatically created SQL has some extra checking. But I cannot see those SQLs. I guess must be a way of controlling those SQLs thru params, but don't know which. May be comparison old value - new value of index column is depending on this conversion?

View 14 Replies View Related

Client Tools :: ORA-31180 / Type Mismatch In Invalid PL/SQL DOM Handle

Jul 23, 2010

ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle

I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.

View 10 Replies View Related

SQL & PL/SQL :: Invalid Identifier Error In Select Statement?

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

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

Server Utilities :: Schema Impdb Invalid Statement Error

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

Client Tools :: Loop Select Statement

Mar 10, 2010

I have this following select statement

Select name
from names_list
where name like ('A%')

The output returns a list of names beginning with 'A'...What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show

Andrews
Andrews
Andrews
Apple
Apple
Apple

View 8 Replies View Related

SQL & PL/SQL :: Invalid Cursor Error In Procedure

Feb 2, 2011

I have this stored procedure as part of package.

PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)

When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor

Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...

There is no cursor used in the procedure. It just inserts records in the table for given input values.

View 10 Replies View Related

Client Tools :: Spool Without SQL Statement And Commands In PLSQL?

Jan 3, 2011

I have a PLSQL script.

sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0

[code]....

My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc

[code]....

I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc

without the SQL prompt and the statement, and the spool off command?

View 2 Replies View Related

Client Tools :: Toad Explain Plan On Current Statement

Sep 7, 2011

1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"

2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?

View 1 Replies View Related

Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement

Mar 7, 2011

I would like to store my sql query output into text file.Like for example:

select name from emp where emp_id=101;
Here output should be in text file as
swapna.

I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.

View 1 Replies View Related

Client Tools :: Providing Bind Variables As Values In Insert Statement?

Aug 23, 2011

I executed the following PL/SQL block in SqlDeveloper :

VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/

And it gave the error : Quote:Error report:

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"

The same code when executed in iSqlPlus gave no error.

View 13 Replies View Related

Client Tools :: How To Execute A Procedure Within Editor

Jan 15, 2010

something like

exec package name.procedure ?

View 3 Replies View Related

Client Tools :: Creating Procedure Using SQL Developer?

Jan 23, 2012

I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.

I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:

Error(8,1):PLS-00103:Encountered the symbol "/"

The code is as follows:
--------------------------------------
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS
PROCEDURE sampleproc_4(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC_4;

[code].....

View 9 Replies View Related

Client Tools :: How To Execute Stored Procedure With Cursor Within PL/SQL

Apr 5, 2011

To keep things simple: lets say that this is my procedure:

create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;

how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...

my goal is in the end to execute this from Magic 8 (to those who herd of it). p.s this needs to work in oracle 8

View 4 Replies View Related

Client Tools :: Execute A Procedure With Parameter Of Object Type?

Mar 29, 2010

I am trying to execute the procedure from toad, where the type is like an object.How can I do that?

CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;
CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);

In the following procedure I am calling list_t, but how would i pass the values to it?

PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)
DECLARE
p_List TABLE;

[code]...

View 1 Replies View Related

Client Tools :: Remove / Prevent PL/SQL Procedure Successfully Completed Message

Aug 20, 2010

I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.

-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...

PL/SQL procedure successfully completed.

View 2 Replies View Related

Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure

Oct 26, 2010

i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.

even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:

procedure is:

create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;

i pass parameter as given below:

begin
testproc('abc & def');
end;

if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.

begin
testproc('abc &'||'def');
end;

View 9 Replies View Related

Client Tools :: Error 6 Initializing SQL*Plus

Sep 26, 2013

I have created oracle 11 XE..Once it is created it was running fine.I have worked on that 2 days.

Problem is occured when I restart my machine.I m getting below error:

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

I have checked the forum in which it is mentioned that ORACLE_HOME is not properly set.I tried that one alot of time.Even now environment variable shows proper value.

View 9 Replies View Related







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