SQL & PL/SQL :: Procedure Calling Methods

Oct 14, 2013

I have created this procedure for printing ename,sal,job as output using empno as input:-

CREATE OR REPLACE PROCEDURE p_get (
p_empno NUMBER,
p_name OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2
)
IS
BEGIN
SELECT ename, sal, job
INTO p_name, p_sal, p_job
FROM emp
WHERE empno = p_empno;
END;

Now My Requirement I want to call this procedure using Positional,Named and Mix Methods...I am calling this procedure using Positional Method:-

declare
p_name varchar2(20);
p_sal number;
p_job varchar2(20);
begin
p_get(7369,p_name,p_sal,p_job);
dbms_output.put_line('Name='||p_name||' Salary='||p_sal||' Job='||p_job);
end;

how to call the same procedure using NAMED and MIX methods.how to call same procedure using NAMED and MIX methods.

View 11 Replies


ADVERTISEMENT

Calling Procedure Using Trigger

Aug 26, 2013

I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'

Here is my procedure:

create or replace PROCEDURE INSERTINTOPHYSLOG
AS
prec_num long;
srec_num long;

[Code]....

View 2 Replies View Related

Calling PL/SQL Procedure From JavaScript?

Oct 8, 2007

The application I am working on uses Oracle HTP procedures to generate HTML pages for our application. I am trying to perform some cleanup when a user closes a web application by clicking the 'X' button in there browser. I am thinking of using the onUnload trigger to call a javascript function, which in turn would call a procedure in my PL/SQL pacakge for cleaning up logical locks on records.

The body tag delaration looks like this code fragment:

htp.print('<BODY onUnload="clear_locks(' || p_user_id || ')"

Here is some psuedo code for the function i would like to use:

<SCRIPT>function clear_locks(p_user_id){
call clear_locks(p_user_id);
return true;
}</SCRIPT>

Note that clear_locks is a pl/sql stored procedure.So far I have gotten it to pass in the user ID correctly.Is there a way I can call my clear_locks procedure?

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

PL/SQL :: Calling Procedure In Loop

Oct 2, 2013

I am calling a procedure with the following parameters 

DECLARE     PROFIT_CENTER NUMBER;   BEGIN   PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111

one by one by passing values manually, it is taking almost a minute for each profit center

)Prc_test_calc (  PROFIT_CENTER);  COMMIT;   END;
For each profit_center proc is taking 1 mins (Approx) time.  

But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop  

DECLARE     PROFIT_CENTER NUMBER;  cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --

Plng_pc is a table from where profit_center information is getting fetched  

BEGIN For pc_rec in c_profit loop   Prc_test_calc (  pc_rec .PROFIT_CENTER );  COMMIT; End loop;   END;

View 9 Replies View Related

Calling Procedure With Commit From Trigger

Dec 8, 2006

I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.

Trigger

Create or replace trigger owner_name.table1_trg2
after update on table1
for each row
call owner_name.procedure1;

procedure Code (psuedo):

Create or replace procedure1 as
begin
update table1 set col1 = 'whatever';
commit;
exception when others then
rollback;

Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.

View 5 Replies View Related

Calling Web Service From A Procedure Using SOAP API

Mar 5, 2013

I am trying to call a Web service using the SOAP API and I get the following error.

ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
ORA-06512: at "SYS.XMLTYPE", line 48
ORA-06512: at "MyConnection.SOAP_API", line 121
ORA-06512: at "MyConnection.MyFunction", line 28
31020. 00000 - "The operation is not allowed, Reason: %s"
*Cause: The operation attempted is not allowed
*Action: See reason and change to a valid operation.

However we do have another procedure that is able to make an HTTP call successfully within the same domain. Hence the ACLs that are required are for making an HTTP call are in place. Is there any other security setting to be tweaked to make the to enable me to make a Webservice call using the SOAP package?

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

SQL & PL/SQL :: Calling Shell Script Within Procedure?

Jul 22, 2013

I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.

Below is my procedure:

create or replace
PROCEDURE RD_ODS_REFRESH_LOG
IS
BEGIN
FOR i IN
(
SELECT RESULT FROM RD_REFRESH_LOG

[code].....

And here is CALL_SHELL job that my procedure calls:

BEGIN
SYS.dbms_scheduler.create_job (
job_name => 'CALL_SHELL',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/load_semantic.sh',

[code].....

My ELSE statement never gets executed, why?

View 18 Replies View Related

Precompilers, OCI & OCCI :: Calling Procedure From PRO C?

Nov 26, 2012

I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL

like this

my_procedure( q=>a1 , b=>'abc )

View 1 Replies View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

Feb 26, 2013

It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.

Scenario is like below:

create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;

[code]....

Not getting why the schema is not changing... and how to resolve the error.

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

PL/SQL :: Calling Oracle Procedure With Two OUT Parameters

Jun 20, 2012

I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.

proc_Test (p_resultset=> My_cursor)

How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.

View 5 Replies View Related

PL/SQL :: Calling Procedure From Another And Get Combined Output

Jun 3, 2013

I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:

CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....

I am not able to compile tis proc.

View 5 Replies View Related

PL/SQL :: Differences Between Calling SQL Statement / Procedure?

Apr 22, 2013

I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?

Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;

b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);

create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
   begin
    open cur for select * from employees where department_id = dep_id;   
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.

View 10 Replies View Related

SQL & PL/SQL :: Error In Calling Collection Procedure

Jun 4, 2013

In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are

"UpdateRelatedOrders" and "SetGeoFraudScore"

with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures

"UpdateRelatedOrders" and "SetGeoFraudScore".

I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....

View 19 Replies View Related

Out Of Process Exception On Calling Java Procedure?

Sep 28, 2011

In my application, we are getting the following exception on calling the Java procedure.

java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-04030: out of process memory when trying to allocate 10804020 bytes (joxp heap,f:OldSpace)

[Code].....

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

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

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

SQL & PL/SQL :: Calling Shell Script Through External Procedure

Nov 26, 2006

I am trying to call a shell script through external procedure. To summarize the steps that I followed:

Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.

Then I logged into the sqlplus. Created one library pointing this .so file.

create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';

After this created the external procedure. This got compiled without any errors.

SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2)
as external
library ext_proc
NAME "execute_cmd"
parameters (in_script string);
/
Procedure created.

I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.

However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows

SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>

View 5 Replies View Related

Application Express :: How To Set Request When Calling Procedure

Apr 23, 2013

I use the procedure gReport.navigate.paginate. If there is the possibility to set a value to REQUEST when calling this procedure?

View 7 Replies View Related

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

Apr 9, 2012

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

View 8 Replies View Related

SQL & PL/SQL :: Calling Packaged Procedure With Refcursor As Out Parameter Over DBlink?

Oct 7, 2011

I have a requirement in which I have to call a packaged procedure created in one database DB1 to other database DB2 through a DBLink. The packaged procedure to be called has refcursor as OUT parameter.

When I run the procedure in DB1 , it works fine:
Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1('101062','9138', v_ref_data);

FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);
End;

When I run the packaged procedure from DB2 using DBlink, it gives error:

Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1@dblink('101062','9138', v_ref_data);
FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);

[code]...

I read somewhere on other forums that refcursors can not be passed through the DB links, is it true??

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

Application Express :: Calling A Procedure From A Tabular Form

Apr 30, 2013

I am new to APEX (well all of Oracle really so only have limited knowledge of PL/SQL etc too) but have created a couple of simple applications via wizards. One of these has a tabular form which works fine for updating records for the table that the form was built over but I need a way to write a record to a second (audit) table as well.

My form is used for cycle counting of items in a location. It originally shows the quantities of all the items in a location and allows the user to make corrections by updating any incorrect quantities to the correct value (this would then update the quantity column in the main table). It then needs to calculate the difference between the old and new value and write this as a record to an audit file (might be a positive or negative number).

do I call a procedure once (on submitting the page) and get the procedure to loop through the changed records writing to my audit file OR do I call my procedure once per changed record on the tabular form, if so how do I know what parameters to pass to the procedure?

I have tried researching this online but can find no easy to follow examples. All examples of code I have seen for tabular forms refer to fields referenced by apex_application.g_f01 or f02 etc - how do you know what items on the page these relate to?

View 3 Replies View Related

SQL & PL/SQL :: Calling Procedure Within A Procedure?

Oct 19, 2010

can we call a procedure within a procedure in a package or does it have to be declared before it can be called in the body,where can i find an example

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







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