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
Having:
3 databases: CYR1,CYR2,UTF
Database links was established CYR2 -> CYR1, UTF -> CYR1
Function GETALL is on the CYR1 site
SQL> desc GETALL
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) VARCHAR2
FMT VARCHAR2 IN
[code]....
My questions:
1) What could be reason that oracle would look so deeply in source of REMOTE procedure, especially as because remote procedure is in VALID, compiled state?!!
2) Could we classify such restrictions as a bug?
This is how I call the remote function:
declare
v varchar(300);
begin
v:= getall @orcl8.oegc.tgk11.com@csk ('%slu%',129);
dbms_output.put_line(v);
end;
This is "offending" statement in getall (basic static SELECT), that I had to throw away for call to getall to succeed:
select .... from ПРАВА_ПРОГ_ДЕТАЛИ ...
ПРАВА_ПРОГ_ДЕТАЛИ would occupy exactly 32 byte in UTF8 (2*15_cyrillic_chars + 2 underscores), but in CYR1, this table name is absolutely valid, only 17 chars long, because Cyrillic characters are represented by one byte!
I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
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]...
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 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.
Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.
The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:
SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....
and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....
Oracle 11.2.0.x
Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).
The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.
Question.
Can one support WSS (Web Services Security) with this feature?
I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.
It however uses Basic Authentication (schema name and password). Not WSSE.
As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).
So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.
Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.
I have a requirement to generate the XML file from a pl/sql procedure. The methods which I have searched are either generating the XML from a single query or they are explicitly adding the nodes and elements. I need the method where I can dynamically append child nodes to the file.
View 12 Replies View RelatedHow to make a call to a proc and immediately return without waiting for the called proc to end.
Nothing I try seems to do the trick...The calling program always has to wait.
i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.
View 2 Replies View RelatedI have a table TABLE1 which has following columns
proc_name
table_name
insert
update
delete.
The table contains a proc name, all tables used in that proc,operation is done on the tables. like sample data.
proc_name tab_name insert update del select
----------------------------------------------------------------------------------
Proc1 Table1 Y Y N Y
Proc1 Table2 y Y Y Y
I want to update this table throu running a generic procedure.Can it be possible to craete a procedure which parses through all procs of database and get above info and update TABLE1??
I have a one critical remote PROD database that I want to check every 10 mins for its connectivity. If connection fails then an email & SMS is sent to me. My question is what is the best way to check if the remote database is up and running?
Can I use sqlplus system/manager@PROD? But sometimes this took so long and hanging. I want the fastest response time? How do I write a pl/sql proc check connection? That do something like the ff:
I created a table for my tnsname.ora entries.
cursor is c1 select dbname from tnsnames_tbl;
begin
connect system/manager@c1.dbname;
print c1.dbname || 'DB Connection OK';
exception
when others;
print c1.dbname || 'DB Connection Not OK';
end;
end;
In my below code the procedures "total_score_proc" and "CopyInternalScores" are calling "score_proc" procedure 50 times
for different variable values.
Instead of calling the "score_proc" procedure 50 times.I want to hold the values in to collection , defining it in package and call that procedure only once.
how to implement "score_proc" using bulk collect.
CREATE OR REPLACE PACKAGE total_score_pkg
IS
PROCEDURE total_score_proc(pBUID IN STAGING_ORDER_DATA.BUID%TYPE,
OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
[code]....
I found this thread, and recently i want to embedded oci in pro*c for distributed transaction reason.
can pro*c deal with distributed transaction? i want to use mts in pro*c .
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
[Code].....
developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..
CREATE OR REPLACE PROCEDURE FS.CREATE_PART_test
declare
v_monyy varchar2;
v_yyyymm varchar2;
v_tblspc dba_data_files.tablespace_name%type;
[code]...
I am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break
View 9 Replies View RelatedI have to copy all the procedures in database to local folder and extension( or file type) of each procedure is ".proc".
I did tried with dbms_metadata but as there are 300 procedures it consuming time & I want separate file for each one.
select
dbms_metadata.GET_DDL('PROCEDURE',u.object_name), u.object_name
from
user_objects u
where
object_type = 'PROCEDURE'
AND object_name in( 'P1');
I Want to declare some host variables with global scope. Like this C variable:
MyFile.h
...
char str_var[20];
...
But i can't put SQL sentences in a include file. And if i do this:
MyFile.pc
...
EXEC SQL BEGIN DECLARE SECTION;
char str_var_h[20];
EXEC SQL END DECLARE SECTION;
...
... I can't see this variable from others sources, although I declare it like 'extern'.
we have an application currntly running on an HP UX system that uses Oracle 9i database. the pro*C codes work fine with Oracle 9i, on the older system. however now we are migrating it to LINUX system (ORACLE 10g). in the new system we are facing issues with fetch statement
here is how we have the
the cursor statement:
EXEC SQL DECLARE diff_cns_list CURSOR FOR
select PREV.CNS_CODE,
PREV.CNS_DESCRIPTION,
[code]....
the code runs fine in the old operating system (HP UX) where oracle 9i was used. but fails in Oracle 10g(OS-LINUX).
the probable reason that we found out is that: the fetch statement returns zero rows, that is the reason why this error is being displayed. but Oracle 9i seemed to work out well with this.
we tried reducing the cursor conditions, where it fetches 3 rows. this is when is the fetch statement works fine.
the functionality of the code is that it should work fine even with no rows selected. Is there a way we can modfy the code to work with zero rows as well.
the error we are getting is : ORA-01002: fetch out of sequence
==========================
as per what is given in Oracle sites:
1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
========================
we have checked:
1>this is not the case and ORA-1403 not returned
2>No update statements involved , there is a insert statement which inserts data in this table
3>i am not clear with this one.
create or replace package body test_exp_pkg as
procedure l_test_exp (errcode out Varchar2, errm out varchar2)
as
l_rec zt574%rowtype;
begin
[code]]....
output:
---------
anonymous block completed
in package init
caught in l_test_exp-1422ORA-01422: exact fetch returns more than requested number of rows
caught in l_test_exp errcode and errm set to -1422sqcORA-01422: exact fetch returns more than requested number of rows
caught in test_exp1User-Defined Exception
caught in others errcode and errm
I am working on oracle 11g...I have one normal insert proc
CREATE OR REPLACE PROCEDURE test2
AS
BEGIN
INSERT INTO first_table
(citiversion, financialcollectionid,
dataitemid, dataitemvalue,
[code]....
I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
below is my send_mail proc
Quote:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
[code]...
and below is my sql query
Quote:
select a.id, b.hr_info, c.details
from contact a, employees b, grid c
where a.id=b.id
and b.hc=c.hc
I am trying to compile a pro c file. Rest of the pro c file is compiling fine. But one particular file is showing following error.
INTERNAL ERROR: Failed Assertion [Code=40706]
I am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792');
BEGIN PACKAGE.PROC(201011,'144792'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME
PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PWEEK NUMBER IN
PCLIENT VARCHAR2 IN
CRESULTS REF CURSOR IN/OUT
After searching a bit, i try the following:
SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>
This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
AUTHID CURRENT_USER
AS
--
TYPE CurTyp_Supp IS REF CURSOR;
--
TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);
--
TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);
--
PROCEDURE PROC(pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp);
This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
PROCEDURE PROC (pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp)
QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
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?