select sys.database_name,sys.sysevent,sys.login_user from dual; what is sys in this query? Can I see the other functions that are in sys? What is the query for doing so? In what situations is this sys useful? Where will be the information about sys is stored in database? ...
I have a variable called c_kilometers. I have a cursor that grabs a bunch of records that have those kilometers. I need to run two separate SELECT statements in the cursor that simply grab a kilometer from one table based on values in the cursor, and run another SELECT doing the same thing on another table. SELECT t.kilometers INTO c_ki...
Is it possible to read/write data from/to a file in a PL/SQL block without using the UTL_FILE package in Oracle 10g? I currently have a file containing a set of primary keys (approx. 28000) that I need query a table for additional data that needs to be written out to a file. The schema I am using is very restricted and do not have any c...
I'm still learning some of the PL/SQL differences, so this may be an easy question, but... here goes. I have a cursor which grabs a bunch of records with multiple fields. I then run two separate SELECT statements in a LOOP from the cursor results to grab some distances and calculate those distances. These work perfectly. When I go to u...
Is there any pl/sql package which is already written to handle all the scenearios which prevents SQL Injection.Please let me know if any one aware such package. Thanks for your help ...
Is it possible to dynamically reference the :NEW/OLD pseudo records, or copy them? I'm doing a audit trigger for a very wide table, so would like to avoid having separate triggers for insert/delete/update. When updating/inserting I want to record the :NEW values in the audit table, when deleting I want to record the :OLD values. ...
Is there any way to access a dynamically accessing member of a User- defined record, object or reference cursor using a variable? E.g. Something like get_member(my_object, 'member name'); Or maybe my_object.$'member name'; EXECUTE IMMEDIATE won't work as it does not operate within the scope of my procedure. Let me brifly expla...
As the title says, I need a method of flattening multiple rows into a one luine output per account. For example table looks like this: Account Transaction 12345678 ABC 12345678 DEF 12346578 GHI 67891011 ABC 67891011 JKL I need the output to be: 12345678|ABC|DEF|GHI 67891011|ABC|JKL The amount of transactions ...
I work in a company where PL/SQL is the ONLY language used for coding. We also use a bit of Oracle forms and reports for front end. They tend to force us to get more domain knowledge (financial) rather than work on improving our programming skills. I am also working in support. I have worked for almost 8 months now and the programming ...
Hello. I'm trying to make a report but I'm having problems with my archi nemesis SQL. I have a table where the close date of a transaction is stored. I want to know how many transaction per month there was so I did: SELECT trunct( closedate, 'MONTH' ) FROM MY_TRANSACTIONS I'm using oracle. I'm getting a list like this: 2002-0...
I'm trying to format a numeric value always in the format "9 999,99" (notice the space). The problem is that oracle does not provide me a a numeric mask for getting this format. The closest I get is with to_char(value,'99990,99'), But no way of putting that space after the third digit. some examples of what I would like to get 1345....
I have a query roughly like this: select * from A_TABLE where A_COLUMN = '&aVariable' union select * from A_TABLE where B_COLUMN = '&aVariable'; But when I run it, SQL Developer prompts me for the variable twice, even though it's the same variable. If there's a way to make it prompt only once for a variable that is ...
Does anybody know how to retrieve the values of <ZIPCODE> and <CITY> using PL/SQL? I have followed a tutorial over the net, however, it can retrieve the element names, but not their values. Any of you know what seems to be the problem? I have already consulted Google (the internet's well kept secret) over this but no luck :( <Zipcodes>...
In PL/SQL,I would like to pass a source as well as the target schema as a parameter to a stored procedure. For source we can use: PROCEDURE select_from_schema( the_schema VARCHAR2) IS TYPE my_cursor_type IS REF CURSOR; my_cursor my_cursor_type; BEGIN OPEN my_cursor FOR 'SELECT my_field FROM '||the_schema||'.my_table'; -- Do y...
I have some PLSQL code that calls a remote procedure in order to send an XMLType. What it does, though, is to devide the XMLType into multiple varchar2(4000) parameters, which the procedure accepts. The remote procedure is called via a DBLink. Why does the XMLType have to be split? Is this restriction applicable to recent database versi...
I'm working on a package with some procedures in them and I'm running into a bit of trouble. When I try to test the procedure to convert gallons to liters or the other procedures, it just prints out what was declared in the unnamed block instead of converting the numbers. Any ideas? CREATE OR REPLACE PACKAGE eng_metric IS PROCEDURE ...
How can I define Collections in PL/SQL with variables? Eg. v_owner varchar(128) := 'SCHEMA_USER'; v_tablename varchar(128) := 'TABLENAME'; TYPE t_tab IS TABLE OF SCHEMA_USER.TABLENAME%ROWTYPE; v_tab t_tab; What I want is to use the variables instead of the names of the owner/table. Something like that: TYPE t_tab IS TABLE OF v_owne...
I understand that database views are read-only, or at least read-only by default. Is it possible to enable the change of data brought by an oracle view? Rephrasing: If I make a view of just one table, just to hide some columns, will changes to this data be updated on the table? ...
I have the following two PL/SQL Oracle Queries that return a count: SELECT count(*) INTO counter_for_x FROM Table_Name WHERE Column_Name = 'X'; SELECT count(*) INTO counter_for_y FROM Table_Name WHERE Column_Name = 'Y'; Is it possible to write a single query that returns both the counts and populates the respective counter ...
Hi all, I need help on executing archived log files in the database, instead of inserting each of the logs in the execute statement below one by one is there a way i can include all the logs at once in one single statement in a script? EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50662_581101112.arc' ,OPTIONS => DBMS_LOG...