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
888 views

Oracle Triggers - Before Update and Before Insert - Separate or Combined?

I'm running Oracle DB 9i. I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that ...
0
votes
1answer
444 views

Migrating stored procedure from Sybase to Oracle having temporary tables

I am migrating a few stored procedures from Sybase to Oracle using Oracle Developer tool. The tool migrates alomost 70%-80% of the stored procedures except a few cases where there is a temporary table ...
0
votes
1answer
466 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
0
votes
1answer
63 views

Specific issue on data pump API in oracle

I have a client/server architecture. Using an Oracle dbms on the database server side. I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that ...
0
votes
2answers
228 views

How to reuse code in Oracle Script?

I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist. declare ...
0
votes
1answer
2k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
0
votes
1answer
220 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
223 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
2answers
104 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
1answer
282 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
1answer
139 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
65 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 ...
0
votes
1answer
1k 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 ...
0
votes
1answer
3k 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. ...
0
votes
2answers
3k views

How to forcibly flush the undo log/tablespace of UNDOTBS1

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
0
votes
1answer
1k views

Error in trigger: table is mutating

CREATE OR REPLACE TRIGGER "TRANSACTION" before update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD for each row begin declare mportfolioname varchar2(20); ...
0
votes
1answer
79 views

Combine multiple queries into single query

I am trying to run the following query: SELECT Freight.Freight_No, Freight.Available, Locomotive.Loco_No, Locomotive.Available, Driver.Driver_No, Driver.Name, ...
0
votes
1answer
442 views

PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows. 3200000 rows are involved in the update, and ...
0
votes
1answer
263 views

Invalid rowid error

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations. declare cursor update_lock is select empno from emp where deptno=&no for update of sal; num ...
0
votes
1answer
722 views

Massive update to Clob fields oracle 10g

I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL? For example, in table t1 there is a clob field called VBase that needs to have the ...
0
votes
1answer
1k views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
0
votes
1answer
3k views

Split CLOB into multiple VARCHAR2

I currently am working with some data that is being stored as a CLOB between 4000 and 6000 characters. I would like to perform the TRANSLATE function on it but I learned that it's not possible with a ...
0
votes
2answers
2k views

How do I manage column order when using PL/SQL's %ROWTYPE records?

I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like: DECLARE TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE; l_data ...
0
votes
0answers
16 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
67 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
21 views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
0
votes
0answers
24 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
28 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
1answer
85 views

Oracle Package in Data Dictionary that can't be dropped

I've encountered a very interesting problem that I can't figure out how to fix. I have a package that I wanted to create on my database with a wrapped body. I mistakenly omitted the word "BODY" from ...
0
votes
1answer
57 views

Rows to columns

I am new to Oracle. I know MySQL. I wrote below query in MySQL: Tables: CREATE TABLE agl_asset ( asset_id BIGINT(20) NOT NULL, caption VARCHAR(64) DEFAULT NULL, description VARCHAR(4000) ...
0
votes
0answers
119 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
2answers
67 views

ORA-01410 Error

I am getting ORA-01410 and ORA-06512 for the following block of code in a trigger im firing for deletion of a record in my table. Are the calls correct ? getlong ('xxx', 'YYY', :OLD.ROWID), getlong ...
0
votes
0answers
79 views

Oracle SQL Loader - BULK UPDATES

Can SQL loader be used to do bulk updates? I have a table that holds customer information. The table has a primary key and it has 5 columns pertaining to the customer. Now we created three more new ...
0
votes
2answers
126 views

Split a string in different columns on basis of no of spaces

i want to split a string in different columns in the following manner through oracle Input String: hello how are you doing Output: hello how, how are, are you, you doing please help guys.. !! ...
0
votes
0answers
99 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
77 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 ...
0
votes
1answer
35 views

Writing a trigger for every time a user logs in

I want to write a trigger which runs every time a user logs in and saves the name of each user, for example. I have written trigger in this way: create or replace TRIGGER LOGON_TRG AFTER LOGON ON ...
0
votes
0answers
757 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' ...
0
votes
0answers
23 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
42 views

plsql script continue excution even there ar promt statement

I am facing new problem in PL/SQL. I programmed a 200 line script, it appears there is nothing wrong. It's weird because Oracle does not give you an error message: EXAMPLE : I am using in the script ...
0
votes
0answers
134 views

Oracle Calling a Procedure Returning a CURSOR in another Procedure

I have 2 packages and a procedures that return a sysref cursor. Now i want to call either of the 2 packages and procedures in another package and procedure that will take the decision on an input ...
0
votes
1answer
292 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
0answers
1k 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
1answer
100 views

Insert Into does not work with multiple rows

Here Is my code : CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER ...
0
votes
0answers
77 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 => ...
0
votes
0answers
162 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
107 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
58 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
64 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
1answer
35 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;