Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [plsql]

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.

0
votes
1answer
16 views

Disable Oracle substitution parsing

I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code. The update script contains the following string: c===l&&p(a,b) which when executed from "...
0
votes
1answer
29 views

Can someone help me with this PL/SQL block?

Write a PL/SQL block to display the details of five employees who are getting the highest salary (table name is Employee) Is this how you do it?? DECLARE Cursor C is select * from Employee ORDER BY ...
-1
votes
0answers
27 views

Oracle loop all columns in all tables in a specific schema and count not null rows

I have a number of databases where I need to loop all columns in all tables to find not null values in each column. For example: Input: +----+---------+---------+---------+---------+ | ID | ...
0
votes
0answers
13 views

Extract table data in a BLOB field

I am trying to extract data from tables in a delimiter separated value format. To extract data i am concatenating all the columns of the table with each other and then converting the concatenated ...
0
votes
2answers
70 views

“Fire” a trigger to populate columns that are NOT NULL?

Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table. The trigger should “fire” before an INSERT statement. The trigger will populate all the ...
1
vote
1answer
60 views

If then else case PL/SQL if text is in list

I have a column that will return something like this: 01 05 Z7, Z4, 01 09,08 I then have two static lists: list a = [01, 02, 03, 04] list b = [Z7, Z2, 05] and our case statement returns one of the ...
3
votes
4answers
96 views

I don't know how to fix ORA-04091 mutating trigger error

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table. The error is: error ORA-04091: table USUARIO.LINEAS is mutating, ...
0
votes
1answer
84 views

Not all variables bound ORA-01008: not all variables bound error

I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, ...
0
votes
1answer
24 views

Oracle - SQL ID from which procedure

I found few SQL IDs in AWR report. How to know, from which PLSQL object is it getting called based on sql id? As AWR removes extra spaces, user_source will not work.
0
votes
0answers
42 views

Oracle 10.2 unable to dynamically execute DBA command

I would like to execute the analyze index command for every user index within a database in order to populate the index_stats view in order to detect fragmented indexes by (DEL_LF_ROWS/LF_ROWS). The ...
0
votes
2answers
57 views

Sequence of columns displayed in output by sproc

CREATE OR REPLACE PROCEDURE test_1 ( p_table_owner IN VARCHAR2, p_table_name IN VARCHAR2, p_result OUT SYS_REFCURSOR ) AS BEGIN OPEN p_result FOR SELECT index_name, ...
0
votes
1answer
33 views

Why can i not call COUNT on a varray column in a select statement using oracle

I have a simple array type definition like this CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS VARRAY(10) OF VARCHAR2 (100); Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1 ...
0
votes
1answer
28 views

Access tables from multiple schemas

I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles ...
0
votes
1answer
82 views

Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors. The query below informs you that ...
0
votes
1answer
44 views

Oracle PL/SQL find nearest object

There are two tables containing objects and their coordinates. One contains hotels, the other contains restaurants. How to build a table that for each hotel lists nearest restaurant name and the ...
1
vote
1answer
94 views

perform multiple conditional join on multiple tables

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') ...
-4
votes
2answers
63 views

Space usage procedure oracle for an input table [closed]

Is there any space usage display procedure in oracle for an input table like sp_space_used in sql server...
0
votes
1answer
21 views

Store Function Value into temporary table

Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those ...
0
votes
1answer
57 views

Syntax error in PL SQL [closed]

i'm trying to do a command that gather stats of tables that has more than 10 million rows. Right now only one table has, but i want the command to execute the gathering to more tables as soon as they ...
0
votes
1answer
40 views

Oracle schema object name when fire trigger

I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is ...
0
votes
1answer
33 views

What slows down this histogram query?

I have a table my_link_t with columns t.weight type number and categorical speedcat type integer values from 2 to 8. I want to split data in buckets from min=0, max=0.6, step = 0.001 and build a 3D ...
1
vote
1answer
32 views

Is there any other way to replace cursor for the following scenario

Table IA Id Status Details 1 open aaa 2 close bbb 3 open ccc 4 open ddd 5 open eeee 6 open ffff 7 open gggg 8 close iii 9 close hhh 10 ...
0
votes
1answer
77 views

Extract values from oracle pl sql cursor

I have a function that returns a cursor. When I execute select t.id, t.f_rc(id) from my_table t I get id F_RC(TID) 1 < cursor > 2 < cursor > 3 < cursor > ... How to expand ...
0
votes
1answer
73 views

Custom return type of a function in Oracle PL SQl

I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
0
votes
1answer
55 views

How to make a function from a concrete SELECT statement in Oracle SQL?

How to replace a concrete value with a variable in Oracle SQL? I have select 5, min(id) from my_table where id > 5 --AND ... complex condition UNION ALL select 6, min(id) from my_table ...
1
vote
0answers
46 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
0
votes
1answer
72 views

Updating Database table from another machine

I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on ...
1
vote
3answers
142 views

PL/SQL date or to_char question

begin if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN DBMS_OUTPUT.PUT_LINE('FRIDAY'); else DBMS_OUTPUT.PUT_LINE('Not Friday'); end if; end; / I am trying to ...
1
vote
2answers
35 views

Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL

I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle. In SQL Server if I create a ...
0
votes
1answer
1k views

is it possible to use utl_file_dir = '*' scope = spfile in 12c

Oracle 12c has deprecated utl_file_dir = '*' scope = spfile, but I need to use this functionality. Is there any option to do it? Is it possible to give permission using Java classes?
0
votes
1answer
32 views

Variable Assignment and Usage in Oracle

Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same? DECLARE v_pNbr NUMBER := 100150; begin select * from TARGETTABLE ...
1
vote
2answers
91 views

PL/SQL - Member Function: Link directly to object without parameters

I want a table generated from a table function in oracle. Like this: +--------------------------------------------------------------------+ | CUSTOMERS ...
0
votes
2answers
90 views

read/write/execute to pl/sql grants [closed]

For a current task and future references, I am wondering how pl/sql grants (select, insert, update, delete, references, alter, index, all) are categorized from a read/write/execute or read/read-write/...
0
votes
1answer
522 views

oracle sql: How to not select select duplicate records from table

Can any help me to fix the below sql query SELECT DISTINCT ORDER_NUMBER, FLAG FROM TABLE Below was the result Order_number FLAG LP-13288 false LP-13288 true LP-13292 false LP-13290 ...
2
votes
0answers
419 views

Changing a JSON object parsed with APEX_JSON

I would like to be able to change a JSON object parsed with the APEX_JSON.parse() function, but I found nothing in the official documentation, no tips or examples about that. In the code below I ...
0
votes
2answers
2k views

Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row? [closed]

I have Table A with 1 row and wanted to update it from another table (which has 3 rows for it and I can take any one of them). Table A ----------- TrnID | trn_Status 2345 | null 9876 | null 9878 | ...
2
votes
0answers
1k views

ORA-01031: insufficient privileges solution oracle

Create or replace procedure myprocedure is stmt varchar2(1000); stmt2 varchar2(1000); begin stmt := 'create global temporary table temp(id number(10))'; execute immediate stmt; ...
2
votes
2answers
254 views

Execute sql file in Exception statement

Hi I'm trying to execute an SQL File in a Exception Statement when I'm executing an Spool file. I have something like this: column dt new_value _dt select to_char(sysdate,'yyyymmdd_hh24mi') dt from ...
0
votes
2answers
80 views

Retrieve date column default values from BINARYDEFVAL column in sys.ecol$

I am trying to retrieve BLOB values in BINARYDEFVAL column from sys.col$ table. The BINARYDEFVAL of sys.ecol$ table stores default column values in BLOB format. For VARCHAR2 and NUMBER I am able to ...
0
votes
0answers
32 views

Grant in advance privileges on Schema A for dynamically created tables on Schema B in oracle

I have the following scenario in my Oracle Database (11g) and I am trying to work around grants and access privileges. Note that the following is just a Minimum Viable Example and it does not cover ...
1
vote
1answer
35 views

PL/SQL Loop Returning One Result

I'm working with the following script: create or replace PROCEDURE a_proc is vi_get_emails varchar2(500); cursor get_emails is SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl....
1
vote
2answers
54 views

I'm going to wrap database trigger's source code, command properly run but source not encrypt

I'm going to wrap my source of database logon trigger. i run the command, "wrap iname='E:\block_tools_trigger_HR.sql' oname='E:\block_tools_trigger_HR.plb'". plb file created without any error but ...
0
votes
1answer
356 views

How do I execute a PL/SQL package from my database SQL> prompt?

Sorry to ask such a basic question, but I am yet to do my Oracle course at work and I am already required to do some run PL/SQL processes. I have installed a 'package' using SQL Developer, see it ...
0
votes
1answer
23 views

Oracle 11g - PL/SQL block - Unable to use a newly added column in subsequent DML

begin execute immedate 'alter table my_table add new_column varchar2(30)'; update my_table set new_column=old_column; end; Here the update statement fails stating that the new_column is not ...
0
votes
1answer
518 views

how do you output a plsql array via ref cursor

I have a legacy procedure that returns a plsql assoc table to a C++ DLL. Now I need to call it via C# and ODP.NET. I have been unable to find any info on how to call a proc that returns a pl/sql ...
2
votes
2answers
256 views

Deploy materialized view with database link using another user

Our database deployment process logs in as sys to deploy database objects. We ran into a problem when we wanted to deploy a private database link for another user (AU). Our solution was to: Create ...
0
votes
1answer
412 views

create a backup table with million records

I want to move old records from TABLEA to TABLEA_AUDIT. TABLEA have around 1.5 million records and it have two nested tables. No of Records : 1557951 Size : 1024 MB I tried it by, 1.using ...
2
votes
1answer
44 views

User defined function can only have select statements

One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this? The ...
0
votes
3answers
1k views

creating trigger which will insert into table from view

I have view LIVE_MATCHES (home,away,comp_name) and table MATCHES (priority ,sport,sub_list) I need to create a trigger that inserts in TABLE from VIEW after the view is updated or inserted (doesn't ...
4
votes
2answers
55 views

Add column for subsequent values for a record

I have a table structured as follows: CREATE TABLE t ( HHID varchar2(1), INDIV_ID int, PRIM varchar2(1) ); insert into t values ('A', 10, 'Y'); insert into t values ('A',...