I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT ( NUMBER0 NUMBER(2) NOT NULL, SHORT_NAME CHAR(35 BYTE) NOT NULL, FULL_NAME CHAR(50 BYTE) )
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT (S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2) RETURN NUMBER AS I_RETURN NUMBER; S_SQL VARCHAR2(300); --S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
DECLARE cnt number(10); BEGIN SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227'))); DBMS_OUTPUT.PUT_LINE (cnt); END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?
I am using the attached program but its giving me compiler error as follows..
Error at line 31, column 1 in file sample.pc proc_modify_tdefs (:in_tdefs,:out_tdefs); 1 PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS' Error at line 31, column 1 in file sample.pc proc_modify_tdefs (:in_tdefs,:out_tdefs);
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number) return T_web_loylty_report_table is v_tbl_nm varchar2(50); i_cntry_cd varchar2(20); v_sql_str varchar2(32567); [code]......
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 comp NEW YORK 20 Compt DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 Comp Ahaaa 80 data ab 80 data ab 80 data ab 80 data ab
9 rows selected.
s@ORCL> ed Wrote file afiedt.buf
1 declare 2 type tp_dept is table of number; 3 arr_dept tp_dept; 4 begin 5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]' 6 returning into arr_dept; 7 dbms_output.put_line('The count is '||arr_dept.count); 8* end; s@ORCL> / returning into arr_dept; * ERROR at line 6: ORA-06550: line 6, column 19: PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type ORA-06550: line 5, column 4: PL/SQL: Statement ignored
I am creating a db function to select * from table_name to be used by the application for retrieving lookup data. I found many examples for how to return cursor as a result of executing a dynamic sql but don't know the optimum way.
following is my current trial:
CREATE OR REPLACE PACKAGE types AS TYPE Cursortype IS REF CURSOR; END; /
CREATE OR REPLACE FUNCTION F_GEN_SELECT_CURS (S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2) /*This function maps S_ACC_MAJOR_ACCOUNT_LIST CoolGen service [code].......
I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:
CREATE TABLE "TFWDB_ENTIDADE" ( "CD_ENTIDADE" NUMBER(10,0) NOT NULL, "DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL, "NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL, "CD_TIPO" NUMBER(5,0) NOT NULL, "ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL, CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"), CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1')) );
Below is the procedure I build to SELECT it:
create or replace procedure spfwdb_sl_entidade_1( p_cd_entidade in number, p_dc_entidade in varchar2, p_nm_entidade in varchar2, p_cd_tipo in number, p_id_status in char, [code]........
I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.
The function is working but I wanted to take an expert opinion if it can be optimized.
CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL (I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)
[Code]....
In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.
CURSOR C_AGREEMENT IS SELECT AGREEMENT_SERIAL FROM VW_AGR_AGREEMENT WHERE NUMBER0 = I_NUMBER0 AND TYPE_ID = I_TYPE_ID -->and sub_number is null; -->and sumb_number = s_sub_number
I've seen several code samples that show how to return a REFCURSOR from a Stored Function, but when I try it with C# it gives me allways WRONG NUMBER OF ARGUMENTS or somehing like that.
I presently don't have my code in here, but it is something like this:
I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.
----Create Test Table CREATE TABLE VC_WORKINGDAYS ( WK_ID number NUMBER(10,0), WK_DATE DATE, );
-- Insert test Data INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');
SQL ----- Result Should be WHY SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today
Attached File(s)
create_Function.zip ( 6.39K ) Number of downloads: 1
I have a problem when trying to create a PLSQL function based on an XML extraction query.
I have three dummy tables:
SQL> get create_address 1 create table ADDRESS 2 ( 3 id NUMBER not null, 4 house_number NUMBER, 5 house_name VARCHAR2(20), 6 street_name VARCHAR2(30),
[code]....
And the following dummy data for these:
1 insert all 2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET) 3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF') 4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET) 5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH') 6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
[code]....
So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.
So I try, very simply, this:
SQL> get get_xml_data 1 create or replace function get_xml_data(p_id in number) return clob is 2 Result clob; 3 begin 4 select xmlroot(xmlelement("HomeData",
[code]....
And, alas, i'm greeted by this:
SQL> / Warning: Function created with compilation errors. SQL> sho err Errors for FUNCTION GET_XML_DATA: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/5 PL/SQL: SQL Statement ignored 24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got - SQL>
I've tried to redo the query in several different ways but so far nothing.
I am using the below function to return a blob (CSV) content, this is working fine..But i need to modify this function to display headline for the report and fixed width column in CSV ( then user no need to expand the each and every column ).
create or replace function GET_BLOB ( p_query varchar2 ) return blob as
Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)
When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.
POSTCODE_TO_LAT_LNG_GM_API(postcode IN VARCHAR2, lat OUT NUMBER, p_long OUT NUMBER)
to convert a postcode into lat/long values. I then need to add them to the returned SQL statement so I used the string concat operator || with to_char but it comes up with this error when I try to apply the changes: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic column'' checkbox below the region source to proceed without parsing.
ORA-00936: missing expressionh4.
h4. btw I'm using Oracle 11g release 11.2.0.3.0 and Apex version 4.1.1.00.23
--this for txn details CREATE TABLE txn_det( txnid NUMBER PRIMARY KEY, amount NUMBER, status varchar2(50), cust_id NUMBER); ----this for customer details CREATE TABLE cust_det( cust_id NUMBER PRIMARY KEY, cust_name VARCHAR2(50), cust_acc number(15));
--data to insert for customer table INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678'); ----data to insert for txn table INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"
any other way to get it? I need like table name field name datatype
create table my_rows ( my_envvarchar2(100), anumber(2), bnumber(2) ) / insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); [code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.
create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,
req_id number); type t_disc_req is table of r_disc_req; function F_GetDiscReq return t_disc_req pipelined; procedure P_ProcessDiscReq;end; CREATE OR REPLACE PACKAGE BODY test13_pkgAS FUNCTION F_GetDiscReq RETURN t_disc_req PIPELINED IS lo_disc_req r_disc_req; BEGIN FOR r_row IN (SELECT disc_line_id, req_id FROM edms_disc_lines_stg WHERE ROWNUM < 10) LOOP lo_disc_req.disc_line_id := r_row.disc_line_id; lo_disc_req.req_id := r_row.req_id; PIPE ROW (lo_disc_req);
I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:
COLUMN_A COLUMN_B COLUMN_C
The second file may have:
COLUMN_A COLUMN_B COLUMN_D
At the destination, I have a table that has:
COLUMN_A COLUMN_B COLUMN_C COLUMN_D
Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?
a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
create trigger on certain column for table structure.
SQL> desc MXMS_BF_TXN_DTL_T Name Null? Type ----------------------------------------- -------- ---------------------------- DOC_NO NOT NULL VARCHAR2(200) SEQ_NO NOT NULL NUMBER(24) GL_CODE VARCHAR2(200) TXN_NATURE VARCHAR2(200) TXN_TYPE_CODE VARCHAR2(200) [code].....
I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .
Name Null? Type ----------------------------------------- -------- ---------------------------- TIMESTAMP DATE WHO VARCHAR2(30) CNAME VARCHAR2(30) OLD VARCHAR2(2000) NEW VARCHAR2(2000)
Description:- TIMESTAMP is for when the modification happen. WHO is for username CNAME is for column which is modified OLD is for old value for the modified column New os for new value for the modified column