PL/SQL :: Calling A Function In Remote Database Inside A Stored Procedure

Apr 9, 2013

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN

[Code]...

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

where my mistake is?

View 7 Replies


ADVERTISEMENT

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

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

SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure

Apr 28, 2011

Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.

View 3 Replies View Related

SQL & PL/SQL :: Calling Exe File From Stored Procedure

Jun 15, 2011

I want to call an exe file through DB Procedure. How it is possible?

1create or replace procedure proc3

2is
3begin
4 host('calc');
6* end;

from FORMS it works fine but it does not work through DB. I found same question through this link

[URL].........

but no answer is given.

View 2 Replies View Related

SQL & PL/SQL :: Calling Stored Procedure With Default Value?

Apr 3, 2012

I have the following Stored Procedure:

CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS

[Code]....

I dont know what I need to pass in order to give it the default value.

View 3 Replies View Related

PL/SQL :: How To Run Report / Calling Web URL Into Stored Procedure

Dec 17, 2012

I have a requirement to run below given URL by Stored Procedure. I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

[URL]

View 4 Replies View Related

SQL & PL/SQL :: Declaring Arrays Inside Stored Procedure?

Jun 17, 2010

how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.

procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)

[code]....

When I try to compile it, I get the error "component EXISTS must be declared".

View 14 Replies View Related

PL/SQL :: How To Get Some Optional Parameters Inside A Stored Procedure

Oct 1, 2013

Can we call one or more parameters inside a stored procedure call (like func_get_args in PHP) ? Example : 

create or replace PROCEDURE test_proc (
val1 in varchar DEFAULT NULL,
val2 in varchar DEFAULT NULL,
val3 in varchar DEFAULT NULL,
[code]..........

View 4 Replies View Related

SQL & PL/SQL :: Trigger Calling An Autonomous Stored Procedure

Jul 7, 2011

I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?

View 18 Replies View Related

Precompilers, OCI & OCCI :: Calling Stored Procedure From C Program

Mar 19, 2010

I need to call a PL/SQL stored procedure from a C program. Something like this sample program provided by Oracle -

main()
{
int i;
EXEC SQL BEGIN DECLARE SECTION;
/* Define type for null-terminated strings. */
EXEC SQL TYPE asciz IS STRING(20);
asciz username[20];
asciz password[20];
[code].......

The question is - how is the Stored procedure get_employees declared ? Or more specifically, how is the salary parameter declared in get_employees ?

View 4 Replies View Related

Performance Tuning :: Calling Stored Procedure In Jsp Page

Feb 16, 2011

We are calling a stored procedure in jsp page. The sql where bottleneck was assumed is giving better performance in sql plus (0.093 seconds) but when called from jsp its taking too much time.

Here is the plan for the sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 |
| 1 | SORT GROUP BY | | 1 | 219 |
| 2 | NESTED LOOPS | | 1 | 219 |
| 3 | NESTED LOOPS | | 1 | 197 |
| 4 | NESTED LOOPS | | 1 | 162 |
| 5 | MERGE JOIN | | 1 | 127 |
| 6 | TABLE ACCESS BY INDEX ROWID | policy_tbl | 1 | 78 |
| 7 | INDEX FULL SCAN | pk_pol_id | 1 | |
| 8 | SORT JOIN | | 1585 | 77665 |
| 9 | TABLE ACCESS BY INDEX ROWID| policy_p_tbl | 1585 | 77665 |
| 10 | INDEX RANGE SCAN | pp_param_1 | 1679 | |
| 11 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 12 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 13 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 14 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 15 | TABLE ACCESS BY INDEX ROWID | policy_dtl_tbl | 1 | 22 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 16 | INDEX UNIQUE SCAN | pk_pg | 1 | |
--------------------------------------------------------------------------------

View 2 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

SQL & PL/SQL :: Remote Database - Cannot Perform DML Operation Inside A Query

Jun 16, 2010

I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.

If i execute this function everything is fine.

If I call this function from a remote database, I have this error message:

"ORA-14551: cannot perform a DML operation inside a query".

select function('parameter') from dual;

Result: "OK"

select function@dblink1('parameter') from dual;

Result: "ORA-14551: cannot perform a DML operation inside a query"

View 14 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

Oct 4, 2011

when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14

code which i have written ...

DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT

Procedure Name :

PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Calling Procedure Into Function?

Aug 1, 2011

for my r&d purpose i create function which call procedure,

create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)

[Code]...

but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.

but when i complied fucntion it doesnt gv me any error.

View 15 Replies View Related

SQL & PL/SQL :: Stored Procedure Vs Function?

Jun 9, 2010

Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....

View 3 Replies View Related

Difference Between Stored Procedure And Function?

Jan 12, 2010

I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.

The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..

I want to know what is the difference betn stored procedure and function?

From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).

View 2 Replies View Related

SQL & PL/SQL :: Wrap Stored Procedure Or Function?

Feb 13, 2012

I am familiar with WRAP command.It is used in command prompt.

C:> wrap iname=filename.sql
and this thing will convert filename.plb file.

but now I want to wrap my stored Procedure or function.how can i do this thing?it is saved in my disk?

View 10 Replies View Related

Reports & Discoverer :: How To Call Oracle Reports Inside A Stored Procedure

Oct 14, 2010

Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:

a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?

[code]...

Now, i want to convert this into oracle,plsql? Is it possible or not?

View 3 Replies View Related

SQL & PL/SQL :: Stored Procedure Function - Numeric Or Value Error

Apr 16, 2010

I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7

The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"

and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"

The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.

create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....

View 26 Replies View Related

PL/SQL :: Call VB Function From A Package Stored Procedure

Apr 30, 2013

I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.

View 5 Replies View Related

JDeveloper, Java & XML :: Calling Java Stored Procedure From Oracle

Jun 20, 2011

I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.

Some thing like following:

I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.

My problem is how can I issue the

SELECT details FROM CONNECTION_DETAILS for USER2

so that i can get the details..?

Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.

I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?

View 1 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

View 7 Replies View Related

SQL & PL/SQL :: Created Function For Knowing Status Of Link Which Connect With Remote Database

May 22, 2013

I have created a function for knowing the status of link which connect me with remote database.function is as follows

---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....

Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.

View 1 Replies View Related

SQL & PL/SQL :: Calling Database Link Dynamically For A Procedure

Feb 15, 2011

How to call a procedure by passing the db link dynamically.

View 1 Replies View Related

Forms :: Calling Procedure At (Form / Library And Database) Level

Mar 17, 2011

I've a procedure let us say SALES_PROC on Form Level and same procedure in Library and in Database also.

-How to call SALES_PROC created at Form Level?
-How to call SALES_PROC created at Library Level (.PLL)?
-How to call SALES_PROC created as stored procedure in Database?

View 5 Replies View Related

SQL & PL/SQL :: Calling A Remote Table In SP?

Feb 21, 2011

I have created a remote link to a sql server db in oracle 11.0.7 works fine in sql plus. I can select tables by select * from table@linkname...the link is a public link But when I put it in the procedure it says invalid table name. then I created a public synonym for the table - i can select from the synonym in sql plus - but in the procedure it says synonym translation invalid...Also I cant grant any privileges to the synonym as it says ddl operations are not allowed on remote objects

View 1 Replies View Related

SQL & PL/SQL :: Calling APIs Inside Cursor

Jun 17, 2011

im calling an api inside a cursor.. for the first loop in a cursor , the api works well. however, for the rest of the loops the api doesn't give any error, but it doesn't insert any row to the table.

if i called the same api for every single value in a cursor seperately the api would insert all rows.

View 1 Replies View Related







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