how can i display the out in matrx format.
example;
select country_code
, item_number
, sum(quantity)
FROM item_table it
[Code]...
output format:
--------- country_code1 country_code2 country_code3 country_code4
item1 10 77 99 87
item2 30 67 56 76
item3 35 45 66 66
item4 50 33 56 67
FOR my case PIVOT_ won't work. I am using 10g database.
How can I create PLSQL table type dynamically?
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
i have a hidden region at the page top i want to show only after clicking my button which is running DA plsql code. This region is like a prompt message container/div at the top of the page so after few seconds like 10 secs i want to hide it again. I tried to use JQUERY but never hide it back, also i tried another TRUE CONDITION within my dynamic action but it doesnt reset it back the region is always shown...
View 2 Replies View RelatedThis is a daily batch job which captures end of the day changes from a set of Oracle 10G "Source" tables in (.exp) file and another overnight batch job will read from (.exp) files and Insert into Oracle 10G "Target" Tables. The .exp files are created by Oracle 10G Data Pump utilities.In future we will be migrating Target to Teradata. All the tables will be migrated. No change in table names or structure.
The plan of action to write the later bit of PLSQL (which read from .exp files and loads it into Target tables) into Java so that the programe can be re-used on teradata.
Questions
1) Can Java read the contents with in .exp files and insert into Oracle tables?
2) Can Teradata read the contents with in .exp files and insert into Teradata tables?
3) Will we be achieving any benefit if we execute DDL (Insert) using Java rather than PLSQL. Obvious one being Platform independent.
there is no transformation of data in flight between .exp to Target tables.
I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example:
in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
I have upgraded my Operating Syatem from Vista to Win7. I have SQL nav version but is not supported on Win7. So I have installed PLSQL developer. When I try to connect its giving the error connection not open. But when I tried the same credentials with SQLPLUS it's working fine.
View 3 Replies View Relatedwe have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
If in a PlSQL procedures there are many DML statements which are part of the same transaction. and there are many calls to other PLSQL blocks also.
what is the best practice to use commit either after each DML statement or to use it in the End of Block?
My application reads a binary file in C++ using fstream.read and needs to save that binary data into the database as a blob.
I am not using OCI.
My struggle is trying to determine the proper way to pass this unstructured data to a PLSQL procedure.
1 > What data type should the input paramter be for the PLSQL procedure?
2 > My binary data is in a char variable. Do I need to convert that to something else before passing it as a parameter.
In any application, we will have many commit statements issued in many places. How can we find out as to where all commits have happened during runtime.
Basically in our application, when am trying an operation, am getting the error ORA-01086: savepoint <save point name> never established.
My guess is that there is a commit somewhere because of which system is not able to rollback to that save point.
i want to write a query in the below format
score days days days
0-20 20-40 40-60
0-200 * * *
200-400 * * *
400-above * * *
* --> no: of matches
test_1 and test_2 are two tables , and in test_1, "score" and "matchno:" are columns and in test_2, "days" is column. in score values are 0 to 1000 and in days values are 0-100
I am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.
how I can go about doing this using a PLSQL stored procedure.
I want to debug the procedure using PLSQL Developer tool?How to use the debug option in that tool?.
View 1 Replies View RelatedAt PLSQL level how can we make our program re-startable such that if there is some abort after the commited update program will restart from the last commit checkpoint.
View 2 Replies View RelatedI am create procedure send the message in form bulider10g to Internet email and the procedure is work .
but now I want to modify this procedure , I want to add picture of my country ( logo ) and the original message . why cant display the image in my email ?
this code of image
<p align="center"><img border="0" src="logo.gif" width="20" height="20"></p>
I Think the problem of this code because 'Content-type: text/html' not image ?????!!!!!!!
utl_smtp.data(v_Mail_Conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);
procedure
EMAIL_SEN VARCHAR2(250);
EMAIL_REC VARCHAR2(250);
DESC_EN VARCHAR2(250);
UB_SEQ NUMBER(10);
BEGIN
DECLARE
v_From VARCHAR2(80) :=admin@yahoo.com ;
[code].........
my db version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
entry in my listener.ora file
===========================
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
[code]......
entry in tnsnames.ora file:
===============================
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc0))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.example.com)
)
)
listener is running perfectly. created c program, compile the program by
gcc -c <filename>
ld -shared -o <filename.so> <filename.o>
cp <filename.so> $ORACLE_HOME/bin
create library & function from db. when I am invoking this function it is throwing error message:
ORA-28546: connection initialization failed, probable Net8 admin error
I have one PLSQL package that does a join of two tables of remote database instance via the datalink. I just wonder where the most calculation(the join) is done, local machine or remote machine? Is there any best practice to have a better performance for such configuration.
View 3 Replies View RelatedI want a sql query to transform my output..Also number of rows are not limited
Current Output
Col_A
1
2
3
4
5
6
7
8
9
10
however i need data as
COL_A COL_BCOL_C COL_D
1 2 3 4
5 6 7 8
Send all type of matrix report with examples.
View 1 Replies View RelatedI've got a requirement for building a 5000 X 5000 matrix using PL/SQL. My original data tables have 5000 rows each and I need to do a correlation analysis using this data and need to store in a physical table and not in-memory. Is this feat achievable using mere PL/SQL? I understand that Oracle DB has a limitation of 1000 columns(but not sure) and hence I'd like to know whether there is any work-around for such scenarios. If not, what are the other alternative method(s) to achieve this feat? Do I need to use any 3rd party tools to get this done?
View 3 Replies View RelatedWriting a query based on this data:
Each row is based on a row of a student.
Grade stands for the school grade, K=> Kindergarten
Tabl is a table the student is assigned to.
Bev stands for beverage, 1c=> White Milk , 2c=> Chocolate Milk, 3c => Orange Juice.
GRADE TABL BEV
K 1 1c
K 2 3c
K 3 1c
K 4 3c
K 1 1c
K 2 2c
K 3 3c
I need to generate a summary report that looks like this:
Grade/Tabl White Chocolate Orange
K-1 2
K-2 1 1
K-3 1 1
K4 1
Is there a way in which I can write a query to display this type of matrix report.
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
I have imported a java class file that inturn gets content from a webservice. One of the functions on that imported class file is as follows ...
Funtion getcontent(A1 JOBJECT,A2 JOBJECT, A3 String) return JOBJECT ....
In my PLSQL package ...How do I invoke this class file ?
So far I have done this ...
Declare
abc ORA_JAVA.JOBJECT;
xyz ORA_JAVA.JOBJECT;
xxx VARCHAR2(25);
value ORA_JAVA.JOBJECT;
BEGIN
[code]...
I am getting an error saying the object type is wrong .... I would like to know how to assign a hardcoded value to the JOBJECT just to test before I continue.
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
execute a BATCH File through PLSQL.
BEGIN
dbms_scheduler.Create_schedule(schedule_name => 'RMAN_TICKER_STARTING',
repeat_interval => 'FREQ=DAILY;BYHOUR=9; BYMINUTE=15,30,45,59',
comments
=> 'schedule to run daily');
dbms_scheduler.Create_program (program_name => 'TICKER_PROGRAME',
[code]....
It was created successfully, but when I execute, it shows the error message.
BEGIN
dbms_scheduler.Run_job ('RMAN_TICKER_JOB', TRUE);
END;
begin
dbms_scheduler.run_job (
'RMAN_TICKER_JOB',TRUE);
end;
Error at line 1
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
[code]....
But I have the CMD file in the location - "F:FEEDLGRTOOLSfeedlgr.cmd".
From last couple of days, the paste option is not working for me in the PLSQL developer. I selected the text in a SQL window, selected copy.
At the desired location when I right click paste option is not visible in a menu. If it is visible then also it did not paste. I tried Ctrl-V also.
I have the following data.
COURSE_SEQ_NO INSTRUCTOR_ID SE SEMESTER_CODE SEMESTER_YEAR QNO ANS ANS_COUNT
------------- ------------- -- ------------- ------------- ---------- --- ----------
74 10225 A 3 2011 1 E 3
74 10225 A 3 2011 1 VG 1
74 10225 A 3 2011 2 E 3
74 10225 A 3 2011 2 VG 1
[code]....
How can I convert it into matrix report like below
QNO E VG
1 3 1
2 3 1
3 3 1
. . .
. . .
. . .
17 3 1
Is there any possibilities to make matrix form just like matrix report.
one is the month column which increase horizontally other is emp_name column which increase vertically and sal column which show the salary of each employee in each month.
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
I have problems with calling procedure directly from webbrowser. I have a procedure test in test_pkg package. I granted execute on it to anonymous , added it to wwv_flow_epg_include_mod_local function and still have error 404.searched this forum and found this: Does listener use wwv_flow_epg_include_mod_local
So I granted to apex_public_user and created synonym for him to my package. And it starts working. But then I wanted to add next proceudre to my package the same way, and change my test procedure and it stop working again. Now I have 404 error again.