Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.

learn more… | top users | synonyms

0
votes
1answer
18 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
1
vote
2answers
2k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
-3
votes
1answer
23 views

Why DBMS_METADATA.GET_DLL not work with EXEC command ?

Using Oracle 11g I just trying to understand the concept here.... Why the command bellow not work? (executed at SQL Developer) execute dbms_metadata.get_ddl('VIEW', 'VW_CARGA','INT') Error ...
0
votes
1answer
18 views

how to correctly return values from a function

I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not. ...
0
votes
1answer
50 views

12c Shell to Linux

I am replacing two Oracle 11g servers with 12c TL/DR - when shelling out to a run an O.S. file, the PATH environmental variable is not being set. On the old servers we use an external library to ...
1
vote
1answer
57 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
0
votes
1answer
20 views

PL/SQL cursors and records

I am a new to Pl/sql and I need to learn to write simple pl/sql scripts. Is there any difference between cursors and records in PL/sql? So far I find them both similar.
3
votes
1answer
282 views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
1
vote
0answers
65 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
0
votes
1answer
125 views

Oracle strong ref cursor with dynamic sql

I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic ...
0
votes
0answers
42 views

export query results to file

I am facing the following issue. I want to execute a query and export all the results in a file. My query is saved as in varchar2 type and i can execute immediate on it and give me all the results. ...
0
votes
1answer
21 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
0
votes
0answers
34 views

From keyword missing in view

I am this view which I am trying to compile. Given below is the view. CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER", "REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", ...
0
votes
2answers
48 views

Optional parameter in where condition

I have the following where condition: lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy') All works fine. But the second unnamed parameter is optional. How ...
0
votes
0answers
43 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
1answer
32 views

Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?

I'm trying to handle two different exceptions could trigger in an external table that reads a CSV. The code works great, but I'm concerned I'm doing it wrong. SET SERVEROUTPUT ON; DECLARE records ...
0
votes
0answers
40 views

Oracle data obfuscation on specific columns with set field sizes

Would like to obfuscate data held in Oracle tables, but: Requirements only apply to specific columns e.g. personal data obfuscated value should not be longer then column size possibly only ...
0
votes
0answers
19 views

How to display '0' against an empty database field (number) with defined fomat mask [duplicate]

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 25,000 Data2 ...
0
votes
1answer
51 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
1answer
103 views

Installing Oracle Database 12c in Windows 7, INS-08109 error [closed]

I'd like to learn PL/SQL so I'm trying to install Oracle. I downloaded the 2 zip files and extracted to the same directory. I run the "setup.exe" file and the install GUI starts: In step 1 I enter ...
0
votes
0answers
97 views

'oracle.jdbc.driver.OracleSQLException': ORA-01031: insufficient privileges. (SYS.DBMS_XMLSAVE)

I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER: FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN ...
0
votes
0answers
16 views

Issue: PLS-00103

I have a package body: PACKAGE BODY "PKG_SYNONYM_TEST" AS type t_table as object ( synWord varchar2(500) ); type t_nested_table as table OF t_table; function search1(i_string varchar2) RETURN ...
5
votes
3answers
535 views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
1
vote
1answer
163 views

Pl/SQL web application

I have a PL/SQL web application,got the entire DB dump from remote DB. Its very old application, running on oracle 9i. I basically want to get the application up and running on my laptop,but i am not ...
0
votes
1answer
53 views

How to run procedure in Oracle SQL developer

Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure -- start the script SET SERVEROUTPUT ON SET LINESIZE 400 SET TIMING ON CREATE OR REPLACE PACKAGE BODY ...
0
votes
0answers
39 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
2
votes
1answer
64 views

PL/SQL doesnt return rows

Please help me debug this: Working: When I execute: set serveroutput on declare middle_name varchar2(30); begin middle_name := '0000000231'; for x in ( select ...
0
votes
1answer
139 views

ORA-01410: invalid ROWID

I get ORA-01410: Invalid ROWID error when executing the following piece of code: DECLARE CURSOR c1 IS SELECT e.empno, e.ename, e.sal, d.dname ...
5
votes
2answers
219 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
3
votes
1answer
2k views

Oracle 11: Change global index to local on huge production table

I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index. So I have idea to use INVISIBLE local ...
0
votes
0answers
43 views

Error ORA-06550

CREATE OR REPLACE PROCEDURE pro_department_report AS v_did department.deptid%TYPE; v_dname department.dname%TYPE; v_sname student.sname%TYPE; v_count NUMBER(2); v_sdid student.deptid%TYPE; CURSOR ...
5
votes
4answers
3k views

PL/SQL: best way to count elements in an array?

Given this: DECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; MY_ARRAY T_ARRAY; V_COUNT INTEGER; I would like to do: BEGIN -- ... some code filling the MY_ARRAY ...
0
votes
2answers
102 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
0answers
206 views

Creating and tuning a materialized view in oracle for use by Cognos BI

I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely ...
1
vote
0answers
805 views

PL/SQL extremely slow FETCH performance

I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
0
votes
2answers
87 views

Cycle or something similar in PL/SQL

Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via alter user %username% account unlock and ...
2
votes
3answers
582 views

how to change a user password while checking the current password in oracle?

In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command: alter user user_name ...
1
vote
1answer
104 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
0
votes
2answers
120 views

How to fetch file path from a folder through PL/SQL

We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible. If possible, please suggest what ...
3
votes
2answers
203 views

Is it good practice to check for existence of records before deleting them

I've came across the following SQL/PLSQL while investigating a stored procedure written by someone else: 1. V_COUNT NUMBER(5,0); ......... 2. SELECT COUNT(*) INTO V_COUNT FROM TABLE1 WHERE ...
5
votes
6answers
278 views

Is Oracle free to download?

I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free? I found this link: ...
0
votes
2answers
154 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
1answer
88 views

Error wile debugging (signature buffer too small)

I'm trying to debug a package in SQL Developer, but I keep getting error: ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [] ORA-06544: ...
0
votes
1answer
938 views

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package. I am using SQLDeveloper 3.0 with an Oracle 11g database. ...
3
votes
1answer
146 views

PLSQL : DBMS Jobs - parallelization?

I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs? for rec in select column from table1 loop execute stored_procedure( rec.column ); end ...
3
votes
2answers
3k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
2
votes
2answers
653 views

Logging/profiling an unpredictably slow stored procedure

I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only ...
0
votes
1answer
292 views

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have ...
4
votes
2answers
333 views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
0
votes
1answer
58 views

Dividing a column into three columns [duplicate]

I have this table: Name Address Code James CT Homeaddress Mark mark@goole.com Email John John@hotmail.com homeemail ... This is ...