PL/SQL :: Executing SQLPLUS Command From Stored Procedure

Aug 28, 2012

I am trying to execute the SQLPLUS command (CONNECT) from the stored procedure.It is throwing below error message.

Stored Procedure:

SQL> select user from dual
2 ;
USER
------------------------------
SYS
SQL> create or replace
2 PROCEDURE PROCEDURE1 AS
3 BEGIN
4 sqlplus sys/sys@D as sysdba;
5 --execute immediate 'create user 'kkk' identified by 'kkk';
6 END PROCEDURE1;
7 /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PROCEDURE1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/9 PLS-00103: Encountered the symbol "SYS" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "SYS" to continue.
[code]....

View 29 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Executing Procedure - Command Not Properly Ended

Jan 18, 2012

After creating this procedure...we r not able to execute it...

SQL> ed
Wrote file afiedt.buf
1 create or replace procedure dyn_pro1
2 (tablename varchar2,colname varchar2, prodid number) is
3 cmd varchar2(100);
4 begin
5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid;
6 execute immediate cmd ;--using College_id;
7* end;
SQL> /

Procedure created.

SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200);
BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "APPS.DYN_PRO1", line 6
ORA-06512: at line 1

View 8 Replies View Related

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

What DML / DDL Command A User Is Executing

Sep 9, 2012

is there anyway to know that what dml/ddl command a user is executing and on which table the impact is taking place?

View 2 Replies View Related

Dynamically Executing A Select Command

Aug 4, 2010

How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.

is it possible can i delete or drop multiple tables in a single drop and delete statement.

View 5 Replies View Related

Executing A Stored Proc On Sql Developer

Aug 9, 2011

I'm trying to run an sp to debug the issue. SQL Developer generated this script for me..

DECLARE
P_MMRRRR VARCHAR2(200);
P_DATA1 xxx_package.xxxtype;
P_DATA2 xxx_package.xxxtype;
BEGIN
P_MMRRRR := 04/2011;
[code]........

The package definition for xxxtype is

TYPE xxxtype IS REF CURSOR;

The error I keep getting is

Bind Variable "P_DATA1" is NOT DECLARED
anonymous block completed

View 5 Replies View Related

Sqlplus Copy Command

Feb 15, 2013

i am trying to copy the data from a remote database which has Oracle 8i thru Sqlplus copy command to the local database which has Oracle 11gR2.

copy from abc/abc@remote
insert emp_new
using
select *
from emp_old
where rownum < 1001;

is it possible to copy the remote database thru sqlplus copy command ?

View 6 Replies View Related

Sqlplus And MS Windows Command Line

Feb 18, 2013

I have some Oracle Databases and sometimes for me is easier to access to the databases with SQLPLUS instead SQLDEVELOPER or TOAD.

But i can't reach to a good configuration of command line prompt to do queries against the database, normaly if it returns a lot o rows and columns it is unreadable.

View 5 Replies View Related

Windows :: How To Run Dos Command From Sqlplus Prompt

Feb 19, 2008

how to run dos command from sql prompt.

i.e.
SQL>!DIR

Same can be done in unix using ! character.

SQL>!ls -lrt

View 6 Replies View Related

Client Tools :: How To Define ED Command In Sqlplus

Jul 25, 2012

In my sql*plus when i am writing the command ed .It was showing the error as \

SQL> ed
SP2-0110: Cannot create save file "afiedt.buf"

i tried to define the editor by using the command define_editor=notepad;

But still my problem was not solved.

View 2 Replies View Related

Client Tools :: SQLPlus Command Parser

Jul 31, 2013

I need to parse .sql file to tree and I can't find how to do it.

(We apply sql files by sqlplus and we have some problems with '/': after anonymous blocks - it haven't applied without '/'; Also not-anonymous block applied twice when we have both ';' and '/'. I need to report about problems before file will apply.) I've tried to use ANTLR with it grammar but it build awful tree.

View 5 Replies View Related

Client Tools :: SQLPlus Not Starting From Command Prompt

Jan 21, 2011

I am able to start SQLPLUS from Start | Program files | Oracle - OraHome92 | Application development |

Unfortunately, when trying to start it from the "command prompt" window with the following command SQLPLUS, it generates the infamous error message like

"... unknown etc... "

Any variables i need to change on my client machine ?

View 11 Replies View Related

Client Tools :: Changing DB Password Using SQLPlus Command Line

Jul 21, 2010

I'm trying to change a password. I can connect to the db then at the SQL prompt I'm using:

alter user <name> identified by <new password>

The dos window comes back with '2' - then just sits there

View 4 Replies View Related

Oracle Installation Error On Ubuntu-sqlplus / Command Not Found

Mar 5, 2011

tried installing oracle 10g in ubuntu. i am successful with the oracle installation.i am able to login to sqlplus using oracle OEM,but when i try to login through command prompt , i get the following error:

rakesh@ubuntu:/$ sqlplus "/ as sysdba"
bash: sqlplus: command not found
[color="#000000"][/color]
rakesh@ubuntu:/$ sqlplus
bash: sqlplus: command not found

i have also tried to set the oracle_home, oracle path & oracle SID.

find the below configuration parameters which i have set to.

root@ubuntu:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# more oracle_env.sh
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
#NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

[code]....

View 4 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

Dec 24, 2012

Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4

All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like

create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;

View 6 Replies View Related

Client Tools :: Handling DATETIME Format Using SQLPLUS COPY Command

Jul 29, 2012

I am using the SQL*PLUS COPY command to move the data from my database to another remote database. The data in my database also contains DATETIME format. But since COPY command cannot handle DATETIME format, I am wondering is there any workaround for this.

Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.

View 12 Replies View Related

Client Tools :: Create Shortcut On Desktop To Have SQLPLUS (command Line)

Sep 6, 2010

The way to create a short cut on desktop to have an sql*plus (command line).

i.e. I want to have an short cut on desktop when I click that short cut that would connect me to a specific database with a username and password (which i supply while creating the shortcut-I would provide the database name, username and password)

View 7 Replies View Related

Getting Errors While Executing Procedure

Oct 4, 2010

I am getting errors while executing the following block.

create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
DECLARE
-- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
TYPE c_collection IS TABLE OF c_Rec;
l_coll c_collection := c_collection();
BEGIN
[code]........

error

06530. 00000 - "Reference to uninitialized composite"

View 1 Replies View Related

PL/SQL :: Executing Procedure Using Variables

Feb 6, 2013

I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:

declare

  START_dt  VARCHAR2(30);
  END_DT VARCHAR2(30);
 
  begin
       
    SELECT '01-APR-2011'
    INTO END_DT
    FROM DUAL;
 [code]....

The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.

View 6 Replies View Related

SQL & PL/SQL :: Executing Server Procedure From Oracle

Jul 31, 2013

How to execute Sql Server procedure from Oracle.I have created a dblink and when calling directly to that procedure it is throwing error.

--creationg sqlserver procedure
create proc getRecords
as
print 'testing';
--creating db link
create database link remote connect by ss identified by ss using 'remote';
[code]....

I have found DBMS_HS_PASSTHROUGH package is capable of doing it but not sure how to do.
Nathan

View 11 Replies View Related

PL/SQL :: Executing Procedure Having Refcursor As Out Parameter

May 13, 2013

I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment

CREATE OR REPLACE PACKAGE TEST_PACK
  IS
Type refCursor  is  REF CURSOR;
PROCEDURE TEST_PROC (out_data out refCursor);
END;
[code]...

i am getting the follwoing errror
ORA-00932: inconsistent datatypes: expected - got.

View 3 Replies View Related

Forms :: Executing Procedure Based On Variable Name

Apr 1, 2011

I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.

I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.

View 1 Replies View Related

SQL & PL/SQL :: Executing Dynamic Procedure With Record Type

Jul 26, 2012

I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"

CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...

I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...

View 10 Replies View Related

SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table

Mar 3, 2010

create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';

stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;

-- when am trying to execute this

begin
test;
end;
-- showing ora-01031, insufficient privileges.

View 9 Replies View Related

SQL & PL/SQL :: How To Send Email In HTML Format By Executing Anonymous Procedure

Oct 11, 2010

How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure

View 1 Replies View Related

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

Execute Create Procedure / Function From SQL Script In SQLPlus

Feb 17, 2010

I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.

Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...

I am using the following command:

sqlplus u/p@<someserver> @<path_to_sqlfile>.sql

this sql file contains create procedure pl/sql code

Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?

View 8 Replies View Related

Scheduling Job In Stored Procedure

Dec 21, 2011

PFB code i used to schedule a job as per my requirement. And the procedure is executing fine, but when im about to run it is getting hang.

create or replace procedure scheduler_alert(frq varchar2,intrvl number) is
begin
dbms_scheduler.create_job(
job_name=>'scheduler_alert',
job_type=>'stored_procedure',
job_action=>'alertlog_error',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ='||frq||';INTERVAL='||intrvl,
enabled=>true,
auto_drop=>false);
end;
/

When im trying to run the job it is getting hang.

exec dbms_scheduler.run_job('scheduler_alert');

View 3 Replies View Related

How To Use Synonym In Stored Procedure

Aug 28, 2009

how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

how to use a synonym in a stored procedure.

View 5 Replies View Related







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