Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

learn more… | top users | synonyms

3
votes
0answers
51 views

UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
3
votes
0answers
229 views

plsql procedure gets invalidated

procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem in my case procedure p1()--resides in db1 is begin .... select col1 ...
2
votes
0answers
89 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
1
vote
0answers
21 views

EXTPROC issue during 10g to 11g database

I am currently 10g to 11g database migration and trying to configure EXTPROC. EXTPROC32 was configured as part of 10g to execute C++ library and working fine. It appears that 11g does not provided ...
1
vote
0answers
696 views

How to drop invalid Java classes from Oracle?

I tried to load some jars into my database, but I think I messed up something, because I tried as DBA and then with some oracle user... This was my first command: call dbms_java.loadjava('-genmissing ...
1
vote
0answers
763 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
218 views

Adding custom meta data to a table in oracle

I'd like to know if there's a possibility to add some custom metadata to a oracle database table. The problem at hand is the following: I'm importing a table from another DB. It might happen that I ...
1
vote
0answers
233 views

dbms_audit_mgmt.clean_audit_trail not purging

my current database version is 11.2.0.3.0. I'm trying to purge my audit trail with a procedure. In my procedure I got this part of code: SYS.dbms_audit_mgmt.clean_audit_trail( audit_trail_type ...
1
vote
0answers
297 views

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to ...
1
vote
0answers
485 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 25,...
1
vote
0answers
2k 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 ...
1
vote
0answers
4k 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 ...
1
vote
0answers
789 views

How can one reliably update a LOB column with data from another LOB column in Oracle?

I have tried: UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE) I have tried: CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS OLD_BLOB BLOB; NEW_BLOB BLOB; ...
0
votes
0answers
53 views

What are PDE files and how can I use them?

I've got a legacy DB which was given to me as a collection of PDE files. I've googled around and didn't come up with any useful information. Saw this question: Import .pde file on PL/SQL Developer .....
0
votes
0answers
45 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
0
votes
0answers
13 views

Help with logic with cursor

I have a table called chapter_element, that houses the content for a web ebook. (There are other tables, but this is where the content is contained.)This is the table structure. CHAPTER_ELEMENT ...
0
votes
0answers
14 views

Dynamic Query with User Input 2 on same Input value

Using PL/SQL I have a Dynamic Query that requires users to provide input. How do I set my where condition on an input to ignore when value is null? I have two condition within the same type Model1 ...
0
votes
0answers
29 views

varray and cursor..need help on counter

DECLARE type ABC is array(10) OF number; v2 abc; CURSOR c1 ISSELECT employee_id FROM employees WHERE rownum<10; v2 :=abc(); counter :=1; BEGIN for i IN 1...10 loop ...
0
votes
0answers
24 views

PL/SQL: Temporary Table insertion in User Defined Functions

I have made a Global Temporary Table (We'll call it TT) and I am trying to insert values from a permanent table (We'll call it PT) into TP. Right now I am trying to run this certain setup inside my ...
0
votes
0answers
46 views

Procedure stuck at a bulk collect

Here's a procedure I'm working on. It's called from another procedure, which uses it in a nested BULK_COLLECT loop on every entry in another table. It gets stuck on "FETCH uniquephysicians BULK ...
0
votes
0answers
230 views

PL/SQL to find Special Characters in multiple columns and tables

I am trying to come up with a script that we can use to locate any special characters that may exist in a column of data except for period, dash or underscore, and using variables. My Data - ...
0
votes
0answers
58 views

Oracle scheduler runs only once

I am Oracle 11g User and made a JOB with follow SQL. begin SYS.DBMS_SCHEDULER.create_job( job_name => 'sync_trx' , job_type => 'PLSQL_BLOCK' , job_action => 'begin mv_trx_data(); end;'...
0
votes
0answers
44 views

PL/SQL follow a Item through a database

Hey I need some help creating a SQL statement for an Oracle DB. The situation: I want to follow a special item through two stations (UWA and KWA) with several steps where it may (!) get cut in up to ...
0
votes
0answers
144 views

How to generate a JSON file from stored procedure(SQL) result

I want to get the data from DB using stored procedures in the form of a JSON file. In simple words, my output should be a JSON file which should be the result of data in DB (collected by a stored ...
0
votes
0answers
142 views

Getting milliseconds elapsed during a stored PL/SQL procedure

I've been using sysdate to time procedures, but a lot of my procedures are very small procedures that get called thousands of times. Is there a way to get milliseconds elapsed instead of seconds? I ...
0
votes
0answers
184 views

ORA-00933: SQL command not properly ended from function

I created a function but I keep getting the errors ORA-00933: SQL command not properly ended ORA-06512: at "FCUBSPROD.FN_GSS_UNPROCESSED_TXNS", line 110 ORA-06512: at line 11 when I try to run it. ...
0
votes
0answers
24 views

FACT table loading

I have the below table structure : Ticket# Tckt_type Amount Acct Num 1 abc 10 1001 1 def 20 1002 So, for the same ticket number, there ...
0
votes
0answers
184 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
0answers
102 views

how to update 'update trigger fired record's column?

I'm new on this task. I tried to update when I/F table insert after trigger. but it wasn't work.. There is my trigger source like.. create trigger T_ATABLE_I after insert on ATABLE for each row .....
0
votes
0answers
134 views

How to cast as type of a table column?

I have multiple views with union like this: select column1, column2 from mytable union select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable But later I change the size ...
0
votes
0answers
441 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ....
0
votes
0answers
404 views

Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I ...
0
votes
0answers
230 views

Inserting into a table and getting the column names from a collection in PL/SQL

I want to copy data from one table to another and would like to have a rather dynamic insert statement. I have the column names I want to insert into in a collection col_names and was wondering if it'...
0
votes
0answers
3k views

PL/SQL Error bad bind variable

Where is the problem with my code. It should work, but Compiler says: Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,25)...
0
votes
0answers
81 views

In PL/SQL what is the usage of row level and column level attributes?

I am new to PL/SQL... What is the usage of row level and column level attributes? Can any one tell me why these are using to access columns of the table? Explain with an example please..
0
votes
0answers
4k views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
0
votes
0answers
225 views

Running DBMS_AUDIT_MGMT package under other user than SYS

I want to run DBMS_AUDIT_MGMT package under user CENTRUMADMIN. But error occurs even when I try to run this part of code: BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT....
0
votes
0answers
376 views

How to startup a oracle database with sql plus or with any developer tool without connecting to the Database

How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled. then I tried to connect my ...
0
votes
0answers
225 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
0answers
140 views

My stored procedures often get invalidated

The reason for invalidation should be because of some DDL activity, but I don't have any DDL in the SPs. I need to find what is that DDL statement. If the object is in an invalid state, when you call ...
0
votes
0answers
92 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
0answers
512 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
0answers
54 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
0answers
457 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
59 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 ...
0
votes
0answers
59 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 ...
0
votes
0answers
384 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 ...
0
votes
0answers
498 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 ...