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'm trying to find a simple way of getting around this. I have a PL/SQL procedure which loops through a list of values in a table. These values are the actual names of the functions I want it to run in sequence. The problem is I can get the string value each time but I need to store the return value of each function into a number variable e.g.
BEGIN
open all_tests;
fetch all_tests BULK COLLECT INTO test_tabl;
close all_tests;
for myindex IN 1..test_tabl.count LOOP
time_taken := test_source1 ==> this will work but how do I avoid hardcoding the name of the function to be executed and use the test_name value instead?? time_taken is declared as a number (ie the return value of each function is a number)
test_detail := test_tabl(myindex).test_name; ==> test_detail now contains the string name of the function
dbms_output.put_line('Test detail is ' || test_detail);
end loop;
This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.
The code i have created is this:-
CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS
FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
[code]........
The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-
VARIABLE RETVAL VARCHAR2(12)
EXEC User_login.user_select('HELLO','HELLO',:RETVAL );
but i am getting the following error
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
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.
when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below
(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)
but when am using the same condition as below the querry executing in 2 min
(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)
my query given below
(SELECT cust.customer_number cust_no, cust.customer_name customer,
cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
0 rtn_qty_nos,
ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
[code]...
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 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 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 RelatedI'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.
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 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?
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.
In my form i have 2 datablocks, the first contains only a display item which is populated from a lov when a user presses a button. The second datablock shows a list of items which should be queried dependant on the value of the above LOV. What i thought i could do is on a post-change trigger for the display item is:
go_block('block_name');
do_key('execute_query');
However, i am told i cannot do these in a post-change trigger, how to acheive what I am looking. Would i perhaps have to create my own trigger somehow or is their a simplier way.
How to find time log for query or any procedure like start time and end time and total time.
So that I can tune that queries properly.
Also how can we find estimated query running time.?
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 am trying to insert data from 9i to 11g using db link with the below query but while doing so the select statement is going for a full table scan even though rowid is used. But when we execute the same select statement without this insert command it is using proper index. Similar issues I am facing for updates.
Query:
INSERT INTO /*+ APPEND */ emp@db_link
select *
from emp where rowid ='AAC2SmAIIAAAHQgAAZ'
l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';
by using the execute immediate or any other command, how can i check whether the query returned any rows or not?
Using PL/SQL I want to search inside a directory and execute the script files with a specific pattern.
I know that with @@ command I can execute a script, however when the name of the files come in a variable I don't know how to execute the script.
Here is the code for searching the files:
set serveroutput on
declare
directory2 varchar2(1024):='/home/oracle/dir';
[Code].....
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
I am using embedded sql in a Fortran program on IBM AIX. Whenever I execute a SQL statement other than connect I get a coredump. So I wrote a small test program, but same thing happens. This is the test program -
PROGRAM TEST
EXEC BEGIN DECLARE SECTION
CHARACTER*20 UDPWD
EXEC END DECLARE SECTION
[code]....
And then when I run test, I see the 'Connected' print & then coredump. Same thing happens for EXEC SQL Prepare statement. Am I not linking some library ?
I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.
The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by (and as far as I understand, supposed to be used with) SYS user. what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users?
I have to write a script to do the following requirement. There is a file called BUSINESS_DATE.TXT. This file get updated once the oracle partition created. In Oracle, artition will be created every day. There is a seperate script scheduled to take care ORACLE partition creation. The above file will have only one row. i.e. 03092012
If Oracle partition creation job failed, the above file won't be updated. My requirement is, I have to check whether the BUSINESS_DATE file is updated today or not. If yes, I will have to move the files from common area to input file directory to process those files. All file name will amend with current date. i.e .
LIDDIFD03092012.TXT
The key part is to check the BUSINESS_DATE.TXT file is updated properly or not. We don't know what time the file will get updated. So we are planning to schedule the new script to run for every 15 mins to check whether the file is updated or not
But...I just thought instead of writing a shell script to do the above one, Why shouldn't I capture the date in a table (New table needs to be created) and use ORACLE TRIGGER to run the shell script to move the files from common area to input file directory to process those files?
My Proposal in ORACLE :
Create table business_date
(
rep_date varchar(15),
curr_timestamp timestamp
)
Once the oracle partition created , one row will be inserted into the above table. This adjustment needs to be implemented partition script. Once this table get record, TRIGGER should call SHELL SCRIPT to move the files from common area to input area.
If I implement ORACLE TRIGGER, The script which will check whether the file got updated or not for 15 mins is not required.
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 generate a report by showing the select, insert, update transactions count per day.
I have been use V$SYSSTAT veiw but there is not include my requested data exactly!
[URL]