I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.
IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN
DBMS_OUTPUT.PUT_LINE 'True';
ELSE
DBMS_OUTPUT.PUT_LINE 'False';
END IF;
I'm having trouble writing values out at the moment
Where are the server side procedures are stored? Where are the server side procedures are visible??
View 5 Replies View RelatedLet us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.
Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?
I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)So I do this, in the hope of trapping the error
BEGIN
run_stored_proc (my_cursor, my_id, param3);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
END;Well, it can still crash.
SQL> @c:mysql
un_test
ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure
ORA-01422: exact fetch returns more than requested number of rowsSo it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?
Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.
I want to to use Java stored procedures and triggers within the Oracle 11g XE ?
View 3 Replies View RelatedWhy do stored procedures and functions improve performance?
A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.
I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?
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....
In SQL server 30 stored procedures are there how to convert all the stored procedure from SQL server to Oracle 11g..
View 5 Replies View RelatedHow to call stored procedures or functions from a database trigger?
View 1 Replies View Relatedi need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/
We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?
View 2 Replies View RelatedI would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
Is there a way to call an oracle report (version 9i) from a stored procedure?
View 2 Replies View Relatedcreate type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)
[Code]....
Now how to use the change_name procedure to change the name of the employees in the table?
I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.
ORACLE 11G
I am using sql developer 3.2 version tool
runtime monitoring procedures....need a sample script
View 1 Replies View RelatedI 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 have a job that runs two procedure parallely . The code is as follows:
DECLARE
jobno1 NUMBER;
jobno2 NUMBER;
flag NUMBER;
BEGIN
dbms_job.submit(jobno1,'pc;', next_date=>SYSDATE);
dbms_job.submit(jobno2, 'pc2;', next_date=>SYSDATE);
[code]....
And the stored procedure (in SQL File) is as follows:
procedure pc:
create or replace procedure pc
as
begin
for idx in 1..100 loop
dbms_output.put_line(idx);
end loop;
end;
/
procedure pc2:
create or replace procedure pc2
as
begin
for idx in 1..1000 loop
dbms_output.put_line(idx);
end loop;
end;
/
However, when I execute the job , I get the following output:
1
2
3
.
.
.
.
100
[code]....
Which indicates the job 1 got executed 1st and then job2. But this is not what I want.
whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob
how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?
we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared
Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103
[code]...
Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.
I've got some quantity of procedures in package
procedure1
procedure2
...
procedureN
I should call them in cycle instead of typing each of them manually. I know it's a bad idea to do something like this "dynamically".
I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.
CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP
IF i = 18 THEN
tmp_no := temp;
END IF;
IF i = 21 THEN
tmp_name := temp;
END IF;
END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));
how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..
By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7)
CREATE OR REPLACE PACKAGE APEX_SYS_PCK AS TYPE refcursortype IS REF cursor PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2) IS BEGIN OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS; END APEX_SYS_PCK;/
When I try to run the script I am getting the following error. How to solve this problem?
Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?
View 4 Replies View RelatedI think there is no limit for number of procedures and functions can be written in a package, but little much confusion.
What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
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.
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?
View 2 Replies View Related
