• UTL_RAW INSTR function? 2019-02-01

    I was surprised that the Oracle raw type has a SUBSTR function but no INSTR function. Is there a way to search for a sequence of bytes in a raw type as with the INSTR function in varchar2 types? --------------Solutions------------- Write raw to blob

    Tags: oracle, plsql
  • Does a nested table retain order while not being stored?

    Does a nested table retain order while not being stored? 2018-12-31

    I have a PLSQL function that populates and returns a nested table: select distinct id bulk collect into my_nested_table from user order by id; return my_nested_table; According to the docs nested tables are multisets and have no inherent ordering. Ca

    Tags: oracle, plsql, nested table
  • PL/SQL assinging values to a variable using cursor columns 2018-11-07

    I am trying to take some statistics against the Emp table, the create table & the rows inserted are given below. I am trying to develop a store procedure which will get all the columns for a particular table from oracle ALL_TAB_COLUMNS & I will ge

    Tags: oracle, plsql
  • Oracle 11gr2 error creating stored procedure with isolation level 2018-10-31

    I was used to MySql syntax, but now I'm trying to study Oracle. Let's take a look at the following stored procedure(using SQL Developer): CREATE OR REPLACE PROCEDURE myPROC(aux OUT NUMBER, par1 IN VARCHAR2, par2 IN VARCHAR2) AS BEGIN COMMIT; SET TRAN

    Tags: oracle, plsql
  • Need help solving Pl/sql issue 2018-09-27

    I am trying to fill a database with random data, but the primary key needs to still be unique. I can fill the database with random data, but now I am trying to solve the primary key issue. When running this code I get errors. set SERVEROUTPUT on crea

    Tags: plsql
  • Need Column name , old value and new value in Trigger 2018-02-26

    Our requirement is if any 1 of the column name in table is updating we need to insert the column name in another table so I had written this code CREATE OR REPLACE TRIGGER Test AFTER UPDATE ON XX_table FOR EACH Row BEGIN FOR C IN (SELECT column_name

    Tags: oracle, triggers, plsql, oracle11g
  • how can detect the JOB ? if its working on SYS? 2017-06-25

    There is a JOB working on the database , its inserting record on a table. I detect the job From the session( on the session its working on SYS). but I cannot find it on the database. I execute the below query on my schema user and the system user , s

    Tags: oracle, plsql
  • Updating a column in PL/SQL

    Updating a column in PL/SQL 2017-05-01

    (Using PL/SQL anonymous program block) I have a table tblROUTE2 of Mexican state highways: +-----------------+------------+---------+----------+----------+----------------------------+-----------+--------+ | TYPE | ADMN_CLASS | TOLL_RD | RTE_NUM1 | R

    Tags: sql, oracle, plsql
  • PLSQL procedure OUT param - Arrays vs Cursor 2016-12-06

    I want to read a OUT param of a PLSQL procedure, to get the list of usernames.[Just a list of names, each name is a String] This PLSQL procedure can return this Output in 2 ways: Return as Array [Table of RAW] a Cursor My question is , what is the be

    Tags: arrays, cursor, plsql
  • Inserting the text of a stored procedure into a record 2016-05-17

    How can I insert into a record the text of a procedure stored in ALL_SOURCE.TEXT ? This part of code gives me error: "missing comma" execute immediate ' insert into results(SrcProcedure) values(' '' || tabela_temporare(1).text || '' ')'; I think

    Tags: sql, oracle, stored procedures, plsql
  • Oracle Sys Refcurser in a function 2016-01-06

    I'm having some trouble returning a pointer to the query. Here is my current code: create or replace procedure getRoute(route IN varchar, routeday IN varchar) return sys_refcursor is v_rc sys_refcursor; begin select DISTINCT BBT_JOURNEYSTOPS.SERVICE

    Tags: sql, oracle, plsql, sys refcursor
  • Convert a String in Java to a VARCHAR IN SQL 2015-11-04

    I am trying to write PL/SQL in Java. I want to pass an argument as String, but in the SQL Procedure it is a VARCHAR(140). However it is giving me an error when I run the program and I don't understand why? This is my SQL PROCEDURE: CREATE OR REPLACE

    Tags: database, sql, java, plsql
  • How to insert duplicate record in the same table with only change the date to the next date 2015-03-17

    I have a requirement where i need to duplicate the present record with data column update to the next date. I have table data like below : cobdate system b_id b_type L_TYPE lode symbol ------------------------------------------------------------ 2015

    Tags: oracle, plsql, oracle11g
  • How to pass an array to a Oracle stored procedure using Hibernate? 2015-02-25

    I have a requirement for which I have to pass an array/arraylist of String values to an oracle stored procedure using hibernate. What are the ways of doing so?

    Tags: plsql, hibernate, arraylist
  • Executing PL/SQL functions from a file 2015-02-11

    I am using SQL*Plus and have a file called functions.sql, which contains 3 PL/SQL functions: create or replace function getcustnamebyid (id number) return varchar2(30) is ... begin ... end; / create or replace function getspcommbyid (id number) retur

    Tags: oracle, plsql, sqlplus
  • What is the best way to manage a large quantity of constants 2015-02-02

    I am currently working on a very complex program that processes rows from an input table and has a huge number of possible outcomes for each record. Because of this I have a very large number of constants defined for the outcome messages. There is on

    Tags: oracle, plsql
  • Execute immediate error in Oracle 2015-01-26

    I want to execute the following command through a cursor where I have column name and column values in a different table. However the script fails with following error message. ORA-00933: SQL command not properly ended execute immediate 'UPDATE EMP_D

    Tags: oracle, plsql, plsqldeveloper
  • convert to_char(..,'MON-YYYY') into sql server 2015-01-19

    I have the following query with plsql SELECT to_char(add_months(l_max_date, l_rec.MON_INTERVAL),'MON-YYYY') FROM dual I am looking to write it with sql sever select CONVERT(VARCHAR(2),MONTH(DATEADD(mm, MON_INTERVAL, l_max_date))) from rec How to writ

    Tags: sql server, plsql
  • How to convert clob data into date time format in pl/sql 2015-01-19

    I have written a code where I am extracting oracle table data from database into excel sheet and sending it as an attachment in mail. Problem is the date column of the table has been displayed as DD-Mon-YY format as I am storing data into clob. My re

    Tags: oracle, plsql
  • Using of schemavalidate returns an unexpected error 2015-01-07

    I experience something strange to me and I am open to any commentary helping me to solve my problem. I am using dbms_xmldom to create xml file in PL/SQL. Let's say I have created such a document named "doc" with this. I write it down to a file with t

    Tags: database, xml, oracle, plsql

Copyright (C), All Rights Reserved.

processed in 3.057 (s). 9 q(s)