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
601 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
387 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
360 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
201 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
166 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
197 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
97 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
252 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
136 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
64 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
2k 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
78 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
430 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
259 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
712 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
998 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
20 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
25 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
59 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
0answers
46 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
96 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
62 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
62 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
94 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
66 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
65 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
34 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
609 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
19 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
41 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
124 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
223 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
91 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
65 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
141 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
96 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
54 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
62 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;
0
votes
1answer
262 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.
0
votes
0answers
217 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
47 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", ...