i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?
View 4 Replies
I've a 6 column report where col5 is set to expand vertically (based upon comments) and works wonderfully. Col6 is a single character column, therefore does not expand so the report looks rather messy.
I would like col6 to expand in accordance to col5. Is it possible?
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
I have the following piece of
CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';
CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;
[Code]..
However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?
in below program,i have to pass column name to cursor at runtime..but this giving error.
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record hr.employees%rowtYPE;
v_stmt_str VARCHAR2(200);
colname varchar2(10):='Last_name';
BEGIN
[code]....
dbms_output.put_line(emp_record);
*
ERROR at line 13:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
How can we pass column index or column name in cursor for loop.
View 3 Replies View Relatedi have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)
for example
create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');
then i got my function
create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop
[code]....
now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;
when i try t compile i got:
Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope
Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....
View 0 Replies View RelatedI am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
[Code].....
Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.
View 3 Replies View RelatedAt my Workplace we have a large Orcle 11g Database with 30 different tables for production control issues.I try to get a couple of different information from the database, so i started with SQL Query's, but for this problem i was not able to write an working query.
In this case i have 2 tables:
Table 1:
ID ;ORDER_NR ;DESCRIPTION ;CREATE_DATE
1 ;A500236 ;CLEAN HOUSE ;02/20/2012
2 ;A623555 ;REPAIR CAR ;01/10/2012
3 ;A866944 ;MAINTAIN EQUIPMENT ;02/11/2012
Table 2:
ID;ORDER_NR;WO_STEP;STEP_DATE;EMPLOYEE
1;A500236 ;A;02/21/2012;W0010
2;A500239 ;F;02/21/2012;W0010
3;A500239 ;S;02/22/2012;W0027
[code]....
And the result of my Query should look like this:
ORDER_NR;DESCRIPTION ;CREATE_DATE;A_STAT_AGE;R_STAT_AGE;U_STAT_AGE
A500236;CLEAN HOUSE ;02/20/2012 ;5 ;3 ;1
A623555;REPAIR CAR ;01/10/2012 ;42 ;39 ;38
A866944;MAINTAIN EQUIPMENT ;02/11/2012 ;15 ;4 ;3
The age of my query result should be calculated from the Create date of the Order.I want to know 2 things, one is how old was the Order when they reached that status A, R and U.The second this ist, how long did the order remain on the stat A,R and U (and if possible all other status also)It could happend that not each order reaches each status, so it ca go directly from A to you in this case i want display a wildcard in this row/column
For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper."
The data table is shown on the picture.i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg.. I can learn and do some similar task myself
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
View 8 Replies View RelatedI want TO find out FIRST two employee joined IN A particular department WITH department information.THE relation IS basically FROM THE scott SCHEMA.I tried LIKE AS follows. IS there ANY other way FOR best PERFORMANCE.
SELECT deptno,dname,loc,
Max(Decode(rn, 1, hiredate))hiredate1,
Max(Decode(rn, 1, ename)) employee1,
Max(Decode(rn, 2, hiredate))hiredate2,
Max(Decode(rn, 2, ename)) employee2
FROM (SELECT d.deptno,dname,loc,hiredate,ename,Row_number() over(PARTITION BY e.deptno ORDER BY hiredate) rn
FROM dept d, emp e
WHERE d.deptno = e.deptno(+))
GROUP BY deptno,dname,loc;
Im trying to generate a member procedure that allows the user to manual enter the required information via substitution variables. The manual INSERT INTO statements work but I cant seem to get it to work within a procedure.
Here is the code for the type, table andstandard insert:
CREATE TYPE toy_typ AS OBJECT
(toy_id NUMBER ( 5),
toy_name VARCHAR2 (20),
toy_cost NUMBER ( 4),
[code]...
the procedure complies but i get a warning error. When i also try to execute the code with either set values or substitutions i get either not enough or to many values error.
I want to extract all OEM Job schedule information so that we can recreate the jobs on a different OEM should the need arise; e.g. Our network A OEM grid control system fails completely.We decide to connect all databases on network A to an OEM grid control system on Network B. Once all agents are switched over to network B we need to recreate and schedule all the OEM jobs that were running from the OEM on A to the new OEM.
Any sql that will get the job info from sysman.mgmt_jobs (and related tables) which I can run on a regular basis OR how to merge the mgmt_job.. tables from one oem into a second oem without corrupting the data already in the second oem ?
I need to alter a table to check that the data in a column is contained in a similar column in another table.
I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.
I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.
Both have a NUMBER(5) data type.
Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?
I am having a little trouble retrieving info from my mock database.
here are my definitions:
response.setHeader("pragma", "no-cache");
response.setHeader("Cache-control", "no-cache, no-store, must-revalidate");
response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT");
response.addHeader("Cache-Control", "post-check=0, pre-check=0");
[code]...
and here is my select statement.
ds = (DataSource) context.lookup(dSource);
conn = ds.getConnection();
stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date");
stmt.setString(1, colleagueID);
result = stmt.executeQuery();
***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>
If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.
I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:
" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.
I need to customize an oracle report and I am not able to find the header section when I click on the header section in the paper layout. The header info is not on the main section either. But when I run the oracle conc prog the header seciton gets printed. I verified the rdf file if it was correct- it is.
View 4 Replies View RelatedI am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.
I am giving you all the statement I issued...
SQL> create user saimon identified by abc1;
User created.
SQL> grant connect, resource to saimon;
Grant succeeded.
SQL> audit table, insert table by saimon by session;
Audit succeeded.
SQL> show parameter audit
NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>
[oracle@DBTEST ~]$ sqlplus saimon/abc1
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create table TB1 (id number, name varchar2(20));
Table created.
SQL> create table TB3 (id number, name varchar2(20));
Table created.
SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;
USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba
Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?
SQL> show user
USER is "SYS"
SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON
I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.
I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3
so far this is what I have
UPDATE calllog
SET timedate = current_timestamp, severityid = severityid - 1
WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or
(severityid = 3 and timedate >= current_timestamp + (2/24)) or
(severityid = 4 and timedate >= current_timestamp + (3/24)))
I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.
Our product needs to use SecureMag Encrypted MagStripe Reader to get the credit card info in POS.After adding the code in every module, most of them work well now. But one of them does not. We met the error FRM-41344.This module is called from another(using call_form) that could also have this functionality(it works well in this module).
the code in WHEN-NEW-FORM-INSTANCE trigger:
go_item('CONTROL.CODE');
:global.msrDevice := :ITEM('CONTROL.opos').interface;
The error raise in this code. The global variable is assigned a value in each module.I got some info from the internet. But I still get error after I did the following operation.When using Oracle Forms, you might receive this run-time error:
FRM-41344: OLE object not defined for object in current record.
which can occur for either of these reasons: The OLE container has lost the definition of the Oracle Video Custom Control. To fix this problem, go into the Forms Designer, and re-insert the Oracle Video Custom Control by clicking the right mouse button inside the OLE container and choosing Insert Object
The Oracle Video Custom Control has not been initialized. To fix this problem, modify the form so that it can navigate to the block that contains the Oracle Video Custom Control. You can either make this block the first block on the form or add a GO_BLOCK command in the WHEN-NEW-FORM-INSTANCE script to navigate to that block. If necessary, you can add a GO_BLOCK command followed by SYNCHRONIZE before any commands that access the Oracle Video Control. (You can tell if the Oracle Video Control has been initialized because the video control buttons will be visible.)
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?
parse and execute the sql string first with dbms_sql and then convert it to ref cursor?
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
