Oracle Database

78K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

hi,

any one has any report on the Least vulnerability database report, which one give the Least vulnerability ?
0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I am facing this error

PLS-00225: subprogram or cursor 'GET_FILES_CUR' reference is out of scope



CREATE OR REPLACE PROCEDURE INSERT_DATA_TO_FILE IS

  LETTER_DATA VMS_FILES.LETTER_FILE%TYPE;
  DECLRATION_DATA VMS_FILES.DECLARATION_FORM%TYPE;
  HEALTH_INSURANCE_DATA VMS_FILES.HEALTH_INSURANCE_FORM%TYPE;
  FILE_EXTENSION VMS_FILES.LETTER_FILE_EXTENSION%TYPE;
  APPLICATION_ID VMS_FILES.APPLICATION_ID%TYPE;

  CURSOR GET_FILES_CUR IS
    SELECT APPLICATION_ID,LETTER_FILE,DECLARATION_FORM,
      HEALTH_INSURANCE_FORM,LETTER_FILE_EXTENSION FROM VMS_FILES;
  BEGIN
     OPEN GET_FILES_CUR;
     LOOP
         FETCH GET_FILES_CUR.APPLICATION_ID INTO APPLICATION_ID;
     
     END LOOP ;
    CLOSE GET_FILES_CUR;
  END;
0
I have the following code which the guys at EE gave me and it is undoubtedly helped me learn and grow in knowledge. I actually understand a good bit.
The data is querying perfectly. I noticed that some of the tables are large and retrieving 100's of mb when running the tables and it occured to me since some of the tables have for instance have a JOB_ID as a primary field or TIME_CARD_ID as primary I thought ..... can I limit or filter the records more in the existing query below by showing only records related to a specific field WHERE JOB_ID = 8083 or WHERE TIME_CARD_ID = 26947.  JOB_ID for instance is in all the JOB tables listed below so I was thinking and hoping this would lower the amount of records I returned?

My original thought was to simply put the WHERE clause (at the end of code line seen as) Select * From JAS.JOBS  WHERE JOB_ID = 8083  ;  "That did not work?"

Then I tried the where clause at the bottom
"where owner ='JAS' and table_name like 'JOB%' and field_name like 'JOB_ID' and JOB_ID = 8083" .........  thinking that logic might work. Nope?
I just wonder if this is possible and if so any thoughts appreciated.

Select * From JAS.JOBS  ;
Select * From JAS.JOB_BLOCKS  ;
Select * From JAS.JOB_CONTACTS  ;
Select * From JAS.JOB_CONTACT_DIVISIONS ;
Select * From JAS.JOB_CPR_CLASSES ;
Select * From JAS.JOB_DETAILS ;
Select * From JAS.JOB_DETAIL_REC_TYPES  ;
Select * From JAS.JOB_EXPENSES  ;
Select * From JAS.JOB_GROUPS  ;
Select * From 

Open in new window

0
Hello

I have a table which has membership data in it. I'd like to be able to run a query against it that will only return member_ids which only have an end date.

Here is the table structure:

MBRATTRVAL_ID  NUMBER
STOREENT_ID    INTEGER
MEMBER_ID      NUMBER
ATTRTYPE_ID    VARCHAR2
MBRATTR_ID     NUMBER
FLOATVALUE     NUMBER
INTEGERVALUE   INTEGER
STRINGVALUE    VARCHAR2
DATETIMEVALUE  TIMESTAMP

The only unique identifier for each record is a member_id. Here is an example:

MBRATTRVAL_ID  STOREENT_ID  MEMBER_ID  ATTRTYPE_ID  MBRATTR_ID  FLOATVALUE  INTEGERVALUE  STRINGVALUE        DATETIMEVALUE
0              NULL         123456789  STRING                1  NULL        NULL          1100123456789
1              NULL         123456789  STRING                2                            free
3              NULL         123456789  DATETIME              3                                               1/1/2013 12:00:00.000000 AM
4              NULL         123456789  DATETIME              4                                               12/31/2323 11:59:59.000000 PM
2              NULL         123456789  STRING                5                            0
9              NULL         987654321  DATETIME              4                                               12/31/2007 11:59:59.000000 PM
10             NULL         567898765  STRING                1  NULL        NULL          1100987654321
11             NULL         567898765  STRING                

Open in new window

0
Hi Team,

Iam trying to encrypt the password from the user tables using the
DBMS_OBFUSCATION_TOOL_KIT.md5(input_STRING =>UTL_118N.STRING_TO_RAW('REC.PASSWORD'));

Iam using cursor for loop to process each record in the table , hence iam passing the field as rec.password.


But is generating the same value for all the records in the user table.

Any help is really appreciated.

USers tables

userid   username password
1             sam            sam0001
2             michael      michael002
0
Hi All,

I'm not sure how to do data formatting using a SQL Statement in Oracle database.

Is it possible to have output from a SQL statement appear like (see attached) or in an excel format using a pivot table?
EE-Question.xlsx
0
Hi,
I am working on a report using oracle report builder.
There is a totals section on the report which I want on a separate page, so I have page break before this field. I don't want header to be printed on this part of the report.
 I've tried looking online and experimented with a few things but the issue seems to be that the report doesn't know what the last page is so if you try to make that header print all but last page it returns an error.
0
I have two databases running in my system , pdborcl and remotedb . These are two publisher databases running in my server.

I have created a public database link from PDBORCL to connect to remotedb by username sam.

I have an table called emp in sam  schema in remotedb, I have created a public synonym on this table from pdborcl db in hr schema.

Iam able to perform all the DML and select operations on the remote table from  hr schema without giving any permissions . How is it possible . Can anyone explain me how this is working.
0
I am trying to write a query in oracle for sales vs received per day of the same item.

Basically I have two identical tables with the exception of one column - one tables shows sales per day, the other shows received per day.

I need to write a report showing the sales AND received per day on the same line, even if one or the other is zero.

How do I write this particular query?
0
I tried to export 11G table and want to import that table but getting error.
On 11G database I have used below command:
exp SCHEMA1/password@DB1File=HISTORYDATA.dmp log=HISTORYDATA.log tables=(HISTORYDATA)

AND Tried to import that on 9i database using below command but getting error.
imp SCHEMA2/Password@DB2File=HISTORYDATA.dmp FromUser=Schema1 ToUser=Schema2 tables=(HISTORYDATA)
and error is Not a vaild export file, header failed verification

 Please help me to give solution.
0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hi,

Recently someone truncated all our tables in a particular schema in an Oracle DB.  The infrasture team have restored the server to a temp server in order for me to copy off the files that I need.  

What's the best way to do this?  Copy each table, view, sequence one by one.

Export to a dump file
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log


If I import a dumpfile will it just overwrite the schema I have on production?


Any help would be greatly appeciated.

Kind regards,
Caoimhe
0
Hi I am using 11G oracle database. I have imported big history in my table using  clob datatype but now I want only 4000varchar so I can show those 4000 char, I am trying to cast that to 4000 char and want to insert in new table but its not working, How I can do that.
0
acctname varchar2(25);
amount Number(18,8);

the above data types i have to increase size as below
test table having the data for the both columns
when i am doing alter acctname is suceeded.
second alter is failed to modify

what is the difference in the below two alter commands
how to Alter second statement of amount column if it is having data.

Alter test modify acctname varchar2(50);
Alter test modify amount Number(18,10);
0
In Microsoft SQL Server I can execute any query as any other user as long as impersonation rights have previously been granted. To clarify by example.

Consider I have the following connection string
Provider=MSOLEDBSQL;Data Source=SQLSERVER;Initial Catalog=SANDBOX;User ID=TestUser;Password=BADPASS;

Open in new window

Now consider I'm executing the following code
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
	conn.Open();
	using (OleDbCommand cmd = new OleDbCommand(string.Empty, conn))
	{
		cmd.CommandText = "INSERT INTO [USERTEST] ([USERNAME], [TIMESTAMP]) VALUES (CURRENT_USER, GETDATE())";
		cmd.ExecuteNonQuery();
	}
	conn.Close();
}

Open in new window

Simply enough, this will insert a row into the table named USERTEST in the default schema of user TestUser. The values will be ("TestUser", GETDATE()).
Now, if I modify the code like this:
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
	conn.Open();
	using (OleDbCommand cmd = new OleDbCommand(string.Empty, conn))
	{
		cmd.CommandText = $"EXECUTE AS USER = 'Fred'";
		cmd.ExecuteNonQuery();
		cmd.CommandText = "INSERT INTO [USERTEST] ([USERNAME], [TIMESTAMP]) VALUES (CURRENT_USER, GETDATE())";
		cmd.ExecuteNonQuery();
		cmd.CommandText = $"REVERT";
		cmd.ExecuteNonQuery();
	}
	conn.Close();
}

Open in new window

It will insert a row into the table named USERTEST in the default schema of user Fred. The values will be ("Fred", GETDATE()).
Essentially, this behaves exactly the way it would if I had replaced the User ID in the connection string with "Fred" (assuming I had the proper login credentials).

I know Oracle has the following command:
alter session set current_schema = <username>;

Open in new window

However, this only alters the target schema. Executing "SELECT USER FROM DUAL" still returns "TestUser". Is there a way in Oracle to duplicate the behavior that SQL Server has here?
0
We are trying to get "missing rows" from our fact table and, in the end, join them to our dimension table.

Trying the approach here: https://www.experts-exchange.com/questions/27968510/Oracle-11g-How-to-insert-missing-rows-in-to-a-sequence-based-on-a-date-and-a-group.html
proved way too resource intensive and for a single row takes over an hour to return.

The fact table has 12 Million records and the dimension table has 1200 or so.  The issue we are facing is that there are not enough dimensions in the dimension table to order by it alone and use a LEAD/LAG approach to fill in the missing data with an outer join.

The fact table has orders in it, by date and interval.  One date has to have 24 intervals (hourly) for the dimension table to lookup.  So 21 = X, 20 = Y, etc.

Without having the fact table populated from a vendor API with all 24 periods of time (intervals), we cannot get a full set of data which is causing issues downstream.

We always need every interval for every day, although, some intervals come in at 96 (fifteen minute periods of time).  This is where we need to get the data back with a full 24 or 96 records, join to the dimension table and get the accurate flag associated with the record.

Same issue as described above in the link, just a small twist on it.

Any suggestions would be greatly appreciated.

Thanks
0
Is there a way to see how long an insert that is running is going to take to finish.  I kicked off an insert two hours ago.  It's there any way to see how long it will take before it finishes.

Oracle DB
0
Based on the question and response in the LINK below. I had an Expert be so kind as to help me with what I need. So I have been working the last 2 hours on how to embed this inside of the existing code in order to get the desired result seen here.
Blob fields and how to manage them when they throw an error.
I need to embed the code that I think identifies the type of data (DATA_TYPE) to search for. My concern is where do I embed it? IF there are rules somewhere or "best practices" on the when and where rules on how to embed code so that it runs in the right order without throwing error messages. If anyone knows I am all ears...?

[b][i]
/*IF THIS IS THE CORRECT CONTENT I NEED TO INSERT .... NOT 100% SURE?*/
CASE 
WHEN main.DATA_TYPE = 'VARCHAR2' 
	AND val.IsValueNumeric = 0 
		THEN main.COLUMN_NAME || ' = ''' || val.SearchValue || ''''
               
WHEN main.DATA_TYPE = 'NUMBER' 
	AND val.IsValueNumeric = 1 
		THEN main.COLUMN_NAME || ' = ' || val.SearchValue  
        ELSE ''
END

/* I think this statement has to go in the WHERE clause in the Second Section.
AND   main.DATA_TYPE IN ( 'VARCHAR2','NUMBER' )[/i][/b]

*/ Information above needs to go into this Second Section of code below*/

Select * From JAS.JOBS  ;
Select * From JAS.JOB_BLOCKS  ;

Open in new window

0
I get the following errors a lot when searching for records in the Oracle SQL database with PL/SQL Developer.
Not sure where to even start with 5 different error codes. Does this mean 5 separate issues to correct before the code can do its job?
What happens often when searching for a record
I looked up blob field. It sounds like I need to address some issue before searching for a word like ' Clean Up '. I do not know if PL/SQL Developer does not like the space between the words or if the tables themselves have to have a special modification for Blob fields. I know that they are like large Comment fields with large numbers of character spaces. 500-1000+
Any thoughts on the error?
Error Stack Message : /* Source of PACKAGE BODY SYS.DBMS_XMLGEN is not available */

Select * From JAS.ITEMS ;
Select * From JAS.ITEM_CHARGES  ;
Select * From JAS.ITEM_CHARGE_SETUP ;
Select * From JAS.ITEM_COMMISSIONS  ;
Select * From JAS.ITEM_COSTS  ;
Select * From JAS.ITEM_DIMENSIONS ;
Select * From JAS.ITEM_DIMENSION_PACKAGES ;
Select * From JAS.ITEM_LABOUR_GROUPS  ;
Select * From JAS.ITEM_LAB_RATES_CODES_FORMULAS ;
Select * From JAS.ITEM_MANUFACTURER_DESCRIPTIONS  ;
Select * From JAS.ITEM_TYPES  ;
Select * From JAS.ITEM_TYPE_FORMULAS  ;
Select * From JAS.ITEM_TYPE_GROUPS  ;
Select * From JAS.ITEM_TYPE_LABOUR_PERCENT  ;
Select * From JAS.ITEM_TYPE_PROPERTIES  ;
Select * From JAS.ITEM_USAGE  ;
Select * From JAS.I_CATEGORIES  ;
Select * From JAS.I_DEPTHS  ;
Select * From JAS.I_DIMENSIONS  ;
Select * From 

Open in new window

0
If the following code allows me to create a variable to search a range of the listed tables that start with some specific character(s) Example ITEM_
I am searching through 3 options for the best solution. Option:
1. The variable prompt '&TABLE_TO_FIND%' Is there a value like ' * ' the asterisk which would allow me to search all of the listed tables?  Like the term "All_tab_cols" when looking at a tables columns? is there an ALL_TABLES when searching with a Substitution Variable that points to the tables?
2. I could put the values in manually but it is time consuming and a constant state of back and forth of copy paste.
3. Would be to have all the tables in the Query Code (See partial portion below) and have the PL/SQL Developer Tool select only the tables I need ignoring the rest. Which I think is the Oracle Bind Variable?  I think? My problem is figuring out all the terms that make a Bind Variable up and how it will relate back to what I need. Any examples would be helpful.

Select * From JAS.ITEMS ;
Select * From JAS.ITEM_CHARGES  ;
Select * From JAS.ITEM_CHARGE_SETUP ;
Select * From JAS.ITEM_COMMISSIONS  ;
Select * From JAS.ITEM_COSTS  ;
Select * From JAS.ITEM_DIMENSIONS ;
Select * From JAS.ITEM_DIMENSION_PACKAGES ;
Select * From JAS.ITEM_LABOUR_GROUPS  ;
Select * From JAS.ITEM_LAB_RATES_CODES_FORMULAS ;
Select * From JAS.ITEM_MANUFACTURER_DESCRIPTIONS  ;
Select * 

Open in new window

0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hi,

  I am getting the below error when trying to open the database.   Please help me how might the right way to solve it.

  Please find the problem below.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 4343 change 10073170157523 time 05/03/2018
06:00:01
ORA-00312: online log 2 thread 1: 'E:\ORADATA\QMS01QA\REDO02.LOG'


SQL>
0
I dug for about an hour last night on the subject of SQLTERMs and their definitions for Oracle SQLSQL Server and a few others.

I noticed that some of the terms are generic then realized that some of the sites did not have even some of the basics. SELECT FROM WHERE  ORDERBY etc.

With that, I wondered if I was looking at terms and definitions of databases vs the CODE. So is there a really good site for finding out what specific code like functions or operations do in a SQL query?
This would be very helpful to me since I have been trying to breakdown many of the parts of the query and what each term means so I can decipher and create my own...?
Not this stuff. I understand this. https://raima.com/database-terminology/ 
What each type of code is and does....? Like Create Table or maybe it is insert table commands.

I figured you guys would know the best stuff. So rather than hope I hit a home run? I'll get on base and you can hit the home run.
0
What is Oracle's SQL standard?  Can someone point me to a web link?  Also, what is the current ANSI standard for SQL?
0
Installing the SQL*PLUS tool from Oracle. Downloaded two large files from the site.
My goal is to have this tool ready for when I get a local copy of the Oracle Database.
1. Do I have to Pay for SQL*PLUS eventually or is it free?
2. Do I have to have an account on the Oracle Website to use SQL*PLUS?
3. Do I have to have the driver and database on the machine I am currently working on to install it first?
4. It seems like I have to have an account first before I install the Oracle Configuration Manager which appears to be a prerequisite? Is that a payed for service?

Proper Account setup....Not sure what needs to be here?
I have been reading documentation such as the following but not sure if I am on the right track. Any advice is appreciated to help install.
https://docs.oracle.com/html/E14501_02/intro.htm#CEGFDGAJ
0
Trying to understand the Colors defining the syntax code in PL SQL. Does it highlight syntax in a way consistent with other SQL developers in the industry.
I would assume like other tools terms are similar. Like Functions are relatively consistent across the board SUM(Fields), AVG(Field) as are Operators ((+,-,=,/,*..etc)
I have used the word Color in the Help file and literally read every line of text that has the word 'COLOR' in it or the word 'RED' etc that has something to do with the syntax and only found one that said ...

Syntax Highlighting
This group of preferences control the syntax highlighting that is used in the editors. You can enable or
disable syntax highlighting, and define the style and color of keywords, comment, strings, numbers and
symbols (:=, =>, ||, and so on). For keywords you can additionally control if they should be converted to
uppercase, lowercase or capitalized. You can also define a custom syntax highlighting, and define the
words that should be highlighted. Pressing the Custom Keywords button will bring up an editor that
allows you to define these words.
The Keyword Case preferences controls how keywords are formatted when they are typed: Uppercase,
Lowercase or Init Caps.

I do know that Red identifies Removed and used as identifying comments not read by the Developer Tool obviously.

Is there a color code or color schema available for PL SQL? I have googled and looked at Help with nothing on the colors of the …
0
Hi Need to write change an regular expression to change the below text
Input string 'Jack and Jill'

and I need to change it to Jill and Jack.

Select regexp_replace('Jack and Jill','(\w).*(\w)','\2.*\1') from dual;

But it  is not working . Any help is really appreciated.
0

Oracle Database

78K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.