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"
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.
I am getting many errors when creating the below procedure.
alter procedure sp_compileinvalid as
Begin
Set lines 999;
Spool run_invalid.sql
select
[code].......
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
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.
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.
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...
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]...
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.
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
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]....
I have compiled some procedures, at the time of compilation i saw some warnings & errors but i did not notice, Now can we see that errors or warnings in db console or any other way.
View 7 Replies View RelatedI 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.
How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure
View 1 Replies View RelatedCREATE 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
)
how to execute one stored procedure for every 10 minutes using oracle jobs
sp name: abc
using anonymous block with sample code.
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
I don't know how to control if a call to a PL function return errors.
I have this ksh:
executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction
And the code of "ejecutaFuncion.sql" is:
executeusers.startProcessing();
EXIT
How i can controle if the "startProcessing" function has any problems from the ksh?
I have created one function in the package.
function : Tier_wh
package : Order_DESC
Function defined as
function TIER_WH( message in out Xorder_desc)
...
...
end;
Xorder_desc is defined as
create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);
how to execute this function which is defined in the package .
i have tablestructure like this
empno ename sal
1 sam 1000
2 tom 2000
3 ric 3000
4 mac 4000
5 doy 5000
i want TO WRITE SELECT QRY WHICH WILL GO like this
empno ename sal prevemp prevename presale
1 sam 1000 0 0 0
2 tom 2000 1 sam 1000
3 ric 3000 2 tom 2000
4 mac 4000 3 ric 3000
5 doy 5000 4 mac 4000
means when each current row executes it shld show details from previous row also means when details of tom is executing it also shows sam details
I've defined DBMS_JOB in Oracle it is not starting on time. As per query it should start at 09:00 PM as given below.
SQL> SELECT TRUNC(SYSDATE) + 21/24 FROM DUAL;
TRUNC(SYSDATE)+21/24
--------------------
7/31/2012 9:00:00 PM
But instead it was started on 7/31/2012 1:14:10 AM. Which is wrong.
Following is script which I am using to submit this job.
DECLARE
X NUMBER;
BEGIN
[Code]....
how to use the MERGE Statement. actually I've used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select 'data' field1, 'data2' field2, ect from dual
union
select 'data' field1, 'data2' field2, ect from dual
union
[code]...
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
I have forms 10g and use oracle database11. I have a form and I need to execute a program x.bat in another PC.
View 2 Replies View Relatedis 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 RelatedDatabase 1(sm01):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.Daily jobs are running fine. But hourly job is not executing automatically. If forced (exec dbms_job.run(<enter here job number>), this execute fine.
job_queue_processes=5
total jobs in schema=2503
total jobs in db = 2614
Even there are many jobs scheduled, next_date for 2234 jobs are lesser than the sysdate. Again in 269(2503-2234) jobs, 2265 are having NULL in the interval column.
Database 2(sm02):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.All the jobs are not running automatically. If forced (exec dbms_job.run(<enter here job number>), these execute fine.
job_queue_processes=5
total jobs in schema=7
total jobs in db = 7
I planning to follow the below steps to avoid the above issue.
1.) Restart the job queue process by executing alter system set job_queue_processes=0
2.) Increase the value for the job_queue_processes.
3.) Restart the database
But I got stuck in the 2nd step. what value I need to put for this job_queue_processes parameter?
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.
I am trying to track down the cause of some ORA-101017 errors that are occurring intermittently. We are running PeopleSoft and every so often (couple days) we see a burst of these errors in one of our application server log files. They are not specific to a particular server and only last about 5 minutes.
There do not appear to be any messages in any of the Oracle logs and the database is not recording any failed log in attempts. No passwords are being changed and/or account changes are being made.
I can think of is that there is some sort of connection limitation to the database which causes these errors to spawn when the maximum number of connections is reached.
My question is whether or not the ORA-01017 error could be inticatve of this type of problem and/or what might be going on.
I am trying to use NETWORK_LINK option in datapump and import a table from one server to another. I gave the below command :
C:>impdp example/example@db DIRECTORY=DATA_PUMP_DIR
NETWORK_LINK=db.legal.regn.net remap_schema=BI:example
tables=BI.BI_DIRECT dumpfile=BI.dmp logfile=BI.log
Got the following errors :
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Is this error related to the permission in the OS level (windows 7 in my case)? I manually created the folder 'DATA_PUMP_DIR' in the specified directory path. Though the directory I created (DATA_PUMP_DIR) shows read-only in the general tab of the property, I am able to create files under the folder 'DATA_PUMP_DIR'.
I'm using it with MICROS and I want to edit an "RDF" then convert to an "REP" I have edited it but when I try to compile it it gives errors such as "statement ignored" or "must be declared". What these error messages mean!
If I do nothing to them and then try to save it as an REP i get a pop up error "REP-0736 there exists uncompiled program unit(s)" then i press OK and get an error reading "REP1430 cannot create REP file for file "uk_conf_lorne(name of my file)". Compilation errors found.
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.